Mastering Financial Modelling in Microsoft Excel

<p>Contents</p> <p>Introduction – who needs this book?</p> <p>Preface to the third edition&nbsp; </p> <p>Acknowledgements </p> <p>Conventions</p> <p>Executive summary</p> <p>Part A&nbsp; DEVELOPING FINANCIAL MODELS </p> <p>&nbsp;</p> <p>1 &nbsp;&nbsp; Overview &nbsp;&nbsp; </p> <p>Introduction &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; </p> <p>What is financial modelling?</p> <p>History of spreadsheets&nbsp;&nbsp; </p> <p>Power of spreadsheets&nbsp;&nbsp; </p> <p>Objectives for the book&nbsp;&nbsp; </p> <p>Example spreadsheet</p> <p>Summary&nbsp;&nbsp; </p> <p>&nbsp;</p> <p>2 &nbsp;&nbsp; Design introduction &nbsp;</p> <p>Introduction</p> <p>Basics of design</p> <p>Objectives</p> <p>User interface&nbsp;&nbsp; </p> <p>Key variables and rules&nbsp;&nbsp; </p> <p>Layout</p> <p>Individual modules&nbsp;&nbsp; </p> <p>Menu structure and macros</p> <p>Management reporting&nbsp;&nbsp; </p> <p>Future development</p> <p>Testing</p> <p>Protection&nbsp;&nbsp; </p> <p>Documentation&nbsp;&nbsp; </p> <p>Peer group comments</p> <p>Summary&nbsp;&nbsp; </p> <p>&nbsp;</p> <p>3 &nbsp;&nbsp; Features and techniques</p> <p>Introduction</p> <p>Formats</p> <p>Number format</p> <p>Lines and borders&nbsp;&nbsp; </p> <p>Colours and patterns&nbsp;&nbsp; </p> <p>Specific colours for inputs and results</p> <p>Data validation&nbsp;&nbsp; </p> <p>Controls – combo boxes and buttons</p> <p>Conditional formatting&nbsp;&nbsp; </p> <p>Use of functions and types of function</p> <p>Add-ins for more functions&nbsp;&nbsp; </p> <p>Text and updated labels</p> <p>Record a version number, author, etc.&nbsp;&nbsp; </p> <p>Use names to make formulas easier to understand</p> <p>Paste names as part of documentation&nbsp;&nbsp; </p> <p>Comment cells&nbsp;&nbsp; </p> <p>Graphics&nbsp;&nbsp; </p> <p>Dynamic graphs to plot individual series</p> <p>Data tables</p> <p>Scenarios&nbsp;&nbsp;</p> <p>Goal seek&nbsp;&nbsp;</p> <p>Solver&nbsp; </p> <p>Use of templates&nbsp;&nbsp; </p> <p>Summary&nbsp;&nbsp; </p> <p>&nbsp;</p> <p>4 &nbsp;&nbsp; Sample model</p> <p>Introduction</p> <p>Aims and objectives&nbsp;&nbsp; </p> <p>User needs and user interface&nbsp;&nbsp; </p> <p>Key variables and rules&nbsp;&nbsp; </p> <p>Breaking down the calculations into manageable groups</p> <p>Setting up individual modules&nbsp;&nbsp; </p> <p>Menu structure&nbsp;&nbsp; </p> <p>Program sheets and macros</p> <p>User assistance&nbsp;&nbsp; </p> <p>Summaries</p> <p>Risk and multiple answers&nbsp;&nbsp; </p> <p>Testing and troubleshooting&nbsp;</p> <p>Protecting and securing&nbsp;&nbsp; </p> <p>Help and documentation&nbsp;&nbsp; </p> <p>Show to peers – take their advice</p> <p>Control loop – listen, learn and modify</p> <p>Summary&nbsp;&nbsp; </p> <p>&nbsp;</p> <p>5 &nbsp;&nbsp; Example model</p> <p>Introduction</p> <p>Case study&nbsp;&nbsp; </p> <p>Design</p> <p>PPP 1</p> <p>PPP 2</p> <p>PPP 3 </p> <p>PPP 4</p> <p>PPP 5</p> <p>Documenting, testing and protecting&nbsp;&nbsp; </p> <p>PPP 6</p> <p>Summary&nbsp;&nbsp; </p> <p>&nbsp;</p> <p>Part B&nbsp; APPLICATIONS &nbsp;&nbsp; </p> <p>&nbsp;</p> <p>6 &nbsp;&nbsp; Analysing performance &nbsp;&nbsp; </p> <p>Introduction</p> <p>Profit and loss&nbsp;&nbsp; </p> <p>Balance sheet&nbsp;&nbsp; </p> <p>Ratios</p> <p>Trend analysis</p> <p>Sustainability</p> <p>Summary&nbsp;&nbsp; </p> <p>&nbsp;</p> <p>7 &nbsp; Cash flow </p> <p>&nbsp;</p> <p>Deriving cash flow&nbsp;&nbsp; </p> <p>Net operating cash flow (NOCF)&nbsp;&nbsp; </p> <p>Free cash flow&nbsp;&nbsp; </p> <p>Cover ratios&nbsp;&nbsp; </p> <p>International cash flow&nbsp;</p> <p>Summary&nbsp;&nbsp; </p> <p>&nbsp;</p> <p>8 &nbsp;&nbsp; Forecasting models&nbsp; </p> <p>Introduction</p> <p>Historic forecasts&nbsp;&nbsp; </p> <p>Trend lines&nbsp;&nbsp; </p> <p>Trend lines for analysis</p> <p>Data smoothing</p> <p>Cyclicality and seasonality</p> <p>Summary&nbsp;&nbsp; </p> <p>&nbsp;</p> <p>9 &nbsp;&nbsp; Forecasting financials</p> <p>Introduction</p> <p></p>