exel 3-2 rop
gysgtclarke
3-ROP.xlsxROP example
| Reorder Point with Safety Stock | |||||||||
| But what if demand isn't constant? | |||||||||
| But what if delivery isn't reliable?? | |||||||||
| Daily demand (average) | DD = | units/day | Historical Data | ||||||
| Standard deviation of Demand = | units/day | Daily Demand (units) | Length of each replenishment cycle (days) | ||||||
| 80 | 12 | ||||||||
| Length of replenishment cycle (average) = | RC = | days | 85 | 12 | |||||
| Standard deviation of Replenishment Cycle = | days | 75 | 11 | ||||||
| 70 | 13 | ||||||||
| Service level target = | 95% | 78 | 12 | ||||||
| z = | 1.644853627 | 83 | 12 | When demand is uncertain (always) and replenishment cycle is uncertain (always). | |||||
| 88 | 13 | ||||||||
| Reorder Point without Variability = | units | 93 | 12 | ||||||
| + | 95 | 13 | |||||||
| Safety Stock (to buffer from variability) = | SS = | units | 94 | ||||||
| = | 90 | ||||||||
| Reorder Point (in the real world!) = | ROP = | units | 83 | ||||||
| (round to the nearest unit) | 90 | ||||||||
| 85 | |||||||||
| 95 | |||||||||
| 80 | |||||||||
| Extra: How much more would it cost to increase Safety Stock to reach a 97% Service Level? To reach 99% Service Level? |
𝑆𝑆=𝑧
ඥ
𝜎
2
𝐷𝐷
∗𝑅𝐶+ 𝜎
2
𝑅𝐶
∗𝐷𝐷
2
𝜎
𝐷𝐷
=
𝜎
𝑅𝐶
=
