Coffee Shop Inventory With AI: Ordering Milk, Beans and Pastries

Export six weeks of item sales, convert drinks into litres of milk, grams of beans and pastry counts, and average the use for each weekday. Set each par level at average use over the days until the next delivery plus a safety buffer, then order par minus tonight's count. Log waste daily and let AI tighten the numbers weekly.

The trap in coffee shop inventory management is ordering from one weekly average. A shop that uses 49 litres of milk on an average day actually uses 38 on a Tuesday and 70 on a Saturday, so ordering from the flat figure leaves the Saturday delivery, which has to last until Tuesday, 35 litres short of what it needs and the Tuesday one 14 litres over. AI earns its place because it does the weekday sums and drafts the order. Your suppliers' units, delivery days and use-by dates are yours to supply and check.

Follow me on Instagram@sagnikteaches

Turn the till report into litres, kilos and pastry counts

Most tills can export item sales by day to a spreadsheet; the report may be called item sales, product mix or sales by item, so look for whichever lists units sold per product per date. Ask for six weeks. Each weekday then has six observations, enough to see its usual level and how far it strays. Keep three columns: date, item and units.

Connect on LinkedInSagnik Bhattacharya

The conversion table is what turns drinks into stock. Weigh and measure rather than guess: steam a jug the way you normally do and see how much milk went in, and weigh a shot. These are illustrative recipes for a shop that makes a double shot with 18 g of coffee in every espresso-based drink:

Subscribe on YouTube@sagnikteaches

Then add a waste allowance for milk left in jugs, pour-offs, remakes and training drinks. Six to eight percent is a sensible starting guess, not a fact; the check at the end of this tutorial tells you what your shop's figure really is. Pastries need no conversion, since one sale is one unit, but they do need the time of any sell-out, which a notebook by the display captures as well as any till.

Usage by weekday, not one weekly average

Here is a shop's whole-milk use in litres for the same weekday across six weeks, worked out from item sales with the conversion table. The shop is open seven days and sells mostly dairy drinks, with oat as a separate line.

The weekly total is 344.5 litres, or 49.2 a day on average. That flat figure is the easy one to start from, and here it is wrong on almost every day. Notice too that weeks 2 and 4 run high across the whole week while weeks 1 and 5 run low. Busy weeks are busy together, which matters for the safety buffer in the next section.

I'm pasting six weeks of daily item sales for a coffee shop (date, item, units sold).
Conversion table: [paste the recipe table].
1. Convert units into litres of whole milk and oat milk and grams of coffee per day.
2. Add 6% waste to each.
3. Give the average and standard deviation for each weekday, using calendar weekdays.
4. My deliveries arrive Tuesday, Thursday and Saturday morning. Total each weekly cover period
   (Tue+Wed, Thu+Fri, Sat+Sun+Mon), then give its average and standard deviation.
5. Show the first three rows of every calculation so I can check them.
Treat weeks flagged "event" or "holiday" separately and tell me which rows you excluded.

ChatGPT, Claude and Gemini all accept uploaded spreadsheet files, so upload the till export rather than pasting thousands of rows, and ask for the formula behind each figure so the sheet can do the sums next week without the chat. If the spreadsheet side is new to you, the prompts in Google Sheets AI prompts show how to ask for pivots and AVERAGEIFS formulas, and you can check the assistant's answer by recalculating one weekday by hand. The instruction to use calendar weekdays matters: without it, some assistants group by position in the file and quietly average a Tuesday with a Wednesday.

Par levels for whole milk, with sample numbers

Here a par level means an order-up-to level: the stock you want on the shelf once a delivery has landed. It has two parts. The first is average use over the days until the next delivery. The second is a safety stock, calculated as a multiplier called z times the standard deviation, which is the usual amount by which a cover period strays from its average. The z number sets how often you accept running short: 1.28 means about nine cover periods in ten are covered, and 1.65 about 19 in 20, if use is roughly bell-shaped. Pick a higher z for items customers notice at once, such as whole milk, and a lower one for slow movers.

The cover period comes from your delivery days. With deliveries on Tuesday, Thursday and Saturday mornings, Tuesday's delivery covers Tuesday and Wednesday, Thursday's covers Thursday and Friday, and Saturday's covers Saturday, Sunday and Monday. Add up each cover period week by week first, then take its average and spread; do not add up daily spreads, which can overstate the buffer.

Set these beside the flat average of 49.2 litres a day. A flat method would order up to 98 litres for Tuesday (14 over par, 17% more than needed), 98 for Thursday (5 short) and 148 for Saturday (35 short, 19% below par), and Saturday is the day a stock-out costs most.

