Multi-item Inventory Models Subject to Constraints

Abstract

The inventory models discussed in the previous chapters assumed a single item being managed. Inventory managers at warehouses and retail shops manage a large number of items in their inventory. Inventory decisions – such as timing of replenishment orders, determining order quantities – are not made for each item independently. Decisions are often made jointly for several items managed at one location. Such decisions may be constrained by either the value of inventory they can hold in stock, or the availability of space to stock the items in their warehouse, or some other similar scarce resource. In this chapter, we discuss multi-item inventory models that are subject to one or more resource constraints such as budget, space, or number of orders. We also discuss methods of treating inventory problems that are subject to more than one constraint. Later in this chapter, we discuss another point of interest to inventory managers – that of optimizing the inventory costs by replenishing jointly to derive the benefits of economies of scale.

Appendix 7A: Using GOAL SEEK to Determine Lagrangean Multiplier

An easier method to find θ, the Lagrangean multiplier, is to use the GOAL SEEK function in MS Excel. The procedure for (explained with reference to space constraint, with three products) this is as follows. Set up an excel spreadsheet with the columns as shown in Fig. 7.1.

Fig. 7.1

Input data setup

Next, create the following rows to compute the EOQ and the space requirement. The formula to compute EOQ for each of the three products is as given below:

Cell	Formula
K15	Round(SQRT((2*K8*K9)/(K10*K12)),0)
L15	Round(SQRT((2*L8*L9)/(L10*L12)),0)
M15	Round(SQRT((2*M8*M9)/(M10*M12)),0)

The formulae to compute the space requirement for each of the items are shown in Fig. 7.2. The formulae for these are as given below:

Fig. 7.2

GOAL SEEK – Computing EOQ and space requirement

Cell	Formula
K16	K15*K11
L16	L15*L11
M16	M15*M11

We are now ready to set up the GOAL SEEK area which needs to be set up as shown in Fig. 7.3.

Fig. 7.3

GOAL SEEK – Setting constraint

The formulae to be used in the GOAL SEEK area are given below. The last one is the total space requirement for all three products put together

Cell	Formula
K20	SQRT((2*$K$8*$K$9)/(($K$12*$K$10) + ($N20*$K$11)))
L20	SQRT((2*$L$8*$L$9)/(($L$12*$L$10) + ($N20*$L$11)))
M20	SQRT((2*$M$8*$M$9)/(($M$12*$M$10) + ($N20*$M$11)))
O20	((K20*K$11) + (L20*L$11) + (M20*M$11))/2

The GOAL SEEK function can be used as follows. Navigate to Data > What-if-Analysis > Goalseek in MS Excel when a GOAL SEEK window pops up. In the pop-up window, set the cell to O$20 (space constraint) and the value to 3500 (cubic feet) by changing the cell which has the θ Theta value. This is shown in Fig. 7.4

Fig. 7.4

GOAL SEEK window

The θ theta value (Lagrangean multiplier) that satisfies the constraint is displayed in cell N20.