Inventory Management

Inventory Management for Retail — Deterministic Demand

Build a simple model to simulate the impact of several replenishment rules (Basic, EOQ) on the inventory costs and ordering costs

For most retailers, inventory management systems take a fixed, rule-based approach to forecasting and replenishment order management.

Given the demand distribution, the objective is to develop a replenishment policy that minimises

Ordering Costs: fixed cost to place an order due to administrative costs, system maintenance or manufacturing costs in (Euros/Order)
Holding Costs: all the costs required to hold your inventory (storage, insurance, and capital costs) in (Euros/unit x time)
Shortage/Stock-out Costs: the costs of not having enough inventory to meet the customer demand (Lost Sales, Penalty) in (Euros/Unit)

In this article, I will present a simple methodology using a discrete simulation model built with Python to test several inventory management rules based on the assumption:

Deterministic Constant Demand: D (Units/Year)
Lead Time between ordering and replenishment (Days)
Cost of shortage and storage (Euros/Unit)

💌 New articles straight to your inbox for free: Newsletter

💡

SUMMARY
I. Scenario
visualisation
Objective
II. Build your Model
What is the best compromise between ordering and holding costs?
1. Visualise the current rule
2. Economic Order Quantity: Q = Q*
3. Include replenishment lead time
4. Real-Time visualisation of Cost of Goods Sold (COGS)
III. Conclusion & Next Steps

Scenario

Problem Statement

As an Inventory Manager at a mid-sized retail chain, you are responsible for setting replenishment quantities in the ERP.

Based on the store manager's feedback, you begin to doubt that the ERP replenishment rules are optimal.

Especially for fast runners, your stores are experiencing lost sales due to stock-outs.

For each SKU, you would like to build a simple simulation model to test several inventory rules and estimate the impact on:

Total Costs: How much does it cost to receive, store and sell this product?
Shortages: what is the % of lost sales due to stock-outs?

In this article, I will assume

💡

# Total Demand (units/year)
D = 2000
# Number of days of sales per year (days)
T_total = 365
# Customer demand per day (unit/day)
D_day = D/T_total
# Purchase cost of the product (Euros/unit)
c = 50
# Cost of placing an order (/order)
c_t = 500
# Holding Cost (% unit cost per year)
h = .25c_e = h * c
# Selling Price (Euros/unit)
p = 75
# Lead Time between ordering and receiving
LD
# Cost of shortage (Euros/unit)
c_s = 12

To simplify the comprehension, let’s introduce some notations

Objective

The goal of this exercise is to

Visualise the current rule used by the store's manager
Calculate the Economic Order Quantity and simulate the impact
Visualise the impact of lead time between ordering and receiving
Real-Time Visualisation of COGS for each rule

Build Model

Economic Order Quantity (EOQ)

The theory behind the Economic Order Quantity (EOQ) is to find the optimal order quantity Q* that will be the best compromise between ordering costs and holding costs.

A low order quantity will give you high ordering costs (increase the number of replenishment orders: D/Q), but will reduce your holding cost (reduce the average inventory level: (Q/2))
The inverse for a high-order quantity

Minimum of Total Relevant Cost — (Image by Author)

Comments

In the chart above, we can see that the Total Relevant Cost (TRC) (total cost without the purchase cost cD) is minimum for Q*=400 units/order.

TRC(Q*) = 5,000 Euros

Visualise the current rule

The current rule is to order the exact quantity needed to meet demand for 10 days every 10 days.

This quantity is way lower than Q*

We can easily understand that the TRC will be way higher than its optimal value:

TRC(10) = 100,062 Euros

To understand why, let’s simulate the rule for a range of 365 days:

💡

Very short replenishment cycles that multiply the number of replenishment orders.

Economic Order Quantity: Q = Q*

For each replenishment cycle, you order Q* = 400 orders, and you reorder when the inventory level is zero.

💡

Longer replenishment cycles that reduce the number of orders by 7 times => lower TRC

Include replenishment lead time

What would be the stock-out level if we have a replenishment lead time, LD = N Days?

Economic Order Quantity with 25 days lead time — (Image by Author)

💡

With 25 days lead time between ordering and receiving we reach 140 units of stock-out quantity per replenishment cycle.

Real-Time Visualisation of COGS

If you want to convince your commercial team and the store managers, you need to speak their languages.

You can prepare a simple visualization of the potential turnover with the COGS (here we’ll exclude purchase costs COGS = TRC) to understand the impacts throughout the year.

Initial Rule

💡

COGS is mainly driven by the ordering costs because of the high frequency of reordering due to the low replenishment quantity.

EOQ Rule

💡

The Economic Order Quantity provides a compromise between ordering costs and holding costs that drastically reduces COGS.

It's time for you to implement this solution!

🔗

You can find the full code in this Github repository: Link.

Conclusion

This simple modelling exercise led to the design of a basic simulation model that shows the impact of customer demand and inventory rules on key performance metrics.

It gives you visibility into your ordering frequency, inventory levels, and the impact of lead times on your supply chain.

The initial assumption of constant deterministic demand is very optimistic.

In the next articles, we will

Study the impact of demand variability on total relevant costs and lost sales

Implement a Periodic Review Policy to limit the number of orders

About Me

Let’s connect on LinkedIn and Twitter. I am a Supply Chain Engineer using data analytics to improve logistics operations and reduce costs.

If you’re looking for tailored consulting solutions to optimise your supply chain and meet sustainability goals, feel free to contact me.