Ice Cream Inventory: Forecast Tubs by Flavour, Weather and Weekday

Multiply the forecast day's expected scoops (from your own log, grouped by temperature band and day type) by each flavour's share of sales in that band, divide by scoops per tub, add 10 percent, and subtract the tubs you hold. Round top sellers up and slow flavours to the nearest tub.

A sales log records what sold, not what guests wanted, so a flavour that ran out at 3 pm looks less popular than it is. That one correction matters more than any clever model, which is why the sheet below is a handful of plain tables you can audit by eye rather than a black box.

Follow me on Instagram@sagnikteaches

A 16-pan scoop shop that orders by feel

Take a single-counter scoop shop with a 16-pan display cabinet and a freezer behind it. A tub here is the container that goes into the cabinet, and the shop gets about 45 scoops from one; use your own figure from a week of counting scoops sold per tub opened. Illustrative figures for eight summer weeks: the shop opens about 463 tubs, bins 21 of them (4.5 percent), and runs out of at least one top-eight flavour on 31 of the 56 days. It takes deliveries on Tuesday and Friday and orders by feel, with a glance at the weather app.

Connect on LinkedInSagnik Bhattacharya

The two mistakes cost different amounts, and the sheet is built around that. A binned tub costs what you paid for it, say $38. Running out of the best-selling sorbet at 3 pm on a hot Saturday costs the scoops you couldn't sell, and only some of those come back as guests choose another flavour. So the rule leans towards holding enough of the flavours that sell, and towards lean stock on the flavours that don't.

Subscribe on YouTube@sagnikteaches

Step 1: a sales log with a column for what you couldn't sell

One row per trading day. Your till exports the sale lines; you add the weather and the notes. The columns:

• Date and day type. Mon-Thu or Fri-Sun to start. Split Friday, Saturday and Sunday later if the data supports it.
• Daily high, in degrees C. Take it from one source, and the same one every time, because sources differ by a degree or two. Log the high that was forecast the day before as well as the actual, so you can see how far forecasts drift.
• Band. A formula, covered next.
• Total scoops, then scoops per flavour. Sixteen columns, one per cabinet flavour. Leave cones, toppings and drinks out.
• Tubs opened and tubs binned, with the reason. Counted at close.
• Stockouts. Which flavour, and what time it ran out.
• Notes. Rain, a school event, a closed hour, a cabinet fault.

A sample row for a hot Saturday in week 2 (illustrative): Fri-Sun, forecast high 31, actual high 30, band B4, 735 scoops in all, 16 tubs opened, none binned, mango sorbet out at 15:00 after 70 scoops, note "school fair 11 to 1". Two columns carry most of the value: the stockout time, which lets you correct the demand later, and the note, which explains a day that doesn't fit its band.

Step 2: four temperature bands and two day types

Bands beat a fitted line for a shop this size, because you can read, argue with and fix a table. Pick four edges from your own range of summer highs. Here they are: B1 under 18 C (64 F), B2 18 to 22.9 C, B3 23 to 27.9 C and B4 28 C (82 F) and over. The band formula, which works in Excel and Google Sheets, is =LOOKUP(C2,{-50,18,23,28},{"B1","B2","B3","B4"}), with the high in column C. Then average total scoops for each band and day type with =AVERAGEIFS(scoops, band, "B3", daytype, "Fri-Sun") and count the days with COUNTIFS. After eight weeks the shop's table reads (illustrative):

Look at the day counts before the averages. Aim for at least five days in a cell. The weekend B1 cell has three, so its 260 is a weak number: merge it with B2 for now, or keep it and treat it as a guess. The shop's eight weeks also tell you what you don't know. Only five hot weekday days were logged, so the 460 deserves more suspicion than the 330. A cell like that fills in as the season goes on, and the first sign of trouble is when a new day lands far from its cell's average.

Step 3: the flavour mix shifts with the heat

For each band, each flavour's share of that band's scoops: =SUMIFS(mango_scoops, band, "B4") / SUMIFS(total_scoops, band, "B4"). Sorbets climb as it warms and the rich flavours slip, which is the pattern the mix sheet exists to capture. The shop's eight shown flavours plus the other eight lumped together (illustrative shares of scoops):

Each column adds to 100 percent, which is your first self-check. Lump the small flavours into a group, and plan them at group level with the rule of one tub on display each. The mix also moves with the day type, but with eight weeks of data, splitting the table twice would leave most cells too thin to trust.

Step 4: the Friday order, worked through

Friday's delivery has to cover Friday to Monday, and the order goes in on Thursday. The four-day forecast says highs of 27 C on Friday, 29 on Saturday, 24 on Sunday and 21 on Monday. Look up each day's band and day type: Friday is B3 and Fri-Sun, so 540 scoops; Saturday is B4 and Fri-Sun, so 720; Sunday is B3 and Fri-Sun, so 540; Monday is B2 and Mon-Thu, so 240. That is 2,040 scoops, or 45.3 tubs at 45 scoops each.

