PLEASE SHOW HOW TO SOLVE USING EXCEL, PLEASE INCLUDE ALL FORMULAS USING FOR EXAMPLE: =PV(C42,C41.C40,0,0)

B4 fix =C4+1 A B I c D E F G H 1 2 3 4 Value in Millions Balance Sheet For the Years ended 2015 to 2019 and pro forma 2020 2020 2019 2018 2017 2016 2015 6,985 4,185 5,006 4,749 20.225 4,734 4,062 4.400 3,184 16.380 6,036 3,038 3,640 2,630 15.400 3,384 3,090 3,456 2,193 12.723 3,113 Same as previous year 2,931 Avg % of Sales of Prior 5 years 3,384 Avg % of Sales of Prior 5 years 2.268 Same as previous year 11.695 5 Assets 6 Cuma Asas 7 Cash and Equivalents 8 Receivables 9 Inventories 10 Other Current Assets 11 Tota/Cumarts 12 13 Mont-Quent Assers 14 Property. Plant & Equipment (Gross) 15 Accumulated Depreciation 16 Property, Plant & Equipment (Net) 17 Intangibles 18 Other Non-Current Assets 19 Toxa/Mar-CumartAsses 20 Total Assets 21 22 Liabilities & Shareholder's Equity 23 Cum Liabilities 24 Accounts Payable 25 Short-Term Debt 26 Other Current Liabilities 27 Toxa/Current Liables 28 29 Novruzlabs 26,325 11,993 14,332 24,163 2,703 47.1899 62,123 25,304 11196 14,108 23.900 1.925 23,542 10,438 13,104 13,507 1,875 29.485 43,890 23,070 9,721 13,349 13.430 1,831 29.610 41,333 22,821 See ASSUMPTIONS 9,726 Calculate (Hint: See Income) 13,095 8.300 Same as previous year 2,103 Same as previous year 56,313 35,194 3,802 11,441 9,796 25.000 3,617 8,287 10,243 22. NI 2,795 2,172 7,391 2359 2,205 3,731 6,768 2,075 Avg% of Sales of Prior 5 years 2.233 Same as previous year 5,538 Avg % of Sales of Prior 5 years 3,191 2.248 2,808 8.247 30,394 2,847 3,230 7,009 1,396 2 291 10.695 23,054 Calculate See Discretionary Funding") on line 44. Balance using 8,898 the techniquel showed you in dass. 894 Same as previous year 1,845 Same as previous year 8,320 1077 2,088 31.116 24.189 21,483 1,483 4,273 25,251 1,526 3.667 20,726 1,580 2,494 16,762 30 Long-Term Debt 31 Deferred Income Taxes 32 Other Non-Current Liabilities 33 Tota/N-CumartLiabileres 34 Total Liabilities 35 Sharwars Equity 36 Preferred Stock Equity 37 Common Stock Equity 38 Retained Earnings 39 Tota/E 40 Total Liabilities & Equity 41 42 Total Common Shares Outstanding 43 44 Discretionary Funding Needed 45 46 ASSUMPTIONS 47 Net Additions to Plan and Equipment 48 Life of New Equipment inYears 49 New Depreciation (Straight Line) 1634 1726 13,784 7.14 41,333 1,701 Same as previous year 1,559 Same as previous year 10,451 Calculate (Hint: See Income) 62,123 56,313 43,890 35,194 2.564 2.564 2.635 2,659 2,670 2,706 $10,000 10 (--Calculate this A5 fx Sales A B D F F G H 1 1 2 3 4 Dollars in Thousands Income Statement For the Years ended 2015 to 2019 and pro forma 2020 2020 2019 2018 SCORE 2017 2016 2015 $ 56,745 $ 25,920 30,825 18,010 1,895 10,920 346 834 10,432 3,130 7,302 $ 51,407 $ 23,343 28,064 16,504 1,733 9,827 152 629 9,350 2,805 6,545 $ 43.396 $ 20,438 22,958 13,383 1,703 7,872 238 561 7,549 2,265 5,284 $ 42,401 $ 20,005 22,396 12,571 1,693 8,132 308 603 7,837 2.351 5,486 $ 39,244 Trend 19,831 Avg % of Sales of Prior 5 years 19,413 12,406 Avg % of Sales of Prior 5 years 2,271 Calculate (see Assumptions) 4,736 674 Same as Prior Year 794 Calculate (see Assumptions) 4,616 1,385 3,231 $ 5 Sales 6 Cost of Sales 7 Gross Operating Profit 8 Selling, General & Administration Expenses 9 Depreciation & Amortization 10 EBIT 11 Other Income, Net 12 Interest expense 13 Pre-tax Income 14 Income Taxes 15 Total Net Income 16 17 ASSUMPTIONS 18 Tax Rate 19 Additional Depreciation (to add to last year's) 20 Additional Interest Exp (to add to last year's) 21 Additional Debt Rate 22 Basic EPS from Operations 23 Dividends per Common Share 24 Preferred Dividends 25 26 27 28 29 30 30% Hint: Look for additional depreciation on the Balance Sheet notes and link it here. Hint: What is the change in Long Term Debt from the Balance Sheet? Add the new expense to Last Year's expense 12.00% To be applied to Additional Debt as Additional Interest Expense. 1.03 136 $ 0.93 131 $ 0.82 125 $ 0.76 124 $ 0.7 Assume increase of dividends by 10% 121 Same as previous year $