EOQ Calculator Excel

This EOQ calculator can be used by a business to determine the optimum level of inventory units it should order from suppliers or, in the case of a manufacturing business, place in a production run.

The costs associated with inventory include both ordering and holding costs. Ordering costs such as the cost of clerical staff, transport, inspection or production set-up costs, depend on the number of orders placed and tend to increase as the quantity ordered decreases.

In contrast holding costs such as interest costs to fund working capital, warehousing, handling and insurance, depend on the level of inventory held and therefore tend to decrease as the quantity ordered decreases.

It can be seen that the ordering and holding costs move in opposite directions as the quantity ordered changes and therefore at some optimum level the total costs will be at a minimum. This optimum level is referred to as the economic order quantity or EOQ.

EOQ Calculator Formula

The formula used by the EOQ calculator can be stated as follows.

EOQ = (2 x D x K/h)1/2
Variables used in EOQ Formula
D = Total demand for the product during the accounting period
K = Ordering cost per order
h = Holding cost per unit

Using the EOQ Calculator

The Excel EOQ calculator, available for download below, can be used to calculate the quantity of inventory units which minimizes the total cost of ordering and holding the inventory.

EOQ calculator v 1.1
EOQ Calculator Preview

The calculator is used as follows.

Demand

Enter the unit demand for the product for the accounting period. The accounting period is typically one year but can be any length. It should be noted that the total cost calculated by the EOQ calculator will be for the same period.

Ordering Cost

Enter the ordering cost per order. The ordering costs include transport, administrative staff costs, receiving, and inspecting costs. For a manufacturing business the ordering costs are those associated with the setting up of a production run to manufacture the order quantity. Ordering costs should exclude the cost of the product itself.

Holding Cost

Enter the holding cost per unit. The holding costs include the interest cost on finance used to fund inventory working capital, warehousing, cost of administrative staff, insurance, obsolescence, deterioration and shrinkage. Holding costs should exclude the cost of the product itself.

The EOQ calculator determines the economic order quantity which is the optimum number of units a business should order with suppliers or, in the case of a manufacturing business, the batch size to be included in a production run.

The calculator also works our the total ordering and holding costs for the period at the economic order quantity.

EOQ Calculator Download

The EOQ worksheet is available for download in Excel format by following the link below.

The EOQ calculator is one of many financial calculators used in bookkeeping and accounting, discover another at the links below.

Notes and major health warnings
Users use this EOQ calculator Excel 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 economic order quantity calculator that you might use to carry out an inventory calculation in Excel. It is purely illustrative of a EOQ model 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 Excel EOQ inventory calculator, please let us know and we will try to fix it.
EOQ Calculator Excel November 3rd, 2017Team

You May Also Like


Related pages


accounting basics journal entriescash flow to creditorjob card format excel sheetmarkup calculator ukbank reconciliation journal entry examplesstock valuation formulaswhen accounts receivable are collectedaccrued interest accounting entrytransactional analysis made simplesuspense account balance sheetcredit note definition accountingdouble entry bookkeeping excelaccounting adjusting journal entriesinventory shrinkagehow to record payroll journal entriesperpetual inventory exampleaccounts receivable journal entry examplevolume variance analysisconsignor consigneepv growing annuityunearned revenue entrycost of good sold balance sheetbad and doubtful debts journal entryreplenishment of petty cashaccounts receivable turnover definitionperiodic inventory systemsjournal entry for bills receivablewhat are consumable goodscalculating factory overheadreorder point calculationunearned revenue debit or creditretained earnings meaningretained profits meaningjournal entries to record cash dividends are made on thedifferent types of journal entries in accountingdouble depreciation formulacorrecting entry accountingcalculate pmt formulafifo reservepetty cash excel templatemarkup and margin calculatorpercent markup on costcogs formula manufacturingprice markup calculatorhow to estimate salvage valueadjusting journal entries accountingthe eight steps of the accounting cycledepreciation of fixed assets journal entryaccounting for withholding taximputed interest calculation examplevendor statement reconciliationpmt function excelcar loan accounting entrypurchase accounting journal entriesdifference between markup and marginjournal entry for payment by chequesales ledger double entryformulas for simple and compound interesthow do you calculate quick ratiooverhead rate calculatorchart of accounts general ledgercalculate loan amount excelaccounts payable adjusting entrycash conversion calculatoryear end accounting entriesfuture annuity formulacalculating degree of operating leverageunearned revenue tax treatmentpayroll accrual entrycash to accrual conversion worksheetbookkeeping forms pdfhow to calculate effective annual interest ratehow to calculate the irr in excelworksheet of accountingconsignor and consignee meaningdepreciation of fixed assets journal entrya dividend preference for preferred stock means that