<<

. 30
( 62 .)



>>

The shaded cells (shown in gray) are the input cells. A good
choice of color would be pale yellow, the palest in Excel™s palette.
If that is still too strong, remember you can change the shading
for that (or any other color choice in the palette) through the
Tools>Options>Color>Modify setting. Please refer to Chapter 3,
˜˜Starting Out.™™




TLFeBOOK
Chapter 9
160




The Income Statement Input

A B C D E F G
1 First Corporation
2
3 Proj Proj Proj
4 INCOME STATEMENT 2000 2001 2002 2003 2004 2005
5
6 Revenues 825.0 900.0 1,000.0
7 Percent growth % na 9.1% 11.1% 10.0% 10.0% 10.0%
8 Revenues 825.0 900.0 1,000.0 1,100.0 1,210.0 1,331.0
9
10 COGS 450.0 490.0 550.0
11 As % revenues 54.5% 54.4% 55.0% 55.0% 55.0% 55.0%
12 COGS 450.0 490.0 550.0 605.0 665.5 732.1
13 Gross profit 375.0 410.0 450.0 495.0 544.5 599.0
14 Gross margin 45.5% 45.6% 45.0% 45.0% 45.0% 45.0%
15
16 SGA 125.0 135.0 150.0
17 As % revenues 15.2% 15.0% 15.0% 15.0% 15.0% 15.0%
18 SGA 125.0 135.0 150.0 165.0 181.5 199.7
19
20 Operating expenses 25.0 28.0 30.0
21 As % revenues 3.0% 3.1% 3.0% 3.0% 3.0% 3.0%
22 Operating expenses 25.0 28.0 30.0 33.0 36.3 39.9
23 EBITDA 225.0 247.0 270.0 297.0 326.7 359.4
24 EBITDA margin 27.3% 27.4% 27.0% 27.0% 27.0% 27.0%
25
26 Depreciation 60.0 75.0 80.0
27 As % prior Net PPE na 8.6% 8.4% 8.5% 8.5% 8.5%
28 As % revenues 7.3% 8.3% 8.0%
29 Depreciation 60.0 75.0 80.0 85.0 91.8 102.5
30
31 Amort of intangibles 4.0 4.0 4.0 4.0 4.0 4.0
32 Amort of intangibles 4.0 4.0 4.0 4.0 4.0 4.0
33 EBIT 161.0 168.0 186.0 208.0 230.9 252.9
34 EBIT margin 19.5% 18.7% 18.6% 18.6% 19.1% 19.0%
35
36 Non-oper expenses 10.0 10.0 8.0 10.0 11.0 12.0
37 As % revenues 1.2% 1.1% 0.8%
38 Non-oper expenses 10.0 10.0 8.0 10.0 11.0 12.0
39
Input




TLFeBOOK
Putting Everything Together 161




The Income Statement Input (Part 2)

A B C D E F G
40 Interest income 3.0 5.0 6.0
41 Surplus funds 2.3 3.6 1.3
42 Cash 4.0 4.0 4.0
43 ST investments 1.7 1.7 1.8
44 Interest income 3.0 5.0 6.0 7.9 9.3 7.1
45
46 Interest expense 50.0 50.0 50.0
47 Necessary to finance 0.0 0.0 1.0
48 ST notes 1.4 1.4 1.4
49 Debt 1 15.0 15.0 15.0
50 Debt 2 22.5 22.5 22.5
51 Debt 3 8.8 8.8 8.8
52 Interest expense 50.0 50.0 50.0 47.7 47.7 48.7
53 EBT 104.0 113.0 134.0 158.2 181.5 199.3
54 EBT margin 12.6% 12.6% 13.4% 14.4% 15.0% 15.0%
55
56 Provision for taxes 360 40.0 47.0
57 Tax rate % 34.6% 35.4% 35.1% 35.0% 35.0% 35.0%
58 Provision for taxes 36.0 40.0 47.0 55.4 63.5 69.7
59 Net income 68.0 73.0 87.0 102.7 118.0 129.5
60 Net margin 8.2% 8.1% 8.7% 9.3% 9.7% 9.7%
61
62 Dividends 10.0 12.0 11.0
63 Payout ratio % 14.7% 16.4% 12.6% 10.0% 10.0% 10.0%
64 Dividends 10.0 12.0 11.0 10.3 11.8 13.0
65 Net to ret™d earnings 58.0 61.0 76.0 92.6 106.2 116.6
66
67
Input




TLFeBOOK
Chapter 9
162




The Balance Sheet, Current Assets Input

