Spreadsheet-based supply chain simulation for teaching risk pooling combined with facility location

Abstract

Inventory risk pooling is the use of centralized inventory to maintain lower overall inventory and safety stock. Generally, in a decentralized system with no risk pooling, a retailer maintaining its own inventory and safety stock requires higher levels of inventory than would be if the inventory was pooled at a centralized location. Most pedagogy relies on algebraic formula-based examples, which has two issues: (1) student struggle visualizing variability concepts and algebraic notation and (2) the location of the centralized inventory location and its effect on lead times are often ignored or assumed to be negligible. To address these issues, the spreadsheet-based simulation offers visual cues of the inventory reduction benefits, as well as demonstrating the impact of the facility location. The simulation was developed using MS Excel, and has a simple, easy-to-use interface, as well as easy-to-understand graphical and statistical output. Both undergraduate and MBA students respond favorably to the simulation because it helps them understand the math concepts, and it strengthens their spreadsheet skills. The inventory policy is an order-up-to-level with a weekly periodic review system. Backorders must be filled. Inputs include the number of customers, desired service level, weekly customer standard deviation of demand, lead times and inventory order-up-to levels. Outputs include weekly and annual inventory and service levels for both the decentralized and centralized (risk pooling) systems.