Chapter 14 Spreadsheet-Related Problem—Working Capital Policy

Chapter 14 Spreadsheet-Related Problem—Working Capital Policy
Three companies—Aggressive, Moderate, and Conservative—have different working capital
management policies as implied by their names. For example, Aggressive employs only minimal  current assets, and it finances almost entirely with current liabilities plus equity. This restricted  approach has a dual effect. It keeps total assets low, which tends to increase return on assets; but
because of stock-outs and credit rejections, total sales are reduced, and because inventory is  ordered more frequently and in smaller quantities, variable costs are increased. Condensed  balance sheets for the three companies follow:
Aggressive Moderate Conservative
Current assets $225,000 $300,000 $450,000
Fixed assets 300,000 300,000 300,000
Total assets $525,000 $600,000 $750,000
Current liabilities (cost = 12%) $300,000 $150,000 $ 75,000
Long-term debt (cost = 10%) 0 150,000 300,000
Total debt $300,000 $300,000 $375,000
Equity 225,000 300,000 375,000
Total liabilities and equity $525,000 $600,000 $750,000
Current ratio 0.75 2.0 6.0
The cost of goods sold functions for the three firms are as follows:
Cost of Goods Sold = Fixed Costs + Variable Costs
Aggressive: Cost of goods sold = $300,000 + 0.70(Sales)
Moderate: Cost of goods sold = $405,000 + 0.65(Sales)
Conservative: Cost of goods sold = $577,500 + 0.60(Sales) Because of the working capital differences, sales for the three firms under different economic conditions are expected to vary as follows:
Aggressive Moderate Conservative
Strong economy $1,800,000 $1,875,000 $1,950,000
Average economy 1,350,000 1,500,000 1,725,000
Weak economy 1,050,000 1,200,000 1,575,000
a. Construct income statements for each company for strong, average, and weak economies using the following format:
Sales
Less: Cost of goods sold
Earnings before interest and taxes (EBIT)
Less: Interest expense
Earnings before taxes (EBT)
Less: Taxes (at 40%)
Net income (NI)
NOTE: On page 245 of the text, there is an Example you will follow. To complete this problem,  you will need to create a table that matches table 14.1, and turn that in also.