Export six to eight weeks of till sales, have AI build a sheet that takes the median cups for each weekday, then turn cups into pearl batches, milk litres and cup counts using per-drink amounts from your recipe cards. Add exam-week and event multipliers, round up to batch and carton sizes, and check forecast against actual weekly.
AI should build and audit the sheet, not do the weekly arithmetic. Chat assistants miscount; spreadsheets do not. It also cannot see your calendar, so exam weeks, school holidays and events have to come from you. Every quantity below is illustrative, for a one-counter shop selling about 1,280 cups a week, so measure your own before relying on any of them.
Why by weekday: in the example a Saturday sells more than three times a Monday, so a single "average day" order leaves you short at weekends and binning stock midweek. Tapioca pearls punish a bad forecast fastest, because they are cooked in batches and have a short hold time, so the sheet is built around them, with milk and cups following the same cup count. The per-cup amounts come from your recipe cards; the tutorial on drink recipe cards and staff quizzes shows how to write them down once and keep them honest.
Four numbers from the till that drive every order
Export item-level sales for the last six to eight weeks: date, item, size, toppings and quantity, plus the hour if your till records it. Check that no customer names, emails or loyalty details are in the file before it goes into any AI tool. Stock forecasting never needs them. Four numbers then do nearly all the work.
| Number | Where it comes from | Illustrative value |
|---|---|---|
| Baseline cups per weekday | Median of the last six normal weeks for that day | Mon 100, Tue 120, Wed 140, Thu 140, Fri 200, Sat 320, Sun 260 |
| Pearl share | Cups with pearls divided by all cups, from the toppings column | 55% |
| Milk share and amount | Milk-based cups divided by all cups; millilitres per cup from the recipe card | 70% of cups, 110 ml each |
| Size mix | Cups by size | 70% 700 ml, 30% 500 ml |
Use the median, not the average. Say Tuesday sold 118, 124, 144, 121, 117 and 119 cups over six weeks, where the 144 was an exam week. The mean is 124 and the median is 120. One odd week drags the mean up and quietly inflates every normal Tuesday's order; the median barely moves. A prompt that gets the AI to do the first pass looks like this:
Attached is a six-week export of my bubble tea shop's sales (date, item, size, toppings, quantity). It contains no customer data.
1. For each weekday, list the cups sold in each of the six weeks and the median.
2. Flag any weekday 20% or more above or below its median and say which week it was.
3. Give the share of cups that included pearls, the share that were milk-based, and the 700 ml / 500 ml split.
4. Write Google Sheets formulas I can paste to repeat this each week.
Do not estimate anything you cannot calculate from the file. Tell me what is missing.A first pass comes back looking something like this (illustrative):
Tuesday cups by week: 118, 124, 144, 121, 117, 119. Median 120 (mean 124). Week 3 is 20% above the median and is flagged. Pearls appear in 55% of cups, 70% are milk-based, size split is 70% large and 30% regular. Not calculable from the file: millilitres of milk per cup, because the export holds no recipe data.
Three things deserve a second look before you trust it. The summary table quoted the mean (124) for Tuesday while the text said the median, so ask it to restate the table with medians. It counted "popping boba" toppings as pearls, so sample ten rows and tell it how your menu names each topping. And it flagged week 3 without knowing why. Only you know that was exam week, so add a "week note" column and fill it in; the next export then carries the label. With that column in place, the formula for a weekday baseline ignores flagged weeks automatically:
=MEDIAN(FILTER(D2:D43, B2:B43="Tue", F2:F43=""))
(D = cups, B = weekday, F = week note, blank for a normal week)If your till export is messy, the tutorial on what to upload and check when AI analyses a sales spreadsheet covers the tidy-up that comes before any of this.
The weekday forecast sheet, filled in
Here is the full sheet for a normal week. Each row is a weekday and each column turns the cup forecast into something you can cook or order.
| Day | Forecast cups | Pearl servings (55%) | Pearl batches | Milk litres | Cups to stock (+4%) |
|---|---|---|---|---|---|
| Monday | 100 | 55 | 2.0 | 7.7 | 104 |
| Tuesday | 120 | 66 | 2.5 | 9.2 | 125 |
| Wednesday | 140 | 77 | 2.5 | 10.8 | 146 |
| Thursday | 140 | 77 | 2.5 | 10.8 | 146 |
| Friday | 200 | 110 | 3.5 | 15.4 | 208 |
| Saturday | 320 | 176 | 5.5 | 24.6 | 333 |
| Sunday | 260 | 143 | 4.5 | 20.0 | 271 |
| Week | 1,280 | 704 | 23.0 | 98.6 | 1,333 |
The formulas are short enough to read aloud, which is the test of a sheet staff will actually keep using:
Pearl servings = cups x 0.55
Pearl batches = CEILING(servings / 32, 0.5) (1 kg dry pearls = 32 servings)
Milk litres = cups x 0.70 x 0.110
Cups to stock = ROUNDUP(cups x 1.04, 0) (4% allowance for remakes and spills)The batch rounding is deliberate. The week needs 704 servings, which is 22.0 batches, but the sheet says 23.0 because each day rounds up to the next half batch. That extra kilogram of dry pearls (about 4.5%) is the price of not running dry; if it turns into binned pearls, move to quarter batches in the afternoons. The weekly total gives your dry-pearl order and the daily rows give the cook plan.
Ask the AI to build this sheet from a one-paragraph description of the columns, then audit it yourself with three cells: Saturday pearl servings (320 x 0.55 = 176), Saturday batches (176 / 32 = 5.5) and the weekly milk total (896 milk cups x 0.110 = 98.56 litres). If all three match, the rest of the sheet follows the same pattern. If one does not, fix the formula before the assistant fills in the other six rows with the same error. Gemini in Google Sheets and similar built-in assistants can write these formulas too; the Google Sheets AI prompts tutorial covers how to ask for them.
Pearl batches: how many to cook and when to start the next
Three measurements turn a batch into a number you can plan with. Cook a normal batch and weigh the dry pearls going in. Weigh the cooked pearls coming out. Then weigh a standard serving from the recipe card. In the example, 1 kg of dry pearls gave 2.3 kg cooked, and a 70 g serving makes 32 servings per kilogram (2,300 / 70 is 32.9, rounded down for the pearls left in the pot). Your pearls, your pot and your serving will give different numbers, so measure on your own equipment before you copy anyone's yield.
The hold time is the second input. Pearl brands differ, and food-safety rules differ by place, so take the hold time from your supplier's instructions and your local food-safety guidance, and use the shorter if they disagree. Say your rule is to bin pearls four hours after cooking. A Saturday needs 5.5 batches, but you cannot cook them all at opening. They have to be cooked through the day, one batch ahead of demand, and the trigger for each new batch is the level in the tub, not the clock.
| Cook trigger | Tuesday | Saturday |
|---|---|---|
| Forecast pearl servings | 66 | 176 |
| Average per trading hour (nine hours) | 7.3 | 19.6 |
| Busiest-hour pace (1.8 times the average, from your hourly sales) | 13 | 35 |
| Servings sold in a 45-minute cook and rest at that pace | 10 | 26 |
| Plus a buffer | 3 | 4 |
| Start the next batch when the tub reaches | 13 servings | 30 servings |
That table is the part most stock tips skip. It says a Saturday and a Tuesday need different triggers, because the same 45-minute cook is a very different risk when the queue is five deep. Put the number on a card by the pot. Add a last-batch rule too: in the last ninety minutes of trading, cook half batches only, so the final hour does not end with a full pot to bin. Ask the AI to find your busiest-hour share from the hourly column of your export; it is a ten-second question that replaces a guess.
Milk cartons around two deliveries a week
Milk is the item where the delivery pattern matters as much as the forecast. Take a shop supplied on Monday and Thursday mornings in 2-litre cartons, with a 15% safety margin. Each order must last until the next delivery, so the Thursday order has to cover Thursday to Sunday and the Monday order covers Monday to Wednesday.
- Thursday order. Thursday to Sunday forecast: 10.78 + 15.40 + 24.64 + 20.02 = 70.84 litres. With the margin that is 81.5 litres. Subtract 6 litres counted on the shelf and you need 75.5 litres, which rounds up to 38 cartons.
- Monday order. Monday to Wednesday forecast: 7.70 + 9.24 + 10.78 = 27.72 litres. With the margin, 31.9 litres. Subtract 5 litres on the shelf and you need 26.9 litres, which rounds up to 14 cartons.
Over a week the two orders come to 52 cartons, or 104 litres, against 98.6 litres forecast. The gap is mostly carton rounding plus the margin left on the shelf when the next delivery arrives, and it is cheap insurance compared with telling a Saturday queue there is no milk tea. Count the shelf before every order, and mark opened cartons with the date so the oldest goes first. If you also stock oat or other plant milks, run the same calculation per type using each drink's share; the tutorial on coffee shop inventory for milk, beans and pastries shows the same idea with several milk types.
Cups, lids and straws: order by size, not by total
Cups do not spoil, so the risk is the wrong size, not too much. A weekly total of 1,333 cups looks fine until the 700 ml cups run out on Saturday while a carton of 500 ml cups sits unopened. Split the forecast by size mix first: of the 1,333 cups with the allowance, 70% large is 933 and 30% regular is 400. Lids or sealing film and straws follow the cup count, one each, so check them on the same day as the cups.
Now order against the shelf. Say the Tuesday count shows 380 large and 110 regular cups, the supplier ships in cartons of 500, and you keep a 15% margin.
- Large (700 ml). 933 x 1.15 = 1,073 needed, less 380 on hand = 693 to order. One carton is 500, which is not enough, so order 2 cartons (1,000).
- Regular (500 ml). 400 x 1.15 = 460 needed, less 110 on hand = 350 to order. One carton of 500 covers it, so order 1.
Pack sizes can force more stock than the margin calls for. After this delivery the shop holds 1,380 large cups against 933 used in a week. If storage or cash is tight, ask whether your supplier sells half cartons or sleeves, or move the order to a different delivery day. Dry pearls follow the same logic on a larger scale: 23.0 kg of forecast use plus 15% is 26.5 kg, less 5 kg on hand is 21.5 kg, which with 3 kg bags means eight bags (24 kg).
Exam weeks and events: where the multipliers come from
A multiplier is just last term's ratio. Say last term's exam week sold 600 cups from Monday to Thursday, against a baseline of 500 (100 + 120 + 140 + 140). The multiplier is 600 / 500 = 1.2. Apply it to the same four days next exam week and leave Friday to Sunday alone, if last term's data shows weekends did not move.
| Day | Baseline cups | Exam-week cups | Pearl batches | Milk litres |
|---|---|---|---|---|
| Monday | 100 | 120 | 2.5 (was 2.0) | 9.2 |
| Tuesday | 120 | 144 | 2.5 | 11.1 |
| Wednesday | 140 | 168 | 3.0 (was 2.5) | 12.9 |
| Thursday | 140 | 168 | 3.0 (was 2.5) | 12.9 |
| Friday to Sunday | 780 | 780 | 13.5 | 60.1 |
| Week | 1,280 | 1,380 | 24.5 (was 23.0) | 106.3 (was 98.6) |
That is 100 more cups sold, which means 104 more cups to stock, 1.5 kg more dry pearls and about 8 litres more milk. It is small enough to miss by hand and large enough to cause a stock-out on a Wednesday afternoon, which is why the multiplier lives in the sheet. The real work is knowing which weeks to flag. Paste the term dates and exam timetable the school or college publishes into the AI and ask it to list the weeks to flag, then check the list against the published page yourself. The AI cannot know the local calendar; it can only reformat the one you give it.
Keep an event column for everything else: school holidays, a heatwave forecast, a promotion that moves volume (a buy-one-get-one Tuesday will shift sales between days), a local event that ends near your door. Each time one happens, record the ratio against baseline, and next time it appears you have a multiplier from your own data. The ice cream shop inventory tutorial uses the same approach with weather as the main driver.
Shelf life decides how tight each order can be
The forecast says how much you will use. The shelf life says how close to that figure you are allowed to buy. Fill in a column like this from your supplier's labels and instructions, not from memory.
| Item | How long it keeps | Ordering rule |
|---|---|---|
| Dry tapioca pearls | Long when sealed; the bag carries a best-before date | Weekly, with a generous margin; oldest bags first |
| Cooked pearls | Hours, not days; your hold time from supplier instructions and local food-safety rules | Cook to the trigger; keep batches separate so each has a cook time on its label |
| Fresh milk | The carton's use-by date; your own rule once opened | Twice a week; date opened cartons |
| Brewed tea | Hours; your own hold rule | Brew in batches tied to the same day-part forecast as the pearls |
| Syrups, powders, jellies | Pack dates, usually long | Monthly, from usage per cup |
| Cups, lids, film, straws | No shelf issue; space is the limit | Weekly, by size |
Perishables get tight, frequent orders and non-perishables get looser ones. The mistake is treating them the same: a shop that buys milk like cups binned cartons, and a shop that buys cups like milk keeps running out of lids on a Friday. If you want a wider view of when forecasts mislead, why AI stock forecasts go wrong lists eight common causes.
Five ways the forecast fails and how each one shows up
The figures below are invented, but each failure is a common one.
- The baseline swallowed an odd week. The average Tuesday looked like 124 instead of 120, and the mid-afternoon pearls were binned every Tuesday for a month. Fix: medians, and a week-note column that excludes flagged weeks.
- The assistant did the arithmetic. A chat reply said weekly milk was 94 litres; the sheet said 98.6. Both looked plausible. Fix: only the sheet's total counts, and any figure the AI states gets compared with it.
- The menu changed and the ratios did not. After a brown sugar launch the pearl share moved from 55% to 63%. On a 320-cup Saturday that is 202 servings against a planned 176, a shortfall of about 26. The tub empties at half past three. Fix: re-measure shares from the till every month and after any launch.
- An event the sheet never heard about. A Saturday sold 352 cups against a forecast of 320. Pearls ran out at half past four and, at an average of $5.50 a cup, roughly 30 missed sales cost about $165. Fix: an event column that you and your staff fill in, plus a note after each surprise.
- Ordering to the forecast with no margin. A late delivery on Thursday meant the Friday shift opened with 3 cartons of milk. Fix: the 15% margin, and one carton count the night before every delivery.
None of these is an AI failure as such. They are the ordinary ways any forecast breaks, which is why a short weekly review matters more than the sophistication of the first version.
Forecast against actual: a ten-minute Monday review
Once a week, put actual cups next to the forecast and calculate the miss as (actual - forecast) / forecast. Here is an illustrative week.
| Day | Forecast | Actual | Miss |
|---|---|---|---|
| Monday | 100 | 104 | +4.0% |
| Tuesday | 120 | 118 | -1.7% |
| Wednesday | 140 | 143 | +2.1% |
| Thursday | 140 | 139 | -0.7% |
| Friday | 200 | 214 | +7.0% |
| Saturday | 320 | 352 | +10.0% |
| Sunday | 260 | 261 | +0.4% |
| Week | 1,280 | 1,331 | +4.0% |
The week is within 4%, which is fine, but Saturday missed by 10%. One miss like that is noise. If a day misses by more than 10% two weeks running, look for a missing event or a changed mix. Alongside the miss, log three things every week: stock-outs (any time an item hit zero during trading), kilograms of pearls and cartons of milk binned, and the number you cooked for the busiest day. Ten minutes on Monday morning is enough.
The cost is small. Building the first version takes two to three hours, mostly measuring your own yields and writing down shares. After that the weekly update is about twenty minutes. Building and auditing the sheet needs nothing beyond the free tier of ChatGPT, Claude or Gemini; a paid plan at about $20 a month only matters if you upload large files often. When you have six months of weekly checks, you will know your real error rate, and that is the number to put beside the stock-outs and the bin when you decide whether the routine pays for itself.
Bubble tea stock forecasting: questions from owners
How many weeks of till data do I need before forecasting?
Six normal weeks gives a usable median for each weekday. A new shop with less history should order conservatively, count stock every evening for the first month, and replace the guesses with real figures as each weekday builds up data. Do not wait for a perfect dataset; a rough sheet corrected weekly beats an ordering habit based on memory.
Can the AI connect to my till and order stock for me?
Some tills and inventory apps offer their own forecasts, but automatic ordering is a step to earn later. Start with a sheet you understand, run it for two months beside your normal ordering, and compare. If the sheet beats your habit and you can explain every column, then look at tools that read your till directly.
How do I handle a new flavour or a seasonal drink?
Track each drink's share of cups rather than a total, and treat the first fortnight of a launch as unknown. Use actual sales to set its share, then rebuild the pearl, milk and cup ratios. A menu change that moves pearl share by eight points is large enough to empty a batch tub on a busy afternoon.
Do popping boba and jellies need the same method?
Yes. Work out the share of cups with each topping, multiply by the amount in the recipe card, then round to pack or tub size. Only the shelf life differs: sealed tubs usually have a long date, while anything opened or cooked needs the shorter rule from the supplier's instructions.
Is it safe to paste item-level sales into ChatGPT or Claude?
Item-level sales carry no personal data if you strip names, emails, loyalty numbers and card details first. On consumer plans, switch off the model-training setting in privacy settings. Business plans do not train on your content by default, but the same habit of exporting only what the task needs still applies.
Further reads
- Restaurant Inventory Management With AI: Par Levels and Orders — Par levels and orders for a kitchen with more ingredients.
- How to Forecast Covers and Cut Food Waste With AI — Forecast covers to cut food waste, the same method for meals.
- Coffee Shop Pricing: Cost per Cup and Margins Worked Out With AI — Turn your per-cup quantities into cost and margin.
- Recipe Costing Spreadsheet for Restaurants: Build It With ChatGPT — Build the costing sheet that sits beside this forecast.
- AI in Hospitality Management: Catch Supplier Price Creep on Invoices — Catch supplier price rises before they reach your margin.
- How to Automate Purchase Orders and Supplier Emails With AI — Draft supplier orders automatically once the numbers are trusted.
- How Cafés Use AI to Fill Quiet Hours With Targeted Offers — Use the quiet weekday hours the forecast reveals.
- How to Use AI to Set Reorder Points and Prevent Stockouts — Set reorder points for the slower lines: syrups, powders and jellies.
- 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 Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: your own till export and recipe cards; supplier pack instructions and local food-safety rules for pearl hold and cook times; OpenAI and Anthropic privacy settings help pages. All shop figures in the examples are illustrative.