Part A: Create pro forma financial statements.

This spreadsheet is set up so that green cells contain numbers and white cells contain formulas. Follow the steps below to prepare proformas for 2015, assuming that New England Corp. will make up any funding shortfall with long-term debt, and will use any funding surplus to pay down long-term debt (i.e., let long-term debt be the plug figure).

1. As a starting point, assume that sales growth in 2015 will be equal to the average sales growth for 2013 and 2014. Enter the formula for this assumption in the Assumptions section, and then enter the formula for projected sales in 2015.

2. For all financial statement items that would be expected to vary with sales, use the historical average over the past three years of the ratio of that item to sales as a projection of the percentage of sales for 2015. I suggest that you enter these percentages in the column on the right Assumed % of Sales. (Hint: If you enter your formula correctly in one cell, locking in the appropriate references, then you can simply copy and paste the formula to other cells.) Having this column allows you to return and change the assumption for these items later on. For simplicity, in addition to items that typically vary as a percentage of sales, also assume that depreciation expense, Gross PPE, and short-term debt vary as a percentage of sales.

3. Fill in the 2015 forecast for each item that would be expected to vary with sales. (Again, if you enter the formula correctly, you can copy and paste it to other cells.)

4. Fill in all cells in the 2015 forecast that are just formulas (e.g., pre-tax income is just EBIT interest expense).

5. 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 tax rate, dividend payout rate, and interest rates should be made above in the Assumptions section. For the tax rate and dividend payout rate assume a 2015 projection equal to the average of the previous three years. Assume that interest rates will remain the same as the previous year. Also assume no new equity will be issued in 2015.

6. 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 (one that cant be remedied with iterative calculation). Instead, you need to make the formula be total assets current liabilities total equity. This will balance the balance sheet and not be circular.

Q1: Under the assumptions outlined above, what level of long-term debt will be required by New England Corporation in 2015?_________________

Q2: What is projected net income for New England in 2015? _________________

1 New England Corporation 2 Pro forma financial statements Actual Actual Actual | Actual pro forma%of Sales 2011 2012 2013 20142015 Assumed . - Assumptions 25.8890 20.7890 46.2890 49.9190 40.1496 39.24% 4S-10% 38.50% 6.30% 6.50% 7.15% 5.3090 5.5096 5.9696 4 Sales growth rate 5 Tax rate 8 Dividend payout rate 7 Interest rate (short-term debt) a Interest rate lon-term debt) Income Statement (S millions) 11 Sale $62.498$78.67395.023 40.121 52.578 63.186 3.241 12 Cost of goods sold 13 SG&A expense 14 Depreciation expense 15 EBIT 16 17 Interest expenee 18 Pre-tax income 19 20 Tazes 21 Net income 7.135 13.53 14.662 S17.490 10.07610.094 S10.766 4.663 038 $5.4135.056$5.74 23 Allocation of net income 24 Dividends 25 Addition to retained earnings S2.124 S2.432 $2.921 3.289 52.624 $2.752 Balance Sheet (S millions) 28 ASSETS 29 Current assets 30 Cash and marketable securities Accounts receivable $7.916 13.739 14.341 17.276 22.854 30.991 3459 $42.017 S48.004 61.761 7.909 32 Inventory 33 Total current assets 22.40224.623 35 Gross PPE 38 Accumulated depreciation 37 Net PPE 39 Total assets 222.199284.026337.593 410.898 5156.056 $214.616 $263.85 331.083 190.615 $256.634 S31189 392.844 66.142 69.410 41 LIABILITIES AND EQUITY 42 Currenlabilties 43 Accounts pavable $21.326$43.500 $56.173$63.250 53.258 S42.002 S77.330 96.622 5116.508 Short-tarm debt 20.676 40.450 45 46 47 48 Total current liabilities Long-term debt (PLUG Total liabilities 6.800 30.910 35.240 4.930 568.802 10.240131.862 165438 50 Shareholders' equity 51 Paid-in capital 52 Retained earnings 53 Total sharebolders' equity 60.300 87.100107.200 14740 $121.813 $151.902 $174.626 $217.578 55 Total liabilities and shareholders' equity 190.615 5260.142 306488 383.017