Answered step by step
Verified Expert Solution
Question
00
1 Approved Answer
Hello. I am in need of a pro forma income statement and balance sheet for 2015 in the provided Excel spreadsheet attached here. Also, PART
Hello.
I am in need of a pro forma income statement and balance sheet for 2015 in the provided Excel spreadsheet attached here.
Also, PART TWO - I need an executive summary of the findings. This does not have to be a formal written response, however, I am looking for what will be needed for me to write my executive summary. Therefore, I am just looking for some key findings in a list.
Thanks!
New England Corporation Pro forma financial statements 2011 2012 2013 2014 2015 Actual Actual Actual Actual Pro forma 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% Income Statement ($ millions) Sales Cost of goods sold SG&A expense Depreciation expense EBIT $62.498 40.121 5.578 3.268 $13.531 $78.673 52.578 7.135 4.298 $14.662 $95.023 63.186 8.241 6.106 $17.490 Interest expense Pre-tax income 3.455 $10.076 4.567 $10.094 6.724 $10.766 Taxes Net income 4.663 $5.413 5.038 $5.056 5.092 $5.674 Allocation of net income: Dividends Addition to retained earnings $2.124 $3.289 $2.432 $2.624 $2.921 $2.752 Balance Sheet ($ millions) ASSETS 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 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 Total assets $190.615 $256.634 $311.889 $392.844 LIABILITIES AND EQUITY Current liabilities Accounts payable Short-term debt Total current liabilities $21.326 20.676 $42.002 $48.500 28.830 $77.330 $56.173 40.450 $96.622 $63.250 53.258 $116.508 Long-term debt (PLUG) Total liabilities 26.800 $68.802 30.910 $108.240 35.240 $131.862 48.930 $165.438 Shareholders' equity Paid-in capital Retained earnings 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 Total liabilities and shareholders' equity $190.615 $260.142 $306.488 $383.017 Assumed % of SalesStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started