How many toilet rolls - hotel inventory and useage
Archive - Originally posted on "The Horse's Mouth" - 2010-12-18 19:06:03 - Graham Ellis
Running Well House Manor, we buy a lot of hotel supplies - ranging from sheets and towels when they are to be replaced, through guest soaps and toilet rolls, salt, sugar and jams which are ordered from time to time, to weekly or twice weekly orders such as fruit and fresh products and ultimately the daily paper. And we need to keep track of this expenditure and where it all goes. Lisa's been working on a database extension over the last few days to bring these figures closer to our finger tips - to tell us what's worth worrying about, and what is such a small player in the game that it shouldn't be at the top of our attention list.
It turns out that the algorithms are surprisingly complex. Consider the following purchases on file:
1/3/10 240
4/4/10 360
5/6/10 360
12/8/10 240
7/9/10 240
9/10/10 360
At first glance, that's 1800 purchased over a 222 day period - 8.1 units used per day. But hang on a moment ...
• there were probably some in stock on 1st March
• the 360 purchased on 9th October won't be used until .. an indefinite date thereafter
• there will be stock left at the end of the period
So I really don't trust that 8.1 per day figure.
A true algorithm would take the numbers above, add in the number in stock on 1st March, subtract the number in stock today, and then divide by the number of days from 1st March. But that would meaning saving the stock level immediatly prior to all purchases, and also doing a stock count whenever we want o look at the figures. This seems rather a lot of work for a fine adjustment.
The algorithm I've come up with - which should even out over time - assumes that stock levels are [roughly] the same each time an order is placed - so we've used 240 in the 34 days from 1st March to 4th April, and so on. So the running calculation is made by adding up the numbers purchased on each occasion except the last, calculating the number of days from the first to the last order, and dividing one by the other. The results (from this Perl program - see [source]) are like this:
wizard:dec10 graham$ perl toiletrolls.pl
Over 222 days, 1440 were consumed
That's 6.48648648648649 per day
wizard:dec10 graham$
which is rather over-accurate, but much truer to the figures.
Running Well House Manor, we buy a lot of hotel supplies - ranging from sheets and towels when they are to be replaced, through guest soaps and toilet rolls, salt, sugar and jams which are ordered from time to time, to weekly or twice weekly orders such as fruit and fresh products and ultimately the daily paper. And we need to keep track of this expenditure and where it all goes. Lisa's been working on a database extension over the last few days to bring these figures closer to our finger tips - to tell us what's worth worrying about, and what is such a small player in the game that it shouldn't be at the top of our attention list.