Details

<p>Preface xi</p> <p><b>CHAPTER 1 What Is Financial Modelling? 1</b></p> <p>What’s the Difference between a Spreadsheet and a Financial Model? 3</p> <p>Types and Purposes of Financial Models 4</p> <p>Tool Selection 5</p> <p>What Skills Do You Need to Be a Good Financial Modeller? 10</p> <p>The Ideal Financial Modeller 16</p> <p>Summary 19</p> <p><b>CHAPTER 2 Building a Model 21</b></p> <p>Model Design 21</p> <p>The Golden Rules for Model Design 22</p> <p>Design Issues 24</p> <p>The Workbook Anatomy of a Model 25</p> <p>Project Planning Your Model 27</p> <p>Model Layout Flow Charting 28</p> <p>Steps to Building a Model 28</p> <p>Information Requests 35</p> <p>Version-Control Documentation 36</p> <p>Summary 37</p> <p><b>CHAPTER 3 Best Practice Principles of Modelling 39</b></p> <p>Document Your Assumptions 39</p> <p>Linking, Not Hard Coding 39</p> <p>Only Enter Data Once 40</p> <p>Avoid Bad Habits 41</p> <p>Use Consistent Formulas 41</p> <p>Format and Label Clearly 41</p> <p>Methods and Tools of Assumptions Documentation 42</p> <p>Linked Dynamic Text Assumptions Documentation 48</p> <p>What Makes a Good Model? 51</p> <p>Summary 52</p> <p><b>CHAPTER 4 Financial Modelling Techniques 53</b></p> <p>The Problem with Excel 53</p> <p>Error Avoidance Strategies 54</p> <p>How Long Should a Formula Be? 59</p> <p>Linking to External Files 61</p> <p>Building Error Checks 63</p> <p>Avoid Error Displays in Formulas 66</p> <p>Circular References 67</p> <p>Summary 70</p> <p><b>CHAPTER 5 Using Excel in Financial Modelling 71</b></p> <p>Formulas and Functions in Excel 71</p> <p>Excel Versions 73</p> <p>Handy Excel Shortcuts 74</p> <p>Basic Excel Functions 76</p> <p>Logical Functions 82</p> <p>Nesting: Combining Simple Functions to Create Complex Formulas 84</p> <p>Cell Referencing Best Practices 86</p> <p>Named Ranges 89</p> <p>Summary 92</p> <p><b>CHAPTER 6 Functions for Financial Modelling 93</b></p> <p>Aggregation Functions 93</p> <p>LOOKUP Formulas 100</p> <p>Other Useful Functions 106</p> <p>Working with Dates 115</p> <p>Financial Project Evaluation Functions 121</p> <p>Loan Calculations 126</p> <p>Summary 131</p> <p><b>CHAPTER 7 Tools for Model Display 133</b></p> <p>Basic Formatting 133</p> <p>Custom Formatting 133</p> <p>Conditional Formatting 139</p> <p>Sparklines 143</p> <p>Bulletproofing Your Model 147</p> <p>Customising the Display Settings 151</p> <p>Form Controls 157</p> <p>Summary 171</p> <p><b>CHAPTER 8 Tools for Financial Modelling 173</b></p> <p>Hiding Sections of a Model 173</p> <p>Grouping 178</p> <p>Array Formulas 179</p> <p>Goal Seeking 184</p> <p>Pivot Tables 186</p> <p>Macros 190</p> <p>User-Defined Functions (UDFs) 198</p> <p>Summary 200</p> <p><b>CHAPTER 9 Common Uses of Tools in Financial Modelling 201</b></p> <p>Escalation Methods for Modelling 201</p> <p>Understanding Nominal and Effective (Real) Rates 206</p> <p>Calculating Cumulative Totals 209</p> <p>How to Calculate a Payback Period 210</p> <p>Weighted Average Cost of Capital (WACC) 214</p> <p>Building a Tiering Table 216</p> <p>Modelling Depreciation Methods 219</p> <p>Break-Even Analysis 227</p> <p>Summary 233</p> <p><b>CHAPTER 10 Model Review 235</b></p> <p>Rebuilding an Inherited Model 235</p> <p>Auditing a Financial Model 245</p> <p>Appendix 10.1: QA Log 250</p> <p>Summary 250</p> <p><b>CHAPTER 11 Stress-Testing, Scenarios, and Sensitivity Analysis in Financial Modelling 251</b></p> <p>What’s the Difference between Scenario, Sensitivity, and What-If Analysis? 252</p> <p>Overview of Scenario Analysis Tools and Methods 253</p> <p>Advanced Conditional Formatting 261</p> <p>Comparing Scenario Methods 264</p> <p>Summary 273</p> <p><b>CHAPTER 12 Presenting Model Output 275</b></p> <p>Preparing an Oral Presentation for Model Results 275</p> <p>Preparing a Graphic or Written Presentation for Model Results 276</p> <p>Chart Types 279</p> <p>Working with Charts 291</p> <p>Handy Charting Hints 295</p> <p>Dynamic Range Names 297</p> <p>Charting with Two Different Axes and Chart Types 300</p> <p>Bubble Charts 302</p> <p>Waterfall Charts 307</p> <p>Summary 316</p> <p>About the Author 317</p> <p>About the Website 319</p> <p>Index 321</p>

<p><b>DANIELLE STEIN FAIRHURST</b> is the Principal of Plum Solutions, a Sydney-based consultancy specialising in financial modelling and analysis. With many years' experience as a financial analyst, she helps her clients create meaningful financial models. She has hands-on experience in a number of industry sectors, including telecommunications, information systems, manufacturing, and financial services. Stein Fairhurst is regularly engaged as a speaker, course facilitator, financial modelling consultant, and analyst around the world, and has taught management accounting subjects at Sydney University.</p>

<p>Praise for <i>Using Excel for Business Analysis</i></p> <p>"Danielle brilliantly manages the balance between business relevance and the necessary technical explanations expected from a book such as this. Her ability to walk in the shoes of the reader and deliver concise practical wisdom is unsurpassed. I highly recommend this book to anyone working with numbers, be it a Chief Financial Officer talking to the board or a Business Analyst starting their career. With Danielle's advice you can transform your career from successful to exceptional."<br /> <b>—Garth Holloway</b>, Managing Director, SixFootFour Consulting</p> <p>"Danielle's expertise and experience shines through in a very readable book, introducing the key concepts and considerations inherent in financial modelling. From novice to advanced modeller alike, readers are presented with many useful tips, tools, techniques, and pragmatic examples to refer to time and time again. Readily digestible, this book plugs a gaping academic hole that illustrates the risks and rewards of taking a spreadsheet by the throat and converting it into a robust, transparent, and flexible decision-making device. It will help you to 'Excel '."<br /> <b>—Liam Bastick</b>, Managing Director, SumProduct, and Excel MVP</p> <p>"Excel is a powerful and underestimated tool in many business environments. Whilst there are a number of books on the market that help explain the countless functions and features of the product, there are few if any that clearly articulate and teach the analyst how to unlock the power of Excel as an analysis and modelling tool for critical business decisions. Filled with easy to follow examples and based on many years of practical experience and education, Danielle Stein Fairhurst's book <i>Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals</i> has finally bridged that gap with a step-by-step approach to building models that address important real-world business questions."<br /> <b>—Michael Morgan</b>, Country Manager (Australia), Intergen Business Solutions</p>

SG