Now the order itself. On Wednesday at close the count is 17 litres. Thursday's order is 103 minus 17, so 86 litres. The dairy sells 12-litre crates of six 2-litre cartons, which makes 7.2 crates. Order 7 crates (84 litres) and you hold 101 litres after the delivery, 2 below par but above the 96.2 litres you expect to use. Round up instead when a shortfall would hurt more than a little spare milk, and always check the use-by date on the cartons the supplier usually sends against the days you plan to hold them.

A par is not a target if you cannot store it. Two limits override the sums: fridge space and use-by dates. Say the fridge holds 160 litres of whole milk. Saturday's par of 183 will not fit, so you either order up to 160 and accept a higher risk of a Monday shortage, or arrange a Sunday top-up. And if the Thursday delivery is skipped for a supplier holiday, Tuesday's order has to cover four days: average 174.3 litres, spread 9.5, par 187, again above the fridge. The sheet's job is to show that conflict a week ahead, not on the morning.

Alternative milks get their own lines. If oat is one in five milk drinks, it uses a quarter as much as whole milk (20% against 80%), so Thursday's cover averages about 24 litres with a par near 26, ordered in cases of twelve 1-litre cartons. For the wider logic of reorder points, see using AI to set reorder points and prevent stockouts, and for a kitchen with a menu rather than a drinks list, par levels and orders for a restaurant adapts the same sums.

Beans: weigh the waste, then order whole bags

Beans are the easy item, because they keep for longer and the maths is linear. Use the double shots sold each week from the till (here 1,525, from 190 on a Monday up to 310 on a Saturday), multiply by 18 g, and add the waste: the purge when the grinder is dialled in, remakes and spills. Weigh the waste for three mornings before you pick a percentage; 6% is used here as an example.

The house blend sum is 1,525 × 0.90 × 18 g × 1.06, which is 26.2 kg. Add a safety stock of about 10%, 2.6 kg, to reach a weekly par of 28.8 kg. If Sunday's closing count is 3.1 kg, the order is 28.8 minus 3.1, about 25.7 kg, so 26 one-kilo bags. The slow movers need a different rule: round up to the pack the roaster sells, but never hold more than the roaster's recommended window after the roast date. Ask the roaster how many weeks after roasting their coffee is at its best and how long it holds up, and set the par so you never carry more days of stock than that.

A weekly roaster delivery means the par covers seven days, so one bad guess costs a week. If the roaster offers a mid-week top-up for the house blend, take it and halve the cover period; the safety stock then falls too, and the beans are fresher when they are ground.

Pastries: order the number you can expect to sell

Pastries are the item where over-ordering and running out both cost real money, and the right order is not the average. The rule is short: the more a lost sale costs compared with a leftover, the higher above the average you order. Take a butter croissant costing $1.10 and selling at $3.50. A lost sale costs the $2.40 margin you did not earn; a leftover costs the $1.10 you cannot recover if it cannot be sold tomorrow. Divide the lost-sale cost by the total of both ($2.40 divided by $3.50) and you get 68.6%, which means order enough to cover demand on about 69 days in 100. For bell-shaped demand that works out at the average plus 0.48 times the spread.

To see what this buys, take a Thursday with an average of 32 and a spread of 4. Ordering a flat 40 leaves about 8 unsold on average and costs roughly $8.90 a day in leftovers and lost sales together, against about $5.60 at 36 and $5.00 at 34. Those figures assume demand is roughly bell-shaped and use the spread from your own history, so treat them as an illustration of direction, not a promise. The tray size matters too: a supplier who sells in sixes pushes 33.9 up to 36, which is why Thursday's order sits above its formula. For the wider picture on unsold stock, how bakeries use AI to predict demand and cut unsold stock covers the supplier side.

One trap is specific to pastries. When you sell out, the sales figure is not the demand; it is only a floor. A croissant tray that vanished at 14:10 might have sold 40, 45 or 50. Record the time of every sell-out, and tell the assistant which days were censored in this way, otherwise your averages drift downwards and your orders follow them.

A waste log that tightens every par

A par level is only as good as its last review, and the review needs evidence. Keep a one-line-per-event waste log: date, item, quantity, reason and the cost. The reason is the useful column, because "unsold at close" and "past use-by" lead to different fixes. This is one week of an illustrative log, with the unit costs the shop pays:

