Excel Problem 1. Using the included excel sheet create a vertical analysis (tab 1), horizontal analysis (tab 2), and base year comparison analysis (tab 3). You will fill in all gray cells in the excel workbook. 1:58 Drive 5 + . The Elegant Dump's Restaurant income Statement Revenues Food Beverage Other Percentages SL300,000 $250,000 $25,000 . # Total Revenue $1.575.000 10 11 12 13 14 15 Cost of Sales Food Beverage Other 5435.000 $46,000 $12.000 Total Cost of Sales $493,000 S65.000 $204,000 $13,000 51,082,000 17 Gross Profit 18 Food 19 Severage 20 Other 21 2 Total Gross Profit 2 24 Operating controllable) Expenses 25 Salaries and Wages 20 Employee Benefits 21 Total Labor Cost 20 Direct Operating Expenses 20 Marketing 30 Utilities 31 Admin and General Repairs and Maintenance Music and Entertainment Total Operating Expenses 35 38 Operating income ar Other (Noncontrollable) Expenses 30 Rent Depreciation 41 interest Total Non Controllable Expenses 3 4 Income Before Income Taxes 45 16 $500,000 $125,000 $725,000 $55,000 $65.000 $75,000 $25.000 $35.000 $40,000 $1.020,000 *WXUA 562.000 $20,000 $5.000 $3,000 $28,000 $34.000 48 49 50 Tab 1 - Vertical Tab 2 - Horizo Base) + Utirties 31 Admin and General Repairs and Maintenance 33 Music and Entertainment Total Operating Expenses $600,000 $750,000 $125.000 $175.000 $725.000 S925.000 $55.000 $65,000 $65.000 $75,000 575,000 $85,000 $25.000 $35.000 $35.000 $15.000 $40.000 $10.000 $1,020.000 $1.210.000 38.10% 40.98% 7.941 9.56% 46.03% SO.55% 3.49% 3.55 4.13 4.10% 4.76% 4.64 1.599 2.22 0.82% 2.54% 055 64.76% 66.12% 1915 Operating income 562.000 $79,000! 3.94% 4.32% 17 20 43 41 Other (Noncontrollable) Expenses Rent Depreciation Interest Total Non Controllable Expenses $20,000 $5.000 $3,000 $28.000 $22.000 $8,000 $4.000 $34.000 1.27% 0.32 0.19 1.78 1 20% 0.44% 0.22% 1.86% 43 income Before Income Taxes $34.000 $45.000 2.16 2463 45 46 47 43 31 54 55 7 Tab 1 - Vertical Tab 2 - Horizontal Bas + Utirties 31 Admin and General Repairs and Maintenance 33 Music and Entertainment Total Operating Expenses $600,000 $750,000 $125.000 $175.000 $725.000 S925.000 $55.000 $65,000 $65.000 $75,000 575,000 $85,000 $25.000 $35.000 $35.000 $15.000 $40.000 $10.000 $1,020.000 $1.210.000 38.10% 40.98% 7.941 9.56% 46.03% SO.55% 3.49% 3.55 4.13 4.10% 4.76% 4.64 1.599 2.22 0.82% 2.54% 055 64.76% 66.12% 1915 Operating income 562.000 $79,000! 3.94% 4.32% 17 20 43 41 Other (Noncontrollable) Expenses Rent Depreciation Interest Total Non Controllable Expenses $20,000 $5.000 $3,000 $28.000 $22.000 $8,000 $4.000 $34.000 1.27% 0.32 0.19 1.78 1 20% 0.44% 0.22% 1.86% 43 income Before Income Taxes $34.000 $45.000 2.16 2463 45 46 47 43 31 54 55 7 Tab 1 - Vertical Tab 2 - Horizontal Bas +