<<

. 36
( 62 .)



>>




TLFeBOOK
CHAPTER 10


The IS and BS
Output Sheets




Ifwe have followed the steps in Chapter 9 properly, we will
have a fully working model. But it is still in a ˜˜raw™™ state.
This chapter shows how to create the first output sheets to
show the results with greater visual polish.


CREATING NEW OUTPUT SHEETS
Although the name of the sheet is Input sheet, in actuality there is
a fully working model on the sheet. However, it looks quite ugly.
It has the mish-mash of the input and the calculation lines and
lots of extra lines at the bottom for the balancing formulas. This
is not the kind of printouts that you want to put in front of your
audience. Let™s create output sheets that are neater, presentation-
ready, and, yes, more impressive.
At this point, we can create just another sheet that we can call,
say, ˜˜Output,™™ that will show not only the two financial statements
(in a neater format), but also the cash flow statement and perhaps
other sections that you want to add such as ratios. But this may
make the sheet too long and cumbersome to navigate.
Let™s add, instead, several more sheets, with each sheet
containing only one specific section. This chapter will show
you how. Of course, if you wish to have your output as just
one sheet, by all means create the output that way.
193



Copyright © 2004 by John S. Tjia. Click here for terms of use.
TLFeBOOK
Chapter 10
194




ADD FOUR MORE SHEETS
Using Excel™s own commands, add four more sheets and label
them:
IS
u

BS
u

CF
u

Ratios
u


I could have spelled them out as ˜˜Income Statement,™™
˜˜Balance Sheet,™™ and ˜˜Cash Flow,™™ or only partially shortened
them to ˜˜IncStatement,™™ and so on. This is not important.
However, if you have many sheets in a file, the shorter the tab
names, the more tabs you can see across the bottom of the screen.
If you did decide to spell out the names, you should avoid
having spaces in them. If you have spaces in them, Excel
automatically adds single quotes outside sheet tab names with
spaces, so a reference to a sheet with a space in its name
(˜˜Income Statement™™) will appear as ¼˜Income Statement™!A1.
If you are typing in this formula as you build cross-sheet refer-
ences, you must remember to put these single quotes in. So you
will have created the minor, but repeated, inconvenience of
having to put in these quotes.
The IS and BS sheets will have references to the Input sheet,
reading directly off the calculated lines from that sheet. The CF
and Ratios sheets (and other sheets that you may want to add
later on) will read the numbers from the IS and BS. So the Input
sheet feeds only the starting two sheets, and everything else
reads these two sheets. It™s a good way to streamline the flow
of numbers through a model.


THE IS SHEET
The following illustration is what the IS sheet will look like. The
first year™s column (column B) is shown with the formulas in
them. Copy these formulas across all the columns to column G.
To have the titles on this sheet reflect the titles on the Input sheet,
copy the formulas to column A, also.




TLFeBOOK
The IS and BS Output Sheets 195




Since all the numbers have been calculated in the Input
sheet, it is an easy task to simply bring them to this IS output
sheet. As a matter of preference, you can also directly read the
Input numbers for the subtotal lines, such as ˜˜Gross profit™™
or ˜˜EBITDA.™™ In the illustration, these subtotals are calculated
again; this is a good double-check on the accuracy of the original
calculations in the Input sheet.

IS Sheet
A B C D E F G
1 =Input!A1
2
3 Proj Proj Proj
4 INCOME STATEMENT =Input!B4 2001 2002 2003 2004 2005
5
6 Revenues =Input!B8 900.0 1,000.0 1,100.0 1,210.0 1,331.0
7 COGS =Input!B12 490.0 550.0 605.0 665.5 732.1
8 Gross profit =B6-B7 410.0 450.0 495.0 544.5 599.0
9 Gross margin 45.6% 45.0% 45.0% 45.0% 45.0%
=IF(B$6,B8/B$6,0)
10
11 SGA =Input!B18 135.0 150.0 165.0 181.5 199.7
12 Operating expenses =Input!B22 28.0 30.0 33.0 36.3 39.9
13 EBITDA =B8-B11-B12 247.0 270.0 297.0 326.7 359.4
14 EBITDA margin 27.4% 27.0% 27.0% 27.0% 27.0%
=IF(B$6,B13/B$6,0)
15
16 Depreciation =Input!B29 75.0 80.0 85.0 91.8 102.5
17 Amort of intangibles =Input!B32 4.0 4.0 4.0 4.0 4.0
18 EBIT =B13-B16-B17 168.0 186.0 208.0 230.9 252.9
19 EBIT margin 18.7% 18.6% 18.6% 19.1% 19.0%
=IF(B$6,B18/B$6,0)
20
21 Non-oper expenses =Input!B38 10.0 8.0 10.0 11.0 12.0
22 Interest income =Input!B44 5.0 6.0 7.9 9.3 7.1
23 Interest expense =Input!B52 50.0 50.0 47.7 47.7 48.7
24 EBT =B18-B21+B22-B23 113.0 134.0 158.2 181.5 199.3
25 EBT margin 12.6% 13.4% 14.4% 15.0% 15.0%
=IF(B$6,B24/B$6,0)
26
27 Provision for taxes =Input!B58 40.0 47.0 55.4 63.5 69.7
28 Net income =B24-B27 73.0 87.0 102.7 118.0 129.5
29 Net margin 8.1% 8.7% 9.3% 9.7% 9.7%
=IF(B$6,B28/B$6,0)
30
31 Dividends =Input!B64 12.0 11.0 10.3 11.8 13.0
32 Net to retíd earnings =B28-B31 61.0 76.0 92.6 106.2 116.6
33
IS




