Florist Inventory Management: Use AI to Track Stems and Waste

Florist inventory management comes down to four working tabs: a stem list with pack sizes and cost per stem, a recipe table that turns bouquets sold into stems used, a waste log with reason codes, and a weekly sheet that compares stems received, used, wasted and counted. AI writes the formulas and spots patterns; you count the buckets.

Till software sells bouquets, not stems, and that gap is where flowers disappear without anyone noticing. The recipe tab closes it. A plain workbook in Excel or Google Sheets is enough, and the tabs you build become your list of questions if you later look at florist software. The figures are invented for a one-shop florist. The eight-week build-up to Valentine's Day and Mother's Day is a separate job, covered in preparing for Valentine's and Mother's Day with AI.

Follow me on Instagram@sagnikteaches

Four working tabs and two for storage

Budget a few minutes a day for the waste log and 30 to 45 minutes on Monday once the sheet is set up; the first Monday takes longer. Check that your till can export sales by product for a chosen week. If it cannot, a tally sheet kept by the till gives you the same numbers for the first month.

Connect on LinkedInSagnik Bhattacharya

Stems: pack size and cost per stem, written once

Everything downstream rests on two numbers per variety: stems per bunch and cost per bunch. Cost per stem is one division, and every other tab looks it up.

Subscribe on YouTube@sagnikteaches

The buffer is the safety margin you add when ordering. Use 10% for lines that appear in many recipes and hurt to run out of, such as the main rose, and 5% for fragile lines you have been wasting. The asymmetry is the point: a wasted stem costs its purchase price, while running out of the lead rose can cost the margin on a whole bouquet. Your supplier's pack sizes will differ, so use your own invoices.

Stems tab, row 2 (fill down)
D2  Cost per stem   =C2/B2

Variety names must match exactly across every tab. "Rose, red" and "red rose" are different things to a formula, so give the variety column on the Recipes, Waste and Week tabs a dropdown fed from this list (data validation in Excel, a dropdown from a range in Google Sheets). If your names are already a mess, the tutorial on cleaning messy data shows how to standardise them first.

Recipes: turning bouquets sold into stems used

The till records "1 medium hand-tied". The recipe says what that means in stems. For readability the table below is a grid; in the workbook each product and variety pair is its own row, so a formula can total it.

The bottom row is units sold multiplied by stems per product, added up by column. Red roses, for example: 9 dozens at 12, 4 large hand-ties at 6 and 20 single stems is 108 plus 24 plus 20, or 152. In the workbook two formulas do it all:

Recipes tab, row 2 (fill down)
D2  Units sold   =SUMIFS(Sales!$B:$B, Sales!$A:$A, $A2)
E2  Stems used   =C2*D2

You can have the AI draft the first recipe table from your price list, then correct it. The tutorial on florist pricing formulas for bouquets and arrangements uses the same recipes to set prices, so the work pays twice.

Here are my florist products with short descriptions, and my stem list.
Propose a recipe table with the columns Product | Variety | Stems per product.
Use ONLY varieties from my stem list. Give each stem count as one number.
Where my description does not let you tell, write [ASK ME] instead of guessing.

Stem list: [paste Stems tab]
Products: [paste names and descriptions]

Treat what comes back as a guess. A typical first draft puts 5 blush roses in the small hand-tied because "small" sounds like five stems; your actual bouquet has three. The only true recipe comes from building one of each product while counting stems, so do that once and correct the table. Do the same trim test for leaf-stripping: strip one bunch, count how many stems are still usable, and log any that snap or come up short as waste with the reason "Handling" rather than padding the recipe to hide them.

The Monday reconciliation: received, used, wasted, counted

On Monday, paste the till's weekly sales into the Sales tab and put the week's start date in cell B1 of the Week tab. Add the bunches received from the delivery notes and the stems you count in the buckets (sellable stems only). The opening count is simply last week's counted closing. The formulas do the rest.

Week tab, row 4 (fill down; B1 holds the week start date)
D4  Stems received    =C4*INDEX(Stems!$B:$B, MATCH($A4, Stems!$A:$A, 0))
E4  Stems used        =SUMIFS(Recipes!$E:$E, Recipes!$B:$B, $A4)
F4  Stems wasted      =SUMIFS(Waste!$C:$C, Waste!$B:$B, $A4, Waste!$A:$A, ">="&$B$1, Waste!$A:$A, "<"&$B$1+7)
G4  Expected closing  =B4+D4-E4-F4
I4  Variance          =H4-G4
J4  Check             =IF(ABS(I4)>MAX(5, 0.05*D4), "Check", "")
K4  Waste % received  =IF(D4=0, 0, F4/D4)
L4  Waste cost        =F4*INDEX(Stems!$D:$D, MATCH($A4, Stems!$A:$A, 0))
M4  Cover             =IF(E4=0, "", D4/E4)

