Question

# A factory produces canoes, dinghies, speedboats and catamarans. The resources required are steel, fibreglass, wood and...

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
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.

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