Question

Assume that due to some human resources problems, Frostburg Wires has to reduce the capacity of one of its four working stations by 1500 hours. Which working station can Frostburg Wires choose in order to minimize the impact of this change on its profit? USE EXCEL SOLVER

HERE IS ORGINIAL SCENARIO:

			Decision Variables
			W045C (A)	W023C (B)	W005X (C)	W007X (D)
			2000	0	100	0
Objective Function	Max 34a +30b+60c+ 25d	Profit Per Unit	$            34	$            30	$            60	$            25	$     74,000
							Available Hours
Constraints	1a+2b+0c+1d<=4000	Drawing	1	2	0	1	2000	<=	4000
	1a+1b+4c+1d <=4200	Extrusion	1	1	4	1	2400	<=	4200
	1a+3b+0c+0d<=2000	Winding	1	3	0	0	2000	<=	2000
	1a+0b+3c+2d<=2300	Packing	1	0	3	2	2300	<=	2300
										Frostburg Wires produces four types of wires shown as W045C, W023C, W005X and W007X. To produce wire, 4 manufacturing stages, namely drawing, extrusion, winding and packaging is required. April orders for Frostburg Wires is as follows: Product Units Ordered W045C 1,400 W023C 250 W005X 1,510 W007X 1,116 The cost of producing one unit of each product as well as its selling price is as follows: Product Material ($) Labor ($) Overhead ($) Selling Price ($) W045C 33.00 9.90 23.10 100.00 W023C 25.00 7.5 17.50 80.00 W005X 35.00 10.50 24.50 130.00 W007X 75.00 11.25 63.75 175.00 Each product unit needs a certain amount of time (in hours) at each production stage as follows: Product Drawing Extrusion Winding Packaging W045C 1.0 1.0 1.0 1.0 W023C 2.0 1.0 3.0 0.0 W005X 0.0 4.0 0.0 3.0 W007X 1.0 1.0 0.0 2.0 Plant capacity for each stage of production in terms of hours available is as follows: Drawing Extrusion Winding Packaging 4,000 4,200 2,000 2,300 Assume that Frostburg Wires can fulfill an order fully or partially. Having this information answer the following questions.

Answer 1

the solution presented in the original scenario could not have been correct. The optimum quatity of W045C (A) to be produced as per the solution is 2000 to maximize the profit. But the order for W045C (A) is only 1400. You may not produce more than your order as you will not be able to sell it (the problem states that "Frostburg Wires can fulfill an order fully or partially", that means you can produce less than or equal to the order quantity , but no mention of more than the order. )

This is due to the missing constraints

The constraints on quatities A,B, C and D are needed, so that we do not produce more than the order

The model need to be restated as

Let a,b,c , d be the quantties to be produced of parts W045C , W023C, W005X, W007X respectively.

Maximize

s.t

• 
• 
• 
• 
• 
• 
• 
• 
• 

We set up the excel as

which looks like

Set up the solver constraints using data-->solver

and get the profit maximizing solution as

the quatities at max profit of $71,600 is

W045C (A)	W023C (B)	W005X (C)	W007X (D)
1400	200	300	0

Now we produce 1400 of W045C, thus meeting 100% of the order, and then we produce W005X and W023C

Now to the question

Frostburg Wires has to reduce the capacity of one of its four working stations by 1500 hours. Which working station can Frostburg Wires choose in order to minimize the impact of this change on its profit?

Let us look at the utilization of work stations for this solution

using the following portion

we can construct

Work station	Available Hours	Utilized Hours	Free Hours
Drawing	4,000	1800	2,200
Extrusion	4,200	2800	1,400
Winding	2,000	2000	0
Packaging	2,300	2300	0

We see that Drawing stage has an excess capacity of 2,200 hours. That means Frostburg Wires can reduce the capacity of drawing by 1500 to 2500 hours and not impact the profit. They could also have split thsi reduction between Drawing and Extrusion but the result is the same as they are constrained by the lack of capacity in windinhg and packaging.

The new constraint are

The optimum solution remains the same