Trainings
Excel Tutorials
Blog
About us

EOQ Formula with examples in Excel (Economic Order Quantity)

Table of Contents

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 video / Please activate the automatics subtitle in English) 

https://youtu.be/v_kRuEibYks

Principle of the Wilson Formula / EOQ

It should be known that Wilson’s formula was not invented by Mr. Wilson but by Ford Whitman Harris who developed the mathematical principle. Then it was an industrial consultant specialized in inventory management, Mr. R. H. Wilson, who used and applied this formula to inventory optimization.

Wilson’s formula is meant to calculate the economic order quantity (EOQ), which means that the more inventory you have, the more expensive it is. If you don’t have stock, you don’t have costs, but the more you increase your inventory, the more the cost of owning inventory will increase. This phenomenon is represented by the straight green line in the graph below. On the other hand, the more orders you get, the more expensive it will be. If you sell a thousand parts, it is much more profitable to order 1000 parts than 1000 orders of 1 unit.

Wilson formula example supply chain

The important thing here is to find the intersection point of the 2 curves. This point then gives you the optimal quantity in order to optimize both stock costs and ordering costs.

EOQ Formula (Economic Order Quantity Formula)

Wilson’s formula refers to the Economic Order Quantity (EOQ).

economic order quantity formula

In this Formula, you have three parameters:

  1. Demand or consumption (D)
  2. Order placement costs (CO) = Transaction costs
  3. Stock ownership cost (SC) = Holding costs

EOQ / Wilson formula – Example 1:

Example 1: Demand (D), it is the easiest parameter to calculate. You have a particular product (for example a pair of Nike shoes size 43), and you 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. The size is important here because each item will have a different volume.

The cost of placing an order (CO) is a little more complex because it includes all the costs related to placing each order. This is a unit order, so all the fixed costs generated for each order are considered.

There are different methods of calculation. You can first take all the departments and people who work on the order placement and divide the total cost by the number of orders you place each year. This method is not optimal because generally, a person does not work only on orders.

The method I recommend is to try to estimate the number of hours spent at each process, i.e. time spend to :

  1. Placement of the order
  2. Validation of the order
  3. Approval of the order with your supervisor
  4. Communication with the supplier by email
  5. Tracking of the shipment
  6. Reception of the order
  7. Inspection of the order at the warehouse / factory
  8. Putting it in storage
  9. Supplier payment process

These tasks and hours – you can add or remove them depending on your service – must be multiplied by an hourly rate with all charges included (about 25€ in France)

In the example below, we arrive at 43€ per order. It is a fixed cost per order.

Transaction cost (placing order)

The cost of ownership of the Stock (SC) is really the cost of having a product immobilized in your warehouse, in your store or in your factory. This cost is often very underestimated because only cash costs and storage costs are taken into account. However, there is much more to consider :

  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).
Transaction-costs-orders-EOQ
economic order quantity example excel

In the example above, we have a rate of 9.5% of the purchase price or 2.85€. In concrete terms, the cost of ownership of a product costing €30 over a year would be €2.85.

Average stock costs for 1 year/unit (Holding costs)

Cost inventory Economic order quantity

When we come back with our example of 12,000 pairs of Nike shoes size 43, we have :

D = 12 000

CO = 43€

SC = 2,85€

Economic Order Quantity (EOQ)

D = Demand or consumption 12 000

CO = Order Placement Cost = 43€

SC = Stock Ownership Cost = 2,85€

After the formula we obtain Q = 598 ≈ 600 qt (simpler to have round figures)

We then determine the annual order number by doing N = D/EOQ

With our example we obtain N = 12 000/600 = 20

N = 20 orders/year.

To obtain the frequency of the command: F = 365/N

With our example, we obtain F = 365/20 ≈ 18 which corresponds to one command every 18 days.

Here is a summary diagram of the example:

Wilson formula – Example 2 :

I have another pair of shoes, a little less classic 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 unit stock ownership (SC) cost. The Order Placement Cost (CO) remains the same.

In the end, in this case, we end up with a lower Economic Order Quantity (Q) than in the 1st example, because the demand is lower but also because the cost of the product is higher in terms of stock. As for the order frequency, it will be about 2 months compared to 18 days in example 1.

EOQ-supply-chain-example

EOQ formula example in Excel

We will now see another example directly in Excel. You can simply download it HERE:

In this Excel you can directly find all the parameters you can modify and 50 sample items:

  • Purchase price
  • Cost of ownership of Unit Stock
  • The cost of placing an order
  • Economic Quantity orders
  • Number of orders per year
  • Order frequency in days

5 Limits of the Wilson Formula / EOQ calculation

Unfortunately, this formula dating from one century to today some limits.

The main problem with this formula is that Wilson considers that all parameters are constant

Limit 1: Unstable demand

For example, using the example of the 12,000 quantities sold per year, the formula will consider that the demand is stable and that you sell 1000 quantities per month. 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 do not need to place an order every 18 days, otherwise, you will be overstocked at a time of low demand and under-stocked at a time of high demand.

The trick I recommend is to neglect the frequency of ordering. Quantity Q (600 quantities per order in the example) remains relevant. Simply in peak periods, you will group orders and order 1200, 1800…

Limit 2: The purchase price

The price is also constant. In general, the more you have ordered in quantity, the more discounts you will get. If you have annual discounts, no problem, but if you have discounts based on the quantity ordered, it will become interesting to place order levels as shown in the table below

You can see that it may be interesting to place larger orders to reduce the purchase price per unit of products.

Limit 3: Inconsistent, variable costs and fixed costs

The formula considers that all costs are constant, including transportation and storage. But you know that if you have a warehouse, you have a fixed cost part like rent or depreciation of machines, but you also have variable costs like workforce or even electricity.

Once again, this is not perfect, but what I advise you and what works best is to take the average cost as a percentage or price per quantity that will give you a good direction.

Limit 4: Inconsistent or unpredictable lead time

The formula considers delays as constant. But in reality, you don’t have a constant time frame, you have a time frame that varies for delays or you have implementation orders that are longer and then a supply time frame that is shorter.

Here, either you launch two Wilson formulas with two different time frames, or you take another average time frame

Limit 5: No Safety Stock

The last limit, Wilson’s formula does not consider safety stocks because he considers that all parameters are constant and stable. However, in today’s reality, markets are more and more volatile and uncertain and it is, therefore, essential to have this safety stock to face this uncertainty which could strongly impact your availability or your customer service rate.

In general, as you can see in the graph below, we will combine Wilson’s formula for the economic quantity of orders and frequency with a safety threshold that will allow us to protect our customer availability in the face of uncertainty.

We will talk about the different ways to calculate good safety stock in our next videos, don’t hesitate to subscribe to our AbcSupplyChain channel on Youtube, so you don’t miss anything.

Conclusion

To conclude, the Wilson formula is not perfect but it’s a very good start to optimize your inventory. I like to use an expression from Jeff Bezos, Amazon’s CEO, who advises you to make your decisions with about 70% of the information, because if you wait until you have 90% of all the information, you will probably be too slow and behind your competitors or objectives.

Action Plan EOQ

Download the Wilson Formula Excel here, and test with one or two products and then test your entire item base with the annual request, purchase price, inventory cost, order cost and then have the economic quantity with Wilson formula.

eoq excel download

What I recommend is to compare it with the current batch size you use in software and compare the differences that are extreme. For example, you can reduce the impact of the inventory in question by using the revised batch size.

You should also check our new article : Safety Stock : 6 best formulas to Optimize your inventory

Become an inventory management expert
Join my free 7-day mini training: “How to Reduce Stockouts and Overstock.”
Share
Boost your inventory management skills

Join my free 7-day mini training: 
How to Reduce Stockouts and Overstock.”

  • The method for dealing with Demand Uncertainty.
  • How to integrate the Reliability of your Suppliers (3 examples)
  • The 13 Parameters I use to optimize my inventory (7 of which are essential)
  • How to Automate your inventory management on Excel or ERP (and stop working like a fireman)
  • Case studies of large groups: Zara, H&M, Amazon…
is also on Youtube
+30K Subscribers

(across 2 channels)

online supply chain training platform
Ready to boost your skill ?
Discover our courses
See all courses
Excel training label
Excel Expert
View the course
SCM Analytics training label
SCM Analytics
View the course
Inventory management training label
Inventory Management
View the course
Forecasting training label
Forecasting Expert
View the course
Scroll to Top

FIND OUT HOW MUCH YOU ARE WORTH.

You will receive an email with an estimate of your salary along with some advice on how to boost it.

Your information is confidential and will never be given to third parties. You can unsubscribe in 1 click from any of my emails.