between the proceeds of the common stock issued and the par
value of the common stock. In our modeling, we can simplify
this by showing the two accounts together as Common stock and
Preferred Stock This is another kind of stock that has priority in
dividends and has priority claims on assets if a company is in
liquidation. Preferred stock pays preferred dividends.
Treasury Stock This is stock that has been issued but has been
later reacquired by the company. There are several reasons that a
company may do so: (1) To help keep the stock price high, (2) to
acquire shares for distribution to employees under bonus plans,
(3) to avoid takeovers by an outside party that is buying up
shares in the open market.
Retained Earnings This is capital that increases through earn-
ings. This is the connection point between the income statement
Income Statement and Balance Sheet Accounts 153
and the balance sheet: the net income after dividends flows into
Other Equity Account It is useful to have an extra account. This
is for listing such item as Translation gains or losses, or, if your
model is using non-U.S. settings, for certain equity reserves.
This page intentionally left blank.
In Chapter 7, we went over how to make the balance sheet
balance, and in Chapter 8, the types of accounts in the income
statement and the balance sheet.
In this chapter, we will put everything together in order to
build a working financial model. This will be a model that is
more advanced than the one we created in Chapter 7. But in the
scheme of things, it is still a simple model: a basic income statement,
balance sheet, and cash flow statement, with just the â€˜â€˜typicalâ€™â€™
accounts that you would see in a financial report. We will lay out
the framework to get a projection model â€˜â€˜up and running.â€™â€™
STRUCTURE OF OUR MODEL
The model structure that we will be using will consist of:
An â€˜â€˜Inputâ€™â€™ sheet
An â€˜â€˜Income Statementâ€™â€™ output sheet
A â€˜â€˜Balance sheetâ€™â€™ output sheet
A â€˜â€˜Cash flowâ€™â€™ output sheet
We will be entering our historical data and forecast
assumptions in the â€˜â€˜Inputâ€™â€™ sheet only, and having the other
three sheets show the results.
As noted before, this structure is just one of the virtually limit-
less number of modeling approaches that you can have for creating
a financial projection model. Generally, there are two main app-
roaches to building a model if we want to organize the inputs and
outputs: (1) to put both the input and the output on one sheet and
(2) to separate the input and the output onto separate sheets.
However, I propose a third way in which the input sheet of the
model contains both rows for inputs and rows to show the results.
There still will be the output sheets, but these will read the results
already calculated in the input sheet.
Put Inputs and Outputs on One Sheet
Doing it this way can lead to a very long sheet, with the input
assumptions block at the top, and the income statement, balance
sheet, and other sections arrayed below. This is best for a small
model, where a quick Page Down or Page Up will get you to
where you want to go. It gets a little more tedious when there are
many hundreds of rows.
Put Inputs and Outputs on Separate Sheets
Assumptions Income statement Balance sheet Cash flow, etc.
Putting Everything Together 157
Here, the input assumptions are on one sheet, typically on the
first of many sheets, and the output sheets follow after that. With
this approach, we can lay out the different statements and other
outputs such as the ratios on different sheets, with each sheet
tab identified accordingly. This approach makes more sense with
Put Inputs/Outputs First, Followed by
Pure Output Sheets
Input sheet for Income statement Balance sheet (reads Cash flow, etc.
assumptions and (reads Input sheetâ€™s Input sheetâ€™s output
output results output results for the results for the balance
income statement) sheet)
The first two approaches lack something from a usability
point of view. It would be nice to enter the input and then, instead
of tapping away from that particular block to see the output, to
be able to see the output nearby, one or two rows away. This
way, the feedback of your inputs is immediate and near, and it
gives you a sense of whether it is correct or not.
This third approach is possible if we interleaved the inputs
and outputs of the individual accounts first, as follows:
Row 1: Inputs for revenues
Row 2: Output for revenues
Row 3: Input for COGS
Row 4: Output for COGS
Once we have this block of inputs/outputs done, then
organizing the outputs is a simple matter of referencing only the
finished output lines. In fact, we still have a choice of organizing
the final numbers for either an â€˜â€˜all on one sheetâ€™â€™ or â€˜â€˜separate sheets
for separate statementsâ€™â€™ presentation. One major advantage of the
interleaved approach is that you can write and check your formulas
very easily since the inputs and outputs are next to each other.
Moreover, as you write the formulas, there is no need to tap
PageUp/PageDown or CtrlÃ¾PageUp/CtrlÃ¾PageDown across
This third approach is the one that will be used here,
because we want to be able to:
Easily check the results of our inputs
Show historical interrelationships in a meaningful way
Format the input for maximum clarity and ease of use,
and format the output for maximum visual polish in the
Some other features we want to have:
To input the historical data in columns, and once that is
done, to continue in the same rows with entering forecast
numbers or assumptions.
To be able to specify forecast numbers in two general
forms: in hard-coded numbers or as other inputs such
as percent growth or percent of sales, among other
metrics. We would need the former because we may
need to use the model to replicate another modelâ€™s
forecasts exactly, number for number. In the latter case,
the model should do the calculation to produce the final
The number of historical and forecast periods can also be
flexible. We can have 5 years of historical data, followed by 10
years of projections; or the reverse; or whatever combination,
even all historical and all projected (although the latter should
have at least one year of historical data to start with). We can
make the model as flexible as possible on this account, but for
our model-building purposes at this stage, letâ€™s set the model to
have three historical periods, followed by three forecast periods.
If you want to increase the forecast horizon, copy out the last
columns on each sheet.
Putting Everything Together 159
WHAT WE WANT TO GET TO
Here are several screens that show the model that we want to
end up with. These have been laid out so that you can recreate
them on your own. In fact, for the best learning experience, I
encourage you to create for yourself the model being illustrated
here. Not only will you get a hands-on experience of developing
a model, but you will also have a fully functioning model by the
end of the exercise.
In the section that follows, we will go over the model, row
We are laying out the rows on the sheet that is called
â€˜â€˜Input.â€™â€™ So open a new workbook in Excel, pick a new sheet,
and then rename that sheet â€˜â€˜Inputâ€™â€™ by double-clicking on the
sheet tab. When the sheet tab becomes highlighted, simply type
the name onto it. Press â€˜â€˜Enterâ€™â€™ when you are done.