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.
By:
Danielle Stein Fairhurst
Imprint: John Wiley & Sons Inc
Country of Publication: United States
Edition: 3rd edition
Dimensions:
Height: 226mm,
Width: 152mm,
Spine: 23mm
Weight: 612g
ISBN: 9781119520382
ISBN 10: 111952038X
Series: Wiley Finance
Pages: 432
Publication Date: 05 April 2019
Audience:
Professional and scholarly
,
Undergraduate
Format: Paperback
Publisher's Status: Active
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
DANIELLE STEIN FAIRHURST is the Principal of Plum Solutions, a Sydney-based consultancy specialising in financial modelling and analysis. Her extensive experience as a financial analyst enables her to help her clients create meaningful financial models. She is regularly engaged as a speaker, course facilitator, financial modelling consultant, and analyst.
