Cost per rooms sold calculations

“How much is your cleaning cost per room sold?”

Housekeepers around the world are asked this question. ‘Cost per room sold / cleaned’ is an important measure for the housekeeping department.

Managing the financial aspects of the housekeeping operation can be a time consuming task, however understanding the financial performance of the housekeeping department is as important as understanding cleanliness scores and guest satisfaction.

In order to help housekeeping professionals hsk-knowledge.com has created a simple spreadsheet with automated calculations. By entering a few (high level) figures per month, the spreadsheet will calculate the main key performance indicator (KPI): Total Cost per Rooms Sold. At the same time, the spreadsheet provides a breakdown of information to identify where the housekeeping department is over- or underperforming financially.

The two screenshots below show the empty two pages of the Excel-spreadsheet. Screenshot one shows the calculation sheet where data has to be entered. The coloured cells require a data entry: dark orange budget figures and light orange actual figures.

Please click on the screenshots to enlarge the picture; continue reading below for more detailed information on how to use the spreadsheet.

Step 1

Get hold of the budget for the housekeeping department. The important figures that you need to know are the following:

• forecasted occupancy (total number of rooms) per month,
• total labour cost budget per month,
• total chemical budget per month,
• total equipment budget per month,
• total linen budget per month,
• total guest supplies budget per month,
• total flower budget per month, and
• total buget for outsourced services.

Once you have the figures, you need to enter them into the dark orange fields in the spreadsheet for the relevant months. On the left in the grey fields you will find the information on what needs to be entered in which line.

Step 2

While you are operating on a day to day basis you generate actual costs:

• labour cost
• chemical and cleaning equipment cost
• linen and towel cleaning cost
• guest supplies cost
• flower cost and
• cost for outsourced services (e.g. window cleaning) in case you are not taking care of everything yourself.

You need to keep track of these costs during the month by keeping copies of the invoices. Ask the finance department to provide you with the data you need.

At the end of the month you add up the acutal costs for the different areas based on your invoices:

• total labour cost per month,
• total chemical cost per month,
• overall equipment cost per month,
• total linen cost per month,
• total guest supplies cost per month,
• total flower cost per month,
• and total buget for outsourced services.

Furthermore, you need to get the following figure from your Property Management System (PMS) or ask your finance department for the correct figure:

• actual number of rooms sold per month

Once you have these figures, you need to enter them into the light orange fields in the spreadsheet for the relevant month. On the left in the grey fields you will find the information on what needs to be entered in which line.

See the screenshot above for an example of the first 2 lines of the spreadsheet: forecasted and acutal occupancy. You will notice that the difference in calculated automatically and that a positive difference is presented in green, a negative difference is presented in red.

You should enter these data every month to keep track of your costs!

How to read the results page?

Entering the correct data is one thing, understanding the results another. Below is an example of a completed spreadsheet. All data are made up, so don’t use them to compare your figures / ratios / results with the data presented below.

The screenshots below show line by line what was entered and how the calculation works. Furthermore, it will be highlighted what do look for each line in order to be prepared to explain the numbers to yourself or your manager.

The Spreadsheet

The first two screenshots show the completed spreadsheet.

The above screenshot shows the first part of the document. Budget data and actual figures were included for each line.

The above screenshot shows the cost per room sold results. Each of these lines is explained in detail below.

The spreadsheet is designed to show the cost per month: January to December in one column each. The last column on the right shows the total per year.

Occupancy (forecast vs. actual)

This part of the document asked for the forecasted occupancy (number of rooms the hotel estimates to sell per month) and the actual number of rooms sold per month. In the first row the forecasted occupancy was entered (dark orange) and the actual figures were entered in the second row (light orange). The file automatically calculates the difference between the forecasted and the actual figure and displays a higher actual occupancy in green, and an actual figure lower than the forecasted figure in red. The last column shows the total figure for the year. (see screenshot below)

In the example above, one can see that the occupancy levels for the whole year were higher than forecasted. This can be seen in the last column as it displays a green figure (334). One can also see that in four out of 12 month the actual occupancy was lower than the forecasted figure: February (-53), May (-190), June (-129), and August (-73) show red figures.

Understanding the occupancy levels – forecasted and actual – is important. The forecasted figure helps you to estimate your costs, while the actual figure is important when calculating the actual costs.

