~/sajjad $ cat projects/drinks-ordering
← cd ../projects
Data & reporting
Weekly drinks ordering & supplier case
Live · weekly since Aug 2026555-line price catalogue
A newly opened, drinks-only pub needed to order every week without running dry or tying up cash. I built the ordering from its till data: par levels per line, a cellar count sheet, orders trimmed to a budget, and a trading pack used to negotiate better supplier prices.
The method
- Par per draught line = a week of demand plus a safety stock of 1.65 standard deviations × √7, divided by sellable pints per container, rounded up. Sellable pints allow about 7% for line cleaning and ullage (82 from a 50-litre keg).
- A printable weekly cellar count sheet, with ORDER = PAR − counted, and a branded order document every week.
- When a cellar cooling fault pushed wastage from 7% to 10–12%, the yield assumption changed with it.
- The forward figures are based on till consumption, not order history, because the orders actually sent often differed from the ones calculated.
Decisions it made
- The fastest line emptied a keg in 2.9 days, against 10+ days for the rest, so it moved to double-size containers. The break-even price was worked out first.
- A four-week plan kept draught spend under a £7,500 cap by drawing stock down. A skip-a-week option was modelled and rejected because it cost more and needed one very large delivery.
- Margins by line exposed the weakest draught line, at 41.7% GP, and three price moves worth about £98 a week.
- An audit of a manager’s own order spreadsheet found its order column didn’t follow its own shortfall formula.
The supplier case
Most of the supplier’s price list existed only as blurry photographs. I built a verified catalogue of 555 lines and marked every unreadable price as TBC rather than guessing.
The trading pack for the supplier’s area manager argued for a lower price band on standard-yield margins alone, so it held up without leaning on the cooling fault or a competitor’s quote, which were both deliberately taken out.