If your spreadsheet expects semicolons between arguments, swap them in. Here is what a real Monday might look like, with invented numbers.

Read it from the right. Cover is stems received divided by stems used, and 1.54 for lisianthus means about three stems bought for every two that went into bouquets; the extra third ends up in the bin or waiting in the bucket. The waste column shows 67 stems, 11.4% of what arrived, and at cost that is $60.70 against $497.00 of purchases, or 12.2%. A negative variance means stems left the system without being logged, and the check column flags any line where it is more than 5 stems or 5% of what you received, whichever is larger.

Two lines are flagged. Lisianthus is short by 6 on top of a 25% logged waste rate. That points to unlogged breakages: fragile heads snap in the bucket and nobody writes it down. Recount, then ask everyone to log every binned stem for a week. Eucalyptus is short by 13 with only 6 logged as waste, which usually means the recipe undercounts: extra sprigs go in to fill gaps and off-cuts are not recorded. Build a medium hand-tied from a counted pile of eucalyptus. If it takes 4, not 3, edit the recipe, and the variance closes. Changing the medium recipe from 3 to 4 adds 8 stems used, expected closing drops to 22 and the variance becomes -5, inside the tolerance.

When the week is checked, paste the Week rows into History as values, so next week's averages have something to draw on.

The waste log: six reason codes that each point to a decision

A log that only says "binned" teaches nothing. Six reason codes turn each entry into a decision. Pick the code from a dropdown when you bin the stems, and add a note only when the code does not explain it. Photograph anything damaged on arrival before it goes in the bin.

Totalled for the invented week, the 67 wasted stems split as follows: past best 29, over-ordered 12, damaged on arrival 9, handling 8, display or heat 6 and cancelled order 3. A SUMIFS on the reason column gives each figure, in the same style as the Week tab:

=SUMIFS(Waste!$C:$C, Waste!$D:$D, "Past best", Waste!$A:$A, ">="&$B$1, Waste!$A:$A, "<"&$B$1+7)

Past best is 43% of the stems, mostly lisianthus and blush rose, so that is the line to act on, and it is why those two varieties carry a 5% buffer. If your team types notes instead of choosing codes, the AI can code them in bulk. In Google Sheets, the AI function, written as =AI("prompt", range), does it inside the sheet. Google's help page, Use the AI function in Google Sheets, says it generates the first 350 selected cells at a time, cannot be nested inside other functions, returns text only and needs an eligible Google Workspace or Google AI plan. In Excel, copy the notes into the Copilot pane or any chat assistant and paste the codes back. Excel's =COPILOT() worksheet function was retired on 14 September 2026: results already calculated stay as cached values, but a cell that recalculates shows #NAME?, so older guides built on it no longer work.

Code each florist waste note with exactly one of these codes: Past best,
Over-ordered, Damaged on arrival, Handling, Display or heat, Cancelled order.
Reply with the code only. If a note fits none of them, reply CHECK.

The sixth row is the miss to look for. The AI coded the symptom (soft stems) rather than the cause (a warm box), and a wrong code sends the decision the wrong way: you would cut an order you should have taken up with the supplier. Read every coded line for the first month, then spot-check ten a week. Paste the final codes back as values so they cannot shift later.

Next week's order, set from this week's sheet

The rule is the same for every variety: expected use for the coming week, plus the buffer, minus what is already in the buckets, rounded up to whole bunches. Use the average of the last four weeks of "used" from History as expected use, and wait until you have four weeks before trusting it. History holds one row per week and variety: week start, variety, received, used, wasted, counted closing.

