3-STATEMENT MODEL
The Ultimate Guide
Key Learning Points
A 3-statement model forecasts a company’s income
statement, balance sheet, and cash flow statement by
linking them.
3 statement models are built in Excel and typically the
income statement is created first, followed by the
balance sheet and then the cash flow statement.
The aim of a financial model is to predict a company’s
profitability, financial position, and cash generation.
Building a 3-statement model improves the accuracy of
forecasting because a change in one financial
statement will result in adjustments to the others.
The cash flow statement helps forecast cash and short-
term borrowings and is an important step in linking the
three statements.
The final step is to calculate interest expenses and
include them in the income statement.
9 Steps in Building a
3-Statement Model
Input the historical data
Calculate ratios and statistics
Decide on forecast assumptions
Build forecast I/S except for interest
Build the forecast B/S except for cash and revolver
Build the C/F statement using the rules of cash
Use max/min to fill in B/S cash and revolver
Build the interest calculations
Link interest into the I/S and deal with circular references
Building a 3-Statement Model
1) Input the historical data:
Start by inputting historical data for the income
statement and balance sheet.
2) Calculate ratios and statistics:
These are calculated using the historical data to help
understand historical performance and business drivers.
3) Decide on forecast assumptions:
Use the ratios and statistics to create forecast
assumptions.
4) Build forecast income statement except for interest:
Forecast each income statement using the
assumptions.
However, we can’t forecast interest income and expense
at this point as the cash and debt balances haven’t yet
been forecast
Building a 3-Statement Model
5) Build the forecast balance sheet except for cash and
revolver:
Forecast each balance sheet item using the
assumptions. However, we can’t forecast cash and the
revolver at this point as this relies on the forecast cash
flow statement.
6) Build the cash flow statement using the rules of
cash:
Forecast the cash flow statement using the forecast
income statement and balance sheet and the rules of
cash. We calculate ‘cash net of revolver’ at the bottom of
the cash flow statement – effectively treating a revolver
as a negative cash balance.
Our rules of cash are:
An increase in assets results in a cash outflow
A decrease in assets results in a cash inflow
An increase in liabilities or equity results in a cash
inflow
A decrease in liabilities or equity results in a cash
outflow
Building a 3-Statement Model
7) Use max/min to fill in balance sheet cash and
revolver:
A positive ‘cash net of revolver’ balance is included in the
balance sheet as cash (with a zero revolver balance).
While a negative ‘cash net of revolver’ balance is
included in the balance sheet as a revolver (with a zero
cash balance).
We use the Excel max and min function for this:
a. Cash balance = MAX (0, ending cash net of
revolver)
b. Revolver balance = -MIN (0, ending cash net of
revolver)
8) Build the interest calculations:
Interest income is calculated using the forecast cash
balance and interest expense is calculated using the
forecast revolver and long-term debt balances.
Interest income and interest expense are typically
calculated using the average of the opening and closing
balances.
Building a 3-Statement Model
9) Link interest into the income statement and deal with
circular references:
Interest can now be linked into the income statement and
any circular references which arise will need to be resolved.
The interest will lead to some changes in the net income,
which will, in turn, affect the cash flow statement and cash
on the balance sheet.
Assuming we built our model correctly, this should all
balance.
Important: Before sharing your model with your
colleagues, it is important to do a final check:
Check that the output looks reasonable based on
assumptions
Ensure consistency of formatting: formulas black, hard
numbers blue
Is the layout intuitive?
Are complex calculations broken down into steps?
Is the model documented where necessary?
Is the circular switch ON, are iterations ON?
Is the model saved? (Ctrl + Home on every sheet)
Conclusion
A 3-statement model forecasts a company’s income
statement, balance sheet, and cash flow statement by
linking them.
A change in one financial statement will flow through to
the others, acting as a check on the validity of the
forecasts.
The model usually starts with the income statement, then
the balance sheet, and finally the cash flow statement.
The cash flow statement helps forecast cash and short-
term borrowings and is a key step in linking the three
statements.
Short term borrowings are often referred to as a
“revolver” which is an abbreviation for a revolving credit
facility.
It is important to do a final check on the completed
model before sharing it with others.