Modeling

Author: Edward Ascoli

Proofreader: Stephanie Warren

Published by Adkins & Matchett (UK) Limited - trading as AMT Training

Sixth edition 2017

UK COPYRIGHT NOTICE

© 2003-2017 Adkins & Matchett (UK) Limited - trading as AMT Training

All rights reserved. The text of this publication, or any part thereof, may not be
reproduced in any manner whatsoever without written permission.

Every effort has been made to ensure the accuracy of the contents of this book.
However, the information we have presented is subject to changes in legislation
and legal interpretation and neither the author nor the publishers can accept any
responsibility for any loss arising out of any person acting, or refraining from acting
in reliance on any statement in this publication. This publication is not intended as a
substitute for competent legal or accounting advice.

The right of Edward Ascoli to be identified as author has been asserted in

accordance with the Copyright, Design and Patents Act 1988.

Manufactured in: United Kingdom

Visit us at or buy online: www.amttraining.com

AMTM1722

AMTM1406 - Modeling.indd 1 2/10/2017 1:34:08 PM

AMTM1406 - Modeling.indd 2 2/10/2017 1:34:08 PM
 Introduction

Table of contents
Introduction.................................................................................................1
What is a fully integrated model?................................................................... 1
Steps in creating a model................................................................................ 1
How to use this reference guide..................................................................... 1
Modeling basics.........................................................................................3
Accessing the ribbon with the keyboard........................................................ 3
Useful keyboard shortcuts............................................................................................4
Other essential keyboard shortcuts............................................................... 4
Selecting cells with the sticky shift key.........................................................................4
Moving across worksheets............................................................................................4
Writing a formula with the keyboard..............................................................................5
Writing a cross-sheet formula with the keyboard.......................................... 5
Copying formulas..........................................................................................................6
Modeling formulas............................................................................................ 6
Subtotals.......................................................................................................................7
Growth formulas............................................................................................................8
Relationship (or % of) formulas.....................................................................................9
Base analysis..............................................................................................................10
Cell comments.................................................................................................11
Inserting a cell comment.............................................................................................11
Viewing a cell comment..............................................................................................11
Deleting a cell comment..............................................................................................11
Setting up the model template................................................................13
Setting up the template for a single sheet model........................................ 13
Setting up the template for a multi-sheet model......................................... 15
Matrix integrity................................................................................................ 16
Cell formats - Excel 2007 onwards................................................................ 17
Business comma.........................................................................................................17
Percent format............................................................................................................19
Historical numbers style..............................................................................................21
Input style....................................................................................................................23
Applying a style to a cell.............................................................................................24
Merging styles.............................................................................................................25
Input historical numbers..........................................................................27
Inputting income statement historical numbers.......................................... 27
Depreciation................................................................................................................27
Amortization................................................................................................................28
Interest income and interest expense.........................................................................29
Putting it all together...................................................................................................29
Inputting balance sheet historical numbers................................................ 29
Calculating ratios and building assumptions........................................31
Calculating ratios............................................................................................ 31
Building assumptions.................................................................................... 31
Projecting the income statement............................................................33
Sales / revenue................................................................................................ 33
Cost of sales (COGS)..................................................................................... 33
Depreciation.................................................................................................... 33
Gross profit..................................................................................................... 35
SG&A................................................................................................................ 35
Amortization.................................................................................................... 36

© Adkins Matchett & Toy iii www.amttraining.com

AMTM1406 - Modeling.indd 3 2/10/2017 1:34:08 PM

 Introduction

EBIT.................................................................................................................. 36
Non-recurring expense / (income)................................................................ 36
Interest income and expense........................................................................ 37
Profit before taxes.......................................................................................... 37
Tax.................................................................................................................... 37
Net income...................................................................................................... 38
Shareholder information................................................................................ 38
Basic and diluted shares outstanding.........................................................................38
Basic and diluted earnings per share (EPS)...............................................................39
Dividend per share......................................................................................................40
Dividends....................................................................................................................40
Projecting the balance sheet.................................................................. 41
Sales driven accounts.................................................................................... 41
Net tangible fixed assets (net PP&E)............................................................ 42
Equity............................................................................................................... 43
Debt.................................................................................................................. 45
Debt assumptions.......................................................................................................45
Short-term debt...........................................................................................................46
Long-term debt............................................................................................................47
Balancing the balance sheet.................................................................. 49
Balance check................................................................................................. 49
Cash flow statement....................................................................................... 50
Four rules of cash.......................................................................................................50
Mark up the balance sheet..........................................................................................51
Modeling cash flow from operations...........................................................................52
Operating working capital...........................................................................................53
Other operating accounts............................................................................................55
Net cash flow from operations....................................................................................56
Cash flow from investing.............................................................................................56
Cash flow from financing.............................................................................................57
Net cash flow..............................................................................................................58
Excess cash / revolver................................................................................... 59
Calculating excess cash / revolver..............................................................................59
Balancing the balance sheet.......................................................................................61
Interest income and interest expense................................................... 65
Methods of interest calculation..................................................................... 65
Inaccurate interest calculations...................................................................................65
Circularity....................................................................................................................66
Interest rates...............................................................................................................66
Calculating interest expense......................................................................... 67
Debt summary.............................................................................................................68
Calculating interest income........................................................................... 68
Linking interest into the income statement................................................. 69
Last year method........................................................................................................69
Average method..........................................................................................................70
A problem with circular models...................................................................................71
The ISERROR function...............................................................................................73
And finally................................................................................................ 75
Congratulations!............................................................................................. 75
Ratios............................................................................................................... 75
Stress testing the model................................................................................ 75
Beauty saving................................................................................................. 76
Modeling standards........................................................................................ 76

© Adkins Matchett & Toy iv www.amttraining.com

AMTM1406 - Modeling.indd 4 2/10/2017 1:34:09 PM

 Introduction

Introduction
What is a fully integrated model?
A fully integrated model derives and projects the three main financial
statements of a business over a period of up to 10 years. These are:
the income statement, the balance sheet and the cash flow.

Steps in creating a model

There are a number of steps that you should follow when creating a
fully integrated model. These are listed below and explained in detail
in subsequent sections of this reference guide:

1. Set up the model template.

2. Input historical financials.
3. Calculate ratios and build assumptions.
4. Project the income statement.
5. Project the balance sheet.
6. Balance the balance sheet.
7. Add interest to the income statement.
8. Create a summary page.

How to use this reference guide

This workbook uses a US based fictitious company called Irene
Foods to explain the steps necessary to build a fully integrated
financial model in Microsoft Excel. As you work through this
guide you will see extracts of Excel worksheets. Each extract will
represent a different area of the model being described. A label
in cell A2 of each extract indicates the area of the model that the
extract represents. For example, the following is an extract from the
Income Statement.

Both US GAAP and UK GAAP accounting terminology are used

interchangeably throughout this workbook.

In order to gain full benefit from the guide, you should work through
each section in the order it is presented.

© Adkins Matchett & Toy 1 www.amttraining.com

AMTM1406 - Modeling.indd 1 2/10/2017 1:34:09 PM

 Introduction

© Adkins Matchett & Toy 2 www.amttraining.com

AMTM1406 - Modeling.indd 2 2/10/2017 1:34:09 PM

 Modeling basics

Modeling basics
This section looks at some fundamental Excel skills you will need to
know before attempting to build a model.

Accessing the ribbon with the keyboard

Being able to access the commands on the ribbon using the
keyboard will give you access to many functions quickly. If you press
the ALT key once, you will see letters (known as hotkeys) appear
next to each ribbon tab as shown below.

Pressing the relevant hotkey will open a specific ribbon. For

example, pressing ALT + H will open the Home ribbon, as shown
below.

You will see that each command on the active ribbon will have a
corresponding hotkey. Pressing a letter will activate the command.
For example, to open the font color palette, press F + C in
sequence. In other words, ALT + H + F + C will open the font color
palette, which is located on the Home ribbon.

The Escape key allows you to move up a ribbon level or leave

the ribbon. For example, pressing Escape when the color palette
is open will close the palette and show the hotkeys on the Home
ribbon. Pressing Escape again will show the hotkeys for the ribbon
tabs. Pressing Escape a final time allows you to leave the ribbon
and return to the worksheet.

If you are already used to the ALT hotkeys in Excel 2003, you will
be happy to learn that the majority of these keyboard shortcuts still
work in the later versions of Excel.

© Adkins Matchett & Toy 3 www.amttraining.com

AMTM1406 - Modeling.indd 3 2/10/2017 1:34:09 PM

 Introduction

Useful keyboard shortcuts

The following table provides a list of key commands to make your
life easier:
Command Excel 2003 Excel 2007
onwards
Insert a row ALT i r ALT h i r
Rename a sheet ALT o h r ALT h o r
Zoom your view ALT v z ALT w q
Paste Special ALT e s ALT h v s
Clear all ALT e a a ALT h e a
Clear Formats ALT e a f ALT h e f
Fit width to cell ALT o c a ALT h o i
Print Preview ALT f v ALT f p

Other essential keyboard shortcuts

There are a number of other keyboard shortcuts that are designed
to make your life easier. The following table shows some of the main
ones:

Command Shortcut
Undo CTRL + Z
Redo CTRL + Y
Cut CTRL + X
Copy CTRL + C
Paste CTRL + V
Go to F5

Selecting cells with the sticky shift key

