In this task you are provided with an excel file with 2 spread sheets (Budgeted Rooms Revenue Sheet, Actual Rooms Revenue sheet) with the actual figures on the rooms for a given quarter. You are required to calculate the actual revenue that has been generated for each month in the given quarter based on the actual occupancy rate.Calculate the following details using the appropriate sheet as per the given instructionsBudgeted Rooms Revenue SheetRefer to the projected data sheet.In this sheet first calculate the total projected/forecasted revenue per room type for all 365 days.You are also required to calculate total projected/forecasted revenue per room type for each month for Jan, Feb and Mar.Calculate the total rooms for each monthCalculate the Total Revenue for all room types for each monthCalculate the total projected revenue for 3 months (Jan, Feb and Mar)Actual Rooms Revenue SheetIn this sheet you are provided with the Actual occupancy for each month.Calculate the Variance for each room type for each month (Note before you can do this calculation you have first calculated the budgeted rooms revenue for each month as instructed above using the Budgeted Room Revenue sheet)Calculate the variance % The Bentley HotelForecast room revenuesProjected/Forecasted Room RateOccupancyAnnuallyAvailable days Projected /ForecastedRoom TypeTotal Rooms AvailableRevenue per room typeKing SuiteQueen Suite2070%$5753651080%$475365Deluxe Room5089%$375365Standard Room4090%$275365TotalMonthlyProjected/Forecasted Room RateOccupancyProjected /ForecastedRevenue per room typeRoom TypeTotal Rooms AvailableJan70%King SuiteQueen SuiteDeluxe Room$575$475$375$27520311080%315089%31Standard Room4090%31TotalMonthlyRoom TypeTotal Rooms AvailableProjected/Forecasted Room RateFebProjected /ForecastedOccupancyRevenue per room typeKing Suite$575$475$375$2752070%2880%89%Queen Suite1028Deluxe Room5028Standard RoomTotal4090%28MonthlyMarProjected/Forecasted Room RateOccupancyRoom TypeTotal Rooms AvailableProjected /ForecastedRevenue per room typeKing SuiteQueen Suite2070%$57531$475$375$2751080%31Deluxe Room5089%31Standard Room4090%31Total SITXFINO03 Manage finances within a budgetThe Bentley HotelActual room revenuesMonthlyTotal Rooms AvailableRoom RateVariance%Room TypeKing SuiteQueen SuiteActual Occupancy65%JanRevenue per room type$575$475$375$27520311070%31Deluxe RoomStandard Room5085%314085%31TotalMonthlyActual Occupancy68%Room TypeTotal Rooms AvailableRoom RateFebRevenue per room typeVariance%King SuiteQueen SuiteDeluxe RoomStandard Room$575$475$375$27520281055%285085%284070%28TotalMonthlyRoom TypeTotal Rooms AvailableActual OccupancyRoom RateMarRevenue per room typeVariance%King SuiteQueen Suite$575$475$375$2752067%311080%31Deluxe RoomStandard Room5089%314090%31Total

Budgeted revenue is the revenue which the company expects to be earned during the period. It is…

Answered: Calculate the following details using…

FINANCIAL ACCOUNTING

10th Edition

ISBN:9781259964947

Author:Libby

Publisher:Libby

Chapter1: Financial Statements And Business Decisions

Section: Chapter Questions

Problem 1Q

See similar textbooks

Related questions

Concept explainers

Question

100%

In this task you are provided with an excel file with 2 spread sheets (Budgeted Rooms Revenue Sheet, Actual Rooms Revenue sheet) with the actual figures on the rooms for a given quarter. You are required to calculate the actual revenue that has been generated for each month in the given quarter based on the actual occupancy rate.

Calculate the following details using the appropriate sheet as per the given instructions

Budgeted Rooms Revenue Sheet

Refer to the projected data sheet.

In this sheet first calculate the total projected/forecasted revenue per room type for all 365 days.
You are also required to calculate total projected/forecasted revenue per room type for each month for Jan, Feb and Mar.
Calculate the total rooms for each month
Calculate the Total Revenue for all room types for each month
Calculate the total projected revenue for 3 months (Jan, Feb and Mar)

Actual Rooms Revenue Sheet

In this sheet you are provided with the Actual occupancy for each month.
Calculate the Variance for each room type for each month (Note before you can do this calculation you have first calculated the budgeted rooms revenue for each month as instructed above using the Budgeted Room Revenue sheet)
Calculate the variance %

The Bentley Hotel
Forecast room revenues
Projected/Forecasted Room Rate
Occupancy
Annually
Available days Projected /Forecasted
Room Type
Total Rooms Available
Revenue per room type
King Suite
Queen Suite
20
70%
$575
365
10
80%
$475
365
Deluxe Room
50
89%
$375
365
Standard Room
40
90%
$275
365
Total
Monthly
Projected/Forecasted Room Rate
Occupancy
Projected /Forecasted
Revenue per room type
Room Type
Total Rooms Available
Jan
70%
King Suite
Queen Suite
Deluxe Room
$575
$475
$375
$275
20
31
10
80%
31
50
89%
31
Standard Room
40
90%
31
Total
Monthly
Room Type
Total Rooms Available
Projected/Forecasted Room Rate
Feb
Projected /Forecasted
Occupancy
Revenue per room type
King Suite
$575
$475
$375
$275
20
70%
28
80%
89%
Queen Suite
10
28
Deluxe Room
50
28
Standard Room
Total
40
90%
28
Monthly
Mar
Projected/Forecasted Room Rate
Occupancy
Room Type
Total Rooms Available
Projected /Forecasted
Revenue per room type
King Suite
Queen Suite
20
70%
$575
31
$475
$375
$275
10
80%
31
Deluxe Room
50
89%
31
Standard Room
40
90%
31
Total

SITXFINO03 Manage finances within a budget
The Bentley Hotel
Actual room revenues
Monthly
Total Rooms Available
Room Rate
Variance
%
Room Type
King Suite
Queen Suite
Actual Occupancy
65%
Jan
Revenue per room type
$575
$475
$375
$275
20
31
10
70%
31
Deluxe Room
Standard Room
50
85%
31
40
85%
31
Total
Monthly
Actual Occupancy
68%
Room Type
Total Rooms Available
Room Rate
Feb
Revenue per room type
Variance
%
King Suite
Queen Suite
Deluxe Room
Standard Room
$575
$475
$375
$275
20
28
10
55%
28
50
85%
28
40
70%
28
Total
Monthly
Room Type
Total Rooms Available
Actual Occupancy
Room Rate
Mar
Revenue per room type
Variance
%
King Suite
Queen Suite
$575
$475
$375
$275
20
67%
31
10
80%
31
Deluxe Room
Standard Room
50
89%
31
40
90%
31
Total

Expert Solution

Step by step

Solved in 2 steps with 8 images

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.