This economic order quantity calculator can be used by a business to estimate the unit product quantity a business needs to order from suppliers or batch manufacture in order to minimize its total inventory costs.
Our post on economic order quantity and working capital discusses in more detail how the minimum inventory cost can be calculated and its relationship to working capital.
Using the Economic Order Quantity Calculator
By entering details relating to the demand for the product, ordering cost, product unit cost, and the holding cost % the economic order quantity calculator can be used to estimate the following.
- Economic order quantity (EOQ)
- Holding cost per unit
- Ordering cost
- Holding cost
- Total inventory cost
In addition the calculator provides a graphical representation of the three inventory costs demonstrating that the cost is minimized at the economic order quantity.
The economic order quantity calculator is used as follows.
1. Enter the Demand for the Period
Enter the unit demand for the product for the accounting period. The period can be of any length but is normally a month, quarter or year depending on the length of the accounting period under consideration. For example, if the demand for the product is 912 units a year then enter 912.
2. Enter the Ordering Cost
Enter the cost of placing one order with the supplier or for a manufacturer, enter the set up costs for a production run.
3. Enter the Product Unit Cost
Enter the cost for one unit of the product. For example is a product costs 75 to purchase from a supplier or to manufacture enter 75.
4. Enter the Holding Cost Percentage
The cost of holding one unit of product for the period is represented as a percentage of its unit cost. So for example if the period (defined in step 1) is a year, the product unit cost is 75 and the holding cost percentage is 40%, it will cost the business 30 (75 x 40%) to hold one unit of the product in inventory for the year.
Further details of the calculation of the holding cost can be found in our economic order quantity post.
The economic order quantity calculator calculates the holding cost per unit.
Economic Order Quantity (EOQ)
The economic order quantity model calculator calculates the EOQ based on the following formula.
EOQ = (2 x D x K/h)1/2
D = Total demand for the product during the accounting period
K = Ordering cost per order
h = Holding cost per unit
So for example if based on the values entered the calculator shows an EOQ of 76 it means that to minimize inventory ordering and holding costs the business should place orders with suppliers (or for a manufacturer produce in batches of) 76 units.
Inventory Cost for the Period at EOQ
The calculator calculates the ordering, holding and total inventory cost for the demand period if orders are placed by the business at the EOQ level.
In addition the calculator produces a graph of the three inventory costs over a range of unit quantities to demonstrate that as the number of units per order increases the ordering costs for the period decrease and the holding costs increase.
At the economic order quantity the ordering and holding costs are equal and the total inventory costs are minimized.
Economic Order Quantity Calculator Download
The economic order quantity calculator is available for download in Excel format by following the link below.
Users use this economic order quantity calculator at their own risk. We make no warranty or representation as to its accuracy and we are covered by the terms of our legal disclaimer, which you are deemed to have read. This is an example of an inventory calculator that you might use when estimating how to calculate the economic order quantity for a startup business financial projection. It is purely illustrative of an Excel economic production quantity calculator. This is not intended to reflect general standards or targets for any particular company or sector. If you do spot a mistake in the EOQ carrying cost calculator, please let us know and we will try to fix it.
About the Author
Chartered accountant Michael Brown is the founder and CEO of Plan Projections. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a BSc from Loughborough University.