Excel Supply Chain Analysis: Solving Inventory Problems

1h 20mIntermediate2020-12-15

Authors

Curt Frye

President of Technology and Society, Incorporated

Course details

Excel pro Curt Frye shows how to analyze inventory costs and develop effective inventory policies within a supply chain. He explains how to calculate total costs and total relevant costs for a product. He covers how to incorporate the effects of lead time into analysis and how to determine how much extra those minimum orders cost. Curt goes into how to evaluate quantity discounts and how to calculate production quantities to maximize profit. He discusses how back orders affect inventory policies and how to calculate the economic order quantity for inventory policies that allow back orders. He introduces you to difficulties associated with goods that have a limited lifespan, often called the newsvendor problem, then shows you how to generate the data and summaries required to solve a newsvendor problem. In conclusion, Curt walks you through how to calculate the base stock level for a target level of service and how to calculate a reorder point based on cycle service level performance.

Skills covered

Supply Chain ManagementBusiness AnalysisOperations ManagementSpreadsheetsMicrosoft ExcelProject ManagementBusiness Analysis and StrategyBusiness Software and ToolsMicrosoftOne-Off

Concepts

0. Introduction

01 - Solve supply chain inventory problems with Excel
02 - What you should know

1. Calculating Order Size for Deterministic Demand

03 - Compare setup costs and holding costs
04 - Calculate an economic order quantity (EOQ)
05 - Calculate orders per year and time between orders
06 - Calculate effects of lead time
07 - Determine increased cost of constrained purchases
08 - Examine the effect of quantity discounts on cost
09 - Calculate production order quantities

2. Calculating Economic Order Quantity with Back Orders

10 - Describe how allowing back orders affects order quantities
11 - Calculate the critical ratio
12 - Calculate economic order quantity with back orders
13 - Calculate reorder points for inventory policies that allow back orders

3. Analyzing Single Period (Newsvendor) Problems

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

4. Analyzing Continuous Review Inventory Policies

20 - Describe level of service policies
21 - Calculate base stock required for a target level of service
22 - Determine reorder point based on cycle service level

Conclusion

23 - Next steps