Question
Value of Aggregation (Use Excel) Assume McGrainger, a supplier of MRO products, has 1600 stores distributed throughout the United States. Consider two products that the
Value of Aggregation (Use Excel) Assume McGrainger, a supplier of MRO products, has 1600 stores distributed throughout the United States. Consider two products that the firm supplies large electric motors and industrial cleaners. Large electric motors are high value items with low demand, while the industrial cleaners are low value items with high demand. Each motor costs $500 and each can of cleaner costs $30. Weekly demand for motors at each store is normally distributed with a mean of 20 and standard deviation of 40. Weekly demand for cleaner at each store is also normally distributed with a mean of 1000 cans and a standard deviation of 100. Demand experienced by each store is independent, and supply lead time for both products is four weeks. The firm has an annual holding cost of 25 percent. For each of the products, evaluate the reduction is safety inventory that will result if it is removed from the retail stores and carried only in a central facility. Assume a desired service level of 0.95.
CAN YOU EXPLAIN HOW TO CALCULATE THE TABLE BELOW, IF YES PLEASE INCLUDE THE FORMULA FOR EACH. THANK YOU
MOTORS | CLEANER | |
INVENTORY IS STOCKED IN EACH STORE | ||
Mean weekly demand per store | 20 | 1000 |
Standard deviation | 40 | 100 |
Coefficient of variation | 2 | 0.1 |
Safety inventory per store | 132 | 329 |
Total safety inventory | 211200 | 526400 |
Value of safety inventory | $105,600,000 | $15,792,000 |
INVENTORY IS AGGREGATED AT THE DC | ||
Mean weekly demand per store | 32000 | 1600000 |
Standard deviation | 1600 | 4000 |
Coefficient of variation | 0.05 | 0.0025 |
Aggregate safety inventory | 5264 | 13159 |
Value of safety inventory | $2,632,000 | $394,770 |
SAVINGS | ||
Total inventory saving on aggregation | $102,968,000 | $15,397,230 |
Total holding cost saving on aggregation | $25,742,000 | $3,849,308 |
Holding cost saving per unit sold | $15 | $0 |
Savings as a percentage of product cost | 3.09% | 0.15% |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started