Total cost (forecast vs. actual) & Total cost per room sold (forecast vs. actual)

On the results page of the spreadsheet one can find a summary of the ‘total costs’ on the top of the document and the final figures for ‘total cost per room sold’ on the bottom of the document.

Looking at the top of the document, at the summary of the ‘total cost’. The first line automatically adds up all the budget figures that were entered on the first page of the document in the dark orange cells. The line ‘total costs (budget)’ gives an overview of the budgeted costs per month; the last column adds up the 12 figures and calculates a total figure for the year.

Below the ‘total costs (budget)’ one can see a line called ‘total costs’. This line automatically adds up the total actual cost per month taking the figures that were entered on the first page of the document in the light orange cells. Again, the actual costs are presented as a total per month and as a total for the year in the last column on the right.

In addition to summarising the costs, the spreadsheet automatically calculates the difference between the budgeted and the actual costs. If the actual cost is higher than the budgeted cost, the cell will be highlighted in red. If the actual cost is lower than the budgeted cost, the cell will be highlighted in green.

In the example below one can clearly see that the actual costs are higher than the budgeted cost in most months, except June, July, August and October. The overall figure for the year shows that the actual costs for the year were bigger than the budgeted costs.

Total cost per room sold (budget vs. actual)

On the bottom of the spreadsheet one can find the figure this spreadsheet set out to calculate: ‘total cost per room sold’.

The first line shows the ‘total cost per room sold (budget)’. The figure per month and for the year is calculated based on the budget figures and the forecasted occupancy figures entered on the first page of the spreadsheet.

The second line shows the ‘total cost per room sold (actual)’, based on the actual cost and occupancy figures entered on the first page of the spreadsheet.

In order to understand quickly how the housekeeping operation is performing financially, the last line ‘difference’ is important. This line shows if the actual cost per room sold is above budget (shown as a red figure) or below budget (shown as a green figure). The difference is calculated per month and for the year.

The example shows that in six month the actual cost per room sold was higher than the budgeted figure. For the other six month the actual figure was below the budgeted figure. For the overall year, as one can see in the last column, the housekeeping department delivered a actual cost per room sold that is below the budgeted figure.

Total labour cost (budget vs. actual, per room sold)

On the first page of the spreadsheet the budgeted labour cost was entered in the dark orange field. Furthermore, the actual costs were entered in the light orange fields differentiating between full time, part time, casual, and outsourced staff. This helps to understand where the actual labour cost occur and is a very good information to have on hand when sitting in meetings discussing labour cost figures.

Total labour cost per room sold 

The second page of the spreadsheet highlights the labour cost per room sold. The labour cost per room sold (budget) line shows the amount that one is allowed to spend according to the budgeted figures. The line below shows the actual costs per month. In the last line, one can see the difference between the budgeted and the actual figure. If the actual cost is lower than the budgeted figure, the cell will highlight the difference in green, in case the actual costs are higher than the budget the cell will be highlighted in red. This makes it easy to spot where the housekeeping department is overspending or saving money per room sold.

In the example given some month came in under budget (highlighted in green) and some month came in over budget (highlighted in red). The last column on the right shows the summary for the year. In this case the housekeeping department delivered under budget and saved some money on labour cost per room sold.

Total chemical cost (budget vs. actual, per room sold)

Total chemical cost (budget vs. actual)

On the first page of the spreadsheet the budgeted chemical cost was entered in the dark orange field. Furthermore, the actual costs were entered in the light orange fields. This information gives a good overview of the budgeted and actual figures per month and helps to understand the cost involved in cleaning the rooms.

Total chemical cost per room sold 

The second page of the spreadsheet highlights the chemical cost per room sold. The chemical cost per room sold (budget) line shows the amount that one is allowed to spend according to the budgeted figures. The line below shows the actual costs per month. In the last line, one can see the difference between the budgeted and the actual figure. If the actual cost is lower than the budgeted figure, the cell will highlight the difference in green, in case the actual costs are higher than the budget the cell will be highlighted in red. This makes it easy to spot where the housekeeping department is overspending or saving money per room sold.

Total equipment cost (budget vs. actual, per room sold)

Total equipment cost (budget vs. actual)

On the first page of the spreadsheet the budgeted equipment cost was entered in the dark orange field. Furthermore, the actual costs were entered in the light orange fields. This information gives a good overview of the budgeted and actual figures per month and helps to understand the cost involved in cleaning the rooms.

