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%