Economic Order Quantity (EOQ) definition and Excel formula

Economic Order Quantity (EOQ), definition and formula


By Joannes Vermorel, January 2012

EOQ is the backorder quantity for replenishment that minimizes total inventory costs. The backorder is triggered when the inventory level hits the reorder point. The EOQ is calculated in order to minimize a combination of costs such as the purchase cost (which may include volume discounts), the inventory holding cost, the ordering cost, etc. The order quantity optimization is complementary to the safety stock optimization that focuses on finding the optimal threshold to trigger the reorder.

Model and formula

The classical EOQ formula (see the Wilson Formula section below) is essentially a trade-off between the ordering cost, assumed to be a flat fee per order, and inventory holding cost. Although this formula dating for 1913 is extremely well-known, we believe that it is essentially a poor fit to modern supply chain. First, because of electronic orders (frequently carried through EDI) that have vastly lowered the order cost. Second, because the primary driver for order tuning are price breaks (i.e. quantity discounts) which are not accounted for.

Download Excel sheet: eoq-calculator.xlsm (illustrated calculation)

Thus, we propose here an EOQ formula variant that optimizes the trade-off of carrying costs vs volume discounts. Let's introduce the variables:
  • $Z$ be the lead demand.
  • $H$ be the carrying cost per unit for the duration of the lead time (1).
  • $\delta$ be the delta inventory quantity needed to reach the reorder point (2).
  • $\mathcal{P}$ be the per unit purchase price, a function that depends on the order quantity $q$.

(1) The time scope considered here is the lead-time. Hence, instead of considering the more usual annual carrying cost $H_y$, we are considering $H = \frac{d}{365}H_y$ assuming that $d$ is the lead time expressed in days.

(2) The delta quantity needs to take into account both the stock on hand $q_{hand}$ and the stock on order $q_{order}$, which gives the relationship $\delta = R - q_{hand} - q_{order}$ where $R$ is the reorder point. Intuitively, $\delta+1$ is the minimal quantity to be ordered in order to maintain the desired service level.

Then, the optimal order quantity is given by (the reasoning is detailed below): $$Q = \underset{q=\delta+1..\infty}{\operatorname{argmin}}\left(\frac{1}{2}(q-\delta-1)H+Z\mathcal{P}(q)\right)$$ Despite it's seemingly complicated look, this function can be easily computed with Microsoft Excel, as illustrated by the sheet provided here above.

What about the order cost?

At first glance, it might look as if we are assuming a zero ordering cost, but not quite so. Indeed, the framework we introduce here is relatively flexible and the order cost (if any) can be embedded into the price function $\mathcal{P}$.

Cost function

In order to model the cost function for the order quantity which takes into account volume discounts, let's introduce $R$ the reorder point. The inventory cost is the sum of the inventory carrying cost plus the purchase cost, that is: $$C(q)=\left(R+\frac{q-\delta-1}{2}\right)H+Z\mathcal{P}(q)$$ Indeed, taking an amortized viewpoint over the lead time period, the total quantity to be ordered will be $Z$ the lead demand.

Then, the inventory level is varying all the time, but if we consider strict minimal reorders (i.e. $q=\delta+1$) then, the average stock level over time is equal to $R$ the reorder point. Then, since we are precisely considering order quantity greater than $\delta+1$, those extra ordered quantities are shifting upward the average inventory level (and also postponing the time when the next reorder point will be hit).

The $(q-\delta-1)/2$ represents the inventory shift caused by the reorder assuming that the lead demand is evenly distributed for the duration of the lead time. The factor 1/2 is justified because an increased order quantity of N is only increasing the average inventory level of N/2.

Minimization of the cost function

In order to minimize $C(q)$, we can start by isolating the part that does not depends of $q$ with: $$C(q)=RH+\frac{1}{2}(q-\delta-1)H+Z\mathcal{P}(q)$$ Since $RH$ does not depend on $q$, optimizing $C(q)$ is the same as optimizing $C^*(q)$ where: $$C^*(q)=\frac{1}{2}(q-\delta-1)H+Z\mathcal{P}(q)$$ Then, in this context, since the volume discount function $\mathcal{P}$ is an arbitrary function, there is no direct algebraic solution to minimize this formula. Yet, it does not imply that this minimization is hard to solve either.

A simple minimization for $C^*(q)$ consists of a (naive) extensive numerical exploration, that is computing the function for a large range of $q$ values. Indeed, virtually no business is needing order quantities greater than 1,000,000 units, and letting a computer explore all costs values for $q=1..1,000,000$ takes less 1 second even if the calculation is done within Excel on a regular desktop computer.

However, in practice, this computation can be vastly accelerated if we assume that $\mathcal{P}(q)$ is a strictly decreasing function, that is to say that the price per unit strictly decreases when the order quantity increases. Indeed, if $\mathcal{P}(q)$ decreases, then we can start the value exploration at $q=\delta+1$, iterates, and finally stop whenever the situation $C^*(q+1)>C^*(q)$ gets encountered.

In practice, unit price rarely increases with quantities, yet, some local bumps in the curve may be observed if shipments are optimized for pallets, or any other container that favors certain package sizes.

In the Excel sheet attached here above, we are assuming the unit price to be strictly decreasing with the quantity. If it is not the case, then edit the macro EoqVD() to revert back to a naive range exploration.

Wilson Formula

The most well-known EOQ formula is the Wilson Formula developed in 1913. This formula relies on the following assumptions:

  • The ordering cost is flat.
  • The rate of demand is known, and spread evenly throughout the year.
  • The lead time is fixed.
  • The purchase unit price is constant i.e. no discount is available.

Let's introduce the follow variables:

  • $D_y$ be the annual demand quantity
  • $S$ be the fixed flat cost per order (not a per unit cost, but the cost associated to the operation of ordering and shipping).
  • $H_y$ the annual holding cost.

Under those assumptions, the Wilson optimal EOQ is: $$Q=\sqrt{\frac{2D_yS}{H_y}}$$ In practice, we suggest to use a more locally adjusted variant (time-wise) of this formula where $D_y$ is replaced by $D$ the forecast demand rate for the duration of the lead time (aka the lead demand $Z$ divided by the lead time), and where $H_y$ is replaced by $H$, the carrying cost for the duration of the lead time.

Comparison of the two EOQ formula

For retail or wholesale, we believe that our ad-hoc EOQ formula presented at the top of this page, that emphasizes volume discounts is better suited, hence more profitable, than the Wilson formula. For manufacturers, it depends. In particular, if the order triggers a new production, then indeed, there might be a significant ordering cost (production setup) and little or no benefits in marginal unit cost afterward. In such a situation, the Wilson Formula is more appropriate.

Get optimized sales forecasts with our webapp Salescast. Lokad specializes in inventory optimization through demand forecasting. Optimized replenishments - and much more - are native features of Salescast.