Mastering Financial Modelling in Microsoft Excel 3rd edn A Practitioner's Guide to Applied Corporate Finance

by Day, Alastair

• ISBN13: 9780273772255
• ISBN10: 0273772252
• eBook ISBN(s): 9780273772330
• Additional ISBN(s): 9780273708063
• Edition: 3rd
• Format: Paperback
• Copyright: 2012-08-30
• Publisher: FT Press
• More Book Details
• Purchase Benefits

Summary

All the tools and techniques you need to build, develop, and apply reliable financial models with Microsoft Excel. Updated to reflect Excel 2010 features (including sparklines) and Mac versions of Excel, but still works exceptionally well with older Windows versions of Excel. Fully explains basic and complex modeling techniques, and offers specific, detailed coverage of models ranging from cash flow to company valuation. CD contains models and templates designed for clarity and easy adaptation. This book brings together everything financial professionals and students need to build and develop reliable financial models with the latest versions of Microsoft Excel, and use those models to make more effective and successful decisions. Updated for new Excel 2010 features (including sparklines) as well as the newest versions of Excel for Mac, this edition also remains fully appropriate for older versions of Excel. Leading financial consultant and writer Alastair Day begins by explaining basic modeling techniques, then moves step by- step through building increasingly complex models. In Part I, Day introduces the essentials of model design, demystifying features and techniques every model builder must understand, and showing how they come together in a complete model. Next, in Part II, he focuses on creating accurate models for many specific applications. These include: performance analysis, cash flow, forecasting, variance analysis, breakeven analysis, cost of capital, bonds, investment analysis, risk analysis/management, depreciation, leasing, company valuation, optimization, and much more. Along the way, Day explains relevant data functions and analysis techniques, and presents full chapters on both decision trees and optimization. An accompanying CD-ROM provides an extensive library of templates and sample models designed for easy adaptation. The book also provides end-of chapter exercises, making it an even more valuable resource for formal or classroom study.

Author Biography

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.

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

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