How to Calculate Economic Order Quantity in Excel

EOQ, or Economic Order Quantity, is a mathematical model used in inventory management to determine the optimal order quantity for a company to minimize inventory holding costs and ordering costs. By using EOQ, companies can strike a balance between the costs of holding too much inventory and the costs of ordering too frequently, ultimately maximizing profits. In this article, we will show you how you can calculate economic order quantity in Excel.

Implementing EOQ can bring several benefits to companies. For instance, it can help reduce inventory costs and improve cash flow by reducing the amount of capital. EOQ can also help companies improve customer service by ensuring that the right products are always in stock, reducing the risk of stockout and lost sales.

Moreover, it can streamline the ordering process, reducing administrative costs and allowing companies to focus on other areas of their business. You can decide more effectively how much product to order in a specific time frame by calculating EOQ in Microsoft Excel.

What Is Economic Order Quantity?

It means the optimum level of order quantity, not higher and not lower either. Economic Order Quantity (EOQ) is a calculation that businesses use to figure out how much they should order at once. This helps them avoid having too much inventory, which could cost them more money and take up unnecessary space. By calculating EOQ, inventory managers can find the right balance between meeting demand and keeping costs under control.

It makes no difference if your company sells airplanes, jelly beans, appliances, or furniture. Finding the economic order quantity for any product you buy will probably have an impact on the revenue. Measuring and adhering to the EOQ can be beneficial for any company that controls inventory.

Here,

Ordering Cost: Ordering cost is the cost associated with placing and receiving an order for inventory or goods. It includes all the expenses involved in the procurement process such as the cost of paperwork, transportation, communication, and processing of the order. Ordering costs can also include costs associated with the selection of suppliers, negotiating prices, and managing relationships with suppliers.

Holding Cost: Holding costs are the expenses incurred when merchandise remains unsold. These costs, together with ordering and shortage charges, are a component of overall inventory costs.

Calculate Economic Order Quantity in Excel: Step-by-Step Procedures

Now, we are going to show you how to calculate economic order quantity in Excel with 4 easy steps.

Step 1: Create a Dataset with Proper Parameters

• First, Create a dataset like the picture below

The dataset includes input variables such as demand, volume per order, ordering cost, unit cost, holding cost, and carrying cost. These inputs are used to calculate EOQ.

Step 2: Determine the Demand

• Here, For your upcoming sales, determine the demand. Depending on the products or services, it may differ from company to company.
• After That, Put the value of the demand on cell C4

Step 3: Calculate Holding Costs

“Holding cost” means the total carrying or transportation cost and total inventory cost of a product.

For calculating the holding cost we have to multiply unit cost and carrying cost. Here, cells no C7 and C9 indicate the unit cost and carrying cost respectively. The following image shows the value of holding costs.

Final Step: Determine Economic Order Quantity

• First of all, we have to calculate the number of batches needed for a year. In our datasheet, to obtain the annual ordering cost and holding cost we need a total batch number. For calculating the Batch Number we have to divide the annual Demand by the Volume per Order. In the datasheet, cell C4 shows total Demand and cell B12:B21 means Volume per Order.
• So, we got the batches needed per year for the corresponding Volume per Order

Now, We have to determine the total ordering cost

For that, we simply  multiply “Batches per Year” and “Ordering Cost”(S)

Eventually, we will get the “Ordering cost” for each “Batches per Year”

If you go through the chart you will that the Ordering Cost is decreasing for the higher volume per order.

For Holding Costs, use the following formula for cell E12

Just sum up the corresponding Ordering and Holding Costs to calculate the Total Cost

In this segment of the article, we will try to determine the EOQ point by using a Scatter diagram.

To do that, just select the  cells B12:F21 then go to

Insert  >>  Scatter Diagram

Then select the diagram like the image below.

A diagram will appear before you like the following image.

If you take a close look at the diagram you’ll notice that the line of Ordering costs and holding costs intersect with each.

That intersecting point is the EOQ point. In the X-axis the value of the intersecting point is 400.

So, the Economic Order Quantity of this dataset is 400.

Alternative Way

If you are using a minimal dataset. Then you can use the following formula

Now,

Select cell F4 and enter the formula

The outcome is 400.

Frequently Asked Questions

• What is the objective of EOQ?

The objective of EOQ (Economic Order Quantity) is to determine the optimal order quantity that minimizes the total cost of inventory management. The model takes into account both the cost of holding inventory (i.e., storage costs, capital costs, obsolescence costs) and the cost of ordering (i.e., setup costs, and shipping costs).

• What causes EOQ to decrease?

EOQ will increase as the annual demand and the cost of ordering increase and it will decrease as the cost of carrying inventory and the unit cost increase.

• Is high EOQ good?

A high EOQ (Economic Order Quantity) is not necessarily good or bad on its own. The optimal EOQ will depend on a variety of factors such as the cost of holding inventory, the cost of ordering, and the demand for the product.

Things to Remember

Use consistent units: Make sure that all inputs such as demand, ordering costs, and holding costs are in the same units, such as dollars or units, to avoid errors in calculations.

Check for errors: Double-check your formulas and calculations to avoid errors. Use Excel’s built-in auditing tools such as trace precedents and dependents to ensure that all formulas are correct and properly linked.

By keeping these things in mind, you can calculate EOQ in Excel accurately and use the model to make informed decisions about inventory management that can help minimize costs and maximize profits.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

This lesson covers the complete process of computing the EOQ calculation in Excel. Now that you are familiar with all of the approaches, we hope you will use them efficiently on your Excel spreadsheets. Please use the box below to provide any comments or questions. You can also browse ExcelDemy’s other articles on Excel functions.

Related Articles

• How to Keep Track of Inventory in Excel
• How to Maintain Store Inventory in Excel
• How to Make Inventory Aging Report in Excel
• How to Create Inventory Database in Excel
• Min Max Inventory Calculation in Excel
• How to Calculate Stock to Sales Ratio Using Formula in Excel

<< Go Back to Inventory Management in Excel | Learn Excel