Alongside it, keep the pastry orders, sales and any sell-out times: croissants ordered 36, 36, 36, 36, 42, 54 and 48 across the week, sold 33, 35, 36, 31, 42, 51 and 41, with sell-outs on Wednesday at 14:10 and Friday at 15:20. Paste both into the assistant and ask for a reading rather than a summary.

Here is a week of waste log and croissant orders versus sales (below). Group waste by item and
by reason, give the cost and share of total, and say which par levels look too high or too low.
Treat any day that sold out as "demand at least this much", not an exact figure. Suggest changes
of no more than one tray per day, and say how many weeks of data I need before trusting each one.
[paste the log and the table]

Illustrative output
Total waste: $36.26. Croissants $20.90 (58%), oat milk $4.40, beans $3.96, banana bread $3.40, whole milk $3.60.
Croissants: ordered 288, sold 269 (93%), left 19. Wed and Fri sold out, so demand was at least 36 and 42.
Suggestions: Sunday order 42 instead of 48 (left 7 vs average 44, but only one Sunday observed).
Whole milk 3 L past use-by on Monday follows the 183 L Saturday par; check Sunday's count before ordering.
Needs more weeks: all croissant changes (1 week only). Oat milk (1 event).

Read that with a sceptical eye. One week is noise: a single Sunday with seven leftovers does not make Sunday's average 41. Hold the order steady until a second week agrees, and move by one tray at most. The assistant's point about Monday's milk deserves action sooner, because it links two parts of the system: Saturday's high par produced Monday's spoilage, so the fix is a Sunday count that adjusts the Saturday order, not a lower par. If you want more structured counting routines to feed this log, why a stock count and a system disagree lists the usual causes.

Draft the supplier order from tonight's count

The last step is the one that saves the most time: turning counts and pars into an order message. Give the assistant the closing counts, the pars, the supplier's units and the cut-off, and make it show the arithmetic before it writes anything.

Draft tomorrow's dairy and bakery orders. Use only these figures.
Wednesday close: whole milk 17 L, oat milk 6 L.
Thursday delivery covers Thursday and Friday. Par: whole milk 103 L, oat milk 26 L.
Units: whole milk in crates of 12 L; oat milk in cases of 12 x 1 L.
Rule: order = par - count, rounded to the nearest whole unit. Show the arithmetic first.
Bakery (trays of 6): Thursday croissants 36, Friday 42.
Then write one short message per supplier giving delivery day and quantities in their units.

Illustrative output (contains a slip)
Whole milk: 103 - 17 = 86 L = 7.2 crates -> 7 crates (84 L)
Oat milk: 26 - 6 = 20 L = 1.7 cases -> 2 cases (24 L)
Croissants: Thursday 36 = 6 trays; Friday 42 = 7 trays
Message to dairy: "Hi, order for Thursday morning: 86 crates whole milk, 2 cases oat milk. Thanks, [your name]"

The arithmetic says 7 crates; the message says 86. The assistant copied litres into the crate field, a mistake that would have put more than ten times too much milk on the doorstep. This is why the prompt demands the arithmetic first, and why you read the message against it. Check units, delivery day, any supplier holiday notice and special requests, then send the message yourself.

Event days, supplier holidays and a new seasonal drink

Averages describe ordinary weeks, so the sheet needs a way to handle the days that are not. Three cases come up often enough to plan for.

• An event next door. Add an "event" flag to the date column and ask the assistant to show the par with and without it. If you have no history for events, borrow the busiest comparable day from the last six weeks as the estimate, and note that it is a borrowed number. You decide whether to cover it; the assistant only shows the cost of each choice.
• A supplier holiday or a missed delivery. The cover period lengthens, as in the skipped-Thursday example above. Ask the assistant for the new par and the fridge check together, and decide ahead of time whether to buy top-up stock from a wholesaler or accept a short menu for a day.
• A new seasonal drink. It has no history, so start from the nearest existing drink's weekday pattern and half of its volume, with a syrup or ingredient par of one pack. Track it daily for two weeks and re-run the numbers. A seasonal menu planned from sales data, as in planning a seasonal drinks menu from sales data, gives you the first estimate of its share.

Check it worked: three numbers every week

Three figures tell you whether the pars are right, and they take five minutes to collect.

1. Stock-outs. How many times an item ran out, and when. Any milk stock-out on an ordinary day means the z multiplier is too low or a count was wrong.
2. Waste value. The week's total from the log, tracked against the week before. Falling waste with no new stock-outs means the pars are tightening in the right direction.
3. Theoretical against actual milk. Work out how much milk the drinks sold should have used, then compare it with opening stock plus deliveries minus closing stock.

