Mastering Cash Flow and Valuation Modelling

Alastair Day has 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 financial Modelling, Mastering Risk Modelling and The Financial Director’s Guide to Purchasing Leasing.

Acknowledgements

About the author

Conventions

Overview

Executive summary

Warranty and disclaimer

 

1 Introduction and overview

Scope of the book

Model design methodology

Example models

Summary

 

2 Functions

Categories

Finance

Mathematics

Time

Lookup

Logic

Text

Other functions

Summary

 

3 Model template

Design objectives

Systematic design method

Case objectives

Templates

Input sheet

Timeline

Summary

 

4 Historic statements

Accounting sheets

Income Statement

Balance sheet

Adjustments

Summary

 

5 Sales forecast

Forecasting objectives

Method

Sales

Common size analysis

Forecast sheet

Summary

 

6 Assets

Fixed assets

Excel functions

Existing assets

New asset depreciation

Written down value

Asset summary

Tax depreciation

Summary

 

7 Debt

Debt schedule

Required entries

Interest rates

Forecast income statement

Forecast balance sheet

Summary

 

8 Balance sheet

Current assets

Non-current assets

Current liabilities

Long-term liabilities and shareholders’ funds

Tax

Balancing item

Summary

 

9 Cash flow

Outline

Cash flow statement

Cash for debt service

Cash waterfall

Chart

Summary

 

10 Ratios

Method

Common size analysis

Core ratios

Profitability

Operating efficiency

Financial structure

Cash flow

Debt coverage

Growth

Market ratios

Summary

 

11 Cost of capital

Background

Cost of debt

Preference capital

Historic cost of equity

Forecast cost of equity

Cost of capital

Graphical representation

Summary

 

12 Valuation

Cash flows

Terminal value

Choice of method

Initial valuation

Chart

Summary

 

13 Other approaches

Method

Book value

Adjusted book value

Market value

Multiples

Peer data

Summary

 

14 Alternative methods

Method

Adjusted present value

Economic profit

Summary

 

15 Sensitivity

Outline

Returns

Data matrix

Scenario manager

Manual scenarios

Summary

 

16 Optimisation

Outline

Goal Seek

Solver

Coding Solver

Summary

 

17 Reporting

Reporting

Executive summary

Dashboard

Dynamic charts

Summary

 

18 Auditing and review

Model review

Self-checks

Excel audits tools

Consistency checks

Audit sheet

Summary

 

19 Documentation

Documentation

Navigation

Protection

Version check

Final review

Summary

 

Appendices

1 Functions list, software installation and licence

2 An introduction to Microsoft® Office 2010 (Office 14)

 

Index