I need to make an excel spreadsheet for a companys 2020, 2019, & 2018 annual reports. I provided the company finanical statements, balance sheet and cash flow. I also attached what its supposed to look like. Thank you.
this is what it should look like:
finanical statment:
balance sheet:
cash flow:
End of 2020 End of 2019 End of 2018 name of company (amount in millions Balance Sheet Cash (and cash equivalents) Accounts Recievable Merchandise Inventory Total Current Assets Total Assets Total Current Liabilities Total Liabilities End of 2020 End of 2019 End of 2018 Market value and Share Market Value (Price Per Share) Earnings Per Share End of 2020 End of 2019 End of 2018 Other Net Sales COGS (Cost of Sales) Net Income 2020 2019 2018 Return on Assets = Net Incomel Average Total Assets Total Return on Assets 2020 2019 2018 Debt Ratio= Total Liability/Total Assets Total Debt Ratio 2020 2019 2018 Profit Margin= Net Incomel Net Sales Total Profit Margin 2020 2019 2018 Current Ratio= Current Assets/Current Liabilities Total Current Ratio 2020 2019 2018 Gross Margin Ratio= Net Sales-COGS/Net Sales Gross Margin Ratio Inventory Turnover=COGSIAverage Inventory 2020 2019 2018 2020 2019 2018 Accounts Receivable Turnover=Net Sales/Avg AR, net Total Accounts Receivable Turnover 2017 2016 5-year trend 2018 me World US Politics Economy Business Tech Markets Opinion DOORSARL TO Millions 2020 2019 4904 4553 4451 5.767 5.575 Sales/Revenue 13.69% 7.72 22.32% 3.44 Sales Growth 2577 2341 2224 2042 2,669 2120 Cost of Goods Sold (COGS) Ind DEA COGS excluding DLA 1,900 2.545 2457 117 104 120 142 124 Depreciation & Amortization Expense 117 104 120 17 124 Depreciation - 5 Amortization of Intangibles 2350 52 1009 35 COGS Growth 2409 09 2.563 28 Gross income 169 10.04 Gross income Growth Gross Profit Margin 65 2.096 2248 2.525 SGGA Expense 2.248 2:45 Us 2525 2.095 Other 566 210 10.90 12 SGA Growth 161 573 EBIT 18 30 78 265 6 Unusual Expense (13) 30) 026) 24 Non Operating income/Expense 21 10 19 10 4 Non-Operating Interest income . 82 55 69 Interest Expense 66 Interest Expense Growth 6.24 1973 24.06% 66 12 55 Gross interest Expense 38 - Interest Capitalized 500 (190) 349 407 Pretax income Pretax income Growth -13910 LOV - . Pretax Margin 03 13 54 215 1. Income Tax 10 19 11 income Tax Current Domestic 1 - 1 53 4 Income Tax Current Foreign 21 111 5 Income Tax-Deferred Domestic 190 6 3 50 Income Tax Deferred Foreign 395 25 25 21 Consolidated Nations (127) 395 285 285 291 Consolidated Net Income Minority Interest Expense 0 0 N 3 0 I Net Income (127) 395 283 281 291 Net Income Growth -132.22% 39.37% 0.62% -3.32% -2.86% (127) 395 283 281 291 1 Net Margin Net Income After Extraordinaries Net Income Avallable to common EPS (Basic) EPS (Basic) Growth (127) 395 283 281 291 (032) 0.97 0.73 0.72 0.74 1 -133.12% . 32.62% 1.24% -3.32% 397 389 377 391 391 (0.32) 097 0.73 0.72 0.74 Basic Shares Outstanding EPS (Diluted) EPS (Diluted) Growth Diluted Shares Outstanding -133.12 32.63% 1235 -301 397 408 389 391 391 303 EBITDA 697 661 585 567 EBITDA Growth -56.58% SSS 1801 2015 EBITDA Margin 6.BOX Assets 2020 2019 2018 2017 2016 5-year 1594 1015 713 376 1497 934 713 370 97 81 . - Fiscal year is December November. Al values USD Millions Cash & Short Term Investments Cash Only Short-Term investments Cash & Short Term investments Growth Cash & ST Investments/Total Assets Total Accounts Receivable Accounts Receivables. Net Accounts Receivables Gross Bad Debt/Doubtful Accounts 5702 68775 1 18.87 12:57 540 783 554 485 479 . 1 540 783 485 479 555 789 544 497 491 (15) 10 (12) (12) Accounts Receivable Growth -2000 4656 100 35 Accounts Receivable Turnover 3044 10:10 250 Inventories 799 776 753 711 809 5 3 3 3 Finished Goods Work in Progress Raw Materials Other Current Assets 4 5 4 4 4 1 1 175 188 & 157 119 115 175 Miscellaneous Current Assets 188 157 119 115 - 3126 2870 1997 1,686 1 1443 530 451 424 394 2547 Total Current Assets Net Property. Plant & Equipment Property, Plant & Equipment Gross Buildings Land & Improvements 1884 1435 1376 1250 478 576 466 422 379 9 8 3 E 8 B 487 5 490 Machinery & Equipment 453 408 24 Se 35 2 Construction in Progress 37 561 512 454 451 418 Computer Software and Equipment 1103 1054 Accumulated Depreciation 951 057 312 279 209 280 277 Intangible Assets 265 236 Net Goodwill 237 234 47 43 43 45 Net Other intangibles 262 146 117 118 107 Other Assets 107 146 118 252 117 Tangible Other Assets 3.543 4232 2.987 33358 Total Assets 5.641 Assets - Total Growth 5.50 1241 33292 1947% - Asset Turnover 0.90 . -2585 Return On Average Assets Liabilities & Shareholders' Equity Al values USD Millions ST Det & Current Portion LT Debt Short Term Debt 2020 2019 2018 2017 2016 3 yes 255 8 12 38 39 255 32 38 39 375 360 351 290 270 4205 2568 20365 2115 22 24 15 16 10 Accounts Payable Accounts Payable Growth Income Tax Payable Other Current Liabilities Accrued Pay Miscellaneous Current Liabilities . 892 775 653 443 179 718 223 552 299 356 181 262 308 Total Current Labels 1549 1167 1052 809 762 Current Ratio 2.02 246 217 2.27 5 2.21 Quick Ratio 149 270 133 24 Cash Ratio 103 0.87 06 0.72 0:49 Long-Term Debt 2405 1007 1020 1039 1022 Long Term Debt en Capitalized Lenses 1547 1007 1020 1039 1006 Non-Convertible Debt 1547 1007 1020 1039 1006 Capitated Lease Obligations 15 Provision for Risks & Charges 324 342 377 495 529 Deferred as (498) (408) 0980 5380 (523) Deferred Tues. Det 69 400 395 538 523 Other Liabilities 64 145 120 116 Other Lisbee Deferred Income) 84 64 145 126 116 34 2.661 2576 Non-Equity Reserves 2.528 2.396 299 79 Home World US Politics Economy 75.30% BO 21% 72.72% 62 87% 76.96% Total Liabilities/Total Assets 697 510 660 1299 1564 Common Equity (Total) 0 0 0 0 o 1 - . Common Stock Par/Carry Value Additional Paid in Capital Capital Surplus . 626 658 935 1114 1310 1101 1084 Retained Earnings (121) (158) (159) (165) Cumulative Translation Adjustment/Unrealised For Exch Gain (159) 4 3 2 14 6 3 Unrealized Gain/Loss Marketable Securities (297) (246) (269) (288) (271) Other Appropriated Reserves 17.06% 23.04% 36.94% 20.75% 18.635 Common Equity/Total Assets 1299 1.564 660 697 510 Total Shareholders' Equity 23.045 36.94% 18.63 20.75% 17.06% Total Shareholders' Equity/Total Assets 8 7 5 2 Accumulated Minority interest 1.299 1,572 667 702 512 Total Equity Llabilities & Shareholders Equity 5.641 4.232 3.543 3,358 2,987 Operating Activities Fiscal year is December November. All values USD Thousands 2020 2019 2018 2016 2017 5-Y (1271410) 3949800 285,244.0 284556.0 291209.0 Net Income before Extraordinaries -32,195 Net Income Growth 38.75 0.245 141795.0 1239420 120,205.0 117,387.0 103,678.0 136,5950 1229420 120,205.0 117,387.0 103,678 0 Depreciation Depletion & Amortization Depreciation and Depletion Amortization of intangible Assets Deferred Taxes & Investment Tax Credit Deferred Taxes 5.2000 2000 1 (95.244.0) 1 014963.0) (486.0) 134258.0 66,0780 (95.2440) (149630) 134258.0 (486.0) 66,078.0 - Other Funds 167,526,0 72,0250 19.308D 117.236.0 4,109.0 Funds from Operations 36936.0 575.9840 599,015.0 518,693.0 465 274,0 Changes in Working Capital 382.6500 (163.796.0) (138.64401 72480 (158,724.0) Receivables 234 2170 82.3440) (60.474,0) 39810 6,150,0 Inventories 93,096,0 (224540) (147,389.00 (14 4080) (121379.0) 1 Accounts Payable 12.500 10.0540 1610390 35714,0 43,040.0 Income Taxes Payable 9:3520 (2.590.0) 2.478.0 7653.0 Other Accruals 1070) 55.363.0) (44887.0) 9.408.0) (49,8800) Other Assets/Liabilities 113.9670 (310610 32 343.0) (11108.0) (443080) Net Operating Cash Flow 469,586,0 412,1880 420,3710 5259410 306,550,0 Net Operating Cash Flow Growth 13.93 -20.07% Net Operating Cash Flow/Sales 1055 7155 754 10.72% 6.73% 2020 2019 2018 2017 2016 5-year Investing Activities Al values USD Thousands Capital Expenditures Capital Expenditures (Fixed Assets) (130.383.0) (123560) (159.413.0) (118,678.00 (102,950.0) (130,3830) (179.356.00 (199,4130) (118,618.0) (102.950,0) Capital Expenditures Growth 25.65 -1000 -34.9% -15.22 -2.26% Capital Expenditures / Sales -304 Net Assets from Acquisitions (54.590.0) 17,4270 Sale of Fixed Assets & Businesses Purchase/Sale of investments (14170) (80.153.0) Purchase of investments (109663.0) (1142470) Sale/Maturity of investments 93,5260 34.010 Other Uses - (199740) 77320) Other Sources 12,5310 12,166.0 17175.0 Net Investing Cash Flow (188.599.00 (243,343.0) (179.387.0) (1243910) (68.348.0) Net Investing Cash Flow Growth 22.57% -35.65% -44213 82.00% Net Investing Cash Flow / Sales -424 3.22 -2.54% -150 Financing Activities All values USD Thousands 2020 2019 2018 2017 2016 5-year (63.6390) (1139140) (90,000,0) (70,000.0) (60.000.0) 1 Cash Dividends Paid - Total Common Dividends (63,639,0) (113,9140) 090,000.0) (70,000,0) (60,000,0) i Change in Capital Stock (48,193.0) 192,663.0 (56,0390) C25102.0) (2.563.0) 56.243.0) (43.982.0) (56,039,0) (251020) (2,563.0) 8,0500 236,645.0 2345830 8,050,0 2,0620 Repurchase of Common & Preferred Stk. Sale of Common & Preferred Stock Proceeds from Stock Options Other Proceeds from Sale of Stock Issuance/Reduction of Debt, Net Change In Current Debt Change In Long-Term Debt Issuance of Long-Term Debt Reduction in Long-Term Debt 506,056.0 (23.268.0) (12780) (55,0950) (114 2310) 10.0450 C23,268.0) (1272.0) (662.0) 24,410.0 496,0410 (54433.0) (138,6410) 796,0410 470,5670 180,000.0 . (300,000.0) (525,000.0) (318,6410) Other Funds (108.259.0) (463.0) (2316,0) (1536.0) 3.2450 Other Uses (108259,0) (4630) (1316.0) (L5360) - Other sources 3.245.0 Net Financing Cash Flow 285.995.0 55.0180 (148,633.0) (151733.0) (173,549.0) 419.82% 2045 Net Financing Cash Flow Growth 13702 12.57% -2675 0.95 -3.09% 3.875 Net Financing Cash Flow/ Sales (4.127.00 (2.808.03 (13,344.0) 8.2420 Exchange Rate Effect 0766103 562.895.0 221055.0 790070 258.0590 569920 . . Net Change in Cash 339.203.0 236.8320 260,958.0 203,6000 407,3230 Free Cash Flow 43:23 -25% 100.06 -35935 Free Cash Flow Growth 3.62% Free Cash Flow Yield
