Ottawa Corporation Financial Statements, 2013 and Projected 2014 ($ millions) INCOME STATEMENT BALANCE SHEET Actual Projected Actual Projected 2013 2014 2013 2014 Sales $ 3,500 $ 4,025 Cash $ 150 $ 173 COGS 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 2014 Total liabilities equity $ 3,318 $ 3,625 Sales growth rate 15 0 COGS sales 75 0 External funding required Oper Exp 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 Questions a Enter a formula for external funding required in the first green box How much external financing does Ottawa need in 2014 b 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 2014 Enter a formula for the sustainable growth rate in the second green box What is Ottawas sustainable growth rate c 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 ) d 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 COGS Sales have to go in order to make up the shortfall With COGS 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 ) e Return COGS 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 f Return the dividend payout ratio to 40 Now suppose Ottawa wants to make up any financing shortfall with increased debt How high would the debt equity ratio have to be to make up the difference g Given the above options, and any other options that you can find, make a recommendation for a reasonable and practical solution to Ottawas financing shortfall Your solution can involve changing multiple variables

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 01, 2024

Ottawa Corporation Financial Statements, 2013 and Projected 2014 ($ millions) INCOME STATEMENT BALANCE SHEET Actual Projected Actual Projected 2013 2014 2013 2014 Sales $ 3,500

Ottawa Corporation
Financial Statements, 2013 and Projected 2014 ($ millions)

INCOME STATEMENT			BALANCE SHEET
	Actual	Projected		Actual	Projected
	2013	2014		2013	2014
Sales	$ 3,500	$ 4,025	Cash	$ 150	$ 173
COGS	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 2014			Total liabilities & equity	$ 3,318	$ 3,625
Sales growth rate		15.0%
COGS/sales		75.0%	External funding required
Oper. Exp./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%

Questions:
a. Enter a formula for external funding required in the first green box. How much external financing does Ottawa need in 2014?
b. 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 2014? Enter a formula for the sustainable growth rate in the second green box. What is Ottawas sustainable growth rate?
c. 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.)
d. 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 COGS/Sales have to go in order to make up the shortfall? With COGS/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.)
e. Return COGS/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?
f. Return the dividend payout ratio to 40%. Now suppose Ottawa wants to make up any financing shortfall with increased debt. How high would the debt/equity ratio have to be to make up the difference?
g. Given the above options, and any other options that you can find, make a recommendation for a reasonable and practical solution to Ottawas financing shortfall. Your solution can involve changing multiple variables.