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).
