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 don’t enter the formula as total assets – (total liabilities + equity) or you will get a circular reference (one that can’t be remedied with the 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 the projected net income for New England in 2015? _________________

 

Transcribed Image Text:

1 New England Corporation 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 8222288 228523323 20 21 23 24 26 27 30 31 34 36 25 Addition to retained earnings 37 38 39 40 41 42 43 44 99999858333 29 Current assets 46 49 Pro forma financial statements Sales growth rate Tax rate 51 Dividend payout rate Interest rate (short-term debt) Interest rate (long-term debt) 52 Sales Cost of goods sold SG&A expense Depreciation expense EBIT Interest expense Pre-tax income 54 A Taxes Net income Allocation of net income: Dividends ASSETS Total assets LIABILITIES AND EQUITY Current liabilities Accounts payable Short-term debt 45 Total current liabilities. Cash and marketable securities Accounts receivable Inventory Total current assets 47 Long-term debt (PLUG) 48 Total liabilities Gross PPE Accumulated depreciation Net PPE 50 Shareholders' equity Paid-in capital Retained earnings Total shareholders' equity 2011 Actual Assumptions 55 Total liabilities and shareholders' equity C 2012 Actual Income Statement ($ millions) 46.28% 39.24% 6.30% 5.30% $190.615 3.455 $10.076 Balance Sheet ($ millions) 4.663 $5.413 $2.124 $3.289 $190.615 D 2013 Actual $62.498 $78.673 40.121 52.578 5.578 7.135 8.241 3.268 4.298 6.106 $13.531 $14.662 $17.490 25.88% 20.78% 49.91% 40.14% 48.10% 38.50% 7.15% 5.96% 6.50% 5.50% 26.800 30.910 $68.802 $2.861 $5.275 $6.105 17.276 13.789 14.341 17.909 22.402 24.623 $34.559 $42.017 $48.004 5.038 $5.056 222.199 284.026 337.593 66.142 $156.056 E F 2014 2015 Actual Pro forma 4.567 6.724 $10.094 $10.766 $2.432 $2.624 $95.023 63.186 5.092 $5.674 $2.921 $2.752 410.898 79.814 69.410 73.708 $214.616 $263.885 $331.083 $7.916 22.854 30.991 $61.761 $256.634 $311.889 $392.844 $21.326 $48.500 $56.173 20.676 40.450 28.830 $42.002 $77.330 $96.622 $116.508 $63.250 53.258 35.240 48.930 $108.240 $131.862 $165.438 60.300 87.100 107.200 147.400 70.178 61.513 64.802 67.426 $121.813 $151.902 $174.626 $217.578 $260.142 $306.488 $383.017 H Assumed % of Sales 1 New England Corporation 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 8222288 228523323 20 21 23 24 26 27 30 31 34 36 25 Addition to retained earnings 37 38 39 40 41 42 43 44 99999858333 29 Current assets 46 49 Pro forma financial statements Sales growth rate Tax rate 51 Dividend payout rate Interest rate (short-term debt) Interest rate (long-term debt) 52 Sales Cost of goods sold SG&A expense Depreciation expense EBIT Interest expense Pre-tax income 54 A Taxes Net income Allocation of net income: Dividends ASSETS Total assets LIABILITIES AND EQUITY Current liabilities Accounts payable Short-term debt 45 Total current liabilities. Cash and marketable securities Accounts receivable Inventory Total current assets 47 Long-term debt (PLUG) 48 Total liabilities Gross PPE Accumulated depreciation Net PPE 50 Shareholders' equity Paid-in capital Retained earnings Total shareholders' equity 2011 Actual Assumptions 55 Total liabilities and shareholders' equity C 2012 Actual Income Statement ($ millions) 46.28% 39.24% 6.30% 5.30% $190.615 3.455 $10.076 Balance Sheet ($ millions) 4.663 $5.413 $2.124 $3.289 $190.615 D 2013 Actual $62.498 $78.673 40.121 52.578 5.578 7.135 8.241 3.268 4.298 6.106 $13.531 $14.662 $17.490 25.88% 20.78% 49.91% 40.14% 48.10% 38.50% 7.15% 5.96% 6.50% 5.50% 26.800 30.910 $68.802 $2.861 $5.275 $6.105 17.276 13.789 14.341 17.909 22.402 24.623 $34.559 $42.017 $48.004 5.038 $5.056 222.199 284.026 337.593 66.142 $156.056 E F 2014 2015 Actual Pro forma 4.567 6.724 $10.094 $10.766 $2.432 $2.624 $95.023 63.186 5.092 $5.674 $2.921 $2.752 410.898 79.814 69.410 73.708 $214.616 $263.885 $331.083 $7.916 22.854 30.991 $61.761 $256.634 $311.889 $392.844 $21.326 $48.500 $56.173 20.676 40.450 28.830 $42.002 $77.330 $96.622 $116.508 $63.250 53.258 35.240 48.930 $108.240 $131.862 $165.438 60.300 87.100 107.200 147.400 70.178 61.513 64.802 67.426 $121.813 $151.902 $174.626 $217.578 $260.142 $306.488 $383.017 H Assumed % of Sales

Answer rating: 100% (QA)

Explanation Formula sheet New England Corporation 2011 2012 2013 2014 2015 Assumed Pro forma financial statements Actual Actual Actual Actual Proforma of Sales Assumptions Sales growth rate 02588 0207... View the full answer