70.2K

Verified Solution

Link Copied!

Consider the financial statements for New England Corporation provided below. The company expects sales to increase by 25% in 2019. Its applicable tax rate in 2019 is expected to be 21% (note that this is significantly less than the rate implied by the Income Statement for 2018). Its dividend payout ratio in 2019 will be exactly the same as what it is in 2018. Accounts payable are the only spontaneous liability for this firm. New Englands management plans to raise any funding needed for growth through long-term debt only. Its current interest rate on its existing short-term and long-term debt will remain the same for 2019, and it does not propose to pay down any of its existing short-term or long-term debt (so, effectively, it will be able to roll over its existing short-term debt at the same rate as it is paying currently). On any new long-term borrowings in 2019, New Englands creditors have indicated that they will charge 7.25%.

Based on all the information provided, estimate New England Corporations EFN for 2019 using the percentage of sales method. You MUST set up an Assumptions Box, set up the 2019 pro-forma Income Statement and Balance Sheet, and then use the automated iterations/auto-calc. process in Excel to solve for the estimated EFN.

Once you have copied and pasted this information into Excel, you will note there are no formulas in the Income Statement or Balance Sheet for 2018. When you set up the Pro-Forma Statements for 2019, you will need to make sure to enter formulas for various items that need a formula, including, but not limited to EBIT, EBT, Tax, Net Income, Total Assets, etc.

Do NOT use a manual process of running the iterations to find EFN. That is, you should have only ONE Prof-forma Income Statement and only ONE Pro-forma Balance Sheet for 2019. Using the correct process is likely to elicit a circular reference warning from Excel, which we have discussed how to address.

You will lose a significant number of points if you have multiple columns of pro-forma financials(such as one would need when running these iterations manually rather than relying on Excels built-in feature of running the iterations, which I have referred to as Auto-Calc).

New England Corporation
Income Statement ($ thousands)	2018
Sales	$95,023
Cost of goods sold	63,186
SG&A expense	8,241
Depreciation expense	6,106
EBIT	17,490
Interest expense	6,724
EBT	10,766
Taxes	5,092
Net income	5,674
Allocation of net income:
Dividends	$2,921
Addition to retained earnings	$2,753

Balance Sheet ($ thousands)	31-Dec-18
ASSETS
Current assets
Cash and marketable securities	$7,916
Accounts receivable	$22,854
Inventory	$30,991
Total current assets	$61,761
Net PPE	331,083
Total assets	$392,844
LIABILITIES AND EQUITY
Current liabilities
Accounts payable	$63,250
Short-term debt	$53,258
Total current liabilities	$116,508
Long-term debt	$58,757
Total liabilities	$175,265
Shareholders' equity
Common Stock & Paid-In Capital	$147,400
Retained earnings	$70,179
Total shareholders' equity	$217,579
Total liabilities and shareholders' equity	$392,844

Answer & Explanation Solved by verified expert

See Answer