
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.
(written 2010-12-18, updated 2010-12-20)
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
Q110 - Object Orientation and General technical topics - Programming Algorithms [202] Searching for numbers - (2005-02-04)
[227] Bellringing and Programming and Objects and Perl - (2005-02-25)
[642] How similar are two words - (2006-03-11)
[1157] Speed Networking - a great evening and how we arranged it - (2007-04-21)
[1187] Updating a page strictly every minute (PHP, Perl) - (2007-05-14)
[1391] Ordnance Survey Grid Reference to Latitude / Longitude - (2007-10-14)
[1840] Validating Credit Card Numbers - (2008-10-14)
[1949] Nuclear Physics comes to our web site - (2008-12-17)
[2189] Matching disparate referencing systems (MediaWiki, PHP, also Tcl) - (2009-05-19)
[2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
[2509] A life lesson from the accuracy of numbers in Excel and Lua - (2009-11-21)
[2586] And and Or illustrated by locks - (2010-01-17)
[2617] Comparing floating point numbers - a word of caution and a solution - (2010-02-01)
[2894] Sorting people by their names - (2010-07-29)
[2951] Lots of way of converting 3 letter month abbreviations to numbers - (2010-09-10)
[2993] Arrays v Lists - what is the difference, why use one or the other - (2010-10-10)
[3042] Least Common Ancestor - what is it, and a Least Common Ancestor algorithm implemented in Perl - (2010-11-11)
[3072] Finding elements common to many lists / arrays - (2010-11-26)
[3102] AND and OR operators - what is the difference between logical and bitwise varieties? - (2010-12-24)
[3451] Why would you want to use a Perl hash? - (2011-09-20)
[3620] Finding the total, average, minimum and maximum in a program - (2012-02-22)
[3662] Finding all the unique lines in a file, using Python or Perl - (2012-03-20)
[4325] Learning to program - what are algorithms and design patterns? - (2014-11-22)
[4401] Selecting RECENT and POPULAR news and trends for your web site users - (2015-01-19)
[4402] Finding sum, minimum, maximum and average in Python (and Ruby) - (2015-01-19)
[4410] A good example of recursion - a real use in Python - (2015-02-01)
[4652] Testing new algorithms in PHP - (2016-02-20)
[4656] Identifying the first and last records in a sequence - (2016-02-26)
[4707] Some gems from an introduction to Python - (2016-10-29)
P050 - Perl - General [116] The next generation of programmer - (2004-11-13)
[400] New in the shops - (2005-08-01)
[743] How to debug a Perl program - (2006-06-04)
[1750] Glorious (?) 12th August - what a Pe(a)rl! - (2008-08-12)
[1897] Keeping on an even keel - (2008-11-21)
[2228] Where do I start when writing a program? - (2009-06-11)
[2242] So what is this thing called Perl that I keep harping on about? - (2009-06-15)
[2374] Lead characters on Perl variable names - (2009-08-24)
[2504] Learning to program in ... - (2009-11-15)
[2736] Perl Course FAQ - (2010-04-23)
[2783] The Perl Survey - (2010-05-27)
[2825] Perl course - is it tailored to Linux, or Microsoft Windows? - (2010-06-25)
[2971] Should the public sector compete with businesses? and other deep questions - (2010-09-26)
[3322] How much has Perl (and other languages) changed? - (2011-06-10)
[3332] DNA to Amino Acid - a sample Perl script - (2011-06-24)
[3407] Perl - a quick reminder and revision. Test yourself! - (2011-08-26)
[3823] Know Python or PHP? Want to learn Perl too? - (2012-07-31)
[3902] Shell - Grep - Sed - Awk - Perl - Python - which to use when? - (2012-10-22)
[3911] How well do you know Perl and / or Python? - (2012-11-04)
[4296] Polishing the Perl courses - updated training - (2014-09-17)
[4301] Perl - still a very effective language indeed for extracting and reporting - (2014-09-20)
M300 - Well House Manor - Behind the scenes [203] Holes in on line information - (2005-02-05)
[677] Sympathetic development - (2006-04-09)
[742] A visit from the solicitor - (2006-06-03)
[769] King Edward VII - days of empire - (2006-06-20)
[813] Monday Morning at Well House Consultants Melksham - (2006-07-24)
[868] Smoking, or no - (2006-09-16)
[897] Too much for the National Trust - (2006-10-18)
[927] Hotel door furniture - (2006-11-15)
[931] Before and After - Well House Manor - (2006-11-18)
[939] Swipe cards for hotel rooms - Security issues - (2006-11-23)
[951] What happened at Geekmas - (2006-11-28)
[1000] One Thousand Posts and still going strong - (2006-12-18)
[1083] Behind the scenes - (2007-02-17)
[1105] A week is a long time in the life of a conference centre - (2007-03-10)
[1159] It can take more that one plus one to get two. - (2007-04-22)
[1179] Sizing sheets and other domestic issues - (2007-05-07)
[1189] Meet, greet and welcome - (2007-05-16)
[1285] In the army, or in civvie street? - (2007-07-31)
[1311] What do people look for on a hotel web site? - (2007-08-20)
[1319] Customer feedback - lifeblood of a business - (2007-08-25)
[1369] One business, four different angles! - (2007-09-28)
[1371] Hotel in Melksham at Christmas - (2007-09-29)
[1441] On cancellations, rebooking, and pricing schemes - (2007-11-22)
[1490] Software to record day to day events and keep an action list - (2007-12-31)
[1515] Keeping staff up to date on hotel room status - (2008-01-22)
[1520] Terms and Conditions - Hotel, England - plain English - (2008-01-26)
[1571] Await guests in the early hours - (2008-03-12)
[1637] Providing exceptional service - and carrying on doing so. - (2008-05-09)
[1854] Three Seasonal Pictures - (2008-10-25)
[1900] Table Topics - (2008-11-22)
[1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
[1948] Blame Culture - (2008-12-17)
[1957] Why are cooks bad tempered? - (2008-12-22)
[1973] Required Request - (2009-01-03)
[1991] Rules for a King - (2009-01-13)
[2125] We have lost a regular business guest - (2009-04-10)
[2247] A day in the life of a hotelier - (2009-06-18)
[2396] Easing off in our 50s? - (2009-09-06)
[2910] Robust - testing the system - (2010-08-09)
[2916] Testing the robustness of our hotel and training systems - holiday and sickness times - (2010-08-11)
[2960] The Well House team - September 2010 - (2010-09-19)
[2961] Initial handling of phone calls and walk in visitors - (2010-09-19)
[3743] Sunday - no longer a day of rest - (2012-05-28)
[3844] Rooms ready for guests - each time, every time, thanks to good system design - (2012-08-20)
[3961] Well House Staff Party - (2012-12-22)
[4302] Sunday is never quiet at Well House Manor - (2014-09-21)
[4365] The changing face of Christmas - (2014-12-26)
Some other Articles
Making Perl class definitions more conventional and shorterContrast in picturesThe Christmas Season has arrivedSetting your user_agent in PHP - telling back servers who you areHow many toilet rolls - hotel inventory and useagewxPython geometry - BoxSizer exampleHow do regular expressions work / Regular Expression diagramsMatching to a string - what if it matches in many possible ways?Python regular expressions - repeating, splitting, lookahead and lookbehindMelksham - two many councils?