CFIN6
Spreadsheet Problem Solution
Chapter 15B
a. The income statements for each company in each state of the economy are given below:
(1) Strong Economy
INPUT DATA:
Aggressive
Moderate
Conservative
Sales
$1,800,000
$1,875,000
$1,950,000
Fixed costs
$300,000
$405,000
$577,500
Variable costs
0.70
0.65
0.60
Tax rate
Current assets
$225,000
$300,000
$450,000
Net fixed assets
$300,000
$300,000
$300,000
Current liability
$300,000
$150,000
12.00%
$150,000
$300,000
Equity
$225,000
$300,000
$375,000
40.00%
40.00%
40.00%
KEY OUTPUT:
Current ratio
0.75
2.00
6.00
Return on equity
56.8%
44.0%
25.6%
MODEL-GENERATED DATA:
Income Statement
Aggressive
Moderate
Conservative
Sales
$1,800,000
$1,875,000
$1,950,000
Less cost of goods sold
(1,560,000)
(1,623,750)
(1,747,500)
EBIT
$240,000
$251,250
$202,500
Interest on debt
$213,000
$219,750
$159,750
Taxes
Net income
$127,800
$131,850
Current assets
$225,000
$300,000
$450,000
Net fixed assets
$300,000
$300,000
$300,000
Current liability
$300,000
$150,000
(2) Average Economy
INPUT DATA:
Aggressive
Moderate
Conservative
Sales
$1,350,000
$1,500,000
$1,725,000
Fixed costs
$300,000
$405,000
$577,500
Variable costs
0.70
0.65
0.60
Tax rate
40.00%
40.00%
40.00%
Current assets
$225,000
$300,000
$450,000
Net fixed assets
$300,000
$300,000
$300,000
Current liability
$300,000
$150,000
$150,000
$300,000
Equity
$225,000
$300,000
$375,000
KEY OUTPUT:
Current ratio
0.75
2.00
6.00
Return on equity
20.8%
17.7%
11.2%
MODEL-GENERATED DATA:
Income Statement
Aggressive
Moderate
Sales
$1,350,000
$1,500,000
$1,725,000
Less cost of goods sold
EBIT
$105,000
$120,000
$112,500
Interest on debt
$78,000
$88,500
Taxes
Net income
$46,800
$53,100
(3) Weak Economy
INPUT DATA:
Aggressive
Moderate
Conservative
Sales
$1,050,000
$1,200,000
$1,575,000
Fixed costs
$300,000
$405,000
$577,500
Variable costs
0.70
0.65
0.60
Tax rate
40.00%
40.00%
40.00%
CFIN6
Long-term debt
12.00%
$0
$150,000
$300,000
Equity
$225,000
$300,000
$375,000
KEY OUTPUT:
Current ratio
Return on equity
MODEL-GENERATED DATA:
Income Statement
Aggressive
Moderate
Conservative
Sales
$1,050,000
$1,200,000
$1,575,000
Less cost of goods sold
(1,035,000)
(1,185,000)
(1,522,500)
EBIT
Interest on debt
Taxes
Net income
$15,000
$15,000
$52,500
b. The aggressive company has the highest ROE in a strong economy. In an average economy, the
aggressive and the moderate companies have similar ROEs, and both outperform the conservative
company. But in a weak economy, the conservative company does best, and is, in fact, the only company
INPUT DATA:
Aggressive
Moderate
Conservative
Sales
$1,350,000
$1,500,000
$1,725,000
Fixed costs
$300,000
$405,000
$577,500
Variable costs
0.70
0.65
0.60
Tax rate
40.00%
40.00%
40.00%
Current assets
$225,000
$300,000
$450,000
Net fixed assets
$300,000
$300,000
$300,000
Current liability
20.00%
$300,000
$150,000
$75,000
12.00%
$150,000
$300,000
Equity
$225,000
$300,000
$375,000
KEY OUTPUT:
CFIN6
Current ratio
0.75
2.00
6.00
Return on equity
12.0%
14.4%
9.8%
MODEL-GENERATED DATA:
Income Statement
Aggressive
Moderate
Conservative
Sales
$1,350,000
$1,500,000
$1,725,000
Less cost of goods sold
EBIT
$105,000
$120,000
$112,500
Interest on debt
$45,000
$72,000
Taxes
Net income
$27,000
$43,200
d. The aggressive company’s ROE would, at a normal sales level and a 9 percent short-term rate, fall from
+21% to -15%. Indeed, all of the companies’ ROEs are quite sensitive to the variable cost ratio, but the
aggressive company‘s VC ratio is the one that is most likely to vary from the expected level.
INPUT DATA:
Aggressive
Moderate
Conservative
Sales
$1,350,000
$1,500,000
$1,725,000
Fixed costs
$300,000
$405,000
$577,500
Variable costs
0.80
0.65
0.60
Tax rate
40.00%
40.00%
40.00%
Current assets
$225,000
$300,000
$450,000
Net fixed assets
$300,000
$300,000
$300,000
Current liability
$300,000
$150,000
12.00%
$150,000
$300,000
Equity
$225,000
$300,000
$375,000
KEY OUTPUT:
Current ratio
Return on equity
17.7%
CFIN6
MODEL-GENERATED DATA:
Income Statement
Aggressive
Moderate
Conservative
Sales
$1,350,000
$1,500,000
$1,725,000
Less cost of goods sold
(1,380,000)
(1,380,000)
(1,612,500)
EBIT
Interest on debt
($57,000)
$88,500
Taxes
Net income
($34,200)
$53,100
($30,000)
$120,000
$112,500
e. The central idea is that while working capital should be managed carefully, there is an optimum level. If