Project One
Goal: To learn how to calculate the cycle stock using Excel Solver and implement the cycle stock policy.
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).
Format and Structure for Team Projects
1. Your project must be word-processed using a standard font (e.g., TimesNewRoman 12) and double-spaced.
2. All charts, data sheets and tables (if any) should be done on the computer. Graphs and charts should have a title and properly labeled axes.
3. All pages must be numbered.
4. Order of pages:
- COVER SHEET-The first page of your project must be a cover sheet that includes your name(s), date, the title of your project and the percentage contribution of each team member's effort.
- EXECUTIVE SUMMARY-Briefly describe the problem, your solution and recommendation to managers.
- TABLE OF CONTENTS
- INTRODUCTION, PROBLEM, PURPOSE-This section should include the statements of the project problem and goal.
- FINDINGS- Formulate the quantitative model and conduct quantitative analysis using Excel.
- CONCLUSION-This is where you interpret your findings (what can you conclude or not conclude from your research) and recommend the solution to managers.
- REFERENCE-citations and websites referred if any.
- APPENDICES-attach anything technical or supplementary (e.g., spreadsheet models and explanations).
5. Hand in a hard copy of your project and send me the electronic version of your files, including excel spreadsheets and the project report.