Question

1. Suppose a small company is in the business of making golf bags to be sold at a local golf course where state championships are often held. They make 2 types of golf bags, a standard bag and a deluxe bag. The table below gives the information for limitations of materials and labor hours, and also for the profit for each type of bag. The materials and labor hours are the amount available per week, and the profit is the profit per week as well.

Units are per bag	Standard Leather (square feet)	Premium Leather (square feet)	Cutting and Sewing Labor hours	Finishing Labor Hours	Profit
Standard	7	1	3	1	25
Deluxe	0	9	4	2	65
Total available	210	180	120	40

1. Set up the problem in an excel worksheet, using the sandwich problem done in class as a template. The only difference will be that you have an extra constraint. Each constraint has its own row. Label this worksheet Problem 1

2. Use the solver window to solve the problem. When solver tells you that you have an answer, click on the answer and sensitivity report options.

3. Answer the following questions ON YOUR SENSITIVY REPORT WORKSHEET FOR PROBLEM 1.

a. If the profit for Standard bags increases by $5/bag, does the optimal solution change?

Explain why or why not.

b. If the optimal solution does NOT change, will the value of the objective function at

the optimal solution change? If it does, calculate the new profit. If it does not, explain why not.

c. If the profit for Deluxe bags decreases by $5/bag, does the optimal solution change?

Explain why or why not. (Note: this change is made WITHOUT changing the profit for

Standard bags.)

d. If the optimal solution does NOT change, will the value of the objective function at

the optimal solution change? If it does, calculate the new profit. If it does not, explain why not.

e. If the profit for Standard bags increases by $5/bag and the profit for Deluxe bags

decreases by $5/bag, does the optimal solution change? (Hint: Use the 100% Rule)

f. If the optimal solution does NOT change, will the value of the objective function at

the optimal solution change? If it does, calculate the new profit. If it does not, explain why not.

1. Suppose a small company is in the business of making golf bags to be sold at a local golf course where state championships are often held. They make 2 types of golf bags, a standard bag and a deluxe bag. The table below gives the information for limitations of materials and labor hours, and also for the profit for each type of bag. The materials and labor hours are the amount available per week, and the profit is the profit per week as well.

Units are per bag	Standard Leather (square feet)	Premium Leather (square feet)	Cutting and Sewing Labor hours	Finishing Labor Hours	Profit
Standard	7	1	3	1	25
Deluxe	0	9	4	2	65
Total available	210	180	120	40

1. Set up the problem in an excel worksheet, using the sandwich problem done in class as a template. The only difference will be that you have an extra constraint. Each constraint has its own row. Label this worksheet Problem 1

2. Use the solver window to solve the problem. When solver tells you that you have an answer, click on the answer and sensitivity report options.

3. Answer the following questions ON YOUR SENSITIVY REPORT WORKSHEET FOR PROBLEM 1.

a. If the profit for Standard bags increases by $5/bag, does the optimal solution change?

Explain why or why not.

b. If the optimal solution does NOT change, will the value of the objective function at

the optimal solution change? If it does, calculate the new profit. If it does not, explain why not.

c. If the profit for Deluxe bags decreases by $5/bag, does the optimal solution change?

Explain why or why not. (Note: this change is made WITHOUT changing the profit for

Standard bags.)

d. If the optimal solution does NOT change, will the value of the objective function at

the optimal solution change? If it does, calculate the new profit. If it does not, explain why not.

e. If the profit for Standard bags increases by $5/bag and the profit for Deluxe bags

decreases by $5/bag, does the optimal solution change? (Hint: Use the 100% Rule)

f. If the optimal solution does NOT change, will the value of the objective function at

the optimal solution change? If it does, calculate the new profit. If it does not, explain why not.

Answer 1

variables x1 x2 Description of the Problem

coefficient 30 5 x1 No. of standard bags to be produced.

profit (Z) 1200 x2 No. of delux bags to be produced.

The objective is to maximize profit.

constraints Objective function

1 210 <= 210 Max Z= 25 * x1 + 65 * x2 As the per unit profit on standard and delux bags is 25 and 65 respectively.

2 75 <= 180

3 110 <= 120 The above objective function is to be maximized subject to the following constraints.

4 40 <= 40 7x1<=210 (availability of standard leather)

For the given problem the optimal profit of $1300 can be earned by the producer by manufacturing 20 deluxe bags only. x1+9x2<=180 (availability of premium leather)

3x1+4x2<=120 (availability of cutting and sewing labour hours)

x1+2x2<=40 (availability of finishing labour hours)

Microsoft Excel 15.0 Sensitivity Report

Worksheet: [Book1]Sheet1

Report Created: 3/5/2019 1:00:13 PM

Variable Cells

Final Reduced Objective Allowable Allowable

Cell Name Value Cost Coefficient Increase Decrease

$B$2 coefficient x1 0 0 25 7.5 17.77777778

$C$2 coefficient x2 20 0 65 160 15

1 a) Since the allowbale increase in x1 is 7.5 as per the sensitivity report, the increase of $5 in the profit due to x1 will not change the optimal solution.

1 b) The value of the objective function does not change as the coefficient of x1 is 0 in the optimal solution.

1 c) Since the allowbale decrease in x2 is 715 as per the sensitivity report, the decrease of $5 in the profit due to x2 will not change the optimal solution.

1 d) The value of the objective function does change as the coefficient of x2 is now changed to 60 from 65. The new objective function value is $1200.

1 e) The optimal solution will change as both the coefficients of x1 and x2 have been changed simultaneously. The new profit of $1200 can be earned by producing 30 standard bags and 5 deluxe bags

1 f) The value of the optimal function changes and the new objective function value is $1200.