The Woodstock Appliance Company carries four products. The annual demands for these

products range from 300/year for a high-end vacuum cleaner to 30,000/year for a table

fan. The order costs, holding costs, and purchase costs, as well as how much space each

product occupies, are known for each of the four products. The numerical information is

summarized in the following table.

Product 1 2 3 4

Annual Demand (D) 5,000 10,000 30,000 300

Order Cost (S) $400 $700 $100 $250

Holding Cost Rate 10% 10% 10% 10%

Purchase Price (P) $500 $250 $80 $1,000

Space/unit 12 25 5 20

Woodstock rents a warehouse in NJ to store and distribute its inventory of the four

products. The rent is $7 per square feet per year. The warehouse manager asks you to

develop a “simple” inventory control policy (i.e., the order quantities/cycles) and

determine how much storage space to rent. Assume that the order cycle for each product

is at least one week.

Hints:

1. H=Holding cost rate*Purchase price;

2. For each product, annual inventory cost= annual ordering + annual holding costs;

3. Total inventory cost=sum of annual inventory costs of the four products;

4. Storage space=12Q1+25Q2+5Q3+20Q4, where Q’s are order sizes for the four

products. Use Excel function “sumproduct”;

5. Total cost=total inventory cost + annual rent of storage space;

6. Modify the EOQ model by incorporating four products and the storage space

requirement/cost;

7. Since the total cost function is nonlinear, pick the algorithm option of “GRG

nonlinear” in Solver;

8. Use Excel Solver and play various scenarios;

9. Make sure that the inventory control policy is “simple” (i.e., order cycles are in

integer weeks).

You need to develop three excel spreadsheet models.

I. Try EOQ formula for each product and calculate inventory costs, storage space

needed and total cost;

II. Develop an optimization model and use Solver to determine order quantities and

rental storage space (minimization of total cost);

III. Calibrate what you got in Model II to obtain a “simple” inventory control policy

and determine order quantities/cycles and how much storage space to rent (keep

the total cost close to what you get in II).