Guided project · 6 steps Cellwise Plus
Shop parts reorder list in Excel
Turn 30 days of parts usage into reorder points and an order list with quantities and cost.
You will use: ROUNDUP, IF, SUM
Starting data
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Part | Used (30 days) | On hand | Lead time (days) | Daily use | Reorder point | Order? | Order qty | Unit cost | Order cost | Safety days | |
| 2 | Oil filter, common fit | 84 | 22 | 2 | 4.6 | Days of stock to order | ||||||
| 3 | Synthetic 5W-30 (qt) | 410 | 60 | 2 | 5.25 | |||||||
| 4 | Front ceramic pad set | 18 | 4 | 3 | 52.1 | Order total | ||||||
| 5 | Cabin air filter | 26 | 6 | 4 | 9.8 | |||||||
| 6 | Wiper blade, 22 in | 15 | 9 | 5 | 7.4 | |||||||
| 7 | Brake cleaner (can) | 60 | 30 | 3 | 3.15 |
Build it step by step
Step 1
Start with how fast each part goes: units used in the last 30 days divided by 30. Write it in E2 and fill to E7.
Step 2
The reorder point is enough stock to last through the supplier's lead time plus the safety days in M1, rounded up to whole units. Write it in F2 and fill to F7.
Step 3
Flag what needs ordering: "Order" when on hand is at or below the reorder point, otherwise "OK". Write it in G2 and fill to G7.
Step 4
How many to order? For flagged lines, enough to get back above the reorder point plus the days of stock in M2, rounded up; 0 for the rest. Write it in H2 and fill to H7.
Step 5
Cost of each line: order quantity times unit cost. Write it in J2 and fill to J7.
Step 6
Total the order in M4.