EOQ Formula with examples in Excel (Economic Order Quantity)

How often and in what quantity should you order products? This is what we will try to answer in this article with the economic order quantity (EOQ) or Wilson formula.

EOQ formula, step-by-step tutorial in Excel:

Economic Order Quantity definition & meaning (EOQ)

The Economic Order Quantity (EOQ) is also known as the Wilson formula.

It should be known that Mr. Wilson did not invent the formula. It is rather, Ford Whitman Harris, who developed the mathematical principle. Mr. R. H. Wilson, an industrial consultant specializing in inventory management, then used it and applied the formula to optimize inventory.

The EOQ formula aims to find the optimal quantity Q to order for inventory replenishment.

The goal is to find the balance between two factors driving the costs: the cost to order and the cost to hold inventory.

On one hand, the more quantities you order (Q is big), the more expensive it is. The cost of owning inventory (Holding Costs) will increase proportionally to the quantity, as shown on the graph below:

On the other hand, the more you order at once (Q is big too) the less costly it is. If you sell a thousand pieces, it is much more profitable to order 1000 pieces once, than 1000 orders of 1 piece. This ordering cost is also known as Transaction Cost. It is inversely proportional to the quantity ordered:

Now let’s get a look at the Total Costs curve, which is the sum of the Holding Costs and Transactions Costs. We reach the minimum of this curve for a specific Order Quantity, when the Holding Costs equal the Transactions Costs. So, by finding the intersection point of the Holding Costs and Transactions Costs curves, we find the less costly quantities to order: this is the EOQ.

EOQ Formula

We saw we can solve the EOQ problem graphically. We can also use directly the mathematical solution.

Here is the EOQ formula:

We have those 3 parameters:

1. D = Demand or consumption forecast
2. TC = Transaction costs
3. HC = Holding costs

EOQ formula: tutorial in Excel

We will now see examples in Excel. You can simply download the EOQ CALCULATOR HERE:

Let’s take an example with Nike shoes.

Demand

For the Demand, you can take the quantity planned over the desired period (usually 12 months). In this example, we will take 12,000 thousand pairs of Nike shoes size 43. We assume the demand is constant over the year.

Holding cost

The cost of Stock ownership or Holding Stock (HC) is the cost of having a product immobilized in your warehouse, in your store, or in your factory. People often tend to underestimate this cost, because they only consider cash costs and storage costs. However, there is much more to take into account:

1. Annual unit cost of storage (in % or in value)
2. Insurance costs (a % of the value of your stock)
3. Cash costs (credit to finance the stock)
4. Theft and inventory gaps
5. Promotion costs (promotion volume over the year / total turnover).

In the example above, we use a percentage of the Item Purchase Price. I recommend you to have a chat with your Finance Department to get those values.

The total Holding Costs for our Nike shoes are $2.85, which represents 9.5% of the purchase price.

Transaction costs

The cost of placing an order or Transaction Costs (TC) is a little more complex because it includes the fixed costs of many processes involved in each order.

There are different methods of calculation. You can first take all the departments and people who work on order placement, and divide the total cost by the number of orders per year. This method is not optimal: generally, an employee does not work only on orders.

The method I recommend is to try to estimate the number of hours spent on each process. Here are the most common processes listed below:

You may add or remove processes depending on your specific business procedures. You can deduct the cost of each process using an average salary per hour.

In this example, we have a total of 1.7 hours to handle one order, which represents $42.5. It is a fixed cost per order.

Application of the EOQ Formula

Now, we can apply the formula:

D = Demand = 12 000
TC = Transaction Costs = $42.5
HC = Holding Costs = $2.85

We get an EOQ of 598 qty. As it is simpler to use round values for order management, we can round up the final result:

The annual number of orders N is given by the annual demand D divided by the Quantity Q of one order. Thus, for Q = EOQ, we have:

To get the frequency of orders over the year, we must divide the number of days per year by the number of orders:

Here is a summary diagram of the example:

EOQ Calculation: another example

We have another pair of shoes, a bit fancier and therefore with lower annual demand, let’s say 1000.

The demand is lower, but the purchase price is higher, which leads to a higher holding cost (HC). The Transaction Costs remain the same.

Because D is lower and HC higher, we end up with a lower Economic Order Quantity: a more expensive and less demanded product is ordered much less frequently.

