Question

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

Answer 1

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			0.2588	0.2078	=AVERAGE(D4:E4)
Tax rate		0.4628	0.4991	0.4014	=AVERAGE(C5:E5)
Dividend payout rate		0.3924	0.481	0.385	=AVERAGE(C6:E6)
Interest rate (Short-term debt)		0.063	0.065	0.0715	=E7
Interest rate (Long-term debt)		0.053	0.055	0.0596	=E8
Income Statement ($ million)
Sales		62498	78673	95023	=E11*(1+F4)
Cost of Goods Sold		40121	52578	63186	=$F$11*H12		=AVERAGE(C12/$C$11,D12/$D$11,E12/$E$11)
SG&A Expenses		5578	7135	8241	=$F$11*H13		=AVERAGE(C13/$C$11,D13/$D$11,E13/$E$11)
Depreciation Expenses		3268	4298	6106	=$F$11*H14		=AVERAGE(C14/$C$11,D14/$D$11,E14/$E$11)
EBIT		=C11-C12-C13-C14	=D11-D12-D13-D14	=E11-E12-E13-E14	=F11-F12-F13-F14
Interest Expense		3455	4567	6724	=E44*F7+E47*F8
Pretax Income		=C15-C17	=D15-D17	=E15-E17	=F15-F17
Taxes		4663	5038	5092	=F18*F5
Net Income		=C18-C20	=D18-D20	=E18-E20	=F18-F20
Allocation of net income
Dividends		2124	2482	2921	=F21*F6
Addition to retained earnings		=C21-C24	=D21-D24	=E21-E24	=F21-F24
Balance Sheet ($ Million)
Assets
Current Assets
Cash and marketable securities	2861	5275	6105	7916	=$F$11*H30		=AVERAGE(C30/$C$11,D30/$D$11,E30/$E$11)
Accounts receivable	13789	14341	17276	22854	=$F$11*H31		=AVERAGE(C31/$C$11,D31/$D$11,E31/$E$11)
Inventory	17909	22402	24623	30991	=$F$11*H32		=AVERAGE(C32/$C$11,D32/$D$11,E32/$E$11)
Total current assets	=SUM(B30:B32)	=SUM(C30:C32)	=SUM(D30:D32)	=SUM(E30:E32)	=SUM(F30:F32)
Gross PPE	222199	284026	337593	410898	=$F$11*H35		=AVERAGE(C35/$C$11,D35/$D$11,E35/$E$11)
Accumulated Depreciation	66142	69410	73708	79814	=E36+F14
Net PPE	=B35-B36	=C35-C36	=D35-D36	=E35-E36	=F35-F36
Total Assets	=B33+B37	=C33+C37	=D33+D37	=E33+E37	=F33+F37
Liabilities and equity
Current Liabilities
Accounts Payable	21326	48500	56173	63250	=$F$11*H43		=AVERAGE(C43/$C$11,D43/$D$11,E43/$E$11)
Short term debt	20676	28830	40450	53258	=F11*H44		=AVERAGE(C44/$C$11,D44/$D$11,E44/$E$11)
Total current liabilities	=SUM(B43:B44)	=SUM(C43:C44)	=SUM(D43:D44)	=SUM(E43:E44)	=SUM(F43:F44)
Long term debt	26800	30910	35240	48930	=F39-F53-F45
Total liabilities	=B45+B47	=C45+C47	=D45+D47	=E45+E47	=F45+F47
Shareholder's Equity
Paid in capital	60300	87100	107200	147400	=E51
Retained Earnings	61513	64802	67426	70178	=E52+F25
Total Shareholders' Equity	=SUM(B51:B52)	=SUM(C51:C52)	=SUM(D51:D52)	=SUM(E51:E52)	=SUM(F51:F52)
Total Liabilities and shareholders' Equity	=B48+B53	=C48+C53	=D48+D53	=E48+E53	=F48+F53