It's been a minute, but we're back with another inventory optimization tool for your back pocket!
First, we broke down the Newsvendor inventory management model for selecting stocking levels when demand is uncertain. Then we dove into the Economic Order Quantity (EOQ) inventory model for choosing optimum stocking quantities to minimize holding, shortage, and order costs.
Many manufacturers asked if we could put together some lightweight Excel inventory models based on our blog posts - so we did, and you can download them for free.
This is all part of The Ondema Guide to Production Scheduling, a comprehensive manufacturing resource available for free.
This post dives into the Lot size/Reorder level (Q,R) inventory model, a great hybrid of the Newsvendor and EOQ models, that accounts for lead time and demand uncertainty.
Not only are these models industry-proven, but they form the foundation for almost every inventory management software solution on the market, even the ones with seven-figure price tags.
Economic Order Quantity recap
Graphically, the EOQ process looks like:
Credit: Taylor (2006).
Inventory is stocked at quantity Q. As demand whittles down inventory levels, an order is placed when inventory reaches a certain level such that the next order quantity Q arrives just as the first is depleted.
We can quickly calculate Q and R because demand was known and deterministic. But we all know that's not how the real world works. Demand is somewhat uncertain and reorder points don't necessarily follow uniform cycles.
Wouldn't it be awesome if there was a model that accounted for this? Do I have some good news for you! Introducing...
The Q,R model
Graphically, the Q,R inventory model looks like:
Credit: Gel & Keskinocak (2013).
We choose the reorder level R to meet demand during lead time, attain a desired service level, and balance the costs of stockouts and inventory. We use the Q,R model to balance and minimize total costs of:
fixed cost + holding cost + stockout (backorder) cost
Let's define additional variables:
- d is expected demand per unit time.
- D is demand during lead time.
- τ is lead time.
- K is setup cost per order.
- h is holding cost per unit per unit time.
- c is purchase price (cost) per unit.
- p is stockout (backorder) cost per unit.
- s is the average inventory level before an order arrives.
- n(R) is the expected shortage per cycle and is a standard loss function equal to σ*L(z).
We want to find Q and R such that the expected total cost per unit time is minimized. Expressed mathematically, total cost per unit time is:
Of note: n(R) is a standard loss function that we'll use Excel or normal distribution tables to find.
The optimal solution for Q and R is:
So how do we pull Q and R from these equations? By solving iteratively! This involves starting with a Q0 value and iterating until values converge.
Some of you may be asking:
Don't worry. We'll add a bit of clarity with an example. Using nunchucks, of course. 💥💥💥
Dan's Nunchuck Emporium
We examined optimum stocking for Dan's Luxury Nunchucks (LuxChucks™) because we're innovators leaning into the rich-people-pay-a-lot-for-ninja-weapons market. Who doesn't want to look bad-ass while practicing ninjitsu in their Crocs with the garage door open blasting "Eye of the Tiger" on repeat??!?! NOBODY, that's who.
LuxChucks were a hit, but the market's much bigger. If we want global ninja weapon market domination we need something for the masses. That's why we introduced Dan's Tough Nunchucks (ToughChucks™) for the value and cost-conscious ninja weapons enthusiast.
Fast forward to the present. We're keen on expanding regional ninja weapon market presence through brick-and-mortar locations; namely, Dan's Nunchuck Emporium.
Dan's Nunchuck Emporium uses a Q,R inventory management system to optimize stock levels. For our latest model, KidChucks™, we have the following data:
- Monthly demand is assumed to be normally distributed with an average of 28 and standard deviation of 8.
- Replenishment lead time is about 14 weeks (τ).
- Each unit costs the store $6 (c).
- Although excess demand is serviced through backorders, each KidChuck stockout costs about $10 in bookkeeping and loss of goodwill (p).
- Fixed cost of replenishment is $15 per order (K).
- Holding costs are based on a 30% annual interest rate.
Key questions and solutions
We're going to use the Q,R model to answer two key questions:
1. What is the optimal order quantity and reorder level?
2. What is the expected inventory level (i.e., safety stock) just before the order arrives?
First, let's calculate expected annual demand d and expected demand during lead time D:
Second, let's calculate the variance of demand during lead time (σ2):
Third, let's get weird.
In the first iteration, we're going to calculate the economic order quantity Q0. Of note: h is calculated as the annual interest rate times the unit cost = i * c = 30% * 6 = $1.80 per unit per year.
Now we compute R1 given Q0 above:
And then use R1 to compute Q1.
Q0 and Q1 aren't close, so we need to keep on keepin' on. 🙈🙉🙊
Now we compute R2 given Q1 above:
Luckily, the R values converged and we found the optimal solution:
(Q,R) = (80,115)
Which allows us to calculate the safety stock:
s = R - μ = 115 - 90 = 25 units
Now that the dust has settled...
We can take a couple steps back to understand the relationships between key variables.
It's important to note that as the reorder level R increases, inventory does as well. This means that the economic order quantity Q goes down. Using the numbers from our example above we can visualize this relationship:
Credit: Gel & Keskinocak (2013).
The Q,R model optimizes for Q and R to balance between holding cost, setup/fixed cost, and shortage cost.
- To save on shortage costs, we want large R.
- To save on holding costs, we want small Q and small R.
- To save on fixed costs, we want large Q.
You're a brave soul if you made it this far. Thanks for joining me on today's magical mystery tour.
Lifetime Intern Dan