Problem 1:

The Pirates Cove Inn had total revenues of $750,000 for the month of June. Assume that the Pirates Cove Inns percentages for June were the same as the comparative results of operations information shown in the exhibit below:

INDUSTRY AVERAGES 20X4

Required:

Using the percentages provided above, convert the Pirates Cove Inn Revenue to dollars in an Income Statement Format.

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.

Problem 3:

Misha Mashito, the owner of the MishMash Diner, has requested your assistance in determining some monthly expenses. She provides you with the following information:

Determine the following costs:

Cost of Food Sold Dollars

Cost of Food Sold Percentage

Cost of Beverage Sold Dollars

Cost of Beverage Sold Percentage

Cost of Food Used Dollars

Required:

Calculate each of the above and enter the values into the Excel file on the Problem 3 tab in the Yellow highlighted area. Show your calculations of each line to the right side, in the area indicated.

Problem 4

You are provided with specific account balances and transaction information for the year for the Nicholback Inn.

Financial Data for 20X3

Transaction data for 20X3

• Benefits and payroll taxes for all employees are 20 percent of gross salaries and wages.
• The management fee to be paid to the management company is 2 percent of net room revenue and 10 percent of GOP.
• The food purchases totaled $50,000 during the year. Food inventory was $20,000 and $18,000 at the beginning and end of the year, respectively.
• The cost of employee meals totaled $3,000 for the year.

Required:

• Prepare a Summary income statement for the Nicholback Inn in accordance with Exhibit 3 in Chapter 3 of the Text in the area provided on the Problem 4 tab of the Excel file.
• You need only include the line items given; however they must be in the correct portion of the report format.

You need only include the current period dollar values, and their percentages as appropriate to either departmental or total revenue.

INDUSTRY AVERAGES - 20X4 Revenue Rooms 69.5% 24.8% Food Other Operated Departments Rentals and Other Income 4.4% 1.3% Total Revenue 100.0% Departmental Expenses Rooms 26.4% 72.8% Food and Beverage Other Operated Departments Total Expenses 71.9% 39.5% 8.4% 8.1% Undistributed Operating Expenses Administrative and General Sales and Marketing Property Operations and Management Utilities Total Undistributed Expenses 4.5% 3.7% 24.7% Gross Operating Profit 35.8% Management Fees 3.3% Income Before Fixed Charges 32.5% Food Revenue Beginning Inventory Ending Inventory Purchases Employee Meals Food to Beverage Transfer $ 185,000 $ 10,000 $ 9,000 $ 50,000 $ 750 $ 450 Beverage $ 80,000 $ 2,500 $ 3,000 $ 18,000 $6,500 $78,000 $62,000 $2,100 $400,000 $1,000 - Insurance (Property) Rooms department - salaries and wages Food department - salaries and wages Supplies - food department Room sales Interest expense Cost of food sold Food sales A&G-salaries Advertising Maintenance -contract Depreciation Heat Information & telecom.systems contract Power and lights Franchise fee Supplies and other rooms department Property taxes A&G - other expense Room allowance $180,000 $6,000 $12,000 $28,000 $18,000 $17,000 $29,000 $15,000 $20,000 $28,000 $13,500 $11,500 $4,000