Guided project · 6 steps Cellwise Plus
Weekly order sheet with par levels in Excel
Set par levels from daily usage, then turn a stock count into a supplier order in cases, with the cost.
You will use: ROUNDUP, MAX, SUM, COUNTIF
Starting data
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Daily use | Par | On hand | Need | Case size | Cases | Case cost | Order cost | Days until next delivery | 3 | |
| 2 | Mozzarella (lb) | 18 | 31 | 20 | 64 | Safety buffer | 0.2 | |||||
| 3 | San Marzano tomatoes (can) | 6 | 22 | 6 | 27.5 | |||||||
| 4 | Pizza flour (lb) | 45 | 160 | 50 | 31 | Order total | ||||||
| 5 | Chicken breast (lb) | 14 | 20 | 40 | 118 | Items to order | ||||||
| 6 | Romaine (head) | 22 | 48 | 24 | 29 | |||||||
| 7 | Salmon fillet (lb) | 9 | 11 | 10 | 152 |
Build it step by step
Step 1
Par is what you need on hand to last until the next delivery, plus a buffer. Daily use times the days in L1, times (1 + the buffer in L2), rounded up. Write it in C2 and fill to C7.
Step 2
How many units do you need to get back to par? Never less than 0. Write it in E2, fill to E7.
Step 3
Suppliers sell in cases. How many cases cover what you need? Round up, in G2, fill to G7.
Step 4
Cost of each line: cases times case cost. Write it in I2, fill to I7.
Step 5
Total the order in L4.
Step 6
How many items need ordering at all? Count the lines with at least one case, in L5.