Part C: Break-Even Decisions Return all assumptions to their initial (Part A) values, including returning sales growth back to the average of the previous two years, and returning SG&A/Sales and PPE/Sales back to the average of the previous 3 years. (The Undo button can do this quickly.) Assume now that New England Corp. has determined that they cannot exceed $100 million in long-term debt. So they are looking for other ways to remedy the shortfall in financing. Determine what changes they would have to make under the following options:

Q7: What if they opt to remedy the shortfall by reducing sales growth? What is the highest growth rate they could achieve and not exceed the debt limit? _________________

Q8: Return sales growth to its initial level (average of previous 2 years). Now suppose that they want to remedy the shortfall by cutting the dividend payout ratio. Will this get them under the debt ceiling? _________________

Q9: Return the dividend payout rate to its initial level (average of previous 3 years). Now suppose that they want to remedy the shortfall by using fixed assets more efficiently (i.e., by cutting PPE/Sales). Use Solver to find what PPE/Sales would have to be reduced to in order to stay under the debt ceiling. _________________

PREVIOUS PARTS

Part A: Create pro forma financial statements. This spreadsheet is set up so that green cells contain numbers and yellow cells contain formulas. Follow the steps below to prepare pro formas for year 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. (This is already done. Please see cell F4 and check the formula. Now you are on your own for the rest of the steps)

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. I did one example for you. Please see cell H12) 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. (Hint: yellow cells of column H tell you what items are dependent on Assumed % 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) (Hint: you can refer to formulas used for item calculations for year 2011 2014 to get guidance).

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

2a 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? _________________

Part B: Scenario Analysis

1. Contraction. Assume increased competition and a depressed economy limits sales growth to 7% in 2015. In addition, rising interest rates push the rate on short-term debt to 9.5%. Also, rising costs lower gross margins, pushing COGS/Sales to 68.5%.

Q3: Under the contraction scenario, what level of long-term debt will be required in 2015? _________________

Q4: What is projected net income? _________________

2. High growth. Unexpected demand pushes sales growth to 30% in 2015. Because some costs are fixed, SG&A/Sales drops to 7.5%. Also, much of the sales growth is supported by existing excess capacity, so PPE/Sales is projected to be only 425%. COGS/Sales returns to its initial level (average of previous 3 years). The interest rate on short-term debt returns to its initial level.

Q5: Under the high-growth scenario, what level of long-term debt will be required in 2015? _________________

Q6: What is projected net income? _________________

