as below
helpmeout11
20171026014218economic_order_quantity_and_safety_stock_microwave_spreadsheet.xlsxCurrent
| Current Practice | ||||||||||
| Microwave Model | A | B | C | D | E | F | Total | |||
| $ Value | 500.00 | 500.00 | 500.00 | 500.00 | 500.00 | 500.00 | ||||
| Holding Cost Factor | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | ||||
| Fixed Cost Per Order | 1000.00 | 1000.00 | 1000.00 | 1000.00 | 1000.00 | 1000.00 | ||||
| Daily Demand: | ||||||||||
| Average | 12 | 22 | 88 | 35 | 97 | 44 | ||||
| Std. Dev. | 17 | 18 | 67 | 19 | 22 | 22 | ||||
| Order Quantity p038981: p038981: Current practice is a 30 day supply = 30 * average daily demand | 360 | 660 | 2640 | 1050 | 2910 | 1320 | ||||
| Ship Time (days): | ||||||||||
| Average | 7 | 7 | 7 | 7 | 7 | 7 | ||||
| Std. Dev. | 2 | 2 | 2 | 2 | 2 | 2 | ||||
| Production Time (days): | ||||||||||
| Average | 30 | 30 | 30 | 30 | 30 | 30 | ||||
| Std. Dev. | 4 | 4 | 4 | 4 | 4 | 4 | ||||
| Lead Time (days): | ||||||||||
| Average p038981: p038981: =Average ship time + average production time | 37.00 | |||||||||
| Std. Dev. p038981: p038981: sqrt(sum of variances of ship time^2 + variance of production time^2) | 4.47 | |||||||||
| Demand during Lead Time: | ||||||||||
| Average p038981: p038981: Average daily demand * average lead time | 444 | |||||||||
| Std. Dev. p038981: SQRT((Average lead time * Standard deviation of daily demand^2)+(Average daily demand^2 * Standard deviation of Lead time^2)) | 117 | |||||||||
| Reorder Point p038981: p038981: Current practice is 51*average daily demand | 612 | |||||||||
| Safety Stock Level Bret Kauffman: = Reorder point - average demand during lead time | 168 | |||||||||
| Inventory Performance: | ||||||||||
| Service Level p038981: We know that safety stock = z * standard deviation of demand during lead time therefore the z value = safety stock / standard deviation of demand during lead time once we know the z value, NORMSINV will give us the current level of customer service that IMI is providing for this model. This Service Level = NORMSDIST(safety stock / standard deviation of demand during lead time) | 0.9254 | |||||||||
| Calculation of Total Cost | ||||||||||
| Purchase Cost Bret Kauffman: Value of the microwave * number of microwaves sold (=average daily demand * 365) | 2,190,000 | |||||||||
| Annual Holding Cost: | ||||||||||
| Cycle Stock p038981: p038981: Holding cost factor * Price/unit * Average inventory remember average inventory = order quantity / 2 | 22,500 | |||||||||
| Safety Stock p038981: p038981: Holding cost factor * value/unit * safety stock units |
p038981: p038981: Current practice is a 30 day supply = 30 * average daily demand | 21,000 | ||||||||
| Anuual Fixed Order Cost p038981: p038981: Order cost = Fixed cost per order * (Annual demand / Order quantity) assume 365 days / year | 12,167 | |||||||||
| Annual Total Cost p038981: p038981: Total cost = Purchase cost + ordering cost + holding cost |
p038981: p038981: =Average ship time + average production time |
p038981: p038981: sqrt(sum of variances of ship time^2 + variance of production time^2) |
p038981: p038981: Average daily demand * average lead time |
p038981: SQRT((Average lead time * Standard deviation of daily demand^2)+(Average daily demand^2 * Standard deviation of Lead time^2)) |
p038981: p038981: Current practice is 51*average daily demand |
Bret Kauffman: = Reorder point - average demand during lead time | 2,245,667 | |||
Analysis
| Economic Order Quantity and Safety Stock | Single Model | ||||||||||||||
| Microwave Model | A | B | C | D | E | F | Total | X | |||||||
| $ Value | 500.00 | 500.00 | 500.00 | 500.00 | 500.00 | 500.00 | 500.00 | ||||||||
| Holding Cost Factor | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | ||||||||
| Fixed Cost Per Order | 1000.00 | 1000.00 | 1000.00 | 1000.00 | 1000.00 | 1000.00 | 1000.00 | ||||||||
| Daily Demand: | |||||||||||||||
| Average | 12 | 22 | 88 | 35 | 97 | 44 | |||||||||
| Std. Dev. | 17 | 18 | 67 | 19 | 22 | 22 | |||||||||
| Order Quantity p038981: p038981: EOQ = SQRT(2*Annual demand * Fixed cost per order/ Holding cost per unit of inventory) remember Holding cost = holding cost factor * $value | 265 | ||||||||||||||
| Ship Time (days): | |||||||||||||||
| Average | 7 | 7 | 7 | 7 | 7 | 7 | 8 p038981: p038981: one more day for site customization |
||||||||
| Std. Dev. | 2 | 2 | 2 | 2 | 2 | 2 | 2 | ||||||||
| Production Time (days): | |||||||||||||||
| Average | 30 | 30 | 30 | 30 | 30 | 30 | 30 | ||||||||
| Std. Dev. | 4 | 4 | 4 | 4 | 4 | 4 | 4 | ||||||||
| Lead Time (days): | |||||||||||||||
| Average | 37.00 | ||||||||||||||
| Std. Dev. | 4.47 | ||||||||||||||
| Demand during Lead Time: | |||||||||||||||
| Average | 444 | ||||||||||||||
| Std. Dev. | 117 | ||||||||||||||
| Desired Service Level | 0.9800 | 0.9800 | 0.9800 | 0.9800 | 0.9800 | 0.9800 | 0.9800 | ||||||||
| Reorder Point p038981: p038981: Reorder point = average lead time demand + safety stock Remember safety stock = z * std dev of lead time demand And z = NORMSINV(desired service level) | 683 | ||||||||||||||
|
Bret Kauffman: Use standard Reorder point calculation as you would for any of the other models | Safety Stock Level Bret Kauffman: = Reorder point - average demand during lead time | 239 | |||||||||||||
| Inventory Performance: | |||||||||||||||
| Service Level | 0.9800 | ||||||||||||||
| Calculation of Total Cost | |||||||||||||||
| Purchase Cost Bret Kauffman: Value of the microwave * number of microwaves sold (=average daily demand * 365) | 2,190,000 | ||||||||||||||
| Annual Holding Cost: | |||||||||||||||
| Cycle Stock p038981: p038981: Holding cost factor * Price/unit * Average inventory remember average inventory = order quantity / 2 | 16,545 | ||||||||||||||
| Safety Stock p038981: p038981: Holding cost factor * value/unit * safety stock units |
p038981: p038981: EOQ = SQRT(2*Annual demand * Fixed cost per order/ Holding cost per unit of inventory) remember Holding cost = holding cost factor * $value | 29,909 | |||||||||||||
| Anuual Fixed Order Cost p038981: p038981: Order cost = Fixed cost per order * (Annual demand / Order quantity) assume 365 days / year | 16,545 | ||||||||||||||
| Annual Total Cost p038981: p038981: Total cost = Purchase cost + ordering cost + holding cost |
p038981: p038981: Sum of all models |
p038981: p038981: SQRT(sum of squares of all model standard deviations) |
Bret Kauffman: Use regular EOQ calculation as you would for any of the models |
p038981: p038981: one more day for site customization |
p038981: p038981: Reorder point = average lead time demand + safety stock Remember safety stock = z * std dev of lead time demand And z = NORMSINV(desired service level) |
Bret Kauffman: = Reorder point - average demand during lead time | 2,252,999 |
