Hotels Excel Cost Calculation

Hotels Excel Cost Calculation.

Calculation Budget or Forecast Costs and Profitability for Hotels with Excel.

Calculation of Costs of a Hotel and its Profitability Excel spreadsheet.

Like any company, a hotel also requires cost calculation and profitability analysis to make decisions in line with its strategy.

This way of calculating costs is called Budgeting Costs, Revenues, Margins and Profitability.

It is necessary to differentiate between what is a budget, objective or desired situation depending on the company’s strategy, and forecasts, a situation that is expected to occur.

Regardless of whether a budget or a forecast is used, the advantage of this system is the anticipation of the final results, which allows making decisions and establishing an action plan to achieve the objectives.

It also allows the realization of simulations “What if” or what would happen if we modify or change any of the variables.

All the cost systems that I implement are designed to facilitate simulation and decision-making, reflecting as closely as possible the activity of the company in economic terms, thus facilitating the company not only the use of the system but also understanding of the indicators.

The following image shows the HOME menu panel of the Cost and Profitability Budget for Hotels.

A priori it may seem that there are few functions, but wait and see.

In the start menu there are only 4 buttons, the room revenues button, the cost button, the month filter button and the Analytical P&L button.

We are going to see the first button, the Room Revenues button.

The objective of this section is to calculate the Revenues budget for the hotel rooms, it may be the most important part, but we cannot forget the costs afterwards, as any company the objective is to maximize the profit.

First, we will have to configure the hotel rooms.

In this configuration we will include the different types of rooms that the hotel has and the number of rooms per type. By default, you can configure up to 100 different types of rooms, but like all the system parameters it can be expanded.

We will also configure the holidays of the year, thus in the planning of prices and occupancy it will be easier for us to identify them and then establish a different pricing policy.

Now we start planning and budgeting for prices. To do this we access the link of each month and as you can see in the image we have on the left side the types of rooms that we have previously configured and the number of rooms per type.

Then we have a table with the days of the month, in green the holidays and weekends are shown, in this way you can establish a pricing strategy according to the month, week, holiday, weekend … As if you want to put a different rate for each day of the month.

Within the same month, if we move to the right, we have another table to plan, forecast, budget or simulate the occupancy level.

This data is recorded as a percentage and will depend on the number of rooms and the season. Like the rate, the occupancy level is recorded on a daily basis, so the precision when calculating the budgeted income will be maximum.

But there is still more in the sheet of the month, if we move further to the right we will have a summary of the month of the revenue budget as shown in the following image.

For each type of room we will have the indicators: Maximum capacity rooms per month, Rooms occupied per month, % occupancy per month, Revenue per month and average Revenue per occupied room. And we will also have these indicators for the whole of the month.

We will carry out this planning for all months of the year.

Once the revenues are budgeted, it is the turn of the costs.

First we define the cost centers and business units.

An example of the different centers is shown in the following image.

A Cost Center is one that generates income and can also consume operating costs. For that reason they are classified as Operational Costs. In this example they would be Rooms, Dining Room, Sportclub and Bar

The rest of the centers that do not generate revenues directly and only consume costs but that are necessary for the operation of the hotel are classified as Overhead Costs. In the example they would be Reception, Common Facilities, Commercial, Administration, Financial Costs and Depreciations.

To facilitate the budgeting or Forecastig process, we will differentiate between employee costs and the rest of the expenses that do not correspond to employees.

Expenses can be recorded monthly using accounting and analytical accounts, it depends on how the hotel has organized the information.

Employee costs are also budgeted or Forecasted monthly.

Both the expenses and the costs of the employees have to be distributed as a percentage to the different cost centers or business units.

Once the distributions have been made, the following cost reports can be consulted:

Operational Costs After Distribution

Overheads After Distribution

Operational Cost Employees After Distributions

Employee overheads After Distribution

Once the revenues and costs have been budgeted or forecasted, we can now check what is really interesting, which is the expected benefit, and we will see this in the hotel’s Analytical Operating Account (P&L) for the budgeted year.

We can select the months that we want to consult in case we want to refine our analysis further. In the example we see that we are going to consult from January to October.

As can be seen in the Analytical P&L, the profit centers are shown at the top, the revenue of the rooms we have already calculated previously and are shown automatically, the income of the rest of the profit centers will be recorded manually in based on the historical and according to the objective you want to achieve.

The Operational Costs are shown automatically due to the cost distribution that we have made previously. The difference between revenues and operational costs will result in the gross margin for each profit center.

Adding the gross margin of each profit center we obtain the total gross margin, now it only remains to subtract the total overhead costs that we can show in detail for a better analysis.

And with this difference we obtain the total budgeted or forecasted profitability, or what is the same, the profit that is expected to be obtained before taxes.