1

Introduction and Overview

Scope of the book and the objective to write a generic cash flow model incorporating a range of Excel features and techniques

Model design methodology and the need for modelling standards

Example models to display the method

2

Key financial functions

Finance functions for time value of money, discounted cash flow and loan analysis

Mathematics functions such as SUMPRODUCT

Time for producing robust timelines in a model

Logic such as IF, OR and AND

Text functions for joining and manipulating strings

Analysis Toolpak and the extra functions and statistics methods

3

Model template

Framework and setting objectives

Setting up sheets as modules

Sheet templates for future use

Efficiency techniques to enter code quickly without errors

Planning for sensitivities and building in flexibility

4

Historic statements

Historic income statement components

Entering the historic balance sheet and applying the modelling standard

5

Income statement

Generating income from volume and price

Using sales forecast

Fixed and variable costs

Other elements of costs

6

Assets

Assets sheet

Depreciation methods such as straight line, declining balance and sum of digits

Calculations and required Excel functions

Tax depreciation methods

7

Debt

Debt sheet and types of debt

Setting out the debt calculations

Interest rates and cumulative costs

8

Balance sheet

Forecast current assets

Forecast current liabilities

Equity

Making the accounting statements balance

9

Cash flow

International IFRS statements

Modelling techniques

Ensuring integration of financial statements

10

Ratios / Checks

Financial ratios

Debt ratios

Returns ratios

Reconciliation and checking

Ensuring consistency

11

Cost of capital

Weighted Average Cost of Capital (WACC) theory

Risk free and risk premiums

Deriving betas

Modelling methods

Calculated cost of capital

12

Equity valuation

Terminal value methods

Using different methods

Single enterprise and equity valuation

13

Other approaches

Peer groups comparisons

Market and multiples models

14

Alternative cash methods

Adjusted present value (APV) method

Economic profit output as a comparison with free cash flow

15

Sensitivities

Building in sensitivities

Flexing and stress testing the model

Essential Excel techniques

Dashboard methods for displaying multiple answers

Charting techniques for reporting purposes

16

Targeting

Excel methods

Optimising outputs

Checking for the ‘best’ mix of return, coverage and leverage

17

Reporting

Reporting and summarising data for reporting purposes

Presenting results to different audiences

18

Auditing

Error detection

Excel auditing techniques

Other methods for ensuring consistency

Documenting the findings

19

Documentation

User documentation

Model maintenance

Protecting the model for distribution

Final model audit and review