Calculating the correct amount of inventory to order – Advanced

In our previous post ‘How to calculate the correct amount of inventory to order’ we introduced a basic framework that can be used to begin your calculation.

If you are revisiting the post and want to get straight to the calculator, welcome back! You can get the calculator here or scroll to the bottom of the page where it is embedded.

If this is your first time visiting you’ll find it beneficial to read through the framework before using the calculator.

In this post we are going to be working through some more advanced mathematical models to understand the correct amount of inventory to order to maximize your profit.

We’ll be using the Newsvendor model, which is used in scenarios where there is a lot of uncertainty in the predicted sales (e.g seasonal products, or products that don’t have firm retail PO’s in place).

The framework consists of 2 stages:

1. Economic inputs
2. Calculating the optimal order quantity

We are going to work through the model using an example of an online sunglasses company that is deciding how much inventory to order for summer (we always find having examples makes it easier to follow). Let’s get into it.

1. Economic inputs

Using our sunglasses company as an example, their metrics for one product are as follows:

• Landed cost: $50 (the total cost to get one unit into stock; i.e manufacturing, shipping, taxes, etc.)
• Regular retail price: $150 (the standard cost of the item to consumers)
• Salvage price: $40 (the sale price after summer is over if they need to liquidate inventory)
• Holding cost: $0.25 for each dollar tied up in inventory at the end of summer (i.e storage costs, loan fees, etc.)
  • In this case the Holding cost is: $0.25 * $50 = $12.50 per unit

With these metrics we can then work out how much it costs the company to order one unit too few and one unit too many.

• Overage cost (Co): The cost to order one unit too many 

Co = Landed Cost – Salvage Price + Holding Cost

Co = $50 – $40 +$12.50 = $22.50 per unit

• Underage cost (Cu): The cost to order one unit too few; e.g lost revenue.

Cu = Regular Retail Price – Landed Cost

Cu = $150 – $50 = $100 per unit

So far, so simple, right? Now we need to get into some more complex mathematics. Don’t worry, it’s nothing too crazy.

2. Calculating the optimal order quantity

At this point it is worth emphasizing that this model is used to understand the amount of units the company should order in order to maximize profit, not maximize sales.

As the quantity of units increases the expected cost of under-ordering decreases, but the expected cost of over-ordering increases. The point at which these lines overlap is the optimal quantity to order. So how do we get it?

We need to get the Critical Ratio.

Critical Ratio (CR) or F(Q) = Cu / (Co + Cu)

CR = $100 / ($100 + $22.50) = $100 / $122.50 = 0.8163

At this point it is worth reminding you, our end goal is to find the optimal quantity of units to order (). We can do this using the following formula:

Where:

= mean

= number of standard deviations from mean

= standard deviation

We can use the critical ratio with the Standard Normal Distribution table to calculate Z. You can find the tables here. If you don’t know how to read the table, you can get a quick explainer here.

Otherwise you can use the following formula in Excel or Google Sheets:

Z = norm.s.inv(CR)

Z=norm.s.inv(0.8163)

Z = 0.90

Now we just need the mean () and standard deviation () from your historical demand. To get these data points, put your historical demand data into Excel or Google Sheets and utilize the following functions:

Mean ():

=average(your data range)

Standard deviation ():

=stdev(your data range)

In our sunglasses company example, we can use the following data:

• Mean ()= 2279

• Standard Deviation () = 970

Before we calculate the optimal quantity to order, let’s summarize.

A. Calculate economic inputs:

• Landed cost = $50 (the total cost to get one unit into stock)

• Regular retail price = $150 (the standard cost of the item to consumers)
• Salvage price = $40 (the sale price they sell the item for after summer is over if they need to liquidate inventory)
• Holding cost = $12.50 per unit (i.e storage costs, loan fees, etc)

Overage cost (Co) = Landed cost – Salvage price + Holding cost

Co = $50 – $40 + $12.50

Co = $22.50

Underage cost (Cu) =Regular retail price – Landed cost

Co = $150 – $50

Co = $100

B. Set up for calculating Quantity

• We need to calculate the Critical Ratio, where the expected costs for over-ordering and under-ordering will be lowest:

Critical Ratio (CR) or F(Q) = Cu / (Co + Cu)

CR = $100 / ($100 + $22.50) = $100 / $122.50

CR = 0.8163

• Now we need to find Z, which is the number of standard deviations below or above the mean: 

Z = norm.s.inv(CR)

Z=norm.s.inv(0.8163)

Z = 0.90 

• Collect the mean () and standard deviation () from your historical demand. In our example:

Mean ()= 2,279

Standard Deviation () = 970

C. Calculate the optimal order quantity ()

We can use the following equation:

And there we have it, the optimal order quantity () to maximize profit is 3,152 units. 

You can try this yourself using the calculator below

Points to note

• If your company is growing very quickly, this won’t be that useful, since early data on your demand will have shifted.
• If you sell out frequently, demand is almost impossible to predict since you won’t know how many units could have sold if you were in stock.

If you found this post useful, let me know by commenting below or saying 👋on Twitter.

If you want to discover more about inventory financing, head here.