📌 Stage 6 · Count, Shrink, and Reorder · Inventory & COGS · Reorder points · 35-45 min

Turn September's sales rate and a 14-day cure into a make-now number

Learning goals

  • Compute average daily sales inside the Settings period
  • Build a reorder point from lead-time demand plus safety stock
  • Read the status flag and the suggested make quantity as a pouring plan

Concepts

The reorder point formula

A reorder point answers one question: how low can stock fall before starting another batch still leaves shelves full? It has two parts. Lead-time demand is what you will sell while a new batch is being made: for candles that is pour, cure (14 days for wax to harden and scent to bind), and pack. Safety stock covers demand surprises, sized as days of demand from Settings.

Reorder point = lead-time demand + safety stock. Cross below it and the next batch must start now.

=SUMIFS(Sales!$G$5:$G$1004,Sales!$E$5:$E$1004,$B5,Sales!$C$5:$C$1004,">="&Settings!$C$5,Sales!$C$5:$C$1004,"<"&(Settings!$C$6+1))/(Settings!$C$6-Settings!$C$5+1)

Average daily sales: units of this SKU sold inside the period, divided by the number of days in the period (end minus start plus one, computed from Settings, so it is 30 for September). JG-01 runs 82 divided by 30 = 2.73 units per day.

=ROUNDUP($F5+$G5,0)

Reorder point, rounded up because a fraction of a candle is not a plan. JG-01: 19.1 safety + 38.3 lead-time demand = 57.4, rounded to 58 jars.

=IF($I5<=$H5,"Reorder now","OK")

Status: when on hand has fallen to or below the reorder point, flag it. Equal counts as reorder: at exactly the point you are one surprise away from a stockout.

From flag to plan

A flag without a quantity is anxiety. The suggested make column proposes enough for two lead-times of demand plus safety stock, minus what is already on hand: cover the cure window, cover the next one, keep the buffer, net of the shelf.

For JG-01 that is ROUNDUP(2 times 38.27 + 19.13 - 47) = 49 jars. JG-03 suggests 10 and JG-04 suggests 11. Holiday demand will run hotter than September's rate, so treat these as floors and scale up for the November push.

💡 Tips:

  • The lead time is typed per SKU, not looked up: it is Juniper's own pour-and-cure calendar (14 days for jars, 16 for assembled sets), not a supplier promise. Materials have their own lead times on the Materials sheet for buying wax and jars.

Practice

Fixed case (Sep 2026). The exercise opens on Reorder. Pale-green cells are formulas; the lead time column is typed.

  1. Check the sales rate — Column E shows average daily sales from the period: 2.73 for JG-01 down to 0.40 for JG-05. The Oct 2 order is excluded by the date filter.
  2. Type the reorder point — Cell H5 is blank. Type =ROUNDUP($F5+$G5,0) and press Enter. JG-01 lands on 58. Fill down through H9: 58, 19, 17, 14, 10.
  3. Read the flags — Column J flags JG-01 (47 on hand vs 58), JG-03 (17 vs 17), and JG-04 (13 vs 14) as Reorder now. JG-02 and JG-05 are OK.
  4. Read the plan — Column K suggests 49, 10, and 11 units for the flagged SKUs. That is this weekend's pour list, ready before the holiday rush.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Typed =ROUNDUP($F5+$G5,0) into Reorder!H5 and got 58 for JG-01
  • ☐ Confirmed three SKUs flag Reorder now (JG-01, JG-03, JG-04)
  • ☐ Read the suggested make quantities of 49, 10, and 11 units
  • ☐ Verified the baseline: reorder points 58 / 19 / 17 / 14 / 10, three SKUs below