ch3-04 student_name.xis (h Print each completed worksheet in Vallle printed in the lower left footer and the file name in the lower right foote Chapter 3 Case Problem 2: WINE DEPOT You are to create Wine Depot's financial analyses as of December 31, 2020, and as of December 31, 2021. Following the Chapter 3 examples, use the stu- dent file ch3-05.xls to create a vertical and horizontal analysis of the balance sheet and income statement in the columns provided. (Note: The horizontal analysis will compare December 31, 2020 with December 31, 2021.) Also cre- ate a pie chart of expenses for the year ended December 31, 2021; a column chart of expenses for the years ended December 31, 2020, and December 31, 2021; and a ratio analysis as of December 31, 2021. Save the file as ch3-05_student name.xls (replacing student_name with your name). Note: You will have to use Excel's help feature to create the column chart, because the columns are not adjacent to one another as in the chapter example. Use what- ever chart layout you prefer. Print each completed worksheet in Value view, with landscape orientation, scaling to fit to 1 page wide by 1 page tall, and with your name and date printed in the lower left footer and the file name in the lower right footer. Use Excel's help feature to move charts on your worksheet so that they print on a separate page. Number Font Al XV Wine Depot, Inc G HI Wine Depot Inc. Income Statennt for the period ended December-2020 %Sales December-2021 %Sales %Change 6 Sales 7 Less: Cost of Goods Sold 8 Gross Margin 9 Expenses: 10 Advertising 11 Marketing 12 Depreciation 13 Interest 14 Salary 15 Wages s 375,000 150,000 225,000 S 425,000 191,250 S 8,000 6,000 9,000 3,000 52,000 18,000 1,200 s 18,250 3,000 8,000 1,500 85,000 15,000 7,000 Supplies 17 Utilities 18 Net income before taxes 19 Income taxes 0 Net income 2,800 100,000 125,000 37,500 S 87,500 8.500146,250 87,500 26,250 S 61,250 28 30 32 Income Statement alance Sheet Ratio Analysis O Type here to search 0eoa @ v Wine Depot, Inc. Wine Depot, Inc Balance Stheet as of December 2021 %Assets %Change December-2020 %Assets Assets Current Assets: S 7,000 3,500 8,500 10,500 500 S 5,000 7 Cash Short-Term Investments 6,000 9 Accounts Receivable 6,500 1,800 22,000 85,000 10 Inventory 11 Prepaid Expenses 12 Supplies 13 2.300 32.300 1 03,700 14 Equipment 15 Less: Accumulated Depreciation 8,000 77000 16,000 87.700 $120,000 16 Total 17 18 Liabilities and Stockholders' Equity 9 Liabilities 20 Current Liabilities 21 Accounts Payable 22 Income Tax Payable 3 Notes Payable Equipment 24 Notes Payable- Other S 3,000 1,000 S 16,000 9,000 18,000 3,000 3,75010,750 Payroll Liabilities 2,00051,000 Stockholders' Equity 27 Common Stock 28 Retained Earnings 29 Total 1,000 1,000 47.000 48,000 $ 99,000 10.250 109.250 $ 120,000 31 Income Statement Balance Sheet Ratio Analysis O Type here to search b Copy Format Painter Number Alignment Clipboard A4 Wine Depot, Inc. Ratio Analysis December 2021 5 Profitability 6 Return on owners' investment Return on total investment 8 Profit margin 9 Gross margin 10 Liquidity 11 Current ratio 12 Quick ratio 13 Receivable tumover 14 Inventory tumover 15 Solvency 16 Debt-to-equity 17 Liability 18 23 28 30 31 e Statement Balance Sheet Ratio Analysis O 0 Type here to search