Week tab, row 4 (this week's rows must already be pasted into History)
N4  Four-week average used  =AVERAGEIFS(History!$D:$D, History!$B:$B, $A4, History!$A:$A, ">="&$B$1-21, History!$A:$A, "<="&$B$1)
O4  Stems to order          =MAX(0, N4*(1+INDEX(Stems!$E:$E, MATCH($A4, Stems!$A:$A, 0)))-H4)
P4  Bunches to order        =ROUNDUP(O4/INDEX(Stems!$B:$B, MATCH($A4, Stems!$A:$A, 0)), 0)

The sheet proposes 51 bunches against this week's 59, which at the invented prices is $68.50 less, a 14% cut. Treat it as a proposal, not an order, and make four checks before you send it. Add confirmed pre-orders and events the four-week average does not know about, converting them to stems with the recipes. Look at the Counted column: stems with only a day or two of vase life left are next week's waste, not stock, so take them out before the sheet subtracts them. Check your supplier's cut-off times and minimum order. And apply what you know about the week, such as a holiday weekend or a big wedding. Where the cut is steepest, blush rose and lisianthus, take half of it this week and the rest next week, since you are correcting a pattern rather than reacting to a single week.

One line needs a second look before you trust any of it. The proposed eucalyptus cut, from 10 bunches to 8, comes from the same short recipe that caused Monday's variance. History stores values, so the undercount stays in the four-week average until you correct those rows too. If the test build confirmed 4 sprigs in a medium hand-tied, add 8 stems to each of the four weeks: the average becomes 92, stems to order 84.2 and the order 9 bunches. An order built on a wrong recipe cuts the very line you use more of than the sheet knows, so fix recipes first.

The tutorial on setting reorder points to prevent stockouts covers the other side of the trade-off: running out.

A peak-day forecast from your own history

Not every busy day needs a plan from scratch. For one day that is much bigger than usual, such as the Friday before a long weekend or the day of a large wedding, the same sheet gives a forecast in four steps. The build-up to the big flower holidays (pre-order menus, cut-offs, delivery routes) sits in the separate plan linked earlier; this is only the stem arithmetic for a single day.

1. Export the till's sales for the same weekday over the last eight normal weeks, run them through the recipe tab and average the stems used. Count walk-in and same-day sales only; leave out orders taken in advance if your till flags them.
2. Multiply by a walk-in uplift taken from last year: that day's walk-in use divided by a normal day's. Here it is 1.4.
3. Add the stems for pre-orders already confirmed, converted with the recipes: 6 dozen red roses, 4 medium and 2 large hand-ties.
4. Subtract the stock you expect on hand when the delivery lands, add the buffer and round up to bunches.

Take red roses. The forecast is 128, plus the 10% buffer is 140.8, minus 17 expected in stock is 123.8, and that divided by 10 stems a bunch is 12.4, so order 13 bunches. Across the six lines, one Friday needs about 62% of a normal week's 496 stems, which is why it deserves its own order. Then ask the AI to attack the method, not rewrite it:

Here is my forecast for one busy Friday: [paste table]. Method: average of
the last eight normal Fridays, times a walk-in uplift of 1.4 taken from last
year, plus confirmed pre-orders converted with recipes. List every
assumption in this method, the three most likely ways it could be wrong, and
what I could check this week to test each. Do not change my numbers.

A good answer names the weak points: the uplift rests on a single year of data, late pre-orders may still arrive, a cancelled pre-order leaves you with stems you cannot sell, and weather can change walk-in trade. Each one maps to a check: compare how late pre-orders arrived last year, ask your supplier for the cut-off for a top-up delivery, and re-run the sheet on Wednesday with the pre-orders you then have.

Letting the AI write the formulas, then proving them

The AI has two jobs in this workbook: writing formulas, and reading a month of results. For the formulas, describe your layout exactly, with sheet names, column letters and the header row, and say which functions you want. The more exact the description, the fewer guesses it makes.

I run a one-shop florist. My workbook has these tabs and columns:
Stems: A Variety, B Stems per bunch, C Cost per bunch, D Cost per stem, E Buffer
Recipes: A Product, B Variety, C Stems per product, D Units sold, E Stems used
Sales: A Product, B Units sold (pasted from my till each week)
Waste: A Date, B Variety, C Stems, D Reason, E Note, F Cost
Week: B1 is the week start date. Headers are in row 3 and each variety has
one row from row 4: A Variety, B Opening, C Bunches received, D Stems
received, E Stems used, F Stems wasted, G Expected closing, H Counted
closing, I Variance, J Check, K Waste % of received, L Waste cost.
Write one formula for each of D, E, F, G, I, J, K and L in row 4 that I can
fill down. Use only SUMIFS, INDEX, MATCH, IF, ABS and MAX. Present them in a
table with a one-line explanation each.

Do not trust the answer until you have proved it. Pick one variety, work out opening plus received minus used minus wasted with a calculator, and compare it with the sheet. Then change one input, such as adding 10 stems to the waste log, and check that the right cells move by the right amount. Typical slips are dates stored as text so the weekly SUMIFS returns zero, a variety with a trailing space that breaks a lookup, and ranges that do not stretch when you add rows. The tutorial on Google Sheets AI prompts has more ready-made prompts for sheets like this one, and in Excel the Copilot pane takes the same prompt if your plan includes it.

Once a month, hand the AI the summary rather than the raw files. Paste the History rows and the waste-by-reason totals, which contain no customer details, and ask for the analysis:

Here are my florist stock results for four weeks, one row per variety per
week: [paste History]. Waste by reason: [paste]. Find the three varieties
with the highest waste cost and the three with the largest unexplained
variance. For each, show the arithmetic, say what it points to (ordering,
recipe, handling or supplier) and suggest one change to try next week.
Use only the figures in my tables and do not invent any.

Check two of its sums against the sheet before acting on any recommendation, and keep the change small: one variety, one week, then look again.

When a spreadsheet stops being enough, and what to ask a till vendor

Search for florist inventory help and many of the results sell software. Software is worth having when the sheet starts to break, and not before. The signs are specific:

• Several people enter counts and the sheet gets overwritten or the dropdowns drift.
• Online orders need stock to update on their own.
• You run more than one shop.
• Monday takes you more than an hour even after the sheet is tidy.
• You want supplier orders sent from the system, not typed into an email.

If none of those apply, software would mostly move the same data into a different box. If some do, take these questions to every vendor:

1. Can a product be defined as a recipe of stems, so that one sale deducts the right stems?
2. Does it handle buying in bunches and counting in stems?
3. Can I log waste with a reason code and see its cost?
4. Can I export every table (products, recipes, waste, sales) to a spreadsheet at any time, and import my current sheet on day one?
5. What does it cost per month at list price with the features I need, and what happens to my data if I cancel?
6. Can I run it alongside my sheet for a month before I commit?

Tools do disappear. Clockwise, a calendar assistant, shut down on 27 March 2026 and deleted user data rather than transferring it. Keep the sheet running in parallel through any trial and export monthly. If you do switch, the reason codes, recipes and buffers you have built are your specification: they are what the software has to hold. The tutorial on pricing wedding quotes by stem, labour and hire reuses the same stem costs, so keeping them accurate here helps there too.

Florist stock questions the sheet raises

How often should a florist count stems?

Count the lines that matter once a week at the same time, for example at Sunday close, and count fast movers such as roses twice a week if you can. A full count every day is rarely worth the time. Consistency beats frequency: always count the same buckets, and count only stems that are still sellable.

How do I record a substitution when a variety runs out?

Log the sale as the recipe sold, then add a line on a small substitution tab: product, variety replaced, variety used and stems. That keeps usage figures honest and shows which lines you run out of most often. If the same variety is substituted more than once a week, raise its buffer in the stem list.

What waste rate should I aim for?

There is no universal target worth quoting, because it depends on your mix of walk-in and pre-order sales and on how perishable your lines are. Use your own trend: log four weeks, then aim for a gradual cut in the lines costing most. Judge progress by waste cost against purchases, not by a figure from someone else's shop.

Further reads

• How Florists Can Use AI for Orders, Reviews and Social Posts — Handle orders, reviews and social posts alongside your stock sheet.
• AI Inventory Forecasting for Small Businesses: How It Works — Learn how AI forecasting works before paying for it.
• How Small Shops Can Use AI to Forecast Stock and Reorder — See the same forecast-and-reorder idea in a general shop.
• How Bakeries Can Use AI to Predict Demand and Cut Unsold Stock — Compare how another perishable trade predicts demand and unsold stock.
• How to Forecast Covers and Cut Food Waste With AI — See a waste-cutting forecast for a restaurant, built on the same logic.
• Why AI Stock Forecasts Go Wrong: 8 Mistakes Small Businesses Make — Check your order sheet against common AI forecasting mistakes.
• Garden Centre Marketing Ideas: Plant of the Month Posts With AI — A twelve-month plant calendar, plant cards, post sets and workshop promotions for a garden centre, with AI drafting and your growers checking care claims.
• 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: Google Docs Editors Help, 'Use the AI function in Google Sheets'; Microsoft Support, 'COPILOT function' (retirement notice). All stem counts, prices and sales figures are invented for illustration.