Excel Companion Chapter 5 section 6
ECONOMIC ORDER QUANTITY EOQ.xls

Open the file EOQ.xls

SITUATION 1: Over the course of a year, a large retail store orders 4500 units of a product at a unit cost of $250. Consumer demand for the product is uniform over the year. The retail store does not acquire all 4500 units at the beginning of the year. Regular size orders are placed throughout the year. Each time an order is placed, there is a fixed ordering cost of $75. If the size of the regular order ( or lot) is Q then there will be orders placed at a cost of $75 per order. Each new order is delivered when the inventory reaches zero. Carrying costs per unit for the inventory on hand is assumed to be 5% of the unit cost of the product. If Q is the size of a regular order then we will assume that an average of Q/2 units are held in inventory at an associated carrying cost of (.05)(250). The total annual cost is made up of three parts:

base cost + ordering cost + holding cost. In this situation, total cost as a function of the size of the regular order is: . The retailer's objective is to minimize total cost. She wants to know the optimal size of the order that will achieve this objective. Since base cost of $1,125,000 is a constant we can focus on the ordering cost plus the holding cost as a function of Q:

Given this ordering and holding cost function, we can compare the cost of just a few large orders with more frequent but smaller orders. If the size of the order is 1,500 units then the ordering cost would be only $225. The annual carrying costs would be $9,375 for a sum of $9600. If the size of the order is 150 units then the cost of thirty purchase orders would be $2250 but the carrying costs would drop to $937.50 for a sum of $3,187.50. The second ordering strategy results in a significantly lower total for ordering plus holding.



The graph above suggests that the optimal value of Q occurs somewhere between 200 and 300. A precise theoretical minimum value of 232.38 can be verified using Solver or with calculus. The theoretical number of orders would be 19.365 which, of course, is impossible. If the company places 19 or 20 orders then the total cost will be within one or two dollars of the theoretical lowest cost.

Questions

1 Apply calculus to the parameterized ordering and holding cost function and verify that the theoretical optimal value occurs at Q =  
2 Find the value of Q for which ordering cost,

, is the same as holding cost, . How does this result relate to your answer to question 1?

3 - 5 Click on the tab for EOQ with Discount in the file EOQ.xls.
Suppose the annual demand for a product is 60 units and the fixed cost of a purchase order is $400. The base price of the unit is ordinarily $5,500 but the wholesaler offers a discount on the unit price to $5,400 if ten or more units are ordered each time. Carrying costs are estimated at $100 per unit. We will assume that an average of Q/2 units are held in inventory where Q is the order size. Total cost is base cost + ordering cost + inventory cost.
 
3.a) What is the theoretical total annual cost if the size of the regular order is Q=5?
b) What is the theoretical total annual cost if the size of the regular order is Q=20?
4. What is the annual cost if exactly four orders are placed.
5. If exactly two orders are placed each year then how close is the total cost to the theoretical minimum total cost?

Enter your name and ID here:

First name Last name ID

Return to Table of Contents Return to Chapter 5 Index


Copyright Joseph F. Aieta, Babson College 1997