Mr Sandman
New Member
- Joined
- May 23, 2017
- Messages
- 2
Hello everyone,
I would first like to thank you all for the help that I found on this forum, reading various threads as I was discovering Excel, working (and being stuck) on my project.
Before we get to the good stuff, let me introduce myself and tell you why I am writing this post.
My name is Antoine, I work as a receptionist in a hotel in Belgium, and I am currently enrolled in a yearly program (along with 9 other people nationally) from my company, aimed at making us progress in the corporate ladder quicker than usual.
The program lasts 1 year and is comprised of several challenges, meet-ups etc.
For the first challenge, we had to find a "BHAG" (Big Hairy Audacious Goal.. I didn't choose the name but you can see how fond of acronyms we also are in Belgium) which would benefit us, the employee, but also the company. It had to be challenging etc, getting out of our "comfort zone".
So I choose to work on a problem that I thought needed fixing, I knew it would be technically challenging (knowing nothing about Excel before starting it) but that was the point.
Here it is:
Each day, the morning shift receptionist has to assign rooms to clean to specific workers, typically 28rooms for an 8hours shift, 20rooms for a 6 hours shift.
The Rooms are either:
"DA" = departure = the sheets have to be changed, new towels etc. the room thoroughly cleaned for the next guest = more work generally than staying.
or
"OS" = Staying = only the floor and the bathroom must be cleaned, as well as giving new towels = less work generally than departure.
In the old way, we simply had to assign for instance "Room N°1 to Room N°20 = Worker N°1" - "Room N°20 to Room N°40 = Worker N°2" etc.
Which meant that if there was a group of guests staying for several nights, the worker n°1 could have 18 OS and 2 DA to clean, whereas her coworker would have to do 16 DA and 2 OS.
By now you can see the problem.
This system is highly inefficient, with an unequal distribution of the actual work on a day to day basis. All of that creates tensions every single day when the workers arrive and compare their sheet to the others.
My idea was to automate the assignment in a way that is fair for everyone, by calculating the daily “Index” = Amount of work required to do on a specific day, depending on how many rooms must be cleaned, and which status they have (either DA or OS).
Here is a link to the file : https://sabercathost.com/diNZ/Mr_Excel_-_P.R.A.S.1.5.7.xls
So far - thanks to many tutorials - I have managed to get a decent result, and the basic principle works.
But in order to make it a viable alternative to human input, I need your help to figure out how to express my last needs in formulas, and to know if it is possible in the first place.
On the first sheet, you can see the values corresponding to each room status.
You can see that the easiest rooms to clean are the Double OS of the second floor, with a score of “10” – compared to the smoking triple DA which corresponds to a score of “20”
(Yes, we still have smoking rooms and yes, it attracts the type of customers that you don’t necessarily want in your hotel, leaving the rooms very very dirty.
Only these values would change if I were to implement my program in more than the pilot hotel.
Next you have the “Groundfloor” Sheet, the way it works is you write “DA” or “OS” in the Status column C7:C37 to get the value for each. https://ibb.co/gXJd2a
Same thing for Floor 1 and Floor 2
You then get the Index Sum on cells H7:H37
Once it’s done, you input the number of hours each worker are here for today - 8 or 6 depending on their contract - in the cells O30:O46. https://ibb.co/f0TNTF
You can see that 110 rooms must be cleaned today in my configuration, and since only 4 workers are here today, the remaining rooms should be put “HS” = Blocked, so that they can be cleaned the next day, or whenever there will be enough cleaning staff + the need to have all rooms cleaned because the hotel will be fully booked.
The “Housekeeping Team” part of the sheet then indicates which score each worker much reach, calculated by the daily index divided by the number of working hours for that day, multiplied by 6 or 8 depending on the contract.
The “Per hour” score (W14) should rest at a maximum of 55 (to be determined), above which each worker would have to clean more rooms than her contract allows, and we need to prevent that.
Check the formulas in the Column I, this is where it all happens.
It assigns a number by checking if the index sum corresponds to the target score for each worker, giving 0 if the room is already clean. https://ibb.co/maafFv
Question 1 :
All this is fine and works as intended, the next step (that is indispensable to be truly viable) is to pass this calculation through stages, where each stage, the sheet checks if one name appears in several sheets, which means that the worker must do 2 floors, and I want the sheet to re-do the allocation after subtracting one room to the worker’s score, to account for the extra work that is preparing a second trolley (which equals one room worth or so).
Since there is no way to know if a worker is going to do 2 floors before first assigning, the sheet would have to re-calculate after each worker to check all the logical criteria, and do the subtraction if need be, then re-allocate, re-check, etc.
I have no idea how to express this on Excel and couldn’t find anything to help me on the internet. This is where (hopefully) you guys and girls come in.
Question 2 :
The second thing I need the sheet to do (and that is probably as complex as the first) is to Block “HS” the rooms that exceed the maximum index given by number of workers present.
For instance, if the hotel was fully booked the day before, and there had to be 123 rooms cleaned, but only a maximum of 116 rooms could be cleaned given who works that day, the remaining 7 rooms would have to be put out of order “HS”. https://ibb.co/e50fFv
BUT !
Only Departure rooms “DA” can be put out or order, since staying customers “OS” must get new towels and have their room refreshed.
AND !
Ideally, it would block rooms in such a way that would prevent worker to do 2 floors, for instance it would block rooms 31 and 32 so that Malika only works on the First Floor. https://ibb.co/eCshTF
Again, I have no idea if it is even possible to write such conditions in excel, my whole post is basically to get answers to these two questions.
Thanks for those who spent time to read this tedious explanation, if you have any questions regarding some of the formulas, I’ll be happy to answer them!
(P.R.A.S. stands for Procedural Room Allocation Sheet, by the way
)
I would first like to thank you all for the help that I found on this forum, reading various threads as I was discovering Excel, working (and being stuck) on my project.
Before we get to the good stuff, let me introduce myself and tell you why I am writing this post.
My name is Antoine, I work as a receptionist in a hotel in Belgium, and I am currently enrolled in a yearly program (along with 9 other people nationally) from my company, aimed at making us progress in the corporate ladder quicker than usual.
The program lasts 1 year and is comprised of several challenges, meet-ups etc.
For the first challenge, we had to find a "BHAG" (Big Hairy Audacious Goal.. I didn't choose the name but you can see how fond of acronyms we also are in Belgium) which would benefit us, the employee, but also the company. It had to be challenging etc, getting out of our "comfort zone".
So I choose to work on a problem that I thought needed fixing, I knew it would be technically challenging (knowing nothing about Excel before starting it) but that was the point.
Here it is:
Each day, the morning shift receptionist has to assign rooms to clean to specific workers, typically 28rooms for an 8hours shift, 20rooms for a 6 hours shift.
The Rooms are either:
"DA" = departure = the sheets have to be changed, new towels etc. the room thoroughly cleaned for the next guest = more work generally than staying.
or
"OS" = Staying = only the floor and the bathroom must be cleaned, as well as giving new towels = less work generally than departure.
In the old way, we simply had to assign for instance "Room N°1 to Room N°20 = Worker N°1" - "Room N°20 to Room N°40 = Worker N°2" etc.
Which meant that if there was a group of guests staying for several nights, the worker n°1 could have 18 OS and 2 DA to clean, whereas her coworker would have to do 16 DA and 2 OS.
By now you can see the problem.
This system is highly inefficient, with an unequal distribution of the actual work on a day to day basis. All of that creates tensions every single day when the workers arrive and compare their sheet to the others.My idea was to automate the assignment in a way that is fair for everyone, by calculating the daily “Index” = Amount of work required to do on a specific day, depending on how many rooms must be cleaned, and which status they have (either DA or OS).
Here is a link to the file : https://sabercathost.com/diNZ/Mr_Excel_-_P.R.A.S.1.5.7.xls
So far - thanks to many tutorials - I have managed to get a decent result, and the basic principle works.
But in order to make it a viable alternative to human input, I need your help to figure out how to express my last needs in formulas, and to know if it is possible in the first place.
On the first sheet, you can see the values corresponding to each room status.
You can see that the easiest rooms to clean are the Double OS of the second floor, with a score of “10” – compared to the smoking triple DA which corresponds to a score of “20”
(Yes, we still have smoking rooms and yes, it attracts the type of customers that you don’t necessarily want in your hotel, leaving the rooms very very dirty.
Only these values would change if I were to implement my program in more than the pilot hotel.
Next you have the “Groundfloor” Sheet, the way it works is you write “DA” or “OS” in the Status column C7:C37 to get the value for each. https://ibb.co/gXJd2a
Same thing for Floor 1 and Floor 2
You then get the Index Sum on cells H7:H37
Once it’s done, you input the number of hours each worker are here for today - 8 or 6 depending on their contract - in the cells O30:O46. https://ibb.co/f0TNTF
You can see that 110 rooms must be cleaned today in my configuration, and since only 4 workers are here today, the remaining rooms should be put “HS” = Blocked, so that they can be cleaned the next day, or whenever there will be enough cleaning staff + the need to have all rooms cleaned because the hotel will be fully booked.
The “Housekeeping Team” part of the sheet then indicates which score each worker much reach, calculated by the daily index divided by the number of working hours for that day, multiplied by 6 or 8 depending on the contract.
The “Per hour” score (W14) should rest at a maximum of 55 (to be determined), above which each worker would have to clean more rooms than her contract allows, and we need to prevent that.
Check the formulas in the Column I, this is where it all happens.
It assigns a number by checking if the index sum corresponds to the target score for each worker, giving 0 if the room is already clean. https://ibb.co/maafFv
Question 1 :
All this is fine and works as intended, the next step (that is indispensable to be truly viable) is to pass this calculation through stages, where each stage, the sheet checks if one name appears in several sheets, which means that the worker must do 2 floors, and I want the sheet to re-do the allocation after subtracting one room to the worker’s score, to account for the extra work that is preparing a second trolley (which equals one room worth or so).
Since there is no way to know if a worker is going to do 2 floors before first assigning, the sheet would have to re-calculate after each worker to check all the logical criteria, and do the subtraction if need be, then re-allocate, re-check, etc.
I have no idea how to express this on Excel and couldn’t find anything to help me on the internet. This is where (hopefully) you guys and girls come in.
Question 2 :
The second thing I need the sheet to do (and that is probably as complex as the first) is to Block “HS” the rooms that exceed the maximum index given by number of workers present.
For instance, if the hotel was fully booked the day before, and there had to be 123 rooms cleaned, but only a maximum of 116 rooms could be cleaned given who works that day, the remaining 7 rooms would have to be put out of order “HS”. https://ibb.co/e50fFv
BUT !
Only Departure rooms “DA” can be put out or order, since staying customers “OS” must get new towels and have their room refreshed.
AND !
Ideally, it would block rooms in such a way that would prevent worker to do 2 floors, for instance it would block rooms 31 and 32 so that Malika only works on the First Floor. https://ibb.co/eCshTF
Again, I have no idea if it is even possible to write such conditions in excel, my whole post is basically to get answers to these two questions.
Thanks for those who spent time to read this tedious explanation, if you have any questions regarding some of the formulas, I’ll be happy to answer them!
(P.R.A.S. stands for Procedural Room Allocation Sheet, by the way
)
