EOQ Calculations in Excel

Last week in Operations Management we used Excel to calculate the Economic Order Quantity and graph Carrying Costs, Ordering Costs, and Total Costs.

The Economic Order Quantity or "EOQ" is the order size that
"minimizes" Total Costs. Any more or less and you are spending too much on ordering or too much on keeping inventory.

For example, in the Excel spreadsheet below, if you had an Annual Demand of 12000 units, Ordering Costs of $10 per order, and Holding costs of $4 per unit per year, the EOQ would be 245 units and Total Costs would be $980.00:



We used the following formula in Excel to calculate EOQ: =SQRT((2*B2*B3)/B4)

And the following formula to calculate Total Costs at this point: =$B$6/2*$B$4+$B$2/B6*$B$3

To create the graph, we used the following formulas and simply copied them over a range of 100 to 500 units.

Ordering Costs: =$B$2/D2*$B$3

Holding Costs: =D2/2*$B$4

Total Costs: =F2+G2

Looking at this chart, we can clearly see that our order size of 245 is indeed "optimal." Placing an order for more or less will result in a dramatic increase in Holding or Ordering costs.

The basic EOQ model can also be applied to production runs as well as adapted for quantity discounts.

Comments

Popular posts from this blog

Reliability Calculations in Excel

“How Are We Doing?” Efficiency, Utilization, and Productivity