Now split by flavour. Mango sorbet takes 11 percent of Friday's 540 (59.4 scoops), 14 percent of Saturday's 720 (100.8), 11 percent of Sunday's 540 (59.4) and 8 percent of Monday's 240 (19.2): 238.8 scoops, which is 5.31 tubs. Add the 10 percent safety margin to get 5.84, subtract the 2 tubs on hand at Thursday close, and the sheet says 3.84. Round up and the order is 4 tubs. Doing the same for the rest (illustrative stock levels):

Pistachio gets the different rounding on purpose: it's a slow flavour, a spare tub that doesn't sell is a binned tub, and one tub on display already covers the minimum. The sheet's formula for the top sellers is =MAX(0, ROUNDUP(forecast*1.1 - onhand, 0)), and for slow flavours =MAX(0, ROUND(forecast*1.1 - onhand, 0)). Mark which flavours are "top six" in a helper column and let an IF choose between the two.

The reorder rule in one line, and three overrides

The rule: order enough whole tubs to cover the forecast until the next delivery, plus 10 percent, less what you hold; round the top sellers up and the rest to the nearest tub. Three overrides sit on top of it, and each earned its place from something the shop saw.

• One tub on display for every cabinet flavour, even when the forecast is 0.4 tubs. An empty pan reads as a flavour that has gone, and it's often the one a regular came for.
• The rain rule. If the forecast shows a high chance of rain or storms across the afternoon, plan that day one band lower. The shop picked a threshold of a 50 percent chance for 12 to 6 pm after the miss described below, and you should choose yours from your own log of wet days.
• Event days. A fair, a match or a festival gets its own line: use last year's log for that day or the organiser's expected attendance, and add the extra scoops by hand.

Forecasts drift, so re-run the sheet on the morning of each cut-off and change the order only where the new forecast moves a day by a whole band. The same forecast that decides your tubs should also decide when you talk to customers: weather-triggered posts for an ice cream shop uses the same band logic to time offers.

Sales are not demand: correcting for stockouts

When mango ran out at 3 pm on that hot Saturday, the log recorded 70 scoops. Real demand was higher. The hourly report shows that on hot Saturdays about half of the day's scoops are sold by 3 pm, so a fair estimate of the day's mango demand is 70 / 0.50 = 140 scoops, or 3.1 tubs, twice what the log says. Put the corrected 140 into the mix sheet and keep the real 70 in the sales column. At an average $3.50 a scoop, the roughly 70 scoops lost come to about $245 of sales, partly recovered when guests chose something else. Set that against $38 for a binned tub and the rounding rule makes sense.

If you never mark stockouts, the data trains the sheet to under-order exactly the flavours that sell out, because their sales are capped by what you held. It's the commonest way these forecasts go wrong, and the tutorial on why AI stock forecasts go wrong covers the other usual causes. For a general version of this approach, see a simple seasonal forecast from sales history.

Letting ChatGPT build and audit the sheet

The forecast itself is arithmetic you can see. ChatGPT earns its place on the slow parts: turning a till export with thousands of sale lines into one row per day, writing the formulas, and noticing days that don't fit. On a free plan, OpenAI's pricing page lists data analysis as limited; it's included on Go and above. Claude and Gemini can read an uploaded CSV in much the same way. Remove any loyalty or customer columns before you upload.

I have attached pos_export.csv (one row per sale line: date, time, item, quantity) and weather.csv (date, daily_high_c).
1. Count scoops per day per flavour. Treat every item whose name starts with "Scoop -" as a flavour. Ignore cones, toppings and drinks.
2. Join the daily highs. Assign bands: B1 under 18, B2 18 to 22.9, B3 23 to 27.9, B4 28 and over.
3. Label each day Mon-Thu or Fri-Sun.
4. Return (a) the average total scoops per day for each band and day type, with the number of days in each cell, and (b) each flavour's share of scoops by band.
5. List any day whose total is more than 30% above or below its cell average, with the date and the high.
Show the first five days of your daily table so I can check them against the export. Do not explain causes you cannot see in the data.

Illustrative output and what I'd correct. The model returns both tables and flags four days. Three faults show up when you check the first five days against the export. It treats "Scoop - Mango (kids)" as a separate flavour from "Scoop - Mango", which splits mango's share in two; merge the names and re-run. It counts a refund line (quantity -1) as a sale, so one day's total is off by two scoops. And the fourth day's total comes out as 331 against the owner's own count of 338, because the model dropped sale lines with a blank quantity. None of the errors is large, and all three would go unseen without the five-day check. Make that check a standing rule: if the first five days don't match, nothing else in the output is trustworthy.

The model's flagged days are worth reading even where it can't explain them. It listed a Friday with a 27 C high and only 340 scoops against a 540 average, and wrote "no cause visible in the data", which is the right answer given what it had. The shop's log note for that day said the cabinet's compressor failed at noon. A flag from the model plus a note from you turns an outlier into a lesson. Last, ask it to explain each formula in one plain sentence and to write a weekly update checklist. You then have the sheet's own documentation, which matters when a different team member runs the Thursday order.

