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

Break-Even Decisions

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

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. _________________

ROE Analysis

1. Below the balance sheet (e.g., in row 58) calculate return on equity for each year 2012 to 2015. (Note: To calculate ROE, one could reasonably divide net income by equity in the same year, equity in the previous year, or an average of the two. Here let's just divide by equity in the same year.)

Q11: How does projected ROE for 2015 compare to 2012? _______________________

2. To better understand projected ROE for 2015, calculate the three levers of ROE for 2012 to 2015 in the rows below ROE. Recall that the three levers are profit margin, asset turnover and leverage (assets/equity).

Q12: What is primarily responsible for the projected fall in ROE in 2015 compared to 2012? _______________

Q13: Why is leverage projected to increase so much for 2015, given that the increase in previous years has been relatively moderate? ______________________________________________________I've already done a part of it. I don't know what I'm doing right or wrong. Other parts don't understand. I hope I can get the formula and reason for the conclusion. It's not just the answer

2011 2012 New England Corporation Pro forma financial statements 2013 Actual 2014 Actual 2015 Pro forma Assumed % of Sales Actual Actual Assumptions 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.96% Sales Cost of goods sold SG&A expense Depreciation expense EBIT Income Statement ($ millions) $62498 40.121 5.578 3.268 $13.531 $78.673 5 2.578 7.135 4.298 $14.662 $95.023 63.186 8.241 6.106 $17.490 $117.193 77.162 10.417 6 .687 $22.928 2012 2013 0.641965451 0.668313566 0.089245027 0.090694278 | 0.052288408 0.054627312 2014 AVERAGES 0.664957252 0.65841209 0.086723397 0.088887567 0.064260367 0.057058696 3.455 Interest expense Pre-tax income 4.567 $10.094 6.724 $10.766 11.243 $11.685 $10.076 Taxes INet income 4 663 $5 413 5.038 $5.056 5.092 $5 674 5.310 $6.375 Allocation of net income Dividends Addition to retained earnings $2.124 00 $3.289 $2.432 $2.624 6 94 $2.921 $2.752 $2752 $2.674 $3.701 Balance Sheet($ millions) ASSETS I Current assets Cash and marketable securities Accounts receivable Inventory Total current assets $2.861 13.789 17.909 $34.559 $5.275 | 14.341 22.402 $42.017 $6.105 17.276 24.623 $48.004 $7.916 22.854 30.991 $61.761 $9.583 26.938 3 9021 $75.541 2012 0.0843968411 83.75640825 203.79567711 2013 0.07760467 80.15069318 170.93007 2014 AVERAGES 0.0833073751 0.0817696291% of sales 87.78525259 83.89745134 collection period 179.0216282] 184.5824584 days in inventory 4.544596913| 4.291084246 4.324200087 4.3866270821 Gross PPE Accumulated depreciation Net PPE 222.199 66.142 $156.056 284.026 69.410 $214.616 337.593 73.708 $263.885 410.898 79.814 $331.083 $14.083 86.501 $427.582 Total assets $190.615 $256.634 $311.889 $392.844 $503.124 LIABILITIES AND EQUITY Current liabilities Accounts payable Short-term debt Total current liabilities $21.326 $2306 20.676 $42.002 $48.500 son 28.830 $77.330 $56.173 123 40.450 $96.622 $63.250 53.258 $116.508 $84.317 60.000 $144.317 441.2244867| 389 95311751 365 36912391 398 8489094 Payables period 0.461299314 0.5141465131 0.560479169 0.51 1974999% of sales T D Long-term debt (PLUG) Total liabilities 26.800 | $68 802 30.910 $108.240 35.240 $131.862 48.930116.659 $165.438 $260 976 1.39 1.36 1.55 1.435820202 % of sales Shareholders' equity Paid-in capital Retained carings Total shareholders' equity 60.300 61.513 $121.813 | 87.100 64.802 $151.902 | 107.200 67.426 $174.626 147.400 70.178 $217.578 168.269 73.879 $242.148 T T T T Total liabilities and shareholders' equity $190.615| $260.142 $306.488 $383.017 $503.124 2011 2012 New England Corporation Pro forma financial statements 2013 Actual 2014 Actual 2015 Pro forma Assumed % of Sales Actual Actual Assumptions 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.96% Sales Cost of goods sold SG&A expense Depreciation expense EBIT Income Statement ($ millions) $62498 40.121 5.578 3.268 $13.531 $78.673 5 2.578 7.135 4.298 $14.662 $95.023 63.186 8.241 6.106 $17.490 $117.193 77.162 10.417 6 .687 $22.928 2012 2013 0.641965451 0.668313566 0.089245027 0.090694278 | 0.052288408 0.054627312 2014 AVERAGES 0.664957252 0.65841209 0.086723397 0.088887567 0.064260367 0.057058696 3.455 Interest expense Pre-tax income 4.567 $10.094 6.724 $10.766 11.243 $11.685 $10.076 Taxes INet income 4 663 $5 413 5.038 $5.056 5.092 $5 674 5.310 $6.375 Allocation of net income Dividends Addition to retained earnings $2.124 00 $3.289 $2.432 $2.624 6 94 $2.921 $2.752 $2752 $2.674 $3.701 Balance Sheet($ millions) ASSETS I Current assets Cash and marketable securities Accounts receivable Inventory Total current assets $2.861 13.789 17.909 $34.559 $5.275 | 14.341 22.402 $42.017 $6.105 17.276 24.623 $48.004 $7.916 22.854 30.991 $61.761 $9.583 26.938 3 9021 $75.541 2012 0.0843968411 83.75640825 203.79567711 2013 0.07760467 80.15069318 170.93007 2014 AVERAGES 0.0833073751 0.0817696291% of sales 87.78525259 83.89745134 collection period 179.0216282] 184.5824584 days in inventory 4.544596913| 4.291084246 4.324200087 4.3866270821 Gross PPE Accumulated depreciation Net PPE 222.199 66.142 $156.056 284.026 69.410 $214.616 337.593 73.708 $263.885 410.898 79.814 $331.083 $14.083 86.501 $427.582 Total assets $190.615 $256.634 $311.889 $392.844 $503.124 LIABILITIES AND EQUITY Current liabilities Accounts payable Short-term debt Total current liabilities $21.326 $2306 20.676 $42.002 $48.500 son 28.830 $77.330 $56.173 123 40.450 $96.622 $63.250 53.258 $116.508 $84.317 60.000 $144.317 441.2244867| 389 95311751 365 36912391 398 8489094 Payables period 0.461299314 0.5141465131 0.560479169 0.51 1974999% of sales T D Long-term debt (PLUG) Total liabilities 26.800 | $68 802 30.910 $108.240 35.240 $131.862 48.930116.659 $165.438 $260 976 1.39 1.36 1.55 1.435820202 % of sales Shareholders' equity Paid-in capital Retained carings Total shareholders' equity 60.300 61.513 $121.813 | 87.100 64.802 $151.902 | 107.200 67.426 $174.626 147.400 70.178 $217.578 168.269 73.879 $242.148 T T T T Total liabilities and shareholders' equity $190.615| $260.142 $306.488 $383.017 $503.124