Question

c. Perform a scenario analysis on this projection. How does R&E’s projected external financing require change if a severe recession occurs in 2016? Assume net sales decline 5 percent, cost of goods sold rises to 88 percent of net sales due to price cutting, and current assets increase to 35 percent of net sales as management fails to cut purchases promptly in response to declining sales.

Year	2014	2015	2016
Net sales	$ 20,613	$        25,766	$        30,920
Growth rate in sales		25%	20%
Cost of goods sold/net sales		86%	88%
Gen., sell,, and admin. expenses/net sales		12%	11%
Long-term debt	$     760	$          660	$          560
Current portion long-term debt	$     100	$          100	$          100
Interest rate		10%	10%
Tax rate		45%	45%
Dividend/earnings after tax		50%	50%
Current assets/net sales		29%	35%
Net fixed assets		$          280	$          270
Current liabilities/net sales		14.5%	14.4%
Owners' equity	$   1,730
INCOME STATEMENT
Year	2014	Forecast 2015	2016
Net sales		$     25,766	$     30,920
Cost of good sold		22,159	27,209
Gross profit		3,607	3,710
Gen., sell,, and admin. exp.		3,092	3,401
Interest expense		231	498
Earnings before tax		284	(189)
Tax		128	(85)
Earnings after tax		156	(104)
Dividends paid		78	(52)
Additions to retained earnings		78	(52)
BALANCE SHEET
Current assets		$       7,472	$     10,822
Net fixed assets		280	270
Total assets		7,752	11,092
Current liabilities		3,736	4,452
Long-term debt		660	560
Equity		1,808	1,756
Total liabilities and shareholders' equity		6,204	6,769
EXTERNAL FUNDING REQUIRED		$       1,548	$       4,323

Formulsa for 2016

=C5*(1+D6)

=D20*D7

=D20-D21

=D8*D20

498

=D22-D23-D24

=D12*D25

=D25-D26

=D13*D27

=D27-D28

=D14*D20

=D15

=D31+D32

=D16*D20

=D9

=C36+D29

=D34+D35+D36

=D33-D37

Something is wrong I dont know were the issues is.

Answer 1

I believe the issue might be with regards to circular references in excel. Since the interest has to be calculated on the external funding but the external funding is calculated only after determining additions to retained earnings, the calculations sort of are interdependent, thus causing a circular reference. Simple fix would be to go to Excel and File-Options-Formulas-Enable iterative calculation.

Based on the assumptions given, data for 2016 would look like this:

Note: Dividends will not be paid as earnings after tax is negative. The Interest amount of 367 is derived as : (660+1548+External funding for 2016)*10%

Balance Sheet for 2016

The External Funding formula would be : Total Assets - Total(Liabilities and Equity) and Interest calculation in income statement would be (660 +1548 +External funding for the 2016)*10%. Since the two are dependent on each other, a circular reference gets formed. As mentioned at the beginning, the iteration facility in excel needs to be enabled in order to calculate the two.

Note: 1548 is nothing but the external funding in 2015.