Question

A manufacturing company would like to maximize the profit of manufacturing four products.  The time in hours required for each product in each production department is tabled below, along with the profit for each product.

	Product 1	Product 2	Product 3	Product 4
Department A	25	20	30	15
Department B	12	10	10	15
Department C	8	7	5	6
Profit	$110	$90	100	95

2. Find the computer solution, including the ranging (sensitivity analysis) results, for Question 1 by using QM for Windows or Excel. Determine the optimal solution (including the decision variables and the slack/surplus variables) and optimal profit. Interpret the optimal solution and optimal profit.

3. Answer the following questions by using the ranging results (that is, the sensitivity analysis results) from Question 2. Do NOT solve the problem again by using any computer software or website.

(a) If the profit from a unit of Product 2 increases from $90 to $110, will the optimal number of units of the four products manufactured change? Will the total profit change? If they change, what will be the new total profit?

(b)The company has an opportunity to get some extra hours in Department A. What is the maximum price the company should pay for each additional hour in Department A, and how many hours should they get at that price?

(c)The company has an opportunity to get some extra hours in Department C. What is the maximum price the company should pay for each additional hour in Department C, and how many hours should they get at that price?

(d) Which department should obtain more hours to increase the profit most?

Please answer question #3

question #2 please show in excel.

Please answer each question thank you

Answer 1

2.

Spreadsheet model and solution using Solver is following:

EXCEL FORMULAS:

F3 =SUMPRODUCT(B3:E3,$B$10:$E$10) copy to F3:F5, F7

Optimal solution:

Product 1 = 0

Product 2 = 180

Product 3 = 0

Product 4 = 80

Total profit = $ 23,800

After running the Solver, click on Sensitivity report in following window :

Sensitivity report is following:

3.

(a) Refer sensitivity report, allowable increase for objective coefficient of product 2 is 36.

Therefore, if it is increased from 90 to 110, it is still within allowable limit and hence the optimal solution (i.e. product mix) remains unchanged.

Total profit will change. New total profit = 23800 + (110-90)*180 = 27,400

(b) Refer sensitivity report, shadow price of dept A hours is 2.67

Therefore, maximum price the company should for each additional hour in dept A is $ 2.67 per hour

Allowable increase for this constraint is 1200. Therefore, they should get an additional 1200 hours at this price.

(c) Shadow price of dept C hours is 0.

Therefore, the maximum price they should pay for each additional hour in dept C is 0.

In other words, it means that they should not pay for any additional hours in dept C.

Allowable increase is infinity, so, they can get as many additional hours in dept C at cost of $ 0. The optimal solution will remain unchanged.

(d) Shadow price of dept B hours is 3.67, which is the highest shadow price. Therefore, dept B should obtain more hours to increase the profit most. Each additional hour in dept B will increase the total profit by $ 3.67