Develop an optimization model and use solver

Assignment Help Operation Management

Reference no: EM131688996

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.

Reference no: EM131688996

Questions Cloud

Some of aligning criteria summarizing any pros and cons : Discuss one component in the selection process. Describe some of the aligning criteria summarizing any pros and cons with using them.

Determine the empirical classical or subjective probability : Is the 0.74 probability reported by the BBM Bureau of Measurement best classified as a priori classical probability, empirical classical probability.

Discuss people to engage in criminal behaviors unimpeded : viewing the chaos in the streets in the aftermath of Hurricane Katrina, altered their behaviors to conform to the norms of a new social reality

Describe what quantity of labor to demand : Addresses how your chosen organization determines what quantity of labor to demand and what events could shift the demand and supply of that labor.

Develop an optimization model and use solver : Develop an optimization model and use Solver to determine order quantities and rental storage space (minimization of total cost);

What is the mean of the probability distribution : Priority Mail is the U.S. Postal Service s alternative to commercial express mail companies such as FedEx. An article in The Wall Street Journal presented.

Weak access control policies : You have just been hired as an information security engineer for a large, multi-international corporation.

What are the options for developing the technology : What are the options for developing the technology, a business plan for a new business in the fast food industry

Government security and privacy regulations : When dealing with federal agency data breach policy and data breach notification policy. What laws should be considered?

User Account

All Pages