Comprehensive tools and methods to help you build, develop and apply financial models using Microsoft Excel, enabling you to get better, more accurate results, faster.
The new edition of this bestselling title begins by explaining basic modelling techniques before moving through to more complex models. The book is divided into two parts: the first part outlines model designs and gives templates, key features and techniques. The second part of the book shows how to build corporate financial models in Excel. The accompanying CD allows you to use and adapt templates and models.
This new edition includes a reworking of the book in Excel 2010 (but with older material still included), inclusion of Apple Mac, addition of specific 2010 features and end of chapter exercises.
If you are buying the ebook, companion files can be downloaded from the digital downloads section of http://www.financial-models.com/.
Alastair Dayhas worked in the finance industry for 20 years in treasury and marketing functions. In 1990 he established Systematic Finance as a consultancy and financial lessor concentrating on the computer and communications industries. Alastair has a degree in Economics and German from London University, an MBA from the Open University Business School, and is an associate lecturer in corporate finance with the OUBS. He is the successful author of several books including Mastering Cash Flow and Valuation Modelling, Mastering Risk Modelling and The Financial Director's Guide to Purchasing Leasing.
Contents Introduction - who needs this book? Preface to the third edition Acknowledgements Conventions Executive summary Part A DEVELOPING FINANCIAL MODELS 1 Overview Introduction What is financial modelling? History of spreadsheets Power of spreadsheets Objectives for the book Example spreadsheet Summary 2 Design introduction Introduction Basics of design Objectives User interface Key variables and rules Layout Individual modules Menu structure and macros Management reporting Future development Testing Protection Documentation Peer group comments Summary 3 Features and techniques Introduction Formats Number format Lines and borders Colours and patterns Specific colours for inputs and results Data validation Controls - combo boxes and buttons Conditional formatting Use of functions and types of function Add-ins for more functions Text and updated labels Record a version number, author, etc. Use names to make formulas easier to understand Paste names as part of documentation Comment cells Graphics Dynamic graphs to plot individual series Data tables Scenarios Goal seek Solver Use of templates Summary 4 Sample model Introduction Aims and objectives User needs and user interface Key variables and rules Breaking down the calculations into manageable groups Setting up individual modules Menu structure Program sheets and macros User assistance Summaries Risk and multiple answers Testing and troubleshooting Protecting and securing Help and documentation Show to peers - take their advice Control loop - listen, learn and modify Summary 5 Example model Introduction Case study Design PPP 1 PPP 2 PPP 3 PPP 4 PPP 5 Documenting, testing and protecting PPP 6 Summary Part B APPLICATIONS 6 Analysing performance Introduction Profit and loss Balance sheet Ratios Trend analysis Sustainability Summary 7 Cash flow Deriving cash flow Net operating cash flow (NOCF) Free cash flow Cover ratios International cash flow Summary 8 Forecasting models Introduction Historic forecasts Trend lines Trend lines for analysis Data smoothing Cyclicality and seasonality Summary 9 Forecasting financials Introduction Key drivers Deriving financial statements Alternative approaches Financial analysis Summary 10 Variance analysis Introduction Cash flow budgets Monthly cash model 'Flash' report and graphics Summary 11 Breakeven analysis Introduction Breakeven Operating leverage Financial leverage Combined leverage Summary 12 Portfolio analysis Introduction Formulas Optimum portfolio 13 Cost of capital Introduction Capital Asset Pricing Model Dividend growth model Cost of preference shares Cost of debt Weighted average cost of capital Marginal WACC Summary 14 Bonds Introduction Cash flows Yield measures Duration Portfolio results Summary 15 Investment analysis Introduction Investment model revisited Payback period Accounting return Net present value Internal rate of return Benefit/cost ratio Management tests - cash flow, etc. Scenarios Sensitivity analysis and charts Capital rationing Summary 16 Risk Analysis Introduction Risk assessment process and analysis Risk adjusted rate iation Coefficient of variation Certainty equivalents Real options Simulation Summary 17 Depreciation Introduction Straight line Sum of digits Declining balance US MACRS Amortisation Comparison Summary 18 Leasing Introduction Rental calculations Lease versus purchase Classification Accounting Settlements Summary 19 Company valuation Introduction Accounts Adjusted accounting value Dividends Stock market or market method Free cash flows Summary 20 Optimisation Introduction Elements of optimisation models Linear programming Margin maximisation Pensions Summary 21 Decision trees Introduction Bayes' theorem Terminology Decision tree model Information example Summary 22 Risk management Introduction Forward rate agreements Swaps Foreign exchange Futures Options Black-Scholes Summary 23 Data functions Introduction Dataset Database functions Descriptive statistics Histogram Function output Box plots Summary 24 Data analysis Introduction Imported data Sort Filter Subtotals Pivot table and chart Database import Radar chart Gauge chart Summary 25 Modelling checklist Introduction Design summary Features Techniques Appendices Appendix 1: Software installation Appendix 2: Licence Appendix 3: File list Bibliography and references Index