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. A B E F G H 1 1 The Elegant bump's Restaurant Income Statement 2. 3 Revenues Change 4 Food 20X1 $1,300,000 $250,000 $25,000 20X2 $1,500,000 $300,000 $30,000 20X1 20X2 Change 82.54% 81.97% 15.87% 16.39% 1.59% 1.64% 5 Beverage Other 6 7 8 Total Revenues $1,575,000 $1,830,000 100.00% 100.00% 9 10 Cost of Sales 11 Food 12 Beverage 13 Other 14 $435,000 $46,000 $12,000 $475,000 $52,000 $14,000 27.62% 2.92% 25.96% 2.84% 0.77% 0.76% 15 Total Cost of Sales $493,000 $541,000 31.30% 29.56% 16 17 Gross Profit 18 Food 19 Beverage 20 Other $865,000 $204,000 $13,000 $1,025,000 $248,000 $ 16,000 54.92% 12.95% 0.83% 56.01% 13.55% 0.87% 21 $1,082,000 $1,289,000 68.70% 70.44% 40.98% 9.56% 22 Total Gross Profit 23 24 Operating (Controllable) Expenses 25 Salaries and Wages 26 Employee Benefits 27 Total Labor Cost 28 Direct Operating Expenses 29 Marketing 30 Utilities 31 Admin and General 32 Repairs and Maintenance 33 Music and Entertainment 34 Total Operating Expenses 35 36 Operating Income 37 38 Other (Noncontrollable) Expenses 39 Rent 40 Depreciation 41 Interest 42 Total NonControllable Expenses 43 $600,000 $125,000 $725,000 $55,000 $65,000 $75,000 $25,000 $35,000 $40,000 $1,020,000 $750,000 $175,000 $925,000 $65,000 $75,000 $85,000 $35,000 $15,000 $10,000 $1,210,000 38.10% 7.94% 46.03% 3.49% 4.13% 4.76% 1.59% 2.22% 2.54% 50.55% 3.55% 4.10% 4.64% 1.91% 0.82% 0.55% 66.12% 64.76% $62,000 $ 79,000 3.94% 4.32% 1.27% 1.20% $20,000 $5,000 $3,000 $28,000 $22,000 $8,000 $4,000 $34,000 0.32% 0.19% 0.44% 0.22% 1.78% 1.86% 44 Income Before Income Taxes $34,000 $45,000 2.16% 2.46% 45 16 Tab 1 - Vertical Tab 2 - Horizontal Base Year Screenshot