F G 1 J K L M N N O P Q E 2014 2015 Pro forma H Assumed % of Sales Actual 20.7896 40.14% 38.50% 7.15% 5.96% 23.33 45.449 41.959 7.15% 5.96% Required LT Debt by Sales Growth Rate 5% 10% 15% 20% 25% 30% $95.023 63.186 8.241 6.106 $17.490 $117.193 77.162 10.417 6.687 $22.928 65.8% 8.9% 5.7% Sensitvity of LT Debt to Growth Rate 6.724 $10.766 6.724 $16.204 $1.200 $1.000 5.092 $5674 7.364 $8 840 $0.800 $2.921 $2.752 S $0.500 $3.708 $5.132 LT Debt in millions $0.400 $0.200 $0.000 D 1 New England Corporation 2011 2012 2013 2 Pro forma financial statements Actual Actual Actual 3 Assumptions 4 Sales growth rate 25.88% 5 Tax rate 46.28 49.919 6 Dividend payout rate 39.24% 48.10% 7 Interest rate (short-term debt) 6.30% 6.50% 8 Interest rate (long-term debt) 5.30% 5.50% 9 10 Income Statement ($ millions) 11 Sales $62.498 $78.673 12 Cost of goods sold 40.121 52.578 13 SG&A expense 5.578 7.135 14 Depreciation expense 3.268 4.298 15 EBIT $13.531 $14.662 16 17 Interest expense 3.455 4.567 18 Pre-tax income $10.076 $10.094 19 20 Taxes 4.663 5.038 21 Net income $5.413 $5.056 22 23 Allocation of net income: 24 Dividends $2.124 $2.432 25 Addition to retained eamings $3.289 $2.624 26 27 Balance Sheet (5 millions) 28 ASSETS 29 Current assets 30 Cash and marketable securities $2.861 55.275 $6.105 31 Accounts receivable 13.789 14341 17.276 32 Inventory 17.909 22.402 24.623 33 Total current assets $34.559 $42.017 $48.004 34 35 Gross PPE 222.199 284.026 337,593 36 Accumulated depreciation 66.142 69 410 73.708 37 Net PPE $156.056 $214.616 $263.885 38 39 Total assets $190.615 $256.634 $311.889 40 41 LIABILITIES AND EQUITY 42 Current liabilities 43 Accounts payable $21.326 $48.500 $56.173 44 Short-term debt 20.676 28.830 40.450 45 Total current liabilities $42.002 $77.330 $96.622 46 47 Long term debt (PLUG 26.800 30.910 35.240 48 Total liabilities $68 802 $108 240 $131.862 49 50 Shareholders' equity 51 Paid-in capital 60.300 87.100 107 200 52 Retained earnings 61.513 64.802 67.426 53 Total shareholders' equity $121.813 $151.902 $174.626 54 55 Total liabilities and shareholders' equity $190.615 $260.142 $306.488 56 57 58 ROE ANALYSIS 3.6% 2.9% 59 60 Profit margin 8.79 6.4% 61 Asset tumover 0.24 0.25 62 Leverage 1.69 1.79 63 $7.916 22.854 30.991 $61.761 8.2% 23.0% 33.3% 096 5% 25% 10% $9.583 $26.938 $38.969 $75.489 30% 35% 15% 20% Sales Growth 438.7% 410.898 79.814 $331.083 514.083 86.501 $427.582 $392.844 $503.072 $63.250 53.258 $ $116.508 $84 210 $60.000 $144.210 71.9% 51.2% 48.930 $165 438 136.152 $280.361 147.400 70.178 $217 578 147.400 75.310 $222.710 $383.017 $503.072 2.6% 4.0% 6.0% 0.24 1.81 7.5% 0.23 2.26 68 69 70 Questions and Answers 71 Q1: Under the assumptions outlined above, what level of long-term debt will be required by New England Corporation in 2015? 72 Answer 73 136.152 74 Q2: What is projected net income for New England in 2015? 75 Answer 76 8.84 77 Q3: Under the contraction scenario, what level of long-term debt will be required in 2015? 78 Answer 79 80.276 80 Q4: What is projected net income? 81 Answer 82 5.026 83 Q5: Under the high-growth scenario, what level of long-term debt will be required in 2015? 84 Answer 85 142.056 86 Q6: What is projected net income? 87 Answer 88 10.452 89 Q7: What if they opt to remedy the shortfall by reducing sales growth? What is the highest growth rate they could achieve and not exceed the debt limit? 90 Answer 91 92 Q8: Return sales growth to its initial level (average of previous 2 years). Now suppose that they want to remedy the shortfall by cutting the dividend payout ratio. Will this get them under the debt ceiling? 93 Answer 94 95 29: Return the dividend payout rate to its initial level (average of previous 3 years). Now suppose that they want to remedy the shortfall by using fixed assets more efficiently (i.e., by cutting PPE/Sales). Use Solver to find what PPE/Sales would have to be reduced to in order to stay under the debt ceiling. 96 Answer 97 no F G 1 J K L M N N O P Q E 2014 2015 Pro forma H Assumed % of Sales Actual 20.7896 40.14% 38.50% 7.15% 5.96% 23.33 45.449 41.959 7.15% 5.96% Required LT Debt by Sales Growth Rate 5% 10% 15% 20% 25% 30% $95.023 63.186 8.241 6.106 $17.490 $117.193 77.162 10.417 6.687 $22.928 65.8% 8.9% 5.7% Sensitvity of LT Debt to Growth Rate 6.724 $10.766 6.724 $16.204 $1.200 $1.000 5.092 $5674 7.364 $8 840 $0.800 $2.921 $2.752 S $0.500 $3.708 $5.132 LT Debt in millions $0.400 $0.200 $0.000 D 1 New England Corporation 2011 2012 2013 2 Pro forma financial statements Actual Actual Actual 3 Assumptions 4 Sales growth rate 25.88% 5 Tax rate 46.28 49.919 6 Dividend payout rate 39.24% 48.10% 7 Interest rate (short-term debt) 6.30% 6.50% 8 Interest rate (long-term debt) 5.30% 5.50% 9 10 Income Statement ($ millions) 11 Sales $62.498 $78.673 12 Cost of goods sold 40.121 52.578 13 SG&A expense 5.578 7.135 14 Depreciation expense 3.268 4.298 15 EBIT $13.531 $14.662 16 17 Interest expense 3.455 4.567 18 Pre-tax income $10.076 $10.094 19 20 Taxes 4.663 5.038 21 Net income $5.413 $5.056 22 23 Allocation of net income: 24 Dividends $2.124 $2.432 25 Addition to retained eamings $3.289 $2.624 26 27 Balance Sheet (5 millions) 28 ASSETS 29 Current assets 30 Cash and marketable securities $2.861 55.275 $6.105 31 Accounts receivable 13.789 14341 17.276 32 Inventory 17.909 22.402 24.623 33 Total current assets $34.559 $42.017 $48.004 34 35 Gross PPE 222.199 284.026 337,593 36 Accumulated depreciation 66.142 69 410 73.708 37 Net PPE $156.056 $214.616 $263.885 38 39 Total assets $190.615 $256.634 $311.889 40 41 LIABILITIES AND EQUITY 42 Current liabilities 43 Accounts payable $21.326 $48.500 $56.173 44 Short-term debt 20.676 28.830 40.450 45 Total current liabilities $42.002 $77.330 $96.622 46 47 Long term debt (PLUG 26.800 30.910 35.240 48 Total liabilities $68 802 $108 240 $131.862 49 50 Shareholders' equity 51 Paid-in capital 60.300 87.100 107 200 52 Retained earnings 61.513 64.802 67.426 53 Total shareholders' equity $121.813 $151.902 $174.626 54 55 Total liabilities and shareholders' equity $190.615 $260.142 $306.488 56 57 58 ROE ANALYSIS 3.6% 2.9% 59 60 Profit margin 8.79 6.4% 61 Asset tumover 0.24 0.25 62 Leverage 1.69 1.79 63 $7.916 22.854 30.991 $61.761 8.2% 23.0% 33.3% 096 5% 25% 10% $9.583 $26.938 $38.969 $75.489 30% 35% 15% 20% Sales Growth 438.7% 410.898 79.814 $331.083 514.083 86.501 $427.582 $392.844 $503.072 $63.250 53.258 $ $116.508 $84 210 $60.000 $144.210 71.9% 51.2% 48.930 $165 438 136.152 $280.361 147.400 70.178 $217 578 147.400 75.310 $222.710 $383.017 $503.072 2.6% 4.0% 6.0% 0.24 1.81 7.5% 0.23 2.26 68 69 70 Questions and Answers 71 Q1: Under the assumptions outlined above, what level of long-term debt will be required by New England Corporation in 2015? 72 Answer 73 136.152 74 Q2: What is projected net income for New England in 2015? 75 Answer 76 8.84 77 Q3: Under the contraction scenario, what level of long-term debt will be required in 2015? 78 Answer 79 80.276 80 Q4: What is projected net income? 81 Answer 82 5.026 83 Q5: Under the high-growth scenario, what level of long-term debt will be required in 2015? 84 Answer 85 142.056 86 Q6: What is projected net income? 87 Answer 88 10.452 89 Q7: What if they opt to remedy the shortfall by reducing sales growth? What is the highest growth rate they could achieve and not exceed the debt limit? 90 Answer 91 92 Q8: Return sales growth to its initial level (average of previous 2 years). Now suppose that they want to remedy the shortfall by cutting the dividend payout ratio. Will this get them under the debt ceiling? 93 Answer 94 95 29: Return the dividend payout rate to its initial level (average of previous 3 years). Now suppose that they want to remedy the shortfall by using fixed assets more efficiently (i.e., by cutting PPE/Sales). Use Solver to find what PPE/Sales would have to be reduced to in order to stay under the debt ceiling. 96 Answer 97 no