Excel Supply Chain Analysis: Solving Inventory Problems

Learn how to use Excel to conceptualize, set up, and solve inventory problems to minimize purchasing cost and reduce the risk and cost of stockouts.

Introduction

• Solve supply chain inventory problems with Excel
• What you should know

1. Calculating Order Size for Deterministic Demand

• Compare setup costs and holding costs
• Calculate an economic order quantity (EOQ)
• Calculate orders per year and time between orders
• Calculate effects of lead time
• Determine increased cost of constrained purchases
• Examine the effect of quantity discounts on cost
• Calculate production order quantities

2. Calculating Economic Order Quantity with Back Orders

• Describe how allowing back orders affects order quantities
• Calculate the critical ratio
• Calculate economic order quantity with back orders
• Calculate reorder points for inventory policies that allow back orders

3. Analyzing Single Period (Newsvendor) Problems

• Describe the newsvendor problem and approaches
• Set up a worksheet to identify maximum profit
• Fill in the worksheet and calculate profit
• Calculate and visualize expected profit
• Identify the optimal order quantity using marginal analysis
• Incorporate salvage value into your analysis

4. Analyzing Continuous Review Inventory Policies

• Describe the level of service policies
• Calculate the base stock required for a target level of service
• Determine the reorder point based on cycle service level

5. Practicing Inventory Problem Analysis

• Challenge: Analyze a deterministic demand problem
• Solution: The deterministic demand problem
• Challenge: Analyze a back order problem
• Solution: The back order problem
• Challenge: Analyze a continuous review inventory problem
• Solution: The continuous review inventory problem

Conclusion

• Next steps