Cara menghitung eoq di excel

Economic Order Quantity Formula (Table of Contents)

• Economic Order Quantity Formula
• Economic Order Quantity Calculator
• Economic Order Quantity Formula in Excel (With Excel Template)

Economic Order Quantity or EOQ can be defined as the optimum level of quantity and frequency of orders for a particular level of demand.

Here’s the Economic Order Quantity formula –

where: –

• S: Ordering Cost or Fixed Cost
• D: Annual Quantity Demanded
• H: Holding Cost or Variable Cost

Example of Economic Order Quantity Formula

Let’s take an example to find out the Economic Order Quantity for a company: –

Economic Order Quantity Formula – Example #1

For a company X, annual ordering costs are $10000 and annual quantity demanded is 2000 and holding cost is $5000.

Economic Order Quantity is Calculated as:

• Economic Order Quantity = √(2SD/H)
• EOQ = √2(10000)(2000)/5000
• EOQ = √8000
• EOQ = 89.44

Economic Order Quantity Formula – Example #2

Let’s say for Hindustan Unilever Ltd. it wants to determine economic order quantity for its operations to minimize inventory costs and better cash flow management.

The annual ordering costs are Rs 10 million while the quantity demanded is 100 million. The holding costs are Rs 10 million.

Economic Order Quantity is Calculated as:

• Economic Order Quantity = √(2SD/H)
• EOQ = √2(10 million) (100 million)/10 million
• EOQ = √200
• EOQ = 14.142

Hence the ideal order size is 14.142 to meet customer demands and minimize costs. It is also the reordering point at which new inventory should be ordered.

Economic Order Quantity Formula – Example #3

Let’s say for Maruti Suzuki Limited, it wants to determine the economic order quantity for its operations to minimize inventory costs and better cash flow management.

The annual ordering cost is Rs 25 Crores while quantity demanded is 1 Crore while holding costs is Rs 10 Crore.

Economic Order Quantity is Calculated as:

• Economic Order Quantity = √(2SD/H)
• EOQ = √2 (25 Crore) (1 Crore)/(10 Cr0re)
• EOQ = √5
• EOQ = 2.2360

Hence the ideal order size is 2.2360 to meet customer demands and minimize costs. It is also the reordering point at which new inventory should be ordered.

Explanation of Economic Order Quantity Formula

Economic Order Quantity is used to determine the optimum level of quantity and frequency of orders for a given level of demand. The optimum level is determined while minimizing the cost per order. The key components of the Economic Order Quantity formula are: –

• D: Annual Quantity Demanded
• Q: Volume per order
• S: Ordering Cost or Fixed Costs
• i: Interest rate
• C: Unit cost or variable cost
• H: Holding Cost or Variable Costs

The individual components of the formula need to be understood before determining the Economic Order Quantity. The Economic Order Quantity is used to minimize the costs of the order. As we all know, the total costs include the Fixed Cost and Variable costs. These costs include the purchase costs from the supplier or vendor, ordering costs and carrying costs.

Number of orders – Number of orders is determined by dividing annual quantity demanded with a volume per order.

Number of orders = D / Q

Ordering Cost (S) – Ordering Cost is the fixed cost associated with each unit and is independent of the number of units.

Annual Ordering Cost = Number of orders x S = D/Q x S

Holding Costs – Holding costs are the variable costs associated with storing the products in inventory. Holding costs would also be defined as opportunity costs of investing the money somewhere else rather than putting it in buying inventory.

Holding Cost per Unit = i x C

It is assumed that the demand is constant hence the quantity of inventory depletes constantly over time until it reaches zero after which new order is placed to replenish inventory. Hence annual holding cost can be calculated as below: –

Annual Holding Cost = Q/2 x H

Summing the annual ordering cost and annual holding cost will give us the total cost of orders annually.

Total Annual Cost (TC) = D/Q x S + Q/2 x H

Economic Order Quantity can be found out by taking the first derivative with respect to Q and hence

EOQ = dTC/dQ = √ (2SD/H)

Significance and Use of Economic Order Quantity Formula

Economic Order Quantity is one of the important tools used by companies in cash flow planning for minimizing the cost of inventory or decreasing the amount of cash held up in inventory. This is because for some companies such as FMCG companies have inventory as their largest asset and they need to be properly planned such that sufficient inventory is present for catering to customers but at the same time, it should be minimized so that cost savings can happen for the company. The Economic Order Quantity helps in estimating that level of inventory.

Economic Order Quantity also has various other uses one of which is estimating the reordering point or the point at which an order needs to be placed for more inventory. This is because a reorder needs to happen before inventory runs out otherwise it would lead to a loss of revenue for the company.

Economic Order Quantity Calculator

You can use the following Economic Order Quantity Calculator

Economic Order Quantity Formula in Excel (With Excel Template)

Here we will do the same example of the Economic Order Quantity formula in Excel. It is very easy and simple. You need to provide the three inputs i.e Ordering Cost or Fixed Cost, Annual Quantity Demanded and Holding Cost or Variable Cost

You can easily calculate the Economic Order Quantity using Formula in the template provided.

Conclusion

Economic Order Quantity is defined as the optimum level of quantity and frequency of orders for a particular level of demand. Economic Order Quantity uses ordering costs and holding costs to determine the certain level of orders required. It is used by companies in cash flow planning, minimizing the cost of inventory and also estimating the reordering point for a company.

Recommended Articles

This has been a guide to an Economic Order Quantity formula. Here we discuss its uses along with practical examples. We also provide you with Economic Order Quantity Calculator with downloadable excel template. You may also look at the following articles to learn more –

1. Formula for Price to Book Value
2. Formula for ROA Equity
3. How to Calculate Perpetuity Formula?
4. Calculate Dividend Payout Ratio Using Formula