We order the item every 2 months, compared to 18 days in example 1.

5 Limits of the EOQ formula

The EOQ formula has some limits.

The main problem is that we consider all parameters are constant.

Limit 1: Unstable demand

One of the main assumptions we made is that the demand is constant.

However, this is not necessarily the case, and you may have an unstable demand with peaks in demand or seasonality.

So, using the previous example, you may not order every 18 days. Otherwise, you will be overstocked at a time of low demand and under-stocked at a time of high demand.

You would need to adjust the frequency of ordering. The fixed EOQ quantity (600 quantities per order in the example) remains relevant. In peak periods, you could “group orders” and order multiples of 600 like 1200, 1800… In low sales periods, you could order less frequently.

Limit 2: The purchase price

Another assumption is the constant purchase price. In general, suppliers offer discounts depending on the quantities ordered. If so, it is advisable to record the purchase prices according to different order levels, as shown in the table below.

Sometimes, the discounts on the purchase price are attractive and make the EOQ analysis much less relevant. In this case, you can choose to order more quantities per order.

Limit 3: Inconsistent costs

The formula considers that all costs are constant, including transportation and storage. This is not always the case: for example, you have fixed costs in a warehouse (rent, depreciation of machines) but also have variable costs like workforce or even electricity.

I advise you to not seek perfection: focus on what works best. Focus on the costs that have the most impact on your business, and find an easy way to quantify them (such as a percentage of the purchase price). This will give you a good direction.

Limit 4: Inconsistent or unpredictable lead time

The formula considers supply lead time as constant. But in reality, lead time may vary over the year, and you have uncertainty about the supply delays.

For example, If you can anticipate and see 2 clear patterns during the year (big lead time during summer and shorter ones the rest of the year) then you can easily recalculate the EOQ formula. If you have high uncertainty on the lead time for the whole year, then you need a deeper analysis.

Limit 5: No Safety Stock

Finally, if we assume all parameters are constant and stable, then we do not consider safety stocks. But in the reality of the operations, it is impossible to not cover supply and demand risks. Today’s markets are more and more volatile and uncertain. Therefore, it is essential to hold safety stocks to face this uncertainty, which can strongly impact your profits and your customer service rate.

Thus, we must combine the EOQ with a safety threshold, as shown in the graph below:

To know more about Safety Stock calculations, Reorder Point calculations, and Inventory Management policies, check out my article: Safety Stock Formula & Calculation: 6 best methods.

Conclusion

The Economic Order Quantity is a good tool to minimize total costs, as it is the theoretical optimal quantity to order in Inventory Management. We saw how to use the EOQ formula in Excel through various examples, and we deducted the corresponding frequency to order.

The formula itself has limitations. The main ones are:

• Purchase price discounts: it can be more attractive in comparison to the EOQ savings. Then, the quantities per order can be bigger than the EOQ.
• Demand & Lead time not steady: need to vary the frequency and group orders.
• No Safety Stock: using EOQ doesn’t cover any risk
  
  The last two problems can be partially solved using a reorder point policy with safety stocks.

The EOQ formula is an overly simplistic solution to a very broad problem (as with any problem in Supply Chain). Still, it is a good starting point. By trying to implement it in your inventory management, you will be more aware of your own supply chain challenges.

I have seen so many businesses where order management was lacking the most basic rules or processes. In those cases, implementing simple inventory management principles step-by-step can often bring great results.

Take action and don’t wait. I like this quote from Jeff Bezos, Amazon’s CEO:

Most decisions should probably be made somewhere around 70% of the information you wish you had. If you wait for 90%, in most cases, you’re probably slow.

Jeff Bezos

EOQ: Your Action Plan

1. Download the Wilson Formula Excel here, Test it first with a few products, the most important for your business. Then use it progressively with your entire items portfolio.
2. Calculate the EOQ for your business
3. Compare the Quantity to order with your current settings (important)
4. Adjust major deviations
5. Review your production batches size / orders / MOQ

Become an Inventory Management Expert

If you want to go to the next level, join my next Inventory Management Workshop (free): “How to avoid shortages and overstocks in times of great uncertainty”.

During this webinar, I will share with you:

👉 My method for dealing with sales and supplier uncertainty: 13 parameters to master
👉 How to reduce your inventory and increase your service rate simply and automatically
👉 Excel files and case studies presented live (Zara, H&M, Amazon…)