This spreadsheet is set up so that yellow cells contain numbers and white cells contain formulas. Follow the steps below to prepare pro formas for 20X4, assuming that New England Corporation will make up any funding shortfall with long-term debt and will use any funding surplus to pay down long-term debt (let long-term debt be the plug figure). As a starting point, assume that sales growth in 20X4 will be equal to 12.5%. Enter this figure in the Key Assumptions section in cell C4, and then, in cell C11, enter the formula for projected sales in 20X4, which is = B11*(1+C$4). This will give you sales 12.5% higher than in 20X3. Fill in the 20X4 forecast for each item that would be expected to vary with sales. Again, enter formulas. These will be similar to the formula in number 1 because we are just increasing these items by the sales growth rate. Putting the $ in front of the 4 means that you have locked in the reference to the growth rate, so if you copy and paste that formula into other cells that also grow with sales, you will have the correct formula. Assume, for simplicity, that depreciation expense grows in proportion to sales.

1. Fill in all of the cells in the 20X4 forecast that are just formulas (for example, pretax income is just EBIT-interest expense, and total current assets is just the sum of all the current assets).
2. Fill in the other items that would not be expected to vary with sales; that is, everything else except for long-term debt. Assumptions for the dividend payout ratio and tax rate should be made in the Key Assumptions section. For these, assume that a 20X4 projection equal to 20X3, that no new equity will be issued in 20X4, and that the current portion of long-term debt stays the same as 20X3.
3. Fill in long-term debt as the plug figure. This will be the balancing item that makes assets = liabilities + equity, but dont enter the formula as total assets (total liabilities + equity) or you will get a circular reference. Instead, you need to make the formula be total assets current liabilities total equity. This will balance the balance sheet and will not be circular

Question 1: Under the assumptions outlined above, what level of long-term debt will be required by New England Corporation in 20X4?

Question 2: What is the projected net income for New England Corporation in 20X4?

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