The third is the most revealing. On a Thursday the shop sold 66 flat whites, 84 lattes, 40 cappuccinos, 27 iced lattes and 11 hot chocolates on whole milk. With the conversion table that is 10.56 + 18.48 + 5.60 + 4.05 + 2.75, or 41.4 litres in theory. The stock count says 44 litres were used, 6.2% more, which sits within the 6% to 8% waste allowance. On the following Saturday the shop sold 98 flat whites, 122 lattes, 57 cappuccinos, 44 iced lattes and 19 hot chocolates, 61.9 litres in theory, against 71 litres used. That is 14.8% over, a gap of about 9 litres, well outside the allowance.

A gap that size is a prompt to look, not a verdict. The usual suspects are over-pouring by a new barista, a dropped crate, drinks made but not rung through the till, staff drinks, and a count taken in a hurry. Compare the gap with last Saturday's, check the waste log for a spoiled crate, and watch the next few services. Then each Monday, spend fifteen minutes: paste the last week's item sales, add the closing counts and any flags, review the suggested pars, and send the orders you have checked. If the checks keep passing and the waste number keeps falling, the pars are doing their job.

Questions about ordering for a coffee shop

How many weeks of sales data do I need for par levels?

Six weeks is a workable start, because each weekday then has six observations to average. With less, use a flat buffer of around ten percent and tighten it as data arrives. Flag holiday weeks and event days instead of letting them distort the averages. More than twelve weeks adds little unless your menu has stayed the same.

Do I need inventory software, or is a spreadsheet enough?

A spreadsheet is enough for one shop with a handful of suppliers. Software starts to pay when several people count stock, when you run more than one site, or when you want purchase orders sent to suppliers automatically. Try the routine on a sheet first; if you keep skipping steps, that is the sign to buy something.

How should I handle oat and other alternative milks?

Give each type its own usage line and par level, because they come in different pack sizes, last different lengths of time and cost different amounts. Track each type's share of your milk drinks weekly, and expect it to move when you change the menu. Order in whole cases, with a minimum of one case if regulars depend on it.

What if my supplier has minimum orders or fixed delivery days?

Build both into the sums. The cover period runs to the next delivery you can actually get, and quantities round to the supplier's units and minimums. If a minimum forces you to hold more than par, accept it for items that keep, and for perishables review the delivery schedule or add a second supplier for top-ups.

Can AI place the order for me?

It can draft the arithmetic and the order message, but you should read and send it. A wrong unit, a missed holiday or a misread count costs more than the minutes saved. Check every order against the arithmetic for the first few weeks, and keep checking the units, because that is where drafts most often slip.

Further reads

• How Can a Coffee Shop Use AI? Start With Supplier Prices — Start with supplier prices to cost your menu and spot rises.
• AI in Hospitality Management: Catch Supplier Price Creep on Invoices — Catch supplier price creep on invoices before it hits margin.
• Coffee Shop Management Software: What AI Adds Over a Basic POS — What AI adds over a basic till and when software is worth it.
• Bubble Tea Inventory: Forecast Tapioca, Milk and Cups by Weekday — Forecast tapioca, milk and cups by weekday for a drinks shop.
• Does a Coffee Shop Need an AI Receptionist? When It Pays — A payback sum for an AI receptionist in a coffee shop, using missed large-order calls, plus the case where you should not buy one.
• ChatGPT Prompts for Coffee Shop Owners: 10 for Menus, Posts, Rotas — Ten ChatGPT prompts for a coffee shop, from cost per cup to rotas and posts, each with example inputs and a check before you use it.
• Coffee Shop Pricing: Cost per Cup and Margins Worked Out With AI — Cost five drinks gram by gram, set a margin target from overheads, round to menu prices without losing margin, and have AI build and check the sheet.
• Hotel Breakfast Food Waste: Predict Buffet Demand With AI — A worked breakfast-covers forecast from occupancy, with an accuracy test, buffet replenishment rules and a five-minute waste log for a small hotel.
• Pizza Shop Inventory: Plan Dough and Toppings From Weekend Orders — Turn eight weeks of orders into dough balls, cheese and topping quantities for Friday to Sunday, then tune the plan from a simple waste log.
• AI Pricing for Grocery Stores: Can It Set Short-Date Markdowns? — Turn an expiry list into markdown rules an AI can apply, with one chilled aisle worked through and a two-week check on waste and margin.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: illustrative calculations using standard inventory maths (average use over the cover period plus a safety buffer, and the critical-ratio rule for perishable stock). Check your own suppliers' delivery days, pack sizes and use-by terms.