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.
Table of Contents:
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
About the Author :
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.