Week 3: forecast against actual

After the sheet went live the shop compared each day's forecast scoops with the actual total (illustrative):

The week as a whole landed within 1.4 percent (2,899 scoops against 2,940 forecast), and the average daily miss was 8.3 percent. That's a fair result, and it hides the lesson. Saturday missed by 15 percent because a thunderstorm started at 2 pm, in the middle of a day the forecast called dry. Weekly totals can look fine while single days are wrong, and stock is held by the day. That miss produced the rain rule above. Keep this table every week: the misses show which override to add next.

Spoilage: log it, price it and let slow flavours run lean

Ice cream rarely "goes off" in the way milk does. It loses quality: ice crystals grow, the texture turns coarse, and the shop bins tubs that guests wouldn't enjoy. Dairy-science references name temperature swings, often called heat shock, as the main cause of that coarseness, so the habits that matter are steady cold storage, not leaving tubs out between servings, and rotating oldest first. Frozen storage is commonly specified at -18 C (0 F) or colder, but your supplier's label and the food-safety rules that apply to you set the real limits, including best-before dates. Check them rather than trusting a number in a tutorial.

For the sheet, add a spoilage log with four fields: flavour, tubs binned, reason and the cost. Allowed reasons are a short list: past best-before, heat shock or texture, cabinet or freezer fault, dropped or contaminated, and over-ordered. Illustrative result for the first eight weeks: of 21 binned tubs, 9 were over-ordered slow flavours (pistachio and two others), 5 were past best-before, 4 followed the compressor fault and 3 were heat-shocked after a tub was moved in and out of the cabinet during a busy hour. That split pointed at three different fixes. The slow flavours moved to the lean rounding rule. A best-before date went on the tub's lid at opening, and the oldest tub goes into the cabinet first. And the freezer fault got a service call, which no forecast could have prevented.

At $38 a tub, 21 binned tubs is $798 over eight weeks. That is real money, yet it's about what three or four stockouts like the mango one cost in lost sales before substitutions, and the shop had a top-eight flavour out on 31 days. That's the reason to leave the 10 percent margin on the top sellers and to make the slow flavours lean, rather than trimming everything evenly. The tutorial on cost per scoop and menu prices shows how to put the tub cost and your waste rate into the price of a scoop.

Eight weeks before, four weeks after

These are illustrative figures for the example shop, not results to expect:

Waste fell a little and stockouts fell more, which is what the rounding rules aim for. Time barely moved, because updating the log and checking the forecast takes about as long as guessing did; the gain is that the order can be explained and handed to someone else. Four weeks is also a short window, with one stormy Saturday in it, so the sensible reading is "the direction is right". Keep the log going into the next season, because the bands and mix will be better with a second summer behind them. The tutorial on forecasting bubble tea stock by weekday uses the same weekday method on tapioca and milk, which keep for hours or days rather than weeks, a useful contrast if your shop also sells drinks. For general spreadsheet help with the formulas here, Google Sheets AI prompts is a good companion.

Questions scoop-shop owners ask about forecasting tubs

How many weeks of sales history do I need?

Six to eight weeks of one season is enough to start, as long as each temperature band and day type has at least five logged days. Thin cells should be merged with a neighbour. Keep adding days every week, because the averages improve as the season goes on and as you log stockouts.

Do I need a paid ChatGPT plan for this?

No. The sheet is a few averages and shares you can build by hand. ChatGPT helps most with cleaning a big POS export and writing formulas, and OpenAI's pricing page lists data analysis as limited on Free and included on Go and above. Check the first five days against your own count either way.

What if I churn my own ice cream in batches?

Use the same sheet, then convert tubs to batches with your batch yield. Your lead time is the churn and hardening cycle, so forecast that many days ahead. Cap the stock you hold by the product's shelf life and your storage rules, and apply the same minimum-display and rounding rules.

How accurate should I expect it to be?

In the example, single days missed by 5 to 15 percent, 8 percent on average, while the week as a whole landed within 2 percent. Judge the method by fewer stockouts and fewer binned tubs over a month, not by perfect daily numbers, and treat those figures as one illustration rather than a benchmark.

Further reads

• AI Inventory Forecasting for Small Businesses: How It Works — How AI inventory forecasting works, in plain terms.
• How Small Shops Can Use AI to Forecast Stock and Reorder — The same reorder logic for a general small shop.
• How to Forecast Covers and Cut Food Waste With AI — Cover forecasts and food waste in a restaurant kitchen.
• How Bakeries Can Use AI to Predict Demand and Cut Unsold Stock — The same demand method for bakes that must sell on the day.
• 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 for Golf Course Management: Can It Plan Staffing Around Weather? — Export past rounds, add the weather, and let ChatGPT or Gemini turn each forecast into a weekly staffing sheet for the pro shop, starter, bar and range.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: ChatGPT pricing page (checked October 2026) and dairy-science references on heat shock and frozen storage. All shop figures are illustrative.