Bookstore

A hands-on guide to using Excel in the business context


First published in 2012, Using Excel for Business and Financial Modelling contains step-by-step instructions of how to solve common business problems using financial models, including downloadable Excel templates, a list of shortcuts and tons of practical tips and techniques you can apply straight away.


Whilst there are many hundreds of tools, features and functions in Excel, this book focuses on the topics most relevant to finance professionals. It covers these features in detail from a practical perspective, but also puts them in context by applying them to practical examples in the real world. Learn to create financial models to help make business decisions whilst applying modelling best practice methodology, tools and techniques.


* Provides the perfect mix of practice and theory


* Helps you become a DIY Excel modelling specialist


* Includes updates for Excel 2019/365 and Excel for Mac


* May be used as an accompaniment to the author's online and face-to-face training courses


Many people are often overwhelmed by the hundreds of tools in Excel, and this book gives clarity to the ones you need to know in order to perform your job more efficiently. This book also demystifies the technical, design, logic and financial skills you need for business and financial modelling.

Using Excel for Business and Financial Modelling - A Practical guide, 3rd edition

Preface xi





Chapter 1 What is Financial Modelling? 1





What's the Difference Between a Spreadsheet and a Financial Model? 3





Types and Purposes of Financial Models 5





Tool Selection 6





What Skills Do You Need to Be a Good Financial Modeller? 17





The "Ideal" Financial Modeller 23





Summary 27





Chapter 2 Building a Model 29





Model Design 29





The Golden Rules for Model Design 31





Design Issues 32





The Workbook Anatomy of a Model 33





Project Planning Your Model 36





Model Layout Flowcharting 37





Steps to Building a Model 39





Information Requests 47





Version-Control Documentation 49





Summary 50





Chapter 3 Best-Practice Principles of Modelling 51





Document Your Assumptions 51





Linking, Not Hardcoding 52





Enter Data Only Once 53





Avoid Bad Habits 53





Use Consistent Formulas 53





Format and Label Clearly 54





Methods and Tools of Assumptions Documentation 55





Linked Dynamic Text Assumptions Documentation 62





What Makes a Good Model? 65





Summary 67





Chapter 4 Financial Modelling Techniques 69





The Problem with Excel 69





Error Avoidance Strategies 71





How Long Should a Formula Be? 76





Linking to External Files 78





Building Error Checks 81





Circular References 85





Summary 90





Chapter 5 Using Excel in Financial Modelling 91





Formulas and Functions in Excel 91





Excel Versions 94





Handy Excel Shortcuts 100





Cell Referencing Best Practices 104





Named Ranges 107





Basic Excel Functions 110





Logical Functions 114





Nesting Logical Functions 117





Summary 125





Chapter 6 Functions for Financial Modelling 127





Aggregation Functions 127





LOOKUP Functions 139





Nesting Index and Match 150





OFFSET Function 153





Regression Analysis 158





Choose Function 164





Working with Dates 165





Financial Project Evaluation Functions 171





Loan Calculations 177





Summary 183





Chapter 7 Tools for Model Display 185





Basic Formatting 185





Custom Formatting 186





Conditional Formatting 191





Sparklines 195





Bulletproofing Your Model 199





Customising the Display Settings 203





Form Controls 210





Summary 226





Chapter 8 Tools for Financial Modelling 227





Hiding Sections of a Model 227





Grouping 233





Array Formulas 234





Goal Seeking 240





Structured Reference Tables 242





PivotTables 245





Macros 254





Summary 263





Chapter 9 Common Uses of Tools in Financial Modelling 265





Escalation Methods for Modelling 265





Understanding Nominal and Effective (Real) Rates 270





Calculating a Cumulative Sum (Running Totals) 274





How to Calculate a Payback Period 275





Weighted Average Cost of Capital (WACC) 278





Building a Tiering Table 282





Modelling Depreciation Methods 286





Break-Even Analysis 295





Summary 300





Chapter 10 Model Review 301





Rebuilding an Inherited Model 301





Improving Model Performance 312





Auditing a Financial Model 317





Summary 323





Appendix: QA Log 323





Chapter 11 Stress Testing, Scenarios, and Sensitivity Analysis in Financial Modelling 325





What are the Differences Between Scenario, Sensitivity, and What-If Analyses? 326





Overview of Scenario Analysis Tools and Methods 328





Advanced Conditional Formatting 337





Comparing Scenario Methods 340





Adding Probability to a Data Table 350





Summary 351





Chapter 12 Presenting Model Output 353





Preparing an Oral Presentation for Model Results 353





Preparing a Graphic or Written Presentation for Model Results 355





Chart Types 358





Working with Charts 367





Handy Charting Hints 374





Dynamic Named Ranges 376





Charting with Two Different Axes and Chart Types 382





Bubble Charts 384





Creating a Dynamic Chart 387





Waterfall Charts 391





Summary 395





About the Author 397





About the Website 399





Index 403