TLFeBOOK
Chapter 10
196




THE BS SHEET
The BS sheet follows the same approach. With most of the work
already done in the Input sheet, laying out this sheet is also a
fairly simple task.


BS Sheet
A B C D E F G
1 =Input!A1
2 Proj Proj Proj
3 BALANCE SHEET =Input!B4 2001 2002 2003 2004 2005
4 ASSETS
5 Surplus funds =Input!B72 0.0 0.0 90.0 52.7 0.0
6 Cash =Input!B78 75.0 80.0 80.0 80.0 80.0
7 St investments =Input!B84 32.0 33.0 34.0 35.0 36.1
8 Accounts receivable =Input!B91 75.0 90.0 90.4 99.5 109.4
9 Inventory =Input!B96 135.0 150.0 162.4 173.2 190.5
10 Other current assets =Input!B100 10.0 12.0 11.0 12.1 13.3
11 Current assets =SUM(B5:B10) 327.0 355.0 467.9 452.5 429.3
12
13 Net PPE =Input!B108 950.0 1,000.0 1,080.0 1,206.0 1,369.7
14 Intangibles =Input!B112 50.0 50.0 46.0 42.0 38.0
15 Long-term assets =Input!B117 120.0 150.0 154.0 169.4 186.3
16 Total assets =B11+SUM(B13:B15) 1,447.0 1,565.0 1,747.9 1,869.9 2,023.3
17
18 LIABILITIES
19 Short-term notes =Input!B122 12.0 14.0 14.0 14.0 14.0
20 Accounts payable =Input!B129 70.0 80.0 91.2 100.3 110.3
21 Other current liabilities =Input!B133 20.0 20.0 22.0 24.2 26.6
22 Current liabilities =SUM(B19:B21) 102.0 114.0 127.2 138.5 150.9
23
24 Necessary to finance =Input!B136 0.0 0.0 0.0 0.0 19.4
25 Debt 1 =Input!B141 225.0 150.0 150.0 150.0 150.0
26 Debt 2 =Input!B146 200.0 225.0 225.0 225.0 225.0
27 Debt 3 =Input!B151 110.0 110.0 110.0 110.0 110.0
28 Long-term liabilities =Input!B158 38.0 37.0 44.0 48.4 53.2
29 Total liabilities =B22+SUM(B24:B28) 675.0 636.0 656.2 671.9 708.6
30
31 SHAREHOLDERS™ EQUITY
32 Common stock =Input!B163 560.0 580.0 650.0 650.0 650.0
33 Retained earnings =Input!B167 261.0 337.0 429.6 535.7 652.3
34 Other equity account =Input!B171 11.0 12.0 12.1 12.3 12.4
35 Total SH equity =SUM(B32:B34) 772.0 929.0 1,091.7 1,198.0 1,314.8
36 Total liabs & SH equity =B29+B35 1,447.0 1,565.0 1,747.9 1,869.9 2,023.3
37
BS




TLFeBOOK
The IS and BS Output Sheets 197




Copy the formulas shown in column B across to column G.
To copy the titles from the Input sheet, copy column B back to
column A (notice that A1 already has a formula so that the name
of the company also appears on this sheet). However, copying
back to column A does not work for the ˜˜totals™™ rows with the
summing formulas.
So, in very short order, we have created presentation-ready
sheets for the income statement and the balance sheet. Let™s go to
the next chapter for the CF sheet.




TLFeBOOK
This page intentionally left blank.




TLFeBOOK
CHAPTER 11


The CF Sheet




In Chapter 10 we moved the model into a working state and a
set of output sheets that looked pretty nice. This chapter goes
over the creation of the third important output: the cash flow
statement.


A NEW SHEET: THE CASH FLOW STATEMENT
The cash flow statement (CF) is a new sheet that we have to work
on. Unlike the IS and the BS, whose formats follow the Input
sheet, we have to create a new sheet altogether.
Because the numbers from the Input sheet have already
been brought into the IS and the BS sheets, the CF sheet will
just read from these two statements. The exception, as you will
see, is the capital expenditures (capex) number, which is not
represented in the balance sheet. This number will be read
from the Input sheet.
The cash flow statement is a way of showing how much
cash is coming into a company and how it is being used. Cash
is critical for operations: a business that is running out of cash is
one that is in trouble, no matter how ˜˜profitable™™ it may seem
from its net income number and how large its asset base is. The
cash flow statement is key in showing exactly where a company™s
cash ˜˜sources™™ are and where the cash ˜˜uses™™ are.

<<

. 36
( 62 .)



>>