Problem 2:

The Pirates Cove Inn wants to compare their actual June results to the industry averages they should have achieved based on the calculations you completed for Problem 1. Their actual financial statement for June is in the Excel file provided.

Required:

• Using the Industry Average calculation for the dollar value of each line item, enter the values into the Excel file on the Problem 2 tab. This is what you will be comparing the Pirates Cove actual results to in this problem.
• Prepare a variance column between the actual and industry average results. Measure both the dollar variance and the percentage variance that represents.
• Note: In this variance analysis, you are calculating the differences in the dollar values and the difference in the percentage values

INDUSTRY AVERAGES Pirates Cove Inn Month of June 20X4 Variance Industry Average $ % % $ % Revenue Rooms Food Other Operated Departments Rentals and Other Income Total Revenue $ $ $ $ $ Actual $ 506,250 198,750 33,750 11,250 750,000 Revenue Rooms Food Other Operated Departments Rentals and Other Income Total Revenue 69.5% 24.8% 4.4% 1.3% 100.0% Departmental Expenses Rooms Food and Beverage Other Operated Departments Total Departmental Expenses $ $ $ $ 133,650 144,690 24,266 302,606 Departmental Expenses Rooms Food and Beverage Other Operated Departments Total Departmental Expenses 26.4% 72.8% 71.9% 39.5% Undistributed Operating Expenses Administrative and General Sales and Marketing Property Operations and Management Utilities Total Undistributed Expenses $ $ $ $ $ 63,750 54,000 36,750 33,750 188,250 Undistributed Operating Expenses Administrative and General Sales and Marketing Property Operations and Management Utilities Total Undistributed Expenses 8.4% 8.1% 4.5% 3.7% 24.7% Gross Operating Profit 35.8% Gross Operating Profit $ 259,144 Management Fees 3.3% Management Fees $ 26,250 Income Before Fixed Charges 32.5% Income Before Fixed Charges $ 232,894