Project 1 · Inventory control
Inventory Optimizer
A UK gift wholesaler's 60 busiest items, one year of real weekly demand. Before: reorder 4 weeks of stock whenever the shelf is empty. After: order the EOQ when stock on hand plus on order falls to the reorder point.
Carrying cost / yr
Ordering $30.8k → $19.7k. Lost margin $135.4k → $7k.
Stockout weeks
SKU-weeks with demand left unfilled, all 60 SKUs.
Fill rate after
Share of units demanded that shipped. Was 79.8%.
Fixed inputs: order cost $50, holding cost 25% of unit cost per year, z = 1.65. Unit cost is 60% of median selling price. Prices are the dataset's pounds read as dollars.
On hand by week: 85123A
white hanging heart t-light holder · EOQ 2,973 · safety stock 1,823 · carrying $1,050 → $1,766 · stockout weeks 8 → 0
All 60 SKUs
Tap a row to chart it. Tap a header to sort.
| 22197 | popcorn holder | 49,434 | $0.51 | 6,227 | 4,239 | 2,357 | 11 → 0 | $385 → $753 |
| 85099B | jumbo bag red retrospot | 43,941 | $1.25 | 3,750 | 2,681 | 1,155 | 12 → 0 | $793 → $1,000 |
| 85123A | white hanging heart t-light holder | 39,101 | $1.77 | 2,973 | 3,282 | 1,823 | 8 → 0 | $1,050 → $1,766 |
| 84879 | assorted colour bird ornament | 32,771 | $1.01 | 3,603 | 2,531 | 1,311 | 11 → 0 | $444 → $863 |
| 21212 | pack of 72 retrospot cake cases | 24,526 | $0.33 | 5,453 | 1,577 | 778 | 8 → 0 | $129 → $324 |
| 22178 | victorian glass hanging t-light | 23,313 | $0.75 | 3,527 | 1,505 | 664 | 10 → 1 | $249 → $494 |
| 21977 | pack of 60 pink paisley cake cases | 20,072 | $0.33 | 4,933 | 1,688 | 1,061 | 8 → 0 | $97 → $321 |
| 22386 | jumbo bag pink polkadot | 19,447 | $1.25 | 2,495 | 1,276 | 598 | 10 → 0 | $357 → $599 |
| 84946 | antique silver t-light glass | 18,831 | $0.75 | 3,170 | 1,333 | 642 | 10 → 1 | $212 → $438 |
| 22469 | heart of wicker small | 17,899 | $0.99 | 2,690 | 1,469 | 869 | 11 → 2 | $255 → $501 |
| 23203 | jumbo bag vintage doily | 17,557 | $1.25 | 2,371 | 1,512 | 894 | 9 → 1 | $329 → $596 |
| 22086 | paper chain kit 50's christmas | 17,133 | $1.77 | 1,968 | 1,967 | 1,310 | 8 → 2 | $805 → $1,365 |
What I'd tell the ops manager
Read the carrying cost tile first, because it goes the wrong way. Stock on hand costs $15,117 a year under the old habit and $30,881 under the reorder point policy. It is what the math does on this data. An order costs $50 to place and these items cost about a dollar each, so the economic order quantity lands near 9.5 weeks of demand. Safety stock at a 95% service level and a two week lead time adds about two more weeks. The old habit looks cheap only because the shelf sits empty about a fifth of the year. Average stock on hand goes from roughly three weeks to roughly seven. The cost that falls is ordering. 616 orders become 393, so $30,800 becomes $19,650. Put the two together and holding plus ordering rises about $4,600 a year across all 60 SKUs. What that $4,600 buys is $128,408 of margin that used to walk out the door. Unfilled demand drops from 605 SKU-weeks to 32. Fill rate goes from 79.8% to 99.1%. On one year of real demand for a gift wholesaler's 60 busiest items, that is the trade: tie up more cash in stock, stop turning customers away. I would take that trade, with two caveats. First, the margin figure assumes unit cost is 60% of selling price, because the dataset has prices and not costs. If your margin is thinner, the payback shrinks with it. Second, the $50 order cost drives the lot size. If placing an order really costs you $15, the EOQ comes down and so does the carrying cost. The sliders are there for that reason. These numbers are the 95% service level and two week lead time defaults. Move them to your own case and watch which way the tiles go.
Letter PDF with the current settings.