PLEASE FOLLOW THE EXCEL FORMAT IN HERE. THANK YOU!

E5 fx =C5+D5 F G H A B E 1 The MCU Inn includes a 40-room lodging operation and coffee shop. 2 The average annual revenue and variable expense figures for the past two years have been as follows: 3 4 Rooms offee shop Total 5 Revenue 600,000 400,000 6 Variable expenses 120,000 200,000 320,000 7 8 The fixed costs are as follows: 9 Rooms department 50,000 LO Coffee shop 60,000 11 Overhead 300,000 12 13 14 1 The annual revenue at the breakeven point 15 2 Total net income when total revenue equals $1,200,000 (income tax = 20%) 16 3 The required level of annual revenue when net income is $200,000 (assume no income taxes) 17 18 19 ANSWER 20 1 CMRw = 0.68 21 Total fixed 4100 .00 22 Revenue at breakeven 602941.18 23 0 0 0 24 25 26 2 Revenue % (Rooms) Revenue % (Coffee shop) Total 60% 40% 100% 27 A B D 28 29 20% Variable costs % (Rooms) Variable costs % (Coffee shop) 30 50% 31 32 Total Revenue 1,200,000 33 34 35 Room Sales Coffee Shop Sales Total Revenue 36 37 38 39 40 Variable Expenses Rooms Variable Expenses Coffee Shop Total Departmental Expenses 41 Total Contribution Margin 42 43 44 45 46 47 48 Fixed costs: Rooms department Coffee shop Overhead Total Fixed costs 50000.00 60000.00 300000.00 410000.00 49 50 51 Pre-tax earnings Taxes Net Income 52 53 54