Question

1. EOQ for Multiple Products: In another location, the distributor of the previous problem stocks four different items in common warehouse space. Each item is described by an annual demand rate, a fixed cost per order, a holding cost per year, a unit purchase cost, and a space requirement. The data in the following table describe the four products.

Item	1	2	3	4
Demand	5000	10,000	30,000	300
Fixed cost	400	700	100	250

Holding cost	50	25	8	100
Purchase cost	500	250	80	1000
Space (sq. ft)	12	25	5	10

1. Create an Excel Spreadsheet with Solver to answer the following questions.
2. Considering each product separately, as if it were independent from the others, what are the respective economic order quantities? What is the total annual cost of ordering, holding, and purchasing across the four products at these order quantities?
3. What is the minimum total annual cost for the four products if the average space taken up must be no more than 12,000 sq. ft?
4. What is the minimum total annual cost for the four products if the average space taken up must be no more than 12,000 sq. ft and the number of orders per year must be no more than 65?
5. In part (b), with a square-foot limit on storage space, what is the economic value of more space?
6. In part (c), what is the economic value of more space?

Answer 1

a) Excel Spreadsheet model is following:

Item	1	2	3	4
Demand (D)	5000	10000	30000	300
Fixed cost (A)	400	700	100	250
Holding cost (H)	50	25	8	100
Purchase cost (C)	500	250	80	1000
Space (sq. ft) (s)	12	25	5	10
Order quantity (Q)	=SQRT(2*B2*B3/B4)	=SQRT(2*C2*C3/C4)	=SQRT(2*D2*D3/D4)	=SQRT(2*E2*E3/E4)
					Total space
Average space (s*Q/2)	=B6*B8/2	=C6*C8/2	=D6*D8/2	=E6*E8/2	=SUM(B10:E10)
					# of orders
Number of orders =D/Q	=B2/B8	=C2/C8	=D2/D8	=E2/E8	=SUM(B12:E12)
Ordering cost =A*D/Q	=B3*B12	=C3*C12	=D3*D12	=E3*E12
Holding cost = H*Q/2	=B4*B8/2	=C4*C8/2	=D4*D8/2	=E4*E8/2
Purchasing cost =D*C	=B2*B5	=C2*C5	=D2*D5	=E2*E5
					Total Cost
Total cost =	=SUM(B12:B15)	=SUM(C12:C15)	=SUM(D12:D15)	=SUM(E12:E15)	=SUM(B17:E17)

b) Calculation of EOQ, annual cost of ordering, holding and purchasing costs is shown below.

c) Minimum cost ordering policy is determined by Solver as follows:

Order quantity of item 1 = 270

Order quantity of item 2 = 657

Order quantity of item 3 = 798

Order quantity of item 4 = 35

d) The optimal solution is following:

e)

With limitation on storage space, increase in total cost = 7743954-7743725 = $ 229

Without limitation, total additional space used = 13410-12000 = 1410 sqft

So, economic value of more space = 229/1410 = $ 0.16 per sqft