Table of Contents

Contents Introduction who needs this book? Preface to the second edition Acknowledgements Conventions Executive summaryPart A DEVELOPING FINANCIAL MODELS 1 Overview Introduction What is financial modelling?History of spreadsheets Power of spreadsheets Objectives for the book Example spreadsheetSummary 2 Design introduction IntroductionBasics of designObjectivesUser interface Key variables and rules LayoutIndividual modules Menu structure and macrosManagement reporting Future developmentTestingProtection Documentation Peer group commentsSummary 3 Features and techniquesIntroductionFormatsNumber formatLines and borders Colours and patterns Specific colours for inputs and resultsData validation Controls combo boxes and buttonsConditional formatting Use of functions and types of functionAdd-ins for more functions Text and updated labelsRecord a version number, author, etc. Use names to make formulas easier to understandPaste names as part of documentation Comment cells Graphics Dynamic graphs to plot individual seriesData tablesScenarios Goal seek Solver Use of templates Summary 4 Sample modelIntroductionAims and objectives User needs and user interface Key variables and rules Breaking down the calculations into manageable groupsSetting up individual modules Menu structure Program sheets and macrosUser assistance SummariesRisk and multiple answers Testing and troubleshooting Protecting and securing Help and documentation Show to peers take their adviceControl loop listen, learn and modifySummary 5 Example modelIntroductionCase study DesignPPP 1PPP 2PPP 3 PPP 4PPP 5Documenting, testing and protecting PPP 6Summary Part B APPLICATIONS 6 Analysing performance IntroductionProfit and loss Balance sheet RatiosTrend analysisSustainabilitySummary 7 Cash flow Deriving cash flow Net operating cash flow (NOCF) Free cash flow Cover ratios International cash flow Summary 8 Forecasting models IntroductionHistoric forecasts Trend lines Trend lines for analysisData smoothingCyclicality and seasonalitySummary 9 Forecasting financialsIntroductionKey driversDeriving financial statementsAlternative approachesFinancial analysisSummary 10 Variance analysis IntroductionCash flow budgetsMonthly cash model'Flash' report and graphics Summary 11 Breakeven analysisIntroductionBreakevenOperating leverage Financial leverage Combined leverage Summary 12 Portfolio analysis IntroductionFormulas Optimum portfolio 13 Cost of capital IntroductionCapital Asset Pricing Model Dividend growth modelCost of preference sharesCost of debt Weighted average cost of capitalMarginal WACCSummary 14 Bonds IntroductionCash flows Yield measuresDurationPortfolio resultsSummary 15 Investment analysisIntroductionInvestment model revisitedPayback periodAccounting returnNet present value Internal rate of returnBenefit/cost ratio Management tests cash flow, etc. Scenarios Sensitivity analysis and chartsCapital rationingSummary 16 Risk AnalysisIntroductionRisk assessment process and analysisRisk adjusted rate &nbspiation Coefficient of variationCertainty equivalentsReal optionsSimulation Summary 17 DepreciationIntroductionStraight line Sum of digits Declining balanceUS MACRS AmortisationComparisonSummary 18 Leasing IntroductionRental calculationsLease versus purchase Classification AccountingSettlementsSummary 19 Company valuation IntroductionAccounts Adjusted accounting value Dividends Stock market or market methodFree cash flows Summary 20 Optimisation IntroductionElements of optimisation modelsLinear programmingMargin maximisationPensions Summary 21 Decision trees IntroductionBayes' theorem TerminologyDecision tree modelInformation exampleSummary 22 Risk management IntroductionForward rate agreementsSwapsForeign exchange FuturesOptionsBlack-ScholesSummary 23 Data functions IntroductionDatasetDatabase functions Descriptive statistics Histogram Function output Box plots Summary 24 Data analysisIntroductionImported data Sort Filter Subtotals Pivot table and chart Database importRadar chart Gauge chart Summary 25 Modelling checklist IntroductionDesign summary Features Techniques AppendicesAppendix 1: Software installationAppendix 2: Licence Appendix 3: File list Appendix 4: Microsoft Office 2007 (Office 12)Bibliography and referencesIndex

About the Author

Alastair Day has worked in the finance industry for 20 years in treasury and marketing functions. He is an associate lecturer in corporate finance with the OUBS, and other publications include books such as The Financial Director's Guide to Purchasing Leasing.