Total equipment cost per room sold

The second page of the spreadsheet highlights the equipment cost per room sold. The equipment cost per room sold (budget) line shows the amount that one is allowed to spend according to the budgeted figures. The line below shows the actual costs per month. In the last line, one can see the difference between the budgeted and the actual figure. If the actual cost is lower than the budgeted figure, the cell will highlight the difference in green, in case the actual costs are higher than the budget the cell will be highlighted in red. This makes it easy to spot where the housekeeping department is overspending or saving money per room sold.

Total linen cost (budget vs. actual, per room sold)

On the first page of the spreadsheet the budgeted linen cost was entered in the dark orange field. Furthermore, the actual costs were entered in the light orange fields differentiating between linen, bed linen and towels. Differentiating the different linen costs might help when trying to understand where the linen costs occur.

Total linen cost per room sold

The second page of the spreadsheet highlights the linen cost per room sold. The linen cost per room sold (budget) line shows the amount that one is allowed to spend according to the budgeted figures. The line below shows the actual costs per month. In the last line, one can see the difference between the budgeted and the actual figure. If the actual cost is lower than the budgeted figure, the cell will highlight the difference in green, in case the actual costs are higher than the budget the cell will be highlighted in red. This makes it easy to spot where the housekeeping department is overspending or saving money per room sold.

Total guest supplies cost (budget vs. actual)

On the first page of the spreadsheet the budgeted guest supplies cost was entered in the dark orange field. Furthermore, the actual costs were entered in the light orange fields. These figures provide a good overview of the costs linked to guest supplies consumed in the guest bedroom; very hard for the housekeeping department to manage as guest might take guest supplies home. However it is worth tracking this figure to identify trends.

Total guest supplies cost per room sold

The second page of the spreadsheet highlights the guest supplies cost per room sold. The guest supplies cost per room sold (budget) line shows the amount that one is allowed to spend according to the budgeted figures. The line below shows the actual costs per month. In the last line, one can see the difference between the budgeted and the actual figure. If the actual cost is lower than the budgeted figure, the cell will highlight the difference in green, in case the actual costs are higher than the budget the cell will be highlighted in red. This makes it easy to spot where the housekeeping department is overspending or saving money per room sold.

Total flower cost (budget vs. actual, per room sold)

Total flower cost (budget vs. actual)

On the first page of the spreadsheet the budgeted flower cost was entered in the dark orange field. Furthermore, the actual costs were entered in the light orange fields. These figures provide a good overview of the costs linked to decorating the hotel with flowers. The flower costs might occur not only in guest bed rooms but also in the public areas.

Total flower cost per room sold

The second page of the spreadsheet highlights the flower cost per room sold. The flower cost per room sold (budget) line shows the amount that one is allowed to spend according to the budgeted figures. The line below shows the actual costs per month. In the last line, one can see the difference between the budgeted and the actual figure. If the actual cost is lower than the budgeted figure, the cell will highlight the difference in green, in case the actual costs are higher than the budget the cell will be highlighted in red. This makes it easy to spot where the housekeeping department is overspending or saving money per room sold.

Total outsourced services cost (budget vs. actual, per room sold)

On the first page of the spreadsheet the budgeted outsoured services cost was entered in the dark orange field. Furthermore, the actual costs were entered in the light orange fields. Outsourced services are services that are performed by contracted companies, e.g. window cleaning.

Total outsourced cost per room sold

The second page of the spreadsheet highlights the outsourced services cost per room sold. The oursourced services cost per room sold (budget) line shows the amount that one is allowed to spend according to the budgeted figures. The line below shows the actual costs per month. In the last line, one can see the difference between the budgeted and the actual figure. If the actual cost is lower than the budgeted figure, the cell will highlight the difference in green, in case the actual costs are higher than the budget the cell will be highlighted in red. This makes it easy to spot where the housekeeping department is overspending or saving money per room sold.

Download Options

Free option

Download the Excel-file here for free: cost per room sold calculations

Other options

• You would like to use the Excel-file without the hsk-knowledge.com-logo?
• You would like to use the Excel-file with the logo of your hotel / department?
• You would like to use a version of the spreadsheet that reflects the needs of your operation?

Please send an email to christoph@hsk-knowledge.com for a quote.