Question

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.

1. Enter a formula for external funding required in the first green box. How much external financing does Ottawa need in 2018?

2. 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?

3. 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.)

4. 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 the 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.)

5. 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				$3318 $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%

Answer 1

The answer is as below.Thanks

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.