CFIN6
Spreadsheet Problem Solution
Chapter 16
a.
INPUT DATA:
KEY OUTPUT:
Forecasted sales growth:
Year
Year
1
15.0%
1
AFN
$7.0
2
15.0%
2
AFN
$8.2
3
15.0%
3
AFN
$9.6
4
15.0%
4
AFN
$11.3
5
15.0%
5
AFN
$13.2
Cumulative AFN
$49.3
AFN financing percentages:
Notes payable
50.0%
Year
Long-term bonds
50.0%
Ratios:
0
1
2
3
4
5
Common stock
0.0%
Current ratio
2.5
2.1
1.8
1.6
1.5
1.4
Debt costs:
Profit margin
5.0%
4.7%
4.4%
4.2%
4.0%
3.8%
Notes payable
10.0%
TA turnover
1.1
1.1
1.1
1.1
1.1
1.1
Long-term bonds
12.0%
ROA
5.6%
5.2%
4.9%
4.7%
4.4%
4.2%
Tax rate
40.0%
Debt ratio
34.7%
40.1%
44.9%
49.3%
53.3%
56.9%
ROE
8.6%
8.8%
9.0%
9.2%
9.4%
9.7%
Dividend payout ratio
40.0%
MODEL-GENERATED DATA:
INCOME STATEMENT ($ millions)
Projected
Historical
Initial
Final
Initial
Final
Initial
Final
Initial
Final
Initial
Final
0
1
1
2
2
3
3
4
4
5
5
Sales
80.0
92.0
92.0
105.8
105.8
121.7
121.7
139.9
139.9
160.9
160.9
Operating costs
(71.3)
(82.0)
(82.0)
(94.3)
(94.3)
(108.4)
(108.4)
(124.7)
(124.7)
(143.4)
(143.4)
EBIT
8.7
10.0
10.0
11.5
11.5
13.2
13.2
15.2
15.2
17.5
17.5
Less interest
(2.0)
(2.0)
(2.8)
(2.8)
(3.7)
(3.7)
(4.7)
(4.7)
(6.0)
(6.0)
(7.4)
Earnings before taxes
6.7
8.0
7.2
8.7
7.8
9.6
8.5
10.5
9.2
11.5
10.1
Taxes
(2.7)
(3.2)
(2.9)
(3.5)
(3.1)
(3.8)
(3.4)
(4.2)
(3.7)
(4.6)
(4.0)
NI avail to common
4.0
4.8
4.3
5.2
4.7
5.7
5.1
6.3
5.5
6.9
6.0
Dividends to common
1.6
1.9
1.7
2.1
1.9
2.3
2.0
2.5
2.2
2.8
2.4
Additions to ret earn
2.4
2.9
2.6
3.1
2.8
3.4
3.1
3.8
3.3
4.1
3.6
CFIN6
BALANCE SHEET ($ millions)
Projected
Historical
Initial
Final
Initial
Final
Initial
Final
Initial
Final
Initial
Final
0
1
1
2
2
3
3
4
4
5
5
Cash
4.0
4.6
4.6
5.3
5.3
6.1
6.1
7.0
7.0
8.0
8.0
Accounts receivable
12.0
13.8
13.8
15.9
15.9
18.3
18.3
21.0
21.0
24.1
24.1
Inventories
16.0
18.4
18.4
21.2
21.2
24.3
24.3
28.0
28.0
32.2
32.2
Tot current assets
32.0
36.8
36.8
42.3
42.3
48.7
48.7
56.0
56.0
64.4
64.4
Net plant and equip
40.0
46.0
46.0
52.9
52.9
60.8
60.8
70.0
70.0
80.5
80.5
Total assets
72.0
82.8
82.8
95.2
95.2
109.5
Accounts payable
8.0
9.2
9.2
10.6
10.6
12.2
12.2
14.0
14.0
16.1
16.1
Notes payable
5.0
5.0
8.5
8.5
12.6
12.6
17.4
17.4
23.1
23.1
29.6
Accruals
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
Total current liab
13.0
14.2
17.7
19.1
23.2
24.8
29.6
31.4
37.1
39.2
45.7
Long-term bonds
12.0
12.0
15.5
15.5
19.6
19.6
24.4
24.4
30.1
30.1
36.6
Total debt
25.0
26.2
33.2
34.6
42.8
44.4
54.0
55.8
67.1
69.2
82.4
Common stock
20.0
20.0
20.0
20.0
20.0
20.0
20.0
20.0
20.0
20.0
20.0
Retained earnings
27.0
29.9
29.6
32.7
32.4
35.9
35.5
39.3
38.8
43.0
42.4
Total common equity
47.0
49.9
49.6
52.7
52.4
55.9
55.5
59.3
58.8
63.0
62.4
Total liabs & equity
72.0
76.1
82.8
87.3
95.2
100.2
Additional
Initial
Final
Initial
Final
Initial
Final
Initial
Final
Initial
Final
Funds
1
1
2
2
3
3
4
4
5
5
AFN
6.7
0.3
7.9
0.4
9.3
0.4
10.8
0.5
12.6
0.0
—–
—–
—–
—–
—–
—–
—–
—–
—–
—–
Additional notes
3.4
3.5
3.9
4.1
4.6
4.8
5.4
5.6
6.3
6.6
3.4
3.5
3.9
4.1
4.6
4.8
5.4
5.6
6.3
6.6
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
6.7
7.0
7.9
8.2
9.3
9.6
10.8
11.3
12.6
13.2
b(1). If sales growth is 10 percent, the forecasted AFN and key ratios are as follows:
INPUT DATA:
KEY
OUTPUT:
Forecasted sales growth:
Year
Year
1
10.0%
1
AFN
$3.8
2
10.0%
2
AFN
$4.3
3
10.0%
3
AFN
$4.8
4
10.0%
4
AFN
$5.4
5
10.0%
5
AFN
$6.0
CFIN6
AFN financing percentages:
Notes payable
50.0%
Year
Long-term bonds
50.0%
Ratios:
0
1
2
3
4
5
Common stock
0.0%
Current ratio
2.5
2.2
2.1
1.9
1.8
1.7
Debt costs:
Profit margin
5.0%
4.9%
4.7%
4.6%
4.5%
4.3%
Notes payable
10.0%
TA turnover
1.1
1.1
1.1
1.1
1.1
1.1
Long-term bonds
12.0%
ROA
5.6%
5.4%
5.3%
5.1%
5.0%
4.8%
Tax rate
40.0%
Debt ratio
34.7%
37.4%
39.9%
44.6%
Dividend payout ratio
40.0%
ROE
8.6%
8.7%
8.8%
8.9%
9.0%
9.1%
b(2). If sales growth is 20 percent, the forecasted AFN and key ratios are as follows:
INPUT DATA:
KEY
OUTPUT:
Forecasted sales growth:
Year
Year
1
20.0%
1
AFN
$10.2
2
3
20.0%
3
AFN
$15.2
4
20.0%
4
AFN
$18.6
5
20.0%
5
AFN
$22.6
20.0%
2
AFN
$12.5
Cumulative AFN
$79.0
AFN financing percentages:
Notes payable
50.0%
Year
Long-term bonds
50.0%
Ratios:
0
1
2
3
4
5
Common stock
0.0%
Current ratio
2.5
2.0
1.7
1.5
1.3
1.2
Debt costs:
Profit margin
5.0%
4.6%
4.2%
3.8%
3.6%
Notes payable
10.0%
TA turnover
1.1
1.1
1.1
1.1
1.1
1.1
Long-term bonds
12.0%
ROA
5.6%
5.1%
4.7%
4.3%
4.0%
Tax rate
40.0%
Debt ratio
34.7%
42.6%
49.3%
55.2%
60.3%
Dividend payout ratio
40.0%
ROE
8.6%
8.9%
9.2%
9.5%
10.0%
c(1). Dividend payout equals 70 percent:
INPUT DATA:
KEY
OUTPUT:
Forecasted sales growth:
Year
Year
1
15.0%
1
AFN
$8.3
2
15.0%
2
AFN
$9.7
3
15.0%
3
AFN
$11.3
4
15.0%
4
AFN
$13.1
5
15.0%
5
AFN
$15.1
Cumulative AFN
$57.5
CFIN6
AFN financing percentages:
Notes payable
50.0%
Year
Long-term bonds
50.0%
Ratios:
0
1
2
3
4
5
Common stock
0.0%
Current ratio
2.5
2.0
1.7
1.5
1.4
1.3
Debt costs:
Profit margin
5.0%
4.6%
4.3%
4.0%
3.7%
3.4%
Notes payable
10.0%
TA turnover
1.1
1.1
1.1
1.1
1.1
1.1
Long-term bonds
12.0%
ROA
5.6%
5.1%
4.7%
4.4%
4.1%
3.8%
Tax rate
40.0%
Debt ratio
34.7%
41.7%
47.9%
53.4%
58.2%
62.5%
Dividend payout ratio
70.0%
ROE
8.6%
8.8%
9.1%
9.4%
9.8%
10.1%
c(2). Dividend payout equals 20 percent:
INPUT DATA:
KEY
OUTPUT:
Forecasted sales growth:
Year
Year
1
15.0%
1
AFN
$6.1
2
3
15.0%
3
AFN
$8.5
4
15.0%
4
AFN
$9.9
5
15.0%
5
AFN
Cumulative AFN
15.0%
2
AFN
$7.2
AFN financing percentages:
Notes payable
50.0%
Year
Long-term bonds
50.0%
Ratios:
0
1
2
3
4
5
Common stock
0.0%
Current ratio
2.5
2.1
1.9
1.7
1.6
1.5
Debt costs:
Profit margin
5.0%
4.8%
4.6%
4.4%
4.2%
4.0%
Notes payable
10.0%
TA turnover
1.1
1.1
1.1
1.1
1.1
1.1
Long-term bonds
12.0%
ROA
5.6%
5.3%
5.1%
4.8%
4.6%
4.4%
Tax rate
40.0%
Debt ratio
34.7%
39.0%
42.9%
46.5%
49.7%
52.7%
Dividend payout ratio
20.0%
ROE
8.6%
8.7%
8.9%
9.1%
9.2%
9.4%