The answer needs to be an excel spreadsheet, thanks!

Western Pharmaceuticals

Once George initiated the supply chain design project $($see Western Pharmaceuticals A$),$ his next task was to investigate the firm$$s inventory management capability relative to the refined supply chain. The integration of the Western and Atlantic Medical distribution systems required a refinement of the firm$$s inventory management system.

Although the firm wanted to have a comprehensive inventory analysis, the information available was limited due to the merger and a simultaneous move to an enterprise resource planning system. In fact, in terms of quickly available data, there was only a limited sample from the Atlantic Medical sales and inventory records. For a sample of $100$ stockkeeping units $($SKUs$),$ the database includes the average and standard deviation of weekly sales, average order cycle time $($OCT$),$ replenishment order quantity $($OQ$),$ and the average inventory. Based on history, the current standard deviation in the replenishment cycle time is $1$ week. The sales, order quantities, and inventory are recorded in cases. The historical information is provided for each of the three existing distribution centers.

Atlantic believes that the historical case fill rate is $95$ percent but they are not really sure.

Questions

What should the case fill rate be for each product given the current uncertainty levels and order quantities and how does the calculated aggregate case fill rate differ from the historically observed level?

What are the safety stock and average inventory levels for each product and in aggregate necessary to achieve $95$ percent case fill rate for each product? To what extent do the actual inventory levels deviate from the theoretical inventory levels? What conclusions can you draw from the differences?

What is the inventory carrying cost impact for increasing the case fill rate from the current level of $95$ percent to $99$ percent? Assume an annual inventory carrying cost of $20$ percent. Assuming that $5$ percent is potentially lost due to stockouts $(10095$ percent$)$ and that there is a $25$ percent margin on the average item $($COGS $=75$ percent$),$ would you recommend that the service level be increased? Justify your answer.

What would be the impact on inventory and service of consolidating all of Atlantic Medical stock into a single facility? Apply both the $$square root of N$$ and item level approaches. The square root of N should be applied to aggregate inventory values for a total of all products. The item level approach uses the individual standard deviations. Discuss the differences between the two approaches. Why?