A B C D E F G
68
69 Proj Proj Proj
70 BALANCE SHEET 2000 2001 2002 2003 2004 2005
71 ASSETS
72 Surplus funds 0.0 0.0 0.0 90.0 52.7 0.0
73 Interest rate % 5.000% 5.000% 5.000%
74 Interest income 2.3 3.6 1.3
75
76 Cash 60.0 75.0 80.0 80.0 80.0 80.0
77 % of revenues 7.3% 8.3% 8.0%
78 Cash 60.0 75.0 80.0 80.0 80.0 80.0
79 Interest rate 5.000% 5.000% 5.000%
80 Interest income 4.0 4.0 4.0
81
82 ST investments 30.0 32.0 33.0 34.0 35.0 36.1
83 % growth 3.6% 3.6% 3.3%
84 St investments 30.0 32.0 33.0 34.0 35.0 36.1
85 Interest rate 5.000% 5.000% 5.000%
86 Interest income 1.7 1.7 1.8
87
88 Accounts receivable 60.0 75.0 90.0
89 % of revenues 7.3% 8.3% 9.0%
90 Days of revenues 26.5 30.4 32.9 30.0 30.0 30.0
91 Accounts receivable 60.0 75.0 90.0 90.4 99.5 109.4
92
93 Inventory 120.0 135.0 150.0
94 % of revenues 14.5% 15.0% 15.0%
95 Days of COGS 97.3 100.6 99.5 98.0 95.0 95.0
96 Inventory 120.0 135.0 150.0 162.4 173.2 190.5
97
98 Other current assets 10.0 10.0 12.0
99 % of revenues 1.2% 1.1% 1.2% 1.0% 1.0% 1.0%
100 Other current assets 10.0 10.0 12.0 11.0 12.1 13.3
101 Current assets 280.0 327.0 365.0 467.9 452.5 429.3
102
Input




TLFeBOOK
Putting Everything Together 163




The Balance Sheet, Long-Term Assets Input

A B C D E F G
103 Net PPE 870.0 950.0 1,000.0
104 Capex 130.0 155.0 130.0
105 % of revenues 15.8% 17.2% 13.0% 15.0% 18.0% 20.0%
106 Capex 130.0 155.0 130.0 165.0 217.8 266.2
107 Depreciation 60.0 75.0 80.0 85.0 91.8 102.5
108 Net PPE 870.0 950.0 1,000.0 1,080.0 1,206.0 1,369.7
109
110 Intangibles 58.0 54.0 50.0
111 Amortization 4.0 4.0 4.0
112 Intangibles 50.0 50.0 50.0 46.0 42.0 38.0
113
114 Long-term assets 92.0 116.0 150.0
115 % of revenues 11.2% 12.9% 15.0% 14.0% 14.0% 14.0%
116 % growth na 26.1% 25.0%
117 Long-term assets 100.0 120.0 150.0 154.0 169.4 186.3
118 Total assets 1,300.0 1,447.0 1,565.0 1,747.9 1,869.9 2,023.3
119
Input




TLFeBOOK
Chapter 9
164




The Balance Sheet, Liabilities Input

A B C D E F G
120 LIABILITIES
121 Short-term notes 10.0 12.0 14.0 14.0 14.0 14.0
122 Short-term notes 10.0 12.0 14.0 14.0 14.0 14.0
123 Interest rate 10.000% 10.000% 10.000%
124 Interest expense 1.4 1.4 1.4
125
126 Accounts payable 60.0 70.0 80.0
127 % of revenues 7.3% 7.8% 8.0%
128 Days of COGS 48.7 52.1 53.1 55.0 55.0 55.0
129 Accounts payable 60.0 70.0 80.0 91.2 100.3 110.3
130
131 Other current liabilities 10.0 20.0 20.0
132 % of revenues 1.2% 2.2% 2.0% 2.0% 2.0% 2.0%
133 Other current liabilities 10.0 20.0 20.0 22.0 24.2 26.6
134 Current liabilities 80.0 102.0 114.0 127.2 138.5 150.9
135
136 Necessary to finance 0.0 0.0 19.4
137 Interest rate 10.000% 10.000% 10.000%
138 Interest expense 0.0 0.0 1.0
139
140 Debt 1 200.0 225.0 150.0
141 Debt 1 200.0 225.0 150.0 150.0 150.0 150.0
142 Interest rate 10.000% 10.000% 10.000%
143 Interest expense 15.0 15.0 15.0
144
145 Debt 2 200.0 200.0 225.0
146 Debt 2 200.0 200.0 225.0 225.0 225.0 225.0
147 Interest rate 10.000% 10.000% 10.000%
148 Interest expense 22.5 22.5 22.5
149
150 Debt 3 110.0 110.0 110.0
151 Debt 3 110.0 110.0 110.0 110.0 110.0 110.0
152 Interest rate 8.000% 8.000% 8.000%
153 Interest expense 8.8 8.8 8.8
154
155 Long-term liabilities 40.0 38.0 37.0
156 % of revenues 4.8% 4.2% 3.7% 4.0% 4.0% 4.0%
157 % growth na (5.0%) (2.6%)
158 Long-term liabilities 40.0 38.0 37.0 44.0 48.4 53.2
159 Total liabilities 630.0 675.0 636.0 656.2 671.9 708.6
160
Input




TLFeBOOK
Putting Everything Together 165




The Balance Sheet, Shareholders™ Equity Input
and Balancing Rows

A B C D E F G
161 SHAREHOLDERS™ EQUITY
162 Common stock 460.0 500.0 580.0 650.0 650.0 650.0
163 Common stock 460.0 500.0 580.0 650.0 650.0 650.0
164
165 Retained earnings 200 261.0 337.0
166 Net to retained earnings 92.6 106.2 116.6
167 Retained earnings 200.0 261.0 337.0 429.6 535.7 652.3
168
169 Other equity acct 10.0 11.0 12.0
170 % of revenues 1.2% 1.2% 1.2% 1.1% 1.0% 0.9%
171 Other equity account 10.0 11.0 12.0 12.1 12.3 12.4
172 Total SH equity 670.0 772.0 929.0 1,091.7 1,198.0 1,314.8
173 Total liabs & SH equity 1,300.0 1,447.0 1,565.0 1,747.9 1,869.9 2,023.3
174
175
Input

<<

. 30
( 62 .)



>>