Excel based inventory management templates include: Economic order quantity, EOQ with backorders, EOQ with quantity discounts, EOQ for production lot sizes, and Reorder points and safety stocks (ROP).

Economic order quantity (EOQ)

The purpose of the EOQ model is simple, to find that particular quantity to order which minimizes the total variable costs of inventory.  Total variable costs are usually computed on an annual basis and include two components, the costs of ordering and holding inventory.  Annual ordering cost is the number of orders placed times the marginal or incremental cost incurred per order.  This incremental cost includes several components:  the costs of preparing the purchase order, paying the vendor's invoice, and inspecting and handling the material when it arrives.  It is difficult to estimate these components precisely but a ball‑park figure is good enough.  The EOQ is not especially sensitive to errors in inputs.

Why don't we include the purchase cost of the inventory in this calculation?  It is not a variable cost.  We have to purchase the inventory in any event.  The problem is to minimize those costs that vary with the quantity purchased at one time.

EOQ with backorders (EOQBACK)

Backorders are common in inventories held for resale to customers.  The EOQ model can be modified to handle backorders by including one more cost, the cost per unit backordered.  This cost is extremely difficult to assess.  In theory, the backorder cost should include any special cost of handling the backorder, such as the use of premium transportation, and any cost associated with loss of customer goodwill.  As a surrogate for the true backorder cost, most companies use the profit per unit.

The backorder model works well for Ryan because financing the inventory is so expensive.  It is much less expensive to incur backorders and fill them when the EOQ arrives than it is to hold inventory.  Of course, this is a risky policy and the backorder model must be used with caution.  The model assumes that customers are willing to wait on backorders and are not lost to the company.  If customers are lost, then the model is inappropriate.  There are other models that account for lost customers but they are rarely used in practice because of the risks involved.

EOQ with quantity discounts (EOQDISC)

Many companies blindly purchase in large quantities to get discount prices without considering all the tradeoffs involved.  The costs may well outweigh any savings in purchase price.  The EOQDISC model helps you analyze quantity discount offers and make better purchasing decisions.

If you are in a position to bargain with the vendor on prices, use EOQDISC to find the breakeven price.  This is the price at which you are indifferent about taking the discount because the annual costs for the standard price and the discount price are the same.  You can use the breakeven price as the starting point for negotiating a better discount plan, one that benefits both vendor and customer. 

Vendors sometimes offer several different price/quantity plans, so EOQDISC is set up to evaluate four discount plans at once. 

EOQ for production lot sizes (EOQPROD)

Alvin Air Systems in Alvin , Texas , produces repair and maintenance items for commercial air conditioning systems.  Alvin cannot use the standard EOQ for determining production lot sizes.  One problem is that the standard EOQ assumes that an order quantity or lot of material is received all at once.  At Alvin , most production lots take some time to complete and parts are placed in stock on a daily basis until the run is over.  A related problem is that substantial sales usually occur before the run is over.  To solve these problems, Alvin uses the modified EOQ model for production lot sizes.  Why?  The EOQ must be larger than maximum investment to account for sales made during the production run. 
Vendors sometimes offer several different price/quantity plans, so EOQDISC is set up to evaluate four discount plans at once. 

Reorder points and safety stocks (ROP)

When demand is uncertain, inventory investment from the EOQ model should be supplemented with safety stock.  ROP.xls is a worksheet used to compute safety stocks and reorder points.

Inventory Management

$25.00  Now Only $22.50   Purchase Now

Multiple User License

Full Product List