Financial Forecasting in Excel

ComputerLab1Solutions.xls

Financial statements

New England Corporation	2003	2004
Financial Plan	Actual	Forecast
Key Assumptions
Sales growth rate		12.5%
Tax rate	40.2%	40.2%
Dividend payout ratio	38.5%	38.5%
Interest rate on long-term debt		8.5%
Capital expenditures		$40.00	Sensitivity of long-term debt required to sales growth
				5%	10%	15%	20%	25%
Income Statement (Mil.$)			$142.24	$127.93	$137.47	$147.00	$156.54	$166.08
Sales	$93.02	$104.65
Cost of goods sold	$60.19	$67.71
Selling, gen & adm expenses	$6.84	$7.70
Depreciation expense	$6.61	$7.43
EBIT	$19.39	$21.81
Interest expense	$6.71	$8.69
Pre-tax income	$12.68	$13.13
Taxes	$5.09	$5.27
Net income	$7.59	$7.86
Allocation of net income:
Dividends	$2.92	$3.02
Retained earnings	$4.67	$4.83
Balance Sheet (Mil.$)
Assets
Current assets
Cash & equivalents	$15.92	$17.91
receivables	$122.85	$138.21
inventories	$128.99	$145.11
Total current assets	$267.76	$301.23
Property, plant & equipment (PPE)	$200.90	$240.90
Accumulated depreciation	$81.91	$89.34
Net PPE	$118.99	$151.56
Total assets	$386.75	$452.79
Liabilities and Shareholders' Equity
Current liabilities
Accounts payable	$63.25	$71.16
Current portion long-term debt	$13.26	$13.26
Total current liabilities	$76.51	$84.42
Long-term debt (PLUG)	$88.93	$142.24
Total liabilities	$165.45	$226.66
Shareholders' equity
Paid-in capital	$147.40	$147.40
Retained earnings	$73.90	$78.73
Total shareholders' equity	$221.30	$226.13
Total liabilities & equity	$386.75	$452.79

Sales growth

LT debt in millions

Sensitivity of required LT debt to growth rate

Q1	$142.24	(million)
Q2	$7.86	(million)
Q3	$108.68	(million)
Q4	$5.76	(million)
Q5	$191.08	(million)
Q6	$9.31	(million)
Q7	3.5%
Q8	No. Even cutting the dividend to 0 leaves long-term debt at over $139 million.
Q9	5%	10%	15%	20%	25%
	$127.93	$137.47	$147.00	$156.54	$166.08