Question

A factory produces canoes, dinghies, speedboats and catamarans. The resources required are steel, fibreglass, wood and marine paint. The following table shows

the per-unit resource quantities required for each product,

the price we sell each product for,

the per-unit resource prices, and

the resource quantities available to us for purchase:

Products	Resources				Selling price
	steel	fibreglass	wood	marine paint
canoes	0.000	27.000	1.000	7.000	$381.80
dinghies	15.000	4.000	2.500	2.100	$153.10
speedboats	36.000	4.000	1.000	2.300	$220.60
catamarans	11.000	64.000	13.000	24.000	$1274.60
Price per unit	$2.00	$2.40	$7.00	$40.00
Amount available	1900.000	900.000	55.000	900.000

After calculating the per-unit profits, we can formulate the linear program to maximise profit as follows:

Max P = 30X1 + 12X2 + 40X3 + 48X4
s.t.
15X2 + 36X3 + 11X4 ≤ 1900.00
27X1 + 4X2 + 4X3 + 64X4 ≤ 900.00
X1 + 2.5X2 + X3 + 13X4 ≤ 55.00
7X1 + 2.1X2 + 2.3X3 + 24X4 ≤ 900.00
Xi ≥ 0.

When this linear program is solved in Excel, the following Sensitivity Report is produced:

Variable Cells

Cell	Name	Final Value	Reduced Cost	Objective Coefficient	Allowable Increase	Allowable Decrease
$B$5	Canoes	2.222222222	0	30	10	27.18161926
$B$6	Dinghies	0	-67.16666667	12	67.16666667	1E+30
$B$7	Speedboats	52.77777778	0	40	1E+30	10
$B$8	Catamarans	0	-345.0555556	48	345.0555556	1E+30

Constraints

Cell	Name	Final Value	Shadow Price	Constraint R.H. Side	Allowable Increase	Allowable Decrease
$B$14	Steel	1900	0.277777778	1900	80	984.3478261
$B$15	Fibreglass	271.1111111	0	900	1E+30	628.8888889
$B$16	Wood	55	30	55	23.29218107	2.222222222
$B$17	Marine paint	136.9444444	0	900	1E+30	763.0555556

Now suppose that the amount of fibreglass available changes to 803.80 units. From the Sensitivity Report, what is the most you can say about the effect on the optimal production plan and profit?

Select one:

a. The optimal production plan will not change. The optimal profit will be $2177.78.

b. The optimal production plan will change. The optimal profit will be between $2200.00 and $2273.83.

c. The optimal production plan will change. The optimal profit will be between $2200.00 and $2275.28.

d. The optimal production plan will change. The optimal profit will be $2070.47.

e. The optimal production plan will change. The optimal profit will be $2182.03.

f. The optimal production plan will change. The optimal profit will be less than $2039.78.

Answer 1

When we solve the given lpp in excel using solver,

Here rows B2: B4 is obtained by the formula B2= B3*B2; C2= C3*C2; D2= D3*D2 ; E3= E3*E2

F3 is obtained by F3 = B3*B2+ C3*C2 + D3*D2+ E3*E2

F5= B5*$B$2+C5*$C$2+D5*$D$2+E5*$E$2

F6 = B6*$B$2+C6*$C$2+D6*$D$2+E6*$E$2 and so on.

Using Solver in excel we use the above following values

Data tab > Solver

So we get optimal profit in F6

But now that amount of fibre glass available has changed from 900 to 803.80, our problem will change same way,

Formulating in Solver

And Solving

As we can see the solution does not change, Therefore optimal production plan will not change. The optimal profit will be $2177.78.

Here is the sensitivity report