Instructions:

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 pro formas 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.[1] 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? _________________

2013 2014 2015 Assumed New England Corporation Pro forma financial statements 2011 2012 Actual Actual Assumptions Actual Actual Pro forma % of Sales Sales growth rate Tax rate Dividend payout rate Interest rate (short-term debt) Interest rate (long-term debt) 46.28% 39.24% 6.30% 5.30% 25.88% 49.91% 48.10% 6.50% 5.50% 20.78% 40.14% 38.50% 7.15% 5.96% 23.33% 45.44% 41.95% 7.15% 5.969 1 Income Statement ($ millions) S62.498 40.121 5.578 3.268 S13.531 S78.673 52.578 7.135 4.298 S14.662 $95.023 63.186 8.241 6.106 S17.490 S117.193 77.162 10.417 6.687 $22.928 65.8% 8.9% 5.7% 3.455 S10.076 4.567 S10.094 6.724 S10.766 4.663 S5.413 5.038 $5.056 5.092 S5.674 1 Sales 2 Cost of goods sold 3 SG&A expense Depreciation expense 5 EBIT 5 7 Interest expense 8 Pre-tax income 2 Taxes 1 Net income 2 3 Allocation of net income: 4 Dividends 5 Addition to retained earnings 5 7 3 ASSETS Current assets Cash and marketable securities 1 Accounts receivable 2 Inventory 3 Total current assets + 5 Gross PPE 5 Accumulated depreciation 7 Net PPE 3 - Total assets S2.124 S3.289 S2.432 S2.624 S2.921 S2.752 Balance Sheet ($ millions $2.861 13.789 17.909 S34.559 S5.275 14.341 22.402 S42.017 S6.105 17.276 24.623 $48.004 S7.916 22.854 30.991 S61.761 222.199 66.142 SI56.056 284.026 69.410 S214.616 337.593 73.708 S263.885 410.898 79.814 S331.083 S190.615 S256.634 S311.889 S392.844 $21.326 20.676 S42.002 S48.500 28.830 S77.330 S56.173 40.450 S96.622 563.250 53.258 S116.508 1 LIABILITIES AND EQUITY 2 Current liabilities 3 Accounts payable 2 Short-term debt 5 Total current liabilities 5 7 Long-term debt (PLUG) 3 Total liabilities 2 Shareholders' equity 1 Paid-in capital 2 Retained earnings 3 Total shareholders' equity 26.800 S68.802 30.910 S108.240 35.240 S131.862 48.930 S165.438 60.300 61.513 S121.813 87.100 64.802 SI51.902 107.200 67.426 SI74.626 147.400 70.178 S217.578 S190.615 S260.142 S306.488 S383.017 5 Total liabilities and shareholders' equity 5 2013 2014 2015 Assumed New England Corporation Pro forma financial statements 2011 2012 Actual Actual Assumptions Actual Actual Pro forma % of Sales Sales growth rate Tax rate Dividend payout rate Interest rate (short-term debt) Interest rate (long-term debt) 46.28% 39.24% 6.30% 5.30% 25.88% 49.91% 48.10% 6.50% 5.50% 20.78% 40.14% 38.50% 7.15% 5.96% 23.33% 45.44% 41.95% 7.15% 5.969 1 Income Statement ($ millions) S62.498 40.121 5.578 3.268 S13.531 S78.673 52.578 7.135 4.298 S14.662 $95.023 63.186 8.241 6.106 S17.490 S117.193 77.162 10.417 6.687 $22.928 65.8% 8.9% 5.7% 3.455 S10.076 4.567 S10.094 6.724 S10.766 4.663 S5.413 5.038 $5.056 5.092 S5.674 1 Sales 2 Cost of goods sold 3 SG&A expense Depreciation expense 5 EBIT 5 7 Interest expense 8 Pre-tax income 2 Taxes 1 Net income 2 3 Allocation of net income: 4 Dividends 5 Addition to retained earnings 5 7 3 ASSETS Current assets Cash and marketable securities 1 Accounts receivable 2 Inventory 3 Total current assets + 5 Gross PPE 5 Accumulated depreciation 7 Net PPE 3 - Total assets S2.124 S3.289 S2.432 S2.624 S2.921 S2.752 Balance Sheet ($ millions $2.861 13.789 17.909 S34.559 S5.275 14.341 22.402 S42.017 S6.105 17.276 24.623 $48.004 S7.916 22.854 30.991 S61.761 222.199 66.142 SI56.056 284.026 69.410 S214.616 337.593 73.708 S263.885 410.898 79.814 S331.083 S190.615 S256.634 S311.889 S392.844 $21.326 20.676 S42.002 S48.500 28.830 S77.330 S56.173 40.450 S96.622 563.250 53.258 S116.508 1 LIABILITIES AND EQUITY 2 Current liabilities 3 Accounts payable 2 Short-term debt 5 Total current liabilities 5 7 Long-term debt (PLUG) 3 Total liabilities 2 Shareholders' equity 1 Paid-in capital 2 Retained earnings 3 Total shareholders' equity 26.800 S68.802 30.910 S108.240 35.240 S131.862 48.930 S165.438 60.300 61.513 S121.813 87.100 64.802 SI51.902 107.200 67.426 SI74.626 147.400 70.178 S217.578 S190.615 S260.142 S306.488 S383.017 5 Total liabilities and shareholders' equity 5