Question

A company can decide how many additional labor hours to acquire for a given week. Subcontractor workers will only work a maximum of 25 hours a week. In 1 hour of work, worker 1 can produce 20 units of product A, 18 units of product B, and 25 units of product C. Worker 2 can produce 15 units of product A, 20 units of product B, and 18 units of product C. Worker 3 can produce 16 units of product A, 17 units of product B, and 22 units of product C. The company must produce at least 300 units of product A, 250 units of product B, and 200 units of product C. Worker 1 demands a salary of $90/hr, worker 2 demands a salary of $50/hr, and worker 3 demands a salary of $40/hr. The company must choose how many hours they should contract with each worker to meet their production requirements and minimize labor cost.

(a) Define your decision variables.

(b) Write the mathematical model that can be used to solve this problem (must provide the objective function and constraints mathematically). Note that simply providing the Excel solver input will not earn any credit.

(c) Solve the problem using Excel Solver. Make sure that I can see the Excel Solver input for each question. Otherwise, you will not earn any credit.

Answer 1

(a)

Let Worker-1, 2, and 3 work for W1, W2, and W3 hours per week respectively.

W1, W2, and W3 are the decision variables.

(b)

Min Z = 90 W1 + 50 W2 + 40 W3

Subject to,

20 W1 + 15 W2 + 16 W3 >= 300 (Min product-A)

18 W1 + 20 W2 + 17 W3 >= 250 (Min product-B)

25 W1 + 18 W2 + 22 W3 >= 200 (Min product-C)

Wj <= 25 for j=1,2,3

Wj >= 0 for j=1,2,3

(c)

Solution:

	W1	W2	W3
Value of	0	0	18.75
Cost	$90	$50	$40	$750
s.t.
Product A	20	15	16	300.00	>=	300
Product B	18	20	17	318.75	>=	250
Product C	25	18	22	412.50	>=	200
Max W1	1			0	<=	25
Max W2		1		0	<=	25
Max W3			1	18.75	<=	25