Chapter 4 Problem 12
Ottawa Corporation		Instructions:
Financial Statements, 2013 and Projected 2014 ($ millions)		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 2014.
INCOME STATEMENT	BALANCE SHEET		Questions:
Actual	Projected	Actual	Projected		a. Enter a formula for external funding required in the first green box. How much external financing does Ottawa need in 2014?
2013	2014	2013	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 Ottawa?s sustainable growth rate?
Sales	$ 3,500	$ 4,025	Cash	$ 150	$ 173		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.)
COGS	2,775	3018.75	Accounts receivable	540	621		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.)
Operating expense	360	403	Inventory	1,050	1,208		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?
EBIT	365	604	Total current assets	1,740	2,001		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?
Interest expense	68	80	Property, plant, & equipment	1,578	1,815		g. Given the above options, and any other options that you can find, make a recommendation for a reasonable and practical solution to Ottawa?s financing shortfall. Your solution can involve changing multiple variables.
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%

Chapter 4 Problem 12 Ottawa Corporation Financial Statements, 2013 and Projected 2014 ($ millions) INCOME STATEMENT Actual Projected 2013 2014 Sales $ 3,500 $ 4,025 COGS 2,775 3018.75 Operating expense 360 403 EBIT 365 604 Interest expense 68 80 EBT 297 524 Tax 102 183 Net income $ 195 $ 341 Assumptions for 2014 Sales growth rate COGS/sales Oper. Exp./sales Dividend payout ratio Tax rate Interest rate on debt Total debt/equity 15.0% 75.0% 10.0% 40.0% 35.0% 7.2% 50.0% BALANCE SHEET Actual Projected 2013 2014 Cash $ 150 $ 173 Accounts receivable 540 621 Inventory 1,050 1,208 Total current assets 1,740 2,001 Property, plant, & equipment 1,578 1,815 Total assets 3,318 3,816 Total debt Shareholders' equity Total liabilities & equity External funding required Sustainable growth rate $ 1,106 2,212 3,318 $ 1,208 2,416 3,625 Instructions: Use the pro forma financial statements to answer the questions below. Change the assumptions in the assumptions box as needed to answer 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 20 c. At what rate does the actual sales growth rate equal the sustainable growth rate? How much external financing is required at this growth r d. Return the sales growth rate to 15%. Suppose Ottawa wants to solve the financing shortfall by increasing profit margin. How low would e. Return COGS/Sales to 75%. Now suppose Ottawa wants to solve the shortfall by increasing the retention ratio. How low would the divi f. Return the dividend payout ratio to 40%. Now suppose Ottawa wants to make up any financing shortfall with increased debt. How high w g. Given the above options, and any other options that you can find, make a recommendation for a reasonable and practical solution to Ottawa's financing shortfall. Your solution can involve changing multiple variables. mptions 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 t Ottawa need in 2014? qual to the sales growth rate for 2014? Enter a formula for the sustainable growth rate in the second green box. What is Ottawa's sustainable growth rate? ancing is required at this growth rate? (This can be determined by trial and error.) ng 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 on ratio. How low would the dividend payout ratio have to be in order to eliminate the financing shortfall? l with increased debt. How high would the debt/equity ratio have to be to make up the difference? at all asset accounts will grow at the same rate as sales, and that no new equity will be issued in 2014. Ottawa's sustainable growth rate? es 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 unfami nsult Excel Help if you are unfamiliar with the Goal Seek tool.)