Sticky shift! Holding down the SHIFT key while pressing an arrow key enables
you to select a number of contiguous cells in the direction of the
Hold down the arrow keys from the starting cell. This avoids the runaway mouse
SHIFT key whilst syndrome!
pressing one of the
arrow keys For example, to select cells A1 to E2:
(   ) to
select cells with
the keyboard.

Select cell A1 to make it the starting cell. Hold down the SHIFT key
and press the right arrow key 4 times and the down arrow once.

Moving across worksheets

To move between worksheets, hold down the CTRL key and press
PgDn to move one worksheet to the right or PgUp to move to one
worksheet to the left.

© Adkins Matchett & Toy 4 www.amttraining.com

AMTM1406 - Modeling.indd 4 2/10/2017 1:34:09 PM

 Modeling basics

Writing a formula with the keyboard

To write a formula with the keyboard:

Hat ^ 1. Type =
2. Use the arrow keys to point to the first cell reference in the
The hat character formula.
(^) used in a 3. Enter an operator (e.g. +, -, /, * or ^).
formula raises a 4. Use the arrow keys to point to the second cell reference in the
value to the power formula.
of another value. 5. Repeat steps 3 and 4 until you have completed the formula.
For example, 2^2 6. Press the ENTER key.
is equal to 4.
For example, to create a formula to calculate Gross Profit for Year -2
in the worksheet below:

1. Select cell C6.

2. Type =
3. Press the up arrow three times to point to C3.
4. Type the minus operator (-).
5. Press the up arrow twice to select cell C4.
6. Type the minus operator (-).
7. Press the up arrow once to select C5.
8. Press ENTER.

Writing a cross-sheet formula with the keyboard

Many of your formulas will reference cells from another worksheet.
These types of formulas are referred to as cross-sheet formulas,
and writing them by using the keyboard will save you a lot of time.
To write such a formula:

1. Type =
2. Point to the cell reference using the arrow keys. If the cell is on
another sheet use CTRL+ PgUp or CTRL + PgDn to go to the
sheet and then use the arrow keys to point to the desired cell
3. Type an operator (+, -, /, *, or ^).
4. Point to the second cell reference using the arrow keys. If the
cell is on another sheet use CTRL + PgUp or CTRL + PgDn
to go to the sheet and then use the arrow keys to point to the
desired cell.
5. Repeat steps 3 and 4 until you have completed the formula.
6. Press the ENTER key.

A reference in a formula to another sheet will always have the sheet

name with an explanation mark (!) followed by the cell reference. For
example, when referencing cell B3 on the Income Statement sheet,
the formula will be:

=Income Statement!B3

© Adkins Matchett & Toy 5 www.amttraining.com

AMTM1406 - Modeling.indd 5 2/10/2017 1:34:09 PM

 Modeling basics

To illustrate: let us suppose that you want to create a sales growth

formula on the Income Statement sheet where the percentage
growth assumption is on a separate Assumptions sheet as shown in
the worksheets below:

1. Select cell F3 on the Income Statement sheet.

2. Type =
3. Point to cell E3 on the Income Statement sheet pressing the
left arrow once.
4. Type *(1+
5. Assuming that the Assumptions sheet is directly to the left of
the Income Statement sheet, press CTRL + PgUp once, and
use the arrow keys on your keyboard to point the correct sales
growth assumption. For this example, the correct sales growth
assumption is in cell F4.
6. Type )
7. Press the ENTER key.

Copying formulas
Remember! To copy a formula with the keyboard:

Use sticky SHIFT 1. Select the cell containing the formula.

when selecting 2. Press CTRL + C
cells with the 3. Select the cells where you would like to copy the formula into.
keyboard. 4. Press the ENTER key.

For example, to copy the Gross Profit formula in the example below:

1. Select cell C6.

2. Press CTRL + C
3. Hold down the SHIFT key and press the right arrow twice to
select cells C6 to E6.
4. Press the ENTER key.

Modeling formulas
There are a number of modeling formulas you will encounter when
building your model. The main ones are:

■■ Subtotals
■■ Growth formulas
■■ Relationship or % of formulas

© Adkins Matchett & Toy 6 www.amttraining.com

AMTM1406 - Modeling.indd 6 2/10/2017 1:34:09 PM

 Modeling basics

Subtotals
We have already encountered one type of subtotal formula in our
example above where we deducted COGS and depreciation from
sales to arrive at gross profit.
The SUM function can be used when adding a number of different
cells together. To use the SUM function:

1. Type =SUM(
2. Select the cells to add up.
3. Press Enter.

For example, to calculate total current assets in the example below:

1. Select cell D10.

2. Type =SUM(
3. Select the cells to add up, D4:D9 in this example (remember to
use the arrows and sticky SHIFT!).
4. Press Enter.

Warning!

If you’re working in
continental Europe,
you may have to
use semi-colons (;)
instead of commas
(,) within Excel
functions.

You can also use SUM in conjunction with another formula. For
example, in the worksheet below, you can derive gross profit by
deducting COGS and depreciation from sales.

© Adkins Matchett & Toy 7 www.amttraining.com

AMTM1406 - Modeling.indd 7 2/10/2017 1:34:09 PM

 Modeling basics

Sometimes, you will need to use SUM to add a non-contiguous

range of cells. To do this, simply add a comma (,) between the cell
ranges. In the worksheet below, we use this functionality to derive
total assets.

You can automatically SUM a contiguous row or column of figures

Autosum using the keyboard. To do this:

ALT + = 1. Select the cell below a column of figures or to the right of a row
of figures.
2. Hold down ALT and press =
3. Press ENTER.

When using this function, make sure that Excel has chosen the
correct range before pressing ENTER!

Growth formulas
To increase or decrease a value by a percentage, use a growth
formula.

Hard numbers! Previous value * (1 + % growth rate)

Avoid hard or
numbers in your
formulas. Always Previous value * (1 - % growth rate)
use cell references
instead. For example, to write a formula to grow sales in the worksheet
below:

1. Select cell C3 on the Income Statement.

2. Type =
3. Press the left arrow once to point to cell B3.
4. Type *(1+
5. Point to cell F4 on the Assumptions sheet (CTRL PgUp)
6. Type ).
7. Press ENTER.

© Adkins Matchett & Toy 8 www.amttraining.com

AMTM1406 - Modeling.indd 8 2/10/2017 1:34:09 PM

 Modeling basics

To increase or decrease a value by a quantity, simply add the

quantity to the previous value or subtract the quantity from the
previous value.

Previous value + quantity increase

or

Previous value - quantity increase

Relationship (or % of) formulas

Financial models usually base most accounts on some other driver.
You should use relationship (or % of) formulas to generate such
accounts. For example, sales are the relationship driver of COGS.
As sales increase, COGS will usually increase, since COGS is
directly related to the value of sales within the same period. As
sales decrease COGS will usually decrease. Many financial models
therefore project COGS as a % of sales as shown in the worksheet
below:

© Adkins Matchett & Toy 9 www.amttraining.com

AMTM1406 - Modeling.indd 9 2/10/2017 1:34:10 PM

 Modeling basics

Base analysis
When you forecast a set of financial statements, you will often need
to project complex accounts by separating individual increases and
decreases in the account. For example, when projecting the net
fixed assets (net PP&E) of a business you will want to project capital
expenditure, which will increase the account and depreciation,
which will decrease the account. This can be achieved using BASE
analysis.

B Amount at beginning of year

A + Additions during the year
S - Subtractions during the year
E = Ending amount

To set up a BASE analysis in Excel:

1. Create the BASE analysis label set. We will use net fixed
assets as an example.

2. Enter the ending balance of the latest historical year. In the

worksheet below, this is entered in cell B11.

3. Link the beginning balance of the first projected year to the

ending balance of the previous year.

© Adkins Matchett & Toy 10 www.amttraining.com

AMTM1406 - Modeling.indd 10 2/10/2017 1:34:10 PM

 Modeling basics

4. Enter a formula to calculate the increase account and enter a

formula to calculate the decrease account (information on how
to create formulas for capital expenditure and depreciation is
provided in subsequent sections of this workbook).

5. Now create a formula to calculate the ending amount.

6. Now you can copy the first projected year across to the
remaining projected years. With the worksheet above, we
would select cells C8:C11 and copy the formulas across.

Cell comments
Cells comments provide a useful way of documenting your work.
Use these when you need to explain the contents of a cell. The more
you document your work, the better!

Inserting a cell comment

To insert a cell comment:

1. Either: Choose Insert, Comment from the menu.

Or: Press SHIFT + F2

2. Type your comment text in the text box that appears.

3. Once you have finished typing the text either click another cell
with the mouse or press the ESC key.

Viewing a cell comment

A red comment indicator shows that a cell contains a comment. To
view the comment, either point to the cell containing the comment
with the mouse (you do not need to click) or select the cell and press
SHIFT + F2.

Deleting a cell comment

To delete a cell comment, either right click the cell containing the
comment and select Delete Comment from the menu, or press
ALT e a m using the keyboard.

© Adkins Matchett & Toy 11 www.amttraining.com

AMTM1406 - Modeling.indd 11 2/10/2017 1:34:10 PM

 Modeling basics

© Adkins Matchett & Toy 12 www.amttraining.com

AMTM1406 - Modeling.indd 12 2/10/2017 1:34:10 PM

 Setting up the model template

Setting up the model template

When setting up the template for your model, you must decide
whether you want to create a single sheet model or a multi-sheet
model. With a single sheet model, the different areas of the model
are on one sheet. With a multi-sheet model, the different areas of
the model are on several different sheets. Once you have decided
which type of model to build, you should set up the formatting styles
to use throughout the template.

Setting up the template for a single sheet model

When setting up the template for your single sheet model, you
should start by creating a header area to contain the company
name, dates and so on. To do this, follow these steps:

1. Open a new workbook.

2. Make column A very narrow. This will eventually act as a very
useful tool for navigating the model.
3. Type the name of the company you are forecasting in cell A1
and increase its font size to make it stand out.
4. Widen column B. This will eventually contain the line items of
the model.
5. Use one column for each year starting in cell C2. Most models
have 3-5 years of historical numbers and 5-10 years of
projected numbers.
6. Type Hist. in the cell above each year that is a historical year
and Proj. in the cell above each year that is a projected year.
7. Format your header row to make it stand out. For example,
make date headings bold, add a border to the bottom of the
dates and so on.
8. Freeze your header area so that when you scroll down the
worksheet, you will always see the header rows. To do this,
select the cell below and on the far left of the header row. In
this case, select cell A3. Choose Window, Freeze Panes from
the menu.

You should end up with a worksheet that looks similar to the one
below:

© Adkins Matchett & Toy 13 www.amttraining.com

AMTM1406 - Modeling.indd 13 2/10/2017 1:34:10 PM

 Setting up the model template

With a single sheet model, you should organize your work into the
following sections.

Assumptions

Secondary Calculations

Income Statement

Balance Sheet

Cash Flow Statement

Summary Ratios

You should enter each of the section headings in the narrow column
A as illustrated below.

The rows between each section will eventually contain the line items
of the model. You will add these line items later. At this stage, you do
not need to specify the exact number of rows between each section
as you can insert additional rows while building the model.

Once you have added the section headings as described above,

column A acts as an extremely useful navigation bar. To see this
working:

1. Select one of the section headings in column A.

2. Hold down CTRL and press the up or down arrows on your
keyboard a number of times until you have reached the section
you want to go to.

When your model contains all its line items, this is an invaluable trick
for navigating the different sections of the model.

© Adkins Matchett & Toy 14 www.amttraining.com

AMTM1406 - Modeling.indd 14 2/10/2017 1:34:10 PM

 Setting up the model template

Setting up the template for a multi-sheet model

With a multi-sheet model, each sheet will represent a different
section of your model. To set up the template for such a model, you
should firstly create the header row on one of the sheets and then
copy this sheet in order to create the other sheets. This will ensure
consistency and matrix integrity on all the sheets.

To create your multi-sheet template, follow these steps:

1. Open a new workbook.

2. Make column A of one of the sheets very narrow (the
navigation bar is also very useful in a multi-sheet model).
3. Type the name of the company you are forecasting in cell A1
and increase its font size to make it stand out.
4. Widen column B. This will eventually contain the line items of
the model.
5. Use one column for each year starting in cell C2. Most models
have 3-5 years of historical numbers and 5-10 years of
projected numbers.
6. Type Hist. in the cell above each year that is a historical year
and Proj. in the cell above each year that is a projected year.
7. Format your header row to make it stand out. For example,
make date headings bold, add a border to the bottom of the
dates and so on.
8. Freeze your header area so that when you scroll down the
worksheet, you will always see the header rows. To do this,
select the cell below and to the left of the header row. In this
case, select A3. Choose Window, Freeze Panes from the
menu.

You should end up with a worksheet that looks similar to the one
below:

Now that you have created the model template for one of the sheets,
you should copy this sheet 5 times in order to create sheets for the
different sections stated above.

To copy the sheet, follow these steps:

1. Point to the sheet with the mouse pointer.

2. Hold down the CTRL key and drag the sheet to the right. A little
black down arrow will indicate where the copied sheet will be
inserted.

© Adkins Matchett & Toy 15 www.amttraining.com

AMTM1406 - Modeling.indd 15 2/10/2017 1:34:10 PM

 Setting up the model template

3. Let go of the mouse button before the CTRL key to make a

copy of the sheet. The new sheet will appear as shown below.

4. Repeat steps 1-4 from above until you have copied the desired
number of sheets.

Once you have copied the sheets, you should rename each sheet
so that it corresponds to a section of the model. To rename a sheet:

1. Select the sheet to rename.

2. Either: Press the ALT key and press o h r.

Or: Double click the sheet tab.

3. Type the desired name.

4. Press the ENTER key.

Your template will now have sheet names with correspond to each
section of the model as follows:

Matrix integrity
Setting up the template for a multi-sheet model as described
above is key to ensuring matrix integrity throughout the model. For
example, if column F on the income statement represents Year 1,
column F on all the other sheets should also represent Year 1. If you
decide to add a new column to a worksheet of your template, make
sure you remember to change the other worksheets as well.

© Adkins Matchett & Toy 16 www.amttraining.com

AMTM1406 - Modeling.indd 16 2/10/2017 1:34:10 PM

 Setting up the model template

Cell formats - Excel 2007 onwards

The worksheet cells of a well-designed model will contain the
following main formats:

■■ Business comma
■■ Percent
■■ Historical
■■ Input

You should create these cell formats and save them as Styles so
that they are readily available, before entering data into your model.

Business comma
The business comma cell format is a number format with commas
as thousand separators and one decimal place. Negative numbers
are displayed with brackets. This format is used to display the main
financial numbers in your model. Numbers using the business
format are shown below:

You will notice that all the decimals points are lined up, regardless of
the brackets for the negative numbers.

So that you can access this format easily, you should redefine your
normal style. Doing this will automatically format all cells in your
workbook, using the business comma format. This is not a problem,
as most of the cells in your model will use this style. To do this:

1. Ensure that you are in the file that you are using as your model
template.

2. Choose Cell Styles in the Styles group of the Home ribbon.

This will open the Style gallery.

3. Right click the Normal style and choose Modify. The Style
dialog box will appear.

4. Deselect all the tick boxes with the exception of the Number
tick box. This will ensure that only the number format will be
applied when the style is applied to your workbook.

© Adkins Matchett & Toy 17 www.amttraining.com

AMTM1406 - Modeling.indd 17 2/10/2017 1:34:10 PM

 Setting up the model template

5. Select the Format button. The Format Cells dialog box is

displayed.

6. Choose the Custom category. The Format Cells dialog box will
change its appearance.

7. In the Type edit box, enter the following number format:

© Adkins Matchett & Toy 18 www.amttraining.com

AMTM1406 - Modeling.indd 18 2/10/2017 1:34:10 PM

 Setting up the model template

8. Choose OK. You will be returned to the Style dialog box. Your
format code will appear next to the number check box.

9. Choose OK on the Style dialog box to save the changes to the

style. You will be returned to the worksheet.
When you enter a number into a cell, it will be formatted with the
business comma format. This is because all cells in an Excel
workbook use the Normal style by default.

Percent format
The Percent Format is a number formatted as a percent with one
or two decimal places, depending on your preference. Negative
percentages are also displayed in brackets.

Numbers entered into cells with the Percent Format are displayed
with a % sign and the underlying number is divided by 100. For
example, the number 0.5 is displayed as 50%. When performing
calculations with such cells Excel will use the underlying number,
in this case 0.5. Numbers formatted with the percent format are
displayed as follows:

Once again, you will notice that the decimal places line up,
regardless of the brackets for negative numbers. To set this style up,
modify the existing Percent Style as follows:

1. Ensure that you are in the file that you are using as your model
template.

2. Choose Cell Styles in the Styles group of the Home ribbon.

This will open the Style gallery.

3. Right click the Percent style and choose Modify. The Style
dialog box will appear.

© Adkins Matchett & Toy 19 www.amttraining.com

AMTM1406 - Modeling.indd 19 2/10/2017 1:34:11 PM

 Setting up the model template

4. Deselect all the tick boxes with the exception of the Number
tick box. This will ensure that only the number format will be
applied when the style is applied to your workbook.

5. Select the Format button. The Format Cells dialog box is

displayed.

© Adkins Matchett & Toy 20 www.amttraining.com

AMTM1406 - Modeling.indd 20 2/10/2017 1:34:11 PM

 Setting up the model template

6. Choose the Custom category. The Format Cells dialog box will
change its appearance.

7. In the Type box, enter the following number format:

If you would like your percentages rounded to two decimal

places, simply place two zeros after the decimal point, as
shown below:

8. Choose OK. You will be returned to the Style dialog box. Your
format code will appear next to the number check box.

9. Choose OK to save changes to the style and return to the

worksheet.

Historical numbers style

The Historical numbers style will change the font color of a cell to
blue. You should use this style so that users of your model can
easily distinguish between historical and projected numbers. To
create this style:

© Adkins Matchett & Toy 21 www.amttraining.com

AMTM1406 - Modeling.indd 21 2/10/2017 1:34:11 PM

 Setting up the model template

1. Ensure that you are in the file that you are using as your model
template.

2. Choose Cell Styles in the Styles group of the Home ribbon.

This will open the Style gallery.

3. Click the New Cell Style button at the bottom of the Style
Gallery. The Style dialog box will appear.

4. In the Style name box, type Historical.

5. Deselect all the tick boxes with the exception of the Font tick
box.

6. Choose Format. The Format Cells dialog box is displayed.

© Adkins Matchett & Toy 22 www.amttraining.com

AMTM1406 - Modeling.indd 22 2/10/2017 1:34:11 PM

 Setting up the model template

7. Select the Font tab and choose the blue color. To do this:

a. Choose the Color drop down and choose More colors.

The Colors dialog box will appear.

b. Choose the Custom tab of the Colors dialog box.

c. Set the numbers as shown below:

d. Choose OK to close the Colors dialog box.

8. Choose OK to close the Format Cells dialog box.

9. Choose OK to close the Style dialog box.

Input style
An Input style should be created and used for the assumptions of
your model. Users will know that they can enter new values into the
input cells without destroying the formula engineering behind the
model. The Input style has the following properties: cell color is light
yellow; font color is blue; and the cell has a border. To create an
input style:

1. Ensure that you are in the file that you are using as your model
template.

2. Choose Cell Styles in the Styles group of the Home ribbon.

This will open the Style gallery.

3. Click the New Cell Style button at the bottom of the Style
Gallery. The Style dialog box will appear.

4. In the Style name box, type Input.

5. Deselect all the tick boxes, with the exception of Font, Border
and Fill.

© Adkins Matchett & Toy 23 www.amttraining.com

AMTM1406 - Modeling.indd 23 2/10/2017 1:34:11 PM

 Setting up the model template

6. Choose Format. The Format cells dialog box is displayed

7. Go to the Font tab and choose the Blue color. To do this:

a. Choose the Color drop down and choose More colors.

The Colors dialog box will appear.

b. Choose the Custom tab of the Colors dialog box.

c. Set the numbers as shown below:

d. Choose OK to close the Colors dialog box.

8. Go to the Border tab and choose a thin line style followed by

the Outline button.

9. Go to the Fill tab and choose a light-shaded color of your

choice.

10. Choose OK. You will be returned to the Style dialog box.

11. Choose OK to add the style. You will be returned to the

worksheet.

Applying a style to a cell

To format a cell with a cell style:

1. Select the desired cell or range of cells.

2. Open the Style Gallery located in the Styles group of the Home
ribbon.

3. Choose the style to apply to a cell.

© Adkins Matchett & Toy 24 www.amttraining.com

AMTM1406 - Modeling.indd 24 2/10/2017 1:34:13 PM

 Setting up the model template

Merging styles
Styles only exist in the file that you created them in. You will
therefore not be able to use the styles you created in one file in
another file unless you import the styles from the source file. This is
called Merging styles. To import or merge styles:

1. Ensure that both the file containing the styles (source file) and
the file to receive the styles (destination file) are open. Ensure
you open these from the same Excel application.

2. Go to the destination file.

3. Open the Style Gallery, located in the Styles group of the

Home ribbon.

4. Choose Merge Styles.

5. Select the file containing the styles to import.

6. Choose OK. If styles in the source file have the same names
as styles in the destination file, the following dialog box is
displayed.

7. Either: Choose Yes to replace styles in the destination file

with styles in the source file that have the same
name.
Or: Choose No to only import those styles with unique
names.

© Adkins Matchett & Toy 25 www.amttraining.com

AMTM1406 - Modeling.indd 25 2/10/2017 1:34:13 PM

 Setting up the model template

© Adkins Matchett & Toy 26 www.amttraining.com

AMTM1406 - Modeling.indd 26 2/10/2017 1:34:13 PM

 Input historical numbers

Input historical numbers

An integrated model will normally contain 3-5 years of historical
income statement and balance sheet information, with approximately
the same line items as the available historical statements of the
business to be analyzed.

Inputting income statement historical numbers

The income statement of your model should have approximately the
same line items as the income statement of the business’s published
financial reports. However, there are a number of accounts that
should be forecast separately and should therefore have their own
line item. These accounts are normally:

■■ Depreciation
■■ Amortization
■■ Interest Income
■■ Interest Expense

Depreciation
Unless stated otherwise, the depreciation expense in a company’s
published income statement will normally be included in the cost
of sales account and overhead expense accounts such as SG&A.
If the business is a manufacturing company, the majority of the
depreciation will be in cost of sales. If the company is a service-
oriented business, the majority of the deprecation will be in the
overhead accounts.

When modeling an income statement of a business, you must make

an informed decision as to where the majority of the depreciation is
located, based on the rule stated above. You must then remove the
depreciation from this account and put it on its own line item in your
model. The process of removing the depreciation from an account is
referred to as “cleaning” the account.

When cleaning an account, you must give as much information as

necessary to a potential user of your model as to how you cleaned
the account. For example, if a manufacturing company’s cost of
goods sold was 1,000 and the depreciation expense was 90 in one
of the historical years, its clean cost of goods sold would be 910.
Rather than simply typing 910 for the clean cost of goods sold, you
should write a simple formula as follows:

= 1000 - 90

This will indicate to a potential user what you have done with the
cost of goods sold line when they are comparing your model to the
published financial statements.

You will normally find the depreciation charge for the year in the
operating section of the cash flow or the fixed asset footnote in the
company’s published financial reports.

© Adkins Matchett & Toy 27 www.amttraining.com

AMTM1406 - Modeling.indd 27 2/10/2017 1:34:13 PM

 Input historical numbers

Harry’s Chocolates manufacturer

Harry’s Chocolates is a business that manufactures a variety of

confectionary products. You are in the process of modeling its
income statement. The following are extracts from Harry’s published
income statement:

Year - 2 Year - 1 Year 0

Sales 450 470 490
Cost of sales 300 310 320
Gross profit 150 160 170

Depreciation charge for Year -2, Year -1 and Year 0 are 20, 25 and
30 respectively.

As Harry’s Chocolates is a manufacturing company, you decide

that most of the depreciation will be in the costs of sales account
of Harry’s published financial statements. Your modeled income
statement will therefore look as follows:

Your modeled income statement should contain a clean cost of sales

line and a separate depreciation line, all above the Gross Profit line.
Also notice how a simple formula has been used to work out the
clean cost of sales line. In the worksheet above you can see that the
formula = 320 - 30 has been used to calculate clean cost of sales for
Year 0. This has also been done for Year - 1 and Year - 2.

Amortization
Unless stated otherwise, amortization will normally be included
in one of the overhead cost accounts on a company’s published
income statement. Amortization expense can be found in the
published cash flow statement or the intangibles footnote.

Similar to depreciation, amortization on the modeled income

statement should be forecast separately in its own line item and the
cost account originally containing amortization should be cleaned.
As with depreciation, formulas should be included in the cost line
item to make this as clear as possible.

© Adkins Matchett & Toy 28 www.amttraining.com

AMTM1406 - Modeling.indd 28 2/10/2017 1:34:13 PM

 Input historical numbers

Interest income and interest expense

The interest line item on a company’s published income statement
will normally be interest expense net of interest income. However,
sometimes you will find that interest expense will be disclosed
separately on the face of the published income statement, and
interest income will be included in another income or other expense
account.

In a financial model interest expense and interest income should be

forecast separately. They should therefore have their own line items.
Once again, when cleaning the various accounts to derive interest
income and expense, you should make your calculations explicit by
using formulas in the various line items.

Putting it all together

Once you have added the line items and input the historical
numbers, a typical income statement for a US company will look
similar to the one illustrated below:

Inputting balance sheet historical numbers

The balance sheet should have approximately the same line items
as the company’s published balance sheet. Depending on the
purpose of the model, you will sometimes consolidate some of the
published line items into other assets or other liability accounts.
When doing this, remember to include formulas and cell comments
so that future users of your model can see what you have done.

© Adkins Matchett & Toy 29 www.amttraining.com

AMTM1406 - Modeling.indd 29 2/10/2017 1:34:13 PM

 Input historical numbers

A typical balance sheet will look as follows:

You should also input capital expenditure historical values into

your model. To do this, set up a fixed asset (PP&E) schedule in the
calculations section of the model and input the capital expenditure
values in the appropriate cells as shown below:

Historical capital expenditure values can be found in the cash flow

statement of the published financial reports.

You will complete the remaining parts of the fixed asset (PP&E)
schedule when forecasting the income statement (profit and loss
account).

© Adkins Matchett & Toy 30 www.amttraining.com

AMTM1406 - Modeling.indd 30 2/10/2017 1:34:13 PM

 Calculating ratios and building assumptions

Calculating ratios and building assumptions

Calculating ratios
Ratios should be calculated based on the historical financial
data you input into your model. They should be calculated in the
assumptions area and should be divided into groups, such as:

■■ Income statement
■■ Current assets
■■ Non-current assets
■■ Current liabilities
■■ Non-current liabilities
■■ Equity

A typical model will contain the following ratios shown in the

worksheet below. You will notice that some ratios are missing from
some of the historical years. This is because information was not
available from the annual report when building this model.

Building assumptions
Assumptions are the inputs that drive the various accounts in the
model. The cells containing the assumptions should be formatted
using the Input style (see Input Style, page 25) so that a user of the
model can easily identify them as inputs. In the diagram below the
assumptions are in columns F to J.

© Adkins Matchett & Toy 31 www.amttraining.com

AMTM1406 - Modeling.indd 31 2/10/2017 1:34:14 PM

 Calculating ratios and building assumptions

© Adkins Matchett & Toy 32 www.amttraining.com

AMTM1406 - Modeling.indd 32 2/10/2017 1:34:14 PM

 Projecting the income statement

Projecting the income statement

Sales / revenue
The sales / revenue line is the first line item to be projected on the
income statement using a growth formula as follows:

= previous year sales * (1 + % growth assumption)

It is this line that will drive many of the other accounts in the model
as we are building a sales driven model.

Cost of sales (COGS)

The cost of sales line is normally projected as a % of sales. The
following formula should be used:

= COGS % of sales assumption * Sales

Depreciation
The depreciation should be derived in the calculations area of the
model along with capital expenditure. To derive depreciation:

1. Go to the calculations section of your model and ensure that

you have the following schedule set up. You should have set
this up when inputting the capital expenditure historical values
into the model.

© Adkins Matchett & Toy 33 www.amttraining.com

AMTM1406 - Modeling.indd 33 2/10/2017 1:34:14 PM

 Projecting the income statement

2. You should already have input the historical fixed asset (PP&E)
balances on the balance sheet. Now you must link them into
the fixed assets (PP&E) ending balances in the fixed asset
schedule of the calculations area of the model.

3. Link the beginning PP&E balance of the first projected year to

the ending PP&E balance of the previous year.

4. Write a formula to calculate capital expenditure in the first

projected year. The formula should be a % of sales formula.
:

5. Write a formula to calculate depreciation in the first projected

year. You will see from the assumptions that this model
projects depreciation as a % of the beginning fixed asset
(PP&E) balance.

© Adkins Matchett & Toy 34 www.amttraining.com

AMTM1406 - Modeling.indd 34 2/10/2017 1:34:14 PM

 Projecting the income statement

6. Calculate the ending PP&E balance for the first projected year.
This should be done using the following formula:
= beg. PP&E + Capex - Depreciation

7. Now select cell F4:F7 and copy the formulas across in one go.

Once you have derived the depreciation, you can link this into
the income statement as shown below:

Gross profit
To derive gross profit, subtract clean COGS and depreciation from
sales. This can be done using the following formula:

SG&A
SG&A costs are normally projected as a % of Sales:

= SG&A % of sales assumption * Sales

© Adkins Matchett & Toy 35 www.amttraining.com

AMTM1406 - Modeling.indd 35 2/10/2017 1:34:14 PM

 Projecting the income statement

Amortization
Amortization on the income statement can be linked to the
amortization amount in the assumptions area of the model.

EBIT
EBIT is derived by subtracting SG&A and amortization from gross
profit.

Non-recurring expense / (income)

Non-recurring expense / (income) cannot be projected. However, at
times companies may estimate some major upcoming non-recurring
items that they foresee in the near future (i.e. planned severance or
restructuring charges). With the exception of these announced non-
recurring items, your assumption for non-recurring items should be
zero during the projection period.

© Adkins Matchett & Toy 36 www.amttraining.com

AMTM1406 - Modeling.indd 36 2/10/2017 1:34:14 PM

 Projecting the income statement

Interest income and expense

Interest income and expense should be the very last thing that is
calculated in the model. It should therefore be left blank until the
other parts of the model are complete.

Profit before taxes

Profit before taxes (PBT) is derived using the following formula:

Operating profit - non-recurring expense / (income) + interest

income - interest expense

Tax
Tax expense can be calculated by multiplying the tax rate
assumption by profit before taxes.

© Adkins Matchett & Toy 37 www.amttraining.com

AMTM1406 - Modeling.indd 37 2/10/2017 1:34:15 PM

 Projecting the income statement

Net income
Net income is derived by subtracting taxes from profit before taxes.

Shareholder information
The shareholder information section should present basic and
diluted average shares outstanding, basic and diluted earnings per
share, and dividend per share information. You should place this
under the income statement.

Basic and diluted shares outstanding

You should include basic and diluted shares outstanding as
constants in the assumptions area of the model, and link them to
the shareholder information section of the income statement. You
should keep these numbers flat during the projection period, as it is
an advanced issue to forecast stock re-purchases or issuances, and
as further assumptions such as forecasted share price are needed
and associated cashflow adjustments are also required. For most
purposes this level of forecasting is not necessary, so we typically
assume the numbers stay constant, and equity is neither issued nor
re-purchased in forecast years.

© Adkins Matchett & Toy 38 www.amttraining.com

AMTM1406 - Modeling.indd 38 2/10/2017 1:34:15 PM

 Projecting the income statement

Basic and diluted earnings per share (EPS)

Basic EPS is derived by dividing the earnings available to common
shareholders by the average basic number of shares outstanding
during the year.

Diluted earnings per share is derived by taking the earnings

available to common shareholders and dividing them by the average
diluted number of shares outstanding during the year.

© Adkins Matchett & Toy 39 www.amttraining.com

AMTM1406 - Modeling.indd 39 2/10/2017 1:34:15 PM

 Projecting the income statement

Dividend per share

Dividend per share should be projected using a growth rate in the
assumptions area of the model. Simply use a growth formula as
shown below:

Dividends
The ‘dividend’ line item located below net income (which estimates
dividends paid to shareholders) is calculated using the “dividend
per share” line item derived above and the “basic weighted average
number of shares outstanding” line item. To calculate dividends, use
the following formula:

Dividend per shares x basic weighted average number of shares

© Adkins Matchett & Toy 40 www.amttraining.com

AMTM1406 - Modeling.indd 40 2/10/2017 1:34:15 PM

 Projecting the balance sheet

Projecting the balance sheet

Project every line item on the balance sheet, apart from excess
cash and revolver. These will be derived using the cash flow. Many
of the accounts on the balance sheet can be projected as a % of
sales. These types of accounts are called sales driven accounts.
Other accounts must be derived using BASE analysis derived in
the calculations area of the model. Some accounts are projected
using an amount in the assumptions area of the model. Finally, the
accounts that make the balance sheet balance are excess cash and
revolver. The excess cash and revolver line items are addressed in
the next section of this reference guide.

Sales driven accounts

Many of the line items on the balance sheet are projected as a % of
sales. These accounts typically include:

■■ Required / Operating cash

■■ Trade debtors / Accounts receivable
■■ Prepaid expenses
■■ Deferred tax assets / Liabilities
■■ Accrued liabilities
■■ Other long-term liabilities

Let’s take the example of required cash at a growing retail business.

As sales increase, the company will need more cash on hand in
order to run the business effectively. For example, they need more
cash for more cash registers, they need more cash to pay more
employees, and so on.

Determining required cash as a % sales is not a precise science, but

as a rule of thumb, many practitioners use 3% of sales as a rough
guideline. You would enter this % in the assumptions area of the
model.

To project sales driven accounts, multiply the % assumption by sales

on the income statement. Doing this with the accounts receivables
line item is illustrated in the worksheets below.

There are other line items on the balance sheet that are projected as
a % of cost of sales (COGS). These typically include:

© Adkins Matchett & Toy 41 www.amttraining.com

AMTM1406 - Modeling.indd 41 2/10/2017 1:34:15 PM

 Projecting the balance sheet

■■ Inventories
■■ Accounts payable

To project such accounts, multiply the assumption by the COGS (cost

of sales) line item on the income statement. In the worksheets below,
projecting inventory is used for illustrative purposes.

Net tangible fixed assets (net PP&E)

The next line item on the balance sheet that your model will need
to include is ending net tangible fixed assets (net PP&E). Since you
have already calculated this while preparing the income statement
(see Depreciation, page 27), all you need to do is link the ending
net tangible PP&E balance from the calculations section as shown
below:

© Adkins Matchett & Toy 42 www.amttraining.com

AMTM1406 - Modeling.indd 42 2/10/2017 1:34:15 PM

 Projecting the balance sheet

Equity
Equity should be calculated using a BASE account in the
calculations area of the model. It is not necessary to separately
project the different equity accounts shown on the balance sheet in
the published financial statements. Most financial professionals will
project equity as a single line item. The equity BASE analysis should
include the following line items:

Line item Source

Net income Linked from the income statement
Dividends Linked from the income statement
Share issuance / (repurchases) Linked from the assumptions sheet

To derive equity, follow these steps:

1. Enter the following line items in the calculations area of your

model:

2. Link the equity balance from the last historical year of the
balance sheet into the ending equity balance of the equity
schedule.

3. Link the beginning equity balance of the first projected year to

the ending equity balance of the previous year.

© Adkins Matchett & Toy 43 www.amttraining.com

AMTM1406 - Modeling.indd 43 2/10/2017 1:34:15 PM

 Projecting the balance sheet

4. Link the net income from the first projected year of the income
statement into the first projected year of the equity schedule.

5. Link the dividends from the first projected year of the income
statement into the equity schedule.

6. Calculate the ending balance in the equity schedule.

7. Copy each line item in the equity schedule across the

worksheet.

© Adkins Matchett & Toy 44 www.amttraining.com

AMTM1406 - Modeling.indd 44 2/10/2017 1:34:16 PM

 Projecting the balance sheet

8. Link the equity ending balance into the balance sheet.

Debt
Short-term and long-term debt should be projected in a separate
debt area of the model using the debt amortization information
disclosed in the company’s annual report. The resulting table is
commonly referred to as a debt schedule. While the company may
give guidance on the maturity of the loans, many corporates choose
to re-finance loans at maturity, so we do not necessarily forecast the
reduction in long-term debt where the corporate is likely to maintain
current debt levels. The following illustration is an example of a
typical debt schedule:

Debt assumptions
You should add a debt area to the assumptions area of your
model. This will contain all the debt repayment and interest rate
assumptions, which you will use when building the debt schedule. It
should look similar to the one below:

© Adkins Matchett & Toy 45 www.amttraining.com

AMTM1406 - Modeling.indd 45 2/10/2017 1:34:16 PM

 Projecting the balance sheet

Short-term debt
To model the short-term debt, enter the following line item headings
at the top of the debt sheet.

Ignore the revolver and interest expense for now, which will be
completed later on. To complete the “other-short-term debt”, follow
these steps

1. Link the historical “other short term debt” balance from the
balance sheet into the debt schedule.

2. Link the projected “other short-term debt” balances from the

assumptions sheet into the debt schedule and copy across.

3. Do not model the short-term debt interest now. You will do this
later on.

4. Link the projected short-term debt balances from the debt

schedule into the balance sheet.

© Adkins Matchett & Toy 46 www.amttraining.com

AMTM1406 - Modeling.indd 46 2/10/2017 1:34:16 PM

 Projecting the balance sheet

Long-term debt
To model the long-term debt, enter the following line item headings
to the debt sheet

To complete the “long-term debt” section of the debt sheet, follow

these steps

1. Link the historical debt balance from the balance sheet into the
debt sheet.

2. Link the beginning debt balance to the historical balance.

3. Link the long-term debt repayment from the assumptions sheet

into the debt sheet.

© Adkins Matchett & Toy 47 www.amttraining.com

AMTM1406 - Modeling.indd 47 2/10/2017 1:34:16 PM

 Projecting the balance sheet

4. Calculate the ending long-term debt balance and copy across.

5. Do not model long-term debt interest at this stage. You will do

this later on.

6. Link the long-term debt balance into the balance sheet and
copy across.

© Adkins Matchett & Toy 48 www.amttraining.com

AMTM1406 - Modeling.indd 48 2/10/2017 1:34:17 PM

 Balancing the balance sheet

Balancing the balance sheet

Balance check
You should add a balance check line under the balance sheet
before attempting to balance the model. This line will contain an
IF statement that either returns the text “OK” if the balance sheet
balances or returns the difference if the balance sheet does not
balance. To add the balance check, follow these steps:

1. Enter the Balance Check line item under the balance sheet as
shown below.

2. Enter an IF statement in the first historical year that checks to

see if the balance sheet balances.

In the example above, cell D15 contains the total assets value
and cell D30 contains the total liabilities and equity value.
Make sure the historical balance sheet balances at this stage.
If it does not, check the historical balance sheet inputs.

3. Copy the IF statement across all historical and projected years.

The balance sheet will not balance in the projected years at
this stage.

As mentioned above, the projected years of the balance sheet

do not balance yet. This will be addressed by building the cash
flow statement.

© Adkins Matchett & Toy 49 www.amttraining.com

AMTM1406 - Modeling.indd 49 2/10/2017 1:34:17 PM

 Balancing the balance sheet

Cash flow statement

The cash flow statement is the key to balancing the balancing sheet.
It is always a reconciliation of the effect on cash of the difference
between the beginning and ending balance sheet. A cash flow used
for an annual report generally contains 4 key areas:

Cash Flow from Operating Activities: Cash flow from operating

activities are those which arise as a result of day-to-day operating
activities, such as making sales, purchasing supplies, paying bills
to suppliers and paying salaries. Operating cash flow also includes
interest income and interest expense.

Cash Flow from Investing Activities: Cash flow from investing

activities are those which arise as a result of investments in the
future growth of the business. Capital expenditure and investments
in stocks and bonds of other companies are examples of investing
cash flows.

Cash Flow from Financing: Cash flow from financing includes

any cash flows relating to financing the business through debt
and equity. Paying dividends, repaying debt, issuing debt and
raising cash through issuing equity and repurchasing shares are all
examples of financing cash flows.

Interest expense is included in the operating cash flow because it is

part of net income. The operating cashflow starts with net income.
Some companies reporting under IFRS will move interest expense
from operating cashflow to financing cash flow. This is not allowed
under US GAAP.

Net Cash Flow: Net cash flow is the sum of cash flows from
operations, cash flows from investing and cash flows from financing.
It is added to the previous year’s cash on the balance sheet to arrive
at the ending cash balance for the current year after all operating,
investing and financing decisions for the year have been accounted
for.

Don’t worry!
Four rules of cash
Your balance sheet There are four rules of cash. These are as follows:
is not meant to
balance at this Rules Example
stage. Assets  Cash  Capital expenditure would increase
assets and result in a cash outflow.
Assets  Cash  A customer paying an outstanding
invoice would cause receivables
to decrease and would increase
cash.
L&E  Cash  Issuing debt or equity would cause
liabilities and equity to increase
and cash to increase.
L&E  Cash  Paying back debt or repurchasing
shares would cause liabilities and
equity to decrease and cash to
decrease.

© Adkins Matchett & Toy 50 www.amttraining.com

AMTM1406 - Modeling.indd 50 2/10/2017 1:34:17 PM

 Balancing the balance sheet

Mark up the balance sheet

Before building the cash flow statement, you should mark up the
balance sheet to help you identify which type of cash flow each
balance sheet account is associated with. The Cash and Revolver
lines do not get a label as these will be forecasted as the output
from the cash flow statement. To do this, place an “O”, “I” or “F”
next to every single balance sheet account depending on whether
the account in question will give rise to an operating cash flow, an
investing cash flow or a financing cash flow, respectively. As you
build the cash flow relating to each account on the balance sheet,
you will gradually remove the marks. This will ensure that you don’t
miss any balance sheet accounts when building the cash flow, which
would cause the balance sheet not to balance. In the balance sheet
below, these marks have been input in column K.
Quickly hide
columns!

Notice that the

columns containing
the historical
years are hidden.
This can be done
quickly by selecting
the cells across
the columns and
pressing CTRL + 0.
To unhide columns,
press CTRL +
SHIFT 0.

Some balance sheet accounts are marked up with more than

one letter. This is because the drivers of the account in question
will appear in different sections of the cash flow. For example,
the drivers of PP&E are capital expenditure and depreciation,
which appear in the investing and operating parts of the cash flow
statement respectively. Also the drivers of equity are net income,
dividends and share issuance / (repurchases). Net income appears
in the operating part of the cash flow and dividends and share
issuance / (repurchases) appear in the financing part of the cash
flow.

© Adkins Matchett & Toy 51 www.amttraining.com

AMTM1406 - Modeling.indd 51 2/10/2017 1:34:17 PM

 Balancing the balance sheet

Net PP&E: Net PP&E is derived using a BASE account in the

calculations area of the model. Two components cause this change,
capital expenditure and depreciation. Capital expenditure is an
investing cash flow while depreciation affects the operating cash
flow.

Equity: The equity account is also derived using a BASE analysis

in the calculations area of the model. There are three components
that cause equity to change. These are: Net income, Dividends
and Share issuance / (repurchases). Net income goes in the
operating section of the cash flow. Dividends and share issuance /
(repurchases) go in the financing section of the cash flow.

Modeling cash flow from operations

Net income
When modeling cash flow from operations, the first line item on your
cash flow should be Net income. This should be linked from the
income statement or the Cals sheet. You can now remove the “O”
mark from the equity line item on the balance sheet.

© Adkins Matchett & Toy 52 www.amttraining.com

AMTM1406 - Modeling.indd 52 2/10/2017 1:34:17 PM

 Balancing the balance sheet

Non-cash items
Non-cash items should be added back, such as deprecation and
amortization. Both depreciation and amortization can be linked in
from the income statement (depreciation can also be linked from the
Calcs sheet). Even if depreciation is shown as a negative number
in the income statement, it should appear as a positive number in
the cashflow as it’s being added back as a non-cash expense. Now
remove the “O” mark from PP&E and the “O” mark from intangibles.

Operating working capital

Now you must include the cash effect of changes in operating
working capital. Rather than having separate line items on the cash
flow for all the operating working capital accounts, it is common
practice to calculate operating working capital in the calculations
area of the model. To calculate operating working capital:

1. Enter the operating working capital line items in the

calculations area of the model.

2. Link the operating current asset accounts to the balance sheet.

© Adkins Matchett & Toy 53 www.amttraining.com

AMTM1406 - Modeling.indd 53 2/10/2017 1:34:17 PM

 Balancing the balance sheet

3. Calculate total operating current assets.

4. Link the operating current liabilities to the balance sheet.

5. Calculate total operating current liabilities.

Time saving
tip!

Rather than typing

the individual
operating working
capital headings,
you can link them to
the balance sheet. 6. Calculate operating working capital by deducting operating
As long as your current liabilities from operating current assets.
model has matrix
integrity, you can
then copy these
across and down,
and the appropriate
operating working
capital numbers will
appear.

© Adkins Matchett & Toy 54 www.amttraining.com

AMTM1406 - Modeling.indd 54 2/10/2017 1:34:18 PM

 Balancing the balance sheet

Now that you have calculated operating working capital, you must
calculate the change in operating working capital in the cash flow
statement.

In order to calculate operating working capital, you must take last

year’s operating working capital balance minus this year’s operating
working capital balance in order to achieve the correct sign in the
cash flow. In the example above, as operating working capital
increases from Year 1 to Year 2, cash will decrease in Year 2.

Now you can remove the “O” marks from all the operating working
capital line items on the balance sheet!

Other operating accounts

You must include in the cash flow statement any other accounts that
would give rise to operating cash flows. In the example used in this
document there are three such accounts: other long-term assets,
other long-term liabilities and deferred income taxes. In this example
we are assuming that other long-term assets are operating items.
Always check the footnotes of the published financial statements to
determine which categories to put these items into.

The ∆ symbol

To add the change

symbol (∆) to
your cash flow
statement, type
the letter D and
change the font
of the character
to Symbol. Make
sure you select
the individual D
character within the
cell before applying
the Symbol font
style.

© Adkins Matchett & Toy 55 www.amttraining.com

AMTM1406 - Modeling.indd 55 2/10/2017 1:34:18 PM

 Balancing the balance sheet

To calculate the cash flow impact of long-term assets you must

deduct last year’s balance from this year’s balance.

Quick sum! To calculate the cash flow impact of liability accounts, you must
deduct this year’s balance from last year’s balance.
To quickly SUM
operating cash flow, Net cash flow from operations
select the cell to
contain the SUM To calculate net cash flow from operations, simply add up the
formula (in this operating cash flows as shown below:
case, cell F10) then
hold down ALT and
press the equals
[=] key. Check the
SUM range and
press ENTER.

Cash flow from investing

The cash flow from investing area of the cash flow should include
all cash flows resulting from investing activities. These normally
include:

■■ Capital expenditure
■■ Purchase of intangibles
■■ Proceeds from sale of fixed assets

Capital expenditure and other long-term assets are used in the

model illustrated in this document.

Capital expenditure is an investing cash flow. It should be linked to

the calculations area of the model and should be a negative number
as it represents a cash outflow. To ensure capital expenditure
appears as a negative number, add a minus sign before the formula.

© Adkins Matchett & Toy 56 www.amttraining.com

AMTM1406 - Modeling.indd 56 2/10/2017 1:34:18 PM

 Balancing the balance sheet

Once you have included all the investing cash flow accounts,
calculate total cash flow from investing as shown below:

Remember to remove the “I” marks from the investing cash flow
accounts.

Cash flow from financing

The cash flow from financing section should include all those cash
flows resulting from the financing of the business. These normally
include:

■■ Issuance or repayment of debt

■■ Issuance or repurchase of equity
■■ Dividends

To derive the cash flows from financing activities, simply calculate

the change in the relevant balance sheet accounts, removing the “F”
marks as you do them.

Let’s take long-term debt as an example. In the model illustrated in

this reference guide, long-term debt decreases from 877.9 in Year 0
to 827.9 in Year 1. This gives rise to a cash outflow of $50m as debt
is being repaid. Therefore the formula to calculate cash flow arising
from long-term debt is:

© Adkins Matchett & Toy 57 www.amttraining.com

AMTM1406 - Modeling.indd 57 2/10/2017 1:34:18 PM

 Balancing the balance sheet

Share repurchases should be linked to the treasury stock line on

the balance sheet area and should be a negative number as they
represent a cash outflow.

Dividends should be linked to the retained earnings BASE analysis

in the calculations area of the model. They should be a negative
number as they represent a cash outflow.

Once you have included all the financing cash flows, calculate total
cash flows from financing as follows:

Net cash flow

Net cash flow is the sum of cash flow from operations, cash flow
from investing, and cash flow from financing.

© Adkins Matchett & Toy 58 www.amttraining.com

AMTM1406 - Modeling.indd 58 2/10/2017 1:34:19 PM

 Balancing the balance sheet

Excess cash / revolver

Sometimes the company you are modeling will generate more cash
than it requires. Your model should allow for this by including an
excess cash account. If the company does not generate enough
cash and requires additional funding, your model can address this
through a Revolver account. The excess cash account and the
revolver account provide essential information about the financing
requirements of the business. They are also key to making the
balance sheet balance.

The excess cash line item should be added to the current assets
section of the balance sheet. When doing this, make sure that you
adjust your total current assets SUM function otherwise the balance
sheet will not balance!

The revolver should be added to the current liabilities section of the

balance sheet. Once again, when doing this, make sure you adjust
the total current liabilities SUM function to include the revolver in
total liabilities.

Calculating excess cash / revolver

To calculate whether the company generates excess cash or has an
additional funding requirement (revolver), follow these steps:

1. Input the following line items under the cash flow.

© Adkins Matchett & Toy 59 www.amttraining.com

AMTM1406 - Modeling.indd 59 2/10/2017 1:34:19 PM

 Balancing the balance sheet

2. Link the excess cash / revolver into the ending latest historical
year as shown below. If the model also has an opening
revolver balance, the model will not balance in the projected
years unless you deduct revolver from opening cash as shown
below:

3. Link the beginning excess cash / revolver of the first projected

year to the excess cash / revolver of the previous year.

4. Link the net cash flow line item from the cash flow statement.

5. Calculate the ending excess cash / revolver balance.

© Adkins Matchett & Toy 60 www.amttraining.com

AMTM1406 - Modeling.indd 60 2/10/2017 1:34:19 PM

 Balancing the balance sheet

6. Copy the entire cash flow column across. In the worksheet

above, you should copy column F across the remaining
projected years.

Once you have completed your cash flow as described above, the
excess cash / (revolver) number should equal the Balance Check
line on the balance sheet.
Balance sheet extract

Cash flow statement extract

Balancing the balance sheet

As already mentioned, the excess cash / (revolver) line on the
cash flow statement provides the key to making the balance sheet
balance. A positive number on this line represents excess cash and
must be linked into the excess cash line on the balance sheet. A
negative number on this line represents a revolver and should be
linked into the revolver line on the balance sheet. The most effective
way to achieve this is by using Excel’s MAX and MIN functions.

The MAX function returns the highest number in a range and the
MIN function returns the lowest number in a range. This is illustrated
below:

© Adkins Matchett & Toy 61 www.amttraining.com

AMTM1406 - Modeling.indd 61 2/10/2017 1:34:19 PM

 Balancing the balance sheet

The excess cash line on the balance sheet should contain a MAX
function that has the excess cash / (revolver) number on the
cash flow and zero as its arguments. The MAX function will return
the higher of these two numbers. Therefore, if the excess cash /
(revolver) number on the cash flow is positive, the MAX function will
return this positive number as it is higher than zero. However, if the
excess cash / (revolver) number on the cash flow is negative, the
MAX function will return zero since zero is higher than the negative
number.

© Adkins Matchett & Toy 62 www.amttraining.com

AMTM1406 - Modeling.indd 62 2/10/2017 1:34:19 PM

 Balancing the balance sheet

Likewise, the revolver line on the balance sheet should contain a

MIN function that has the excess cash / (revolver) number on the
cash flow, and zero as its arguments. The MIN function will return
the lower of these two numbers. Therefore, if the excess cash /
(revolver) number is negative, the MIN function will return this
negative number. If the excess cash / (revolver) number is positive,
the MIN function will return zero as zero is the lower of the two
numbers. As the MIN function will return the revolver as a negative
number on the balance sheet, you must multiply the MIN function by
minus one (=MIN(CashFlow!F27,0)*-1). This is shown in the image
below:

Now your balance sheet should balance!

© Adkins Matchett & Toy 63 www.amttraining.com

AMTM1406 - Modeling.indd 63 2/10/2017 1:34:19 PM

 Balancing the balance sheet

© Adkins Matchett & Toy 64 www.amttraining.com

AMTM1406 - Modeling.indd 64 2/10/2017 1:34:19 PM

 Interest income and interest expense

Interest income and interest expense

The final step in creating a fully integrated model is adding interest
income and interest expense to the income statement.

Methods of interest calculation

There are two ways to calculate interest income and expense in a
model. These are:

■■ Multiply last year’s debt or cash balance by the interest rate

■■ Multiply the average of last year’s and this year’s debt or cash
balances by the interest rate

Both methods have their advantages and disadvantages. These are

outlined below:

Inaccurate interest calculations

Using last year’s debt balance instead of an average of last year
and this year’s debt balance often leads to inaccurate interest
calculations. This is especially the case if the debt or cash balance
varies a lot from year to year. The following example will help to
explain this:

Debby’s debt

Debby’s debt balance as at December 31st Year 0 was $100. On

March 31st Year 1, she repaid $75 of this debt. Her debt balance as
at December 31st Year 1 was therefore $25. The interest rate on her
debt is 10%.

If we calculate Debby’s interest using last year’s debt, we get $10

($100 * 10%). If we calculate Debby’s interest using this year’s debt
we get $2.50 ($25 * 10%). If we calculate Debby’s interest using an
average of last year’s and this year’s debt we get $6.30 ((100+25) /
2 *10%).

If we use last year’s debt balance, our interest calculation is too

high. If we were to use this year’s debt balance, our interest
calculation would be too low. An average of the two debt balances,
while not 100% accurate, gives us a much better representation of
the amount of interest expense. The same applies to cash balances
and interest income.

© Adkins Matchett & Toy 65 www.amttraining.com

AMTM1406 - Modeling.indd 65 2/10/2017 1:34:19 PM

 Interest income and interest expense

Circularity
Using an average debt or cash balance in a model makes the model
circular.

The diagram below indicates why the model is circular. Interest

feeds into net income on the income statement, then net income
feeds into the cash flow; cash flow in turn determines the cash / debt
balance for the year; and finally the cash / debt balances determine
the interest income or expense. In other words, interest determines
the excess cash / revolver balance, while the excess cash / revolver
balance also determines interest.
Interest

Net Income Cash / Debt

Cash Flow

Circularity in Excel can be problematic if not handled correctly.

Therefore, some people prefer to use last year’s debt balance as
this method of interest calculation does not cause circularity. The
logic here is that even though this method can lead to inaccurate
interest calculations, given the likely errors in predicting sales and
other variables, the additional error caused by a failure to determine
interest income or expense accurately is usually not that important!

Interest rates
The assumptions sheet should contain the interest rates used in the
model. These interest rates will be linked into the debt schedule of
the model.

Also remember to include the interest rate on cash in the

assumptions sheet.

© Adkins Matchett & Toy 66 www.amttraining.com

AMTM1406 - Modeling.indd 66 2/10/2017 1:34:20 PM

 Interest income and interest expense

Calculating interest expense

Interest expense should be calculated on revolver balances and
long-term debt balances using average balances or beginning
balances.

Remember, average balances provide a more accurate

representation of interest because we assume the debt repayments
or cash flows happen mid-year, but the model becomes intentionally
circular.

Beginning balances assume debt repayments and cash flows

happen at the end of the year, but the model is not circular.

Be consistent with your interest calculation methodology in your

model: if you decide to use average balances, all of your interest
income and expense calculations should be calculated this way; if
you decide to use beginning balances, all of your interest income
and expense calculations should be calculated this way.

To calculate interest expense on revolver balances:

1. Add the following line items at the top of the debt schedule:

2. Link the revolver balance from the balance sheet into the debt
schedule and copy across.

3. Link the interest rate from the assumptions sheet into the debt
sheet and copy across.

4. Calculate interest using average balances or beginning

balances and copy across. In the illustration below, we are
using average balances. If you decide to use beginning
balances, the formula in cell F6 should be =F5*E4, which
should then copy across.

© Adkins Matchett & Toy 67 www.amttraining.com

AMTM1406 - Modeling.indd 67 2/10/2017 1:34:20 PM

 Interest income and interest expense

Also calculate interest on other short-term debt balances as shown

below. Again, the illustration below uses average balances. If you
decide to use beginning balances, the formula in cell F10 should be
=F9*E8.

To calculate interest on long-term debt balances, link the interest

rate from the assumptions sheet and calculate interest using
average or beginning balances. Once again, the illustration as
shown below uses average balances. If you are using beginning
balances, the formula in cell F17 should be =F16*E15.

Debt summary
Add a debt summary for debt balances and interest expense
calculations. Simply link the debt balance numbers and interest
expense numbers to the debt summary.

Calculating interest income

Interest income should be calculated on cash balances. To calculate
interest income:

1. Link operating and excess cash from the balance sheet into
the debt sheet, calculate total cash and copy across.

© Adkins Matchett & Toy 68 www.amttraining.com

AMTM1406 - Modeling.indd 68 2/10/2017 1:34:20 PM

 Interest income and interest expense

2. Link the interest rate from the assumptions sheet and copy
across.

3. Calculate interest income using average cash balances

or beginning cash balances. The calculation method you
choose here should be consistent with your interest expense
calculation method.

The illustration below uses average cash balances. If you are

calculating interest using beginning balances, the formula in
cell F37 should be =F36*E35.

Linking interest into the income statement

Now you are at the final stage of creating your model: feeding the
interest expense and interest income into the income statement.

Last year method

If you calculated interest expense and interest income using the last
year (beginning balances) method, you can simply link the interest
items into the income statement and the model is complete!

In the illustration below, we have linked interest income from row 37

of the Debt sheet and we have linked interest expense from row 30
of the Debt sheet.

© Adkins Matchett & Toy 69 www.amttraining.com

AMTM1406 - Modeling.indd 69 2/10/2017 1:34:21 PM

 Interest income and interest expense

Average method
If you calculated interest using the average method, as soon as
you link one of the interest items into the model, the following error
message will appear:

This is telling you that the formula you are about to create is circular
and Excel does not automatically calculate formulas that are
circular! Choose cancel to clear the dialog box. You will notice that
the interest is now set at zero.

This error message will not appear if your iterations settings are
activated. Iteration settings can be activated or deactivated in the
Formula section of the Excel Options dialog box. The Excel Options
dialog box can be opened by selecting File (Office button for Excel
2007), Options.

In order for Excel to calculate interest income and expense, the

iteration settings must be activated. To do this:

1. Choose File (Office button for Excel 2007) then Options from
the ribbon. The following dialog box is displayed.

2. Choose the Formulas tab.

© Adkins Matchett & Toy 70 www.amttraining.com

AMTM1406 - Modeling.indd 70 2/10/2017 1:34:22 PM

 Interest income and interest expense

3. Activate the Iteration check box.

4. Choose OK.

Once you have done this, you can add both interest items to the
income statement.

In the illustration below, we have linked interest income from row 37

of the Debt sheet, and we have linked interest expense from row 30
of the Debt sheet.

A problem with circular models

One of the main reasons why people do not like working with circular
models is that they are easy to “blow up”! In other words, such
models can become infected with error values such as #REF or
#VALUE, which appear to be impossible to resolve. Circular models
get into this state for a number of reasons. Accidentally deleting
a row that a formula is dependent on is one reason. Accidentally
typing text into a cell that a formula is dependent on is another
reason. Even if you catch yourself making the error, when you use
the famous undo key (CTRL + Z), the model remains in error state.
The best way to resolve this error state is by fixing the problem that
caused the error in the first place, and then deleting the interest
lines on the income statement and reintroducing them. To see this
working, follow these steps:

1. Go into the income statement and type some text on one of the
projected sales lines.

© Adkins Matchett & Toy 71 www.amttraining.com

AMTM1406 - Modeling.indd 71 2/10/2017 1:34:22 PM

 Interest income and interest expense

This introduces an error into the model as the formulas cannot

calculate text.

2. Now press CTRL + Z to undo what you just did in order to

remove the text from the projected sales line. Alternatively,
recreate the revenue growth formula.

Everything above the interest line items goes back to normal,

but the formulas dependent on interest income and expense
are in error state.

3. Delete the interest income and interest expense lines in the

income statement for the projected years.

The error values disappear!

4. Now undo (CTRL + Z) to reintroduce the interest line items.

The model is no longer in error state!

© Adkins Matchett & Toy 72 www.amttraining.com

AMTM1406 - Modeling.indd 72 2/10/2017 1:34:23 PM

 Interest income and interest expense

The ISERROR function

The ISERROR function provides a very convenient and user-friendly
solution to the error state problem. It saves you from having to run
through the steps outlined above. This is especially useful to another
user of your model, who may not be very Excel literate!

The ISERROR function returns TRUE if it finds an error and FALSE

if it does not find an error. When combined with the IF function, it is a
very useful formula.

An integrated model uses the IF and ISERROR functions to return

zero if an interest formula goes into error state or to calculate
the formula if the interest formula is not in error state. To add this
functionality to your model, simply add the ISERROR function to the
interest income and interest expense lines on the income statement
as shown below:
Interest income

Interest expense

With the ISERROR function included, the model will never go into
error state. Something that would normally cause the model to
go into error state will simply cause the interest line items on the
income statement to zero out. Once the problem is rectified, the
model will calculate interest income and expense as normal.

© Adkins Matchett & Toy 73 www.amttraining.com

AMTM1406 - Modeling.indd 73 2/10/2017 1:34:23 PM

 Interest income and interest expense

© Adkins Matchett & Toy 74 www.amttraining.com

AMTM1406 - Modeling.indd 74 2/10/2017 1:34:23 PM

 And finally

And finally
Congratulations!
Congratulations! You should now have a fully integrated model. You
may now wish to add a ratios sheet to help check the validity of your
assumptions. It is very important that you stress test the model to
check its integrity.

Ratios
A typical ratios sheet will have the following types of ratios:

■■ Growth
■■ Profitability
■■ Asset efficiency
■■ Leverage

An example of a ratios sheet using the average balance version of

the model used in this book is provided below:

Stress testing the model

In order to stress test a model, change each assumption in the
model line-by-line and make sure the results of the model update as
you expect. This is a time consuming job! However, it’s essential you
do this to make sure the model is accurate!

When stress testing the model, change each assumption in

isolation. For example, change the sales growth assumption and at
a minimum check the following:

■■ What happens to net income?

■■ What happens to cash?
■■ Does the balance sheet still balance?

Does the model react as you expect it to? If it does not, you need
to investigate what is going on and amend or fix the model. Ideally
you will stress test each assumption both ways. Using the example
above, what happens if sales growth increases? And what happens
if sales growth decreases.

Once again, this is a time consuming job, but arguably one of the
most important parts of the model building process! Now go and
stress test your model!

© Adkins Matchett & Toy 75 www.amttraining.com

AMTM1406 - Modeling.indd 75 2/10/2017 1:34:23 PM

 And finally

Beauty saving
Beauty saving is a simple trick to make sure that a user of your
model will open it up to the beginning of each work sheet. All you
have to do to beauty save is select cell A1 of each worksheet, select
the first worksheet, and then save the model.

Modeling standards
There are a number of standards you should adhere to when
building your model. These standards demand that models be:

■■ Accurate
■■ Flexible
■■ User friendly

This section contains some practical advice you should follow in

order to adopt these standards:

Accurate: always proof read your model. Make sure you sense
check the results. For example, if inventory increases from $100m to
$900 for no apparent reason, check that you have not made an error
when writing a formula.

Flexible: never use hard numbers in your model. Always make sure
that your assumptions are in the assumption area of the model so
that you can easily change them if necessary.

User-friendly: break complex calculations down into steps. This will

save you from having to write huge formulas that people will not be
able to understand!

© Adkins Matchett & Toy 76 www.amttraining.com

AMTM1406 - Modeling.indd 76 2/10/2017 1:34:23 PM