Prepare a working spreadsheet for Davids Shooting Supplies, Inc. for the years 2019 and 2020.
1. The financial statements are attached.
2. Using the financial statements and your spreadsheet, create the Vertical Statements for the years 2020 and 2019, and the Horizontal Statement for the year 2020.
3. Using the financial statements and your spreadsheet, create the ratios covered in the course for the firm for both years 2019 and 2020.
4. Be sure this is a working spreadsheet and that you are not just plugging numbers.
2 3 Balance Sheets S Millions SAssets 31-Dec-20 31-Dec-19 $ $ $ $ 288.50 306.20 423.80 21.30 1,039.BD S S $ S IS 16.60 258 80 372.70 29.90 688.00 $ 7 Current Assets & 9 Cash/Cash equivalents 10 Accounts Receivable 11 Inventory 12 Other Current Assets 13 Total current assets: 14 15 Gross Plant and fupment 16 Accumulated Depreciation 17 Net Plant and Equipment 18 19 Goodwill 20 21 Total Long Term Assets 22 23 Total Assets $ $ $ 911.60 512.20 399.40 15 5 S 82330 429 10 39420 $ 450.00 S 41160 $ 849.40 S 805 80 1,829.20 S 1.493.80 $ $ $ $ 349.30 10.50 18.00 377.BO IS $ $ IS 325.00 4.20 16.80 346.00 $ $ s 189.40 384.60 574.00 S S S 9170 211.90 30.60 $ 951.BO $ 651.60 25 Liabilities and owners equity 26 27 Current liabilities 28 29 Accounts Payable 30 Notes Payable 21 Accrued Income Tax 22 Total Current abilities 33 34 Long term debe 35 36 Debentures 37 Bonds 38 Total Long Term Debt 39 40 Total debt: 41 42 Shareholders equity 43 44 Preferred stock 45 Common stock (54,566,054 shares) 46 Paid in capital 47 Retained Earnings 48 Less: Treasury Stock 49 Total Shareholders equity 50 ST Totalities and shareholders equity 52 53 Important Disclosure note information 54 Common Shares 55 Millions 56 Market Price Per Share 57 $ 5 $ S $ 50.00 842.90 67.80 (23.30) 937.40 $ S S $ S $ 50.00 842.90 (50.70) $ 842.20 $ 1,899.20 $ 1.493.80 Par Value: $91.63 54,566,054.00 54.57 48.51 54,566,054.00 54.57 S 40.50 $ E B D David's Shooting Supplies, Inc. Income Statements $ Millions 31-Dec-20 31-Dec-19 $ olul 1,563.700 1,081.100 482.600 231.100 83.100 168.400 5.600 $ $ $ $ $ 1,386.700 974.800 411.900 197.400 75.300 $ $ $ $ $ $ 139.200 18.000 S $ 162.800 44.300 118.500 $ $ 1 2 3 4 5 6 7 8 Net Sales 9 Cost of Goods Sold 10 G. Profit 11 Selling and Administrative Expenses 12 Depreciation Expense 13 Net Operating Profit (EBIT) 14 Interest expense 15 Earnings Before Tax 16 Income Tax Expense 17 Net Income 18 19 20 21 22 23 24 Net Income 25 Preferred Dividends 26 Net Income to Common Equity 27 Common Dividends 28 Net Income to Retained Earnings 29 Beginning Retained Earnings 30 Ending Retained Earnings 31 32 Earnings Per Share (EPS) 33 121.200 16.100 $ $ 105.100 David's Shooting Supplies, Inc. Statement of Retained Earnings $ Millions 118.500 105.100 S $ $ $ 118.500 105.100 $ $ $ $ $ $ $ $ $ 118.5 (50.700) 67.800 105.100 (155.800) (50.700) $ 2.172 $ 1.926
