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.
Overview
Syllabus
Introduction
- Solve supply chain inventory problems with Excel
- What you should know
- 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
- 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
- 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
- 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
- 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
- Next steps
Taught by
Curt Frye