Financial Forecasting in Excel

Hang_321
ComputerLab1Spreadsheet.xls

Financial statements

New England Corporation 2003 2004
Financial Plan Actual Forecast
Key Assumptions
Sales growth rate
Tax rate 40.2%
Dividend payout ratio 38.5%
Interest rate on long-term debt 8.5%
Capital expenditures $40.00
Income Statement (Mil.$)
Sales $93.02
Cost of goods sold $60.19
Selling, gen & adm expenses $6.84
Depreciation expense $6.61
EBIT $19.39
Interest expense $6.71
Pre-tax income $12.68
Taxes $5.09
Net income $7.59
Allocation of net income:
Dividends $2.92
Retained earnings $4.67
Balance Sheet (Mil.$)
Assets
Current assets
Cash & equivalents $15.92
Receivables $122.85
Inventories $128.99
Total current assets $267.76
Property, plant & equipment (PPE) $200.90
Accumulated depreciation $81.91
Net PPE $118.99
Total assets $386.75
Liabilities and Shareholders' Equity
Current liabilities
Accounts payable $63.25
Current portion long-term debt $13.26
Total current liabilities $76.51
Long-term debt (PLUG) $88.93
Total liabilities $165.45
Shareholders' equity
Paid-in capital $147.40
Retained earnings $73.90
Total shareholders' equity $221.30
Total liabilities & equity $386.75

Answers

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