Use the information below to calculate the number of orders per year when using the eoq:
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: Holding Costs curveOn 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: Holding Costs & Transactions CostsNow 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. Total CostsWe saw we can solve the EOQ problem graphically. We can also use directly the mathematical solution. Here is the EOQ formula: Economic Order Quantity formulaWe have those 3 parameters:
EOQ formula: tutorial in ExcelWe will now see examples in Excel. You can simply download the EOQ CALCULATOR HERE: Let’s take an example with Nike shoes. DemandFor 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 costThe 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:
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. Holding CostsThe total Holding Costs for our Nike shoes are $2.85, which represents 9.5% of the purchase price. Transaction costsThe 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: Transaction CostsYou 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 FormulaNow, we can apply the formula: D =
Demand = 12 000 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 exampleWe 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 formulaThe EOQ formula has some limits. The main problem is that we consider all parameters are constant. Limit 1: Unstable demandOne 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 priceAnother 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 costsThe 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 timeThe 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 StockFinally, 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. ConclusionThe 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:
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:
EOQ: Your Action Plan
Become an Inventory Management ExpertIf 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”. Workshop with Edouard Thieuleux, Supply Chain ExpertDuring this webinar, I will share with you: 👉 My method for dealing with sales and supplier uncertainty: 13 parameters to master Founder of AbcSupplyChain | Supply Chain Expert | 15 years experience in 6 different countries –> Follow me on LinkedIn How do you calculate the number of orders per year using EOQ?A number of orders per year = Annual quantity demanded/ EOQ.
How do you calculate the number of orders in a year?The number of orders in a year = Expected annual demand/EOQ. Total annual holding cost = Average inventory (EOQ/2) x holding cost per unit of inventory.
What is EOQ and its formula?The EOQ formula is as follows. EOQ = Square root of [(2 x demand x ordering cost) / carrying cost] Demand. The demand remains constant according to the assumptions made by EOQ. The demand is how much inventory is used per year or how many units are sold per year.
How do you write EOQ formula?Economic Order Quantity is Calculated as:. Economic Order Quantity = √(2SD/H). EOQ = √2(10 million) (100 million)/10 million.. EOQ = √200.. EOQ = 14.142.. |