In: Finance
Chapter 4 Problem 13: Ottawa Corp.
Use the pro forma financial statements to answer the questions below. Change the assumptions in the assumptions box as needed to answer the questions. In addition to the assumptions listed on the spreadsheet, also assume that all asset accounts will grow at the same rate as sales, and that no new equity will be issued in 2018.
Enter a formula for external funding required in the first green box. How much external financing does Ottawa need in 2018?
Given your answer from (a), do you expect the sustainable growth rate to be greater than, less than, or equal to the sales growth rate for 2018? Enter a formula for the sustainable growth rate in the second green box. What is Ottawa’s sustainable growth rate?
At what rate does the actual sales growth rate equal the sustainable growth rate? How much external financing is required at this growth rate? (This can be determined by trial and error.)
Return the sales growth rate to 15%. Suppose Ottawa wants to solve the financing shortfall by increasing profit margin. How low would the ratio of cost of goods sold/sales have to go in order to make up the shortfall? With cost of goods sold/sales at this lower level, what is the sustainable growth rate? (Hint: The Goal Seek tool can help you find this quickly. Consult Excel Help if you are unfamiliar with the Goal Seek tool.)
Return cost of goods sold/sales to 75%. Now suppose Ottawa wants to solve the shortfall by increasing the retention ratio. How low would the dividend payout ratio have to be in order to eliminate the financing shortfall?
OTTAWA CORP. |
|||||||||||
INCOME STATEMENT ($ millions) |
BALANCE SHEET ($ millions) |
||||||||||
Actual |
Projected |
Actual |
Projected |
||||||||
2017 |
2018 |
2017 |
2018 |
||||||||
Sales |
$3,500 |
$4,025 |
Cash |
$150 |
$173 |
||||||
Cost of goods sold |
2,775 |
3,019 |
Accounts receivable |
540 |
621 |
||||||
Operating expense |
360 |
403 |
Inventory |
1,050 |
1,208 |
||||||
EBIT |
365 |
604 |
Total current assets |
1,740 |
2,001 |
||||||
Interest expense |
68 |
80 |
Property, plant, & equipment |
1,578 |
1,815 |
||||||
EBT |
297 |
524 |
Total assets |
3,318 |
3,816 |
||||||
Tax |
102 |
183 |
|||||||||
Net income |
$195 |
$341 |
Total debt |
1,106 |
1,208 |
||||||
Shareholders' equity |
2,212 |
2,416 |
|||||||||
Assumptions for 2018 |
Total liabilities & equity |
$3,318 |
$3,625 |
||||||||
Sales growth rate |
15.0% |
||||||||||
Cost of goods sold/sales |
75.0% |
External funding required |
|||||||||
Operating expense/sales |
10.0% |
Sustainable growth rate |
|||||||||
Dividend payout ratio |
40.0% |
||||||||||
Tax rate |
35.0% |
||||||||||
Interest rate on debt |
7.2% |
||||||||||
Total debt/equity |
50.0% |
||||||||||
1…For the given 2018 projections | ||
External funding required | 3816-2416-1106= | 294 |
Sustainable growth rate(SGR)= Return on Equity*Retention Ratio | ||
ROE*RR | ||
(341/2416)*60% | 8.47% |
SGR 8.47% < Sales growth Rate, 15% |
Workings for: |
SGR=Sales Growth Rate |
2017 | Projected 2018 | Equation for trial & error | |
Sales | 3500 | 4025 | 3500*(1+x%) |
Cost of goods sold | 2,775 | 3019 | 0.75*(3500*(1+x%)) |
Operating expense | 360 | 403 | 0.10*(3500*(1+x%)) |
EBIT | 365 | 604 | 0.15*(3500*(1+x%)) |
Interest expense | 68 | 80 | 80 |
EBT | 297 | 524 | 0.15*(3500*(1+x%))-80 |
Tax | 102 | 183 | |
Net income | 195 | 341 | 0.65*(0.15*(3500*(1+x%))-80) |
Dividend payout 40% | 136 | ||
Retention 60% | 204 |
Net income= |
0.65*(0.15*(3500*(1+x%))-80) |
ROE= |
(0.65*(0.15*(3500*(1+x%))-80))/(2212+(60%*(0.65*(0.15*(3500*(1+x%))-80)))) |
SGR= ROE*RR |
((0.65*(0.15*(3500*(1+x%))-80))/(2212+(60%*(0.65*(0.15*(3500*(1+x%))-80)))))*60% |
Equating SGR to Sales growth rate, |
((0.65*(0.15*(3500*(1+x%))-80))/(2212+(60%*(0.65*(0.15*(3500*(1+x%))-80)))))*60%=x% |
Solving the above, |
the sales growth rate equals the SGR at 7.8995% |
ie. 7.90% |
BALANCE SHEET ($ millions) | |||
Actual | Projected | 7.90% | |
2017 | 2018(15%) | ||
Cash | 150 | 173 | 162 |
Accounts receivable | 540 | 621 | 583 |
Inventory | 1050 | 1,208 | 1133 |
Total current assets | 1740 | 2,001 | 1877 |
Property, plant, & equipment | 1578 | 1,815 | 1703 |
Total assets | 3318 | 3,816 | 3580 |
Total debt | 1,106 | 1106 | 1106 |
Shareholders' equity | 2,212 | 2416 | 2528 |
Total liabilities & equity | 3318 | 3625 | 3634 |
EFN needed(Plug-in Fig.) | 294 | -54 | |
Total of asset side | 3816 | 3580 | |
As the EFN is NEGATIVE, 0 (ZERO) EFN at 7.90% sales growth rate. | |||
2212+ 204=2416 | |||
2212+316=2528 |
To solve the financing shortfall by increasing profit margin | |||||
Ie. No EFN at this 15% sales growth, but increased P/M by lowering COGS % * sales | |||||
Net income needed= Total assets 3816- current debt 1106-current equity 2212= | |||||
3816-1106-2212= 498 | |||||
So,By trial & error, COGS needs to be 56.31% of sales, as below: | |||||
Sales | 4025 | ||||
Cost of goods sold (56.31%)*Sales | 2266 | ||||
Operating expense | 403 | ||||
EBIT | 1356 | ||||
Interest expense | 80 | ||||
EBT | 1276 | ||||
Tax | 447 | ||||
Net income | 830 | ||||
Dividend payout 40% | 332 | ||||
Retention 60% | 498 |
SGR =ROE*RR | ||
ie (830/(2212+498))*60%= | ||
18.38% |
To solve the shortfall by increasing the retention ratio | |||||
Ie. No EFN at this 15% sales growth, but increased RR by lowering the D/Payout | |||||
Retained income needed= Total assets 3816- current debt 1106-current equity 2212= | |||||
3816-1106-2212= 498 | |||||
So,By trial & error : | |||||
Sales | 4025 | ||||
Cost of goods sold 75%*sales | 3019 | ||||
Operating expense | 403 | ||||
EBIT | 604 | ||||
Interest expense | 80 | ||||
EBT | 524 | ||||
Tax | 183 | ||||
Net income | 341 | ||||
AS the financing shortfall (498) > the net Income (341), under this scenario, even NIL dividend will not be sufficient. |