Answer Happy

Totals Average Turnover Part A Part B $4,230,491 $832,799,258 Warehouse 13, 21, & 24 Total Sales for Warehouses 13, 21, 247 Avg Inventory (actual)? Expected Average Inventory (regression)? Expected Average Inventory (Sq Root Law)? Warehouse 19 Total Sales for Warehouse 197 Avg Inventory (actual)? Avg Expected Inventory (regression)? Allocate #19 Inventory to New Warehouses New Warehouse 19a (40%)? New Warehouse 196 (60 %)? Total? Expected Average Inventory (Sq Root Law)? $790,669 $170,224,639 Before Consolidation $13,628,915 $2,542,945 Before Expansion $75,266,622 $23.523.846 $3.525.278 After Expansion Sales $30,106,649 $45, 150 973 $75.266.622 5.31 6.45 After Consolidation $13,628,915 $359.800 $1,798,133.65 After Expansion Exp Avg Inv $9.400.538 $14,114,308 $23,523.846 $2,492,748 22
4. Complete Part B of the worksheet to split Warehouse 19 into two new warehouses, where 40% of the throughput is allocated to Warehouse 19a, and 60% is allocated to Warehouse 19b. a. Calculate a baseline for warehouse 19: i. Cell C41: Enter the average annual throughput for Warehouse 19. Cell C42: Enter the actual average inventory level for Warehouse 19. Cell C43: Calculate the expected average inventory level for Warehouse 19 using the regression model. ii. iii. b. Split the inventory in Warehouse 19 into two new warehouses and calculate the new average annual throughput to Warehouses 19a and 19b. i. Cell C46: Allocate 40% of the annual throughput to Warehouse 19a. ii. Cell C47: Allocate 60% of the annual throughput to Warehouse 19b. iii. Cell C48: Calculate the total annual throughput. c. Calculate the expected average inventory level at each new warehouse. i. Cell D46: Calculate the expected average inventory level for Warehouse 19a using the regression model. ii. Cell D47: Calculate the expected average inventory level for Warehouse 19a using the regression model. iii. Cell D48: Calculate the expected average inventory level for the system of two warehouses (e.g., the total for Warehouses 19a and 19b) iv. Cell D50: Calculate the expected average inventory level for the system of two warehouses using the Square Root Law.