Question

Expedition manufacturers a variety of specialty clothing for skiing and mountain climbing. The company has decided to begin production on two new parkas designed for use in extremely cold weather: the Mount Everest Parka and the Rocky Mountain Parka. Expedition’s manufacturing plant only has 120 hours of cutting time and 140 hours of sewing time available. There is plenty of material to make the coats, except the fact that the zipper union is on strike and there are only 340 zippers. Because management believes that the Mount Everest Parka is a unique coat that will enhance the image of the firm, management has specified that at least 80 of this model must be produced. Each Mount Everest Parka requires 30 minutes of cutting time and 45 minutes of sewing time; the Labor and Material cost is $150 and the retail price is $250. Each Rocky Mountain Parka requires 20 minutes of cutting time and 15 minutes of sewing time; the Labor and Material cost is $50 and the retail price is $200. How many units of each model should be produced?

Using Solver in Excel, how do you put this in?

Answer 1

Let the quantities of Mount Everest Parka to be manufactured be “ME” and that of Rocky Mountain Parka to be manufactured be “RM”.

Profit from 1 Mount Everest Parka = 250-150 = $100 and profit from 1 Rocky Mountain Parka = 200-50 = $150

Thus objective function will be: 100ME + 150RM. This has to be maximized.

Constraints are:

(i): 30ME+20RM <=120*60 OR 30ME+20RM<=7200

(ii): 45ME+15RM<=140*60 OR 45ME+15RM<=8400

(iii): ME+RM<=340

(iv): ME>=80

Solving the above in excel using the solver function we get the following solution:

ME	80
RM	240
				Formula
Objective function	44,000			100ME + 150RM
Constraints
7,200	<=	7,200		30ME+20RM<=7200
7,200	<=	8,400		45ME+15RM<=8400
320	<=	340		ME+RM<=340
80	>=	80		ME>=80

Thus the company should make 80 units of Mount Everest Parka and 240 units of Rocky Mountain Parka. The profit will be $44,000

Excel's solver image: