fda assignment

8/8/2019 FDA Assignment

1/23

Financial Decision Analysis Course Work

Question A:

Quantity Maximization or Minimization:

Profit (maximization), or cost (minimization). This is known as the ObjectiveFunction. This is the goal of the problem.

Constraints:

Factors liming the extent to which the objective function can be realised. Representlimited resource quantities e.g. land acreage, machine hours, labour hours, cubic litresof paint available etc. (See below on non-negativity constraints).

Decision Variables:

There must be differing alternatives available e.g. a wood merchant can decidebetween the number of guns and amount of butter produced. Each variable will benoted by a different subscript. The problem will be solved for these.

Linearity:Mathematical Relationships must be linear. (This makes the relationships

proportional.)Hence:

2a + 3b = 81 is linear2a + 3b = 90 is not.

Power of the number:

No equation should have a square or higher power.

Inequality:

The inequality will have a or sign (FDA lecture slides, 2010)

- 1 -


2/23


Question B:

Let Z = Profit Maximization.

Decision Variables: If decision variables are shown as follows,

X1= Long Haul Business SeatsX2= Short Haul Business SeatsX3= Long Haul Family SeatsX4= Short Haul Family SeatsX5= Long Haul Advanced SeatsX6= Short Haul Advanced SeatsX7= Long Haul Standard SeatsX8= Short Haul Standard SeatsX9= Military Seats

Then Objective Function to maximise the profit can be shown as below:

Z = 568*X1 + 284*X2 + 412*X3 + 206*X4 + 286*X5 +142*X6 + 175*X7 88*X8+ 0*X9

With subject to Constraints:

Total Number of Seats X1+X2+X3+X4+X5+X6+X7+X8+X9 550

Number of Military Seats X9 = 143

Number of Total Business Seats X1+X2 .20*550

Number of Business Long Haul Seats X1 7/11*(X1+X2)

Number of Advanced Long Haul Seats 30 X5 40

Number of Total Advanced Seats X5+X6 70

Number of Total Standard Seats X7+X8 198

Number of Total Family Seats X3+X4 140

Number of Family Long Haul Seats X3 50

Steel84*X1+49*X2+63*X3+31*X4+54*X5+20*X6+20*X7+124*X8+158*X9 50000

Bolts49*X1+7*X2+21*X3+4*X4+11*X5+9*X6+11*X7+11*X8+58*X9 10000

Rods

112*X1+79*X2+82*X3+64*X4+50*X5+60*X6+21*X7+11*X8+196*X9 60000

- 2 -


3/23


Fireproof Material 6*X1+10*X2 800

With Non-Negativity

Long Haul Business Seats X1 0

Short Haul Business Seats X2 0Long Haul Family Seats X3 0Short Haul Family Seats X4 0Long Haul Advanced Seats X5 0Short Haul Advanced Seats X6 0Long Haul Standard Seats X7 0Short Haul Standard Seats X8 0Military Seats X9 0

Computer Output:

Answer Report:

Microsoft Excel 10.0 Answer Report

Worksheet: [B2.xls]Sheet2

Report Created: 24/03/2010 16:08:12

Target Cell (Max)

Cell Name Original Value Final Value

$B$2 Objective Function 0 65322.72727

Adjustable CellsCell Name Original Value Final Value

$B$6 Long haul business 0 0

$B$7 Short haul Business 0 80

$B$8 Long haul Family 0 0

$B$9 Short haul Family 0 140

$B$10 Long haul Advanced 0 40

$B$11 Short haul Advanced 0 0

$B$12 Long haul Standard 0 13.27272727

$B$13 Short haul Standard 0 0

$B$14 Military 0 143

Constraints

Cell Name Cell Value Formula Status Slack

$B$18 Total No of Seats 416.2727273 $B$18=$C$40 Binding 0

$B$20 No of Total Business Seats 80 $B$20


4/23


$B$29 Steel 33279.45455 $B$29=$C$35 Binding 0

$B$36 Short haul Family 140 $B$36>=$C$36 Not Binding 140$B$37 Long haul Advanced 40 $B$37>=$C$37 Not Binding 40

$B$38 Short haul Advanced 0 $B$38>=$C$38 Binding 0

$B$39 Long haul Standard 13.27272727 $B$39>=$C$39 Not Binding 13.2727273

$B$19 No of Military Seats 143 $B$19=$C$19 Not Binding 0

Sensitivity Report:

Adjustable Cells

Final Reduced Objective Allowable Allowable

Cell Name Value Cost Coefficient Increase Decrease

$B$6 Long haul business 0 0 568 315.1272727 1E+30$B$7 Short haul Business 80 0 284 1E+30 172.636364

$B$8 Long haul Family 0 0 412 64.45454545 1E+30

$B$9 Short haul Family 140 0 206 1E+30 64.4545455

$B$10 Long haul Advanced 40 0 286 1E+30 111

$B$11 Short haul Advanced 0 0 142 1.181818182 1E+30

$B$12 Long haul Standard 13.27272727 0 175 111 1.44444444

$B$13 Short haul Standard 0 0 -88 263 1E+30

$B$14 Military 143 0 0 1E+30 1E+30

Constraints

Final Shadow Constraint Allowable Allowable

Cell Name Value Price R.H. Side Increase Decrease

$B$18 Total No of Seats 416.2727273 0 550 1E+30 133.727273

$B$40 Short haul Standard 0 -263 0 13.27272727 0

$B$20 No of Total Business Seats 80 0 110 1E+30 30

$B$21No of Business Long HaulSeats 0 0 70 1E+30 70

$B$22No of Advanced Long haulSeats 40 0 30 10 1E+30

$B$23No of Advanced Long haulSeats 40 111 40 13.27272727 10

$B$41 Military 143 0 0 143 1E+30

$B$24 No of Total Advanced Seats 40 0 70 1E+30 30

$B$25 No of Total Standard Seats 13.27272727 0 198 1E+30 184.727273$B$26 No of Total Family Seats 140 142.3636364 140 36.5 140

$B$27 No of Family Long Haul Seats 0 0 50 1E+30 50

$B$28 Fireproof Material 800 17.26363636 800 208.5714286 800

$B$29 Steel 33279.45455 0 50000 1E+30 16720.5455

$B$30 Bolts 10000 15.90909091 10000 1471 146

$B$31 Rods 45586.72727 0 60000 1E+30 14413.2727

$B$33 Long haul business 0 -315.127273 0 3.258928571 0

$B$34 Short haul Business 80 0 0 80 1E+30

$B$35 Long haul Family 0 -64.4545455 0 8.588235294 0

$B$36 Short haul Family 140 0 0 140 1E+30

$B$37 Long haul Advanced 40 0 0 40 1E+30

$B$38 Short haul Advanced 0 -1.18181818 0 16.22222222 0$B$39 Long haul Standard 13.27272727 0 0 13.27272727 1E+30

- 4 -


5/23


$B$19 No of Military Seats 143 -922.727273 143 2.517241379 31.2978723

Result:

From the answer report, the optimal product mix to produce is,

80 seats for short haul business class,

140 seats for short haul family class,

40 seats for long haul advanced class,

13.272727 seats for long haul standard class,

143 seats for military.

Hence the total numbers of occupied seats are 416.27 which give $65322.73

maximum contribution to profit.

Question C:

The decimal points in question B needs to be eliminate as the aircraft can only sell theseats in whole number, hence the answer for question be needs to derive in integers.Means the decimal number of 13.27273 for Long Haul Standard Seats needs to reduceup to its allowable decrease. (As per answer report it is 1.444). The shadow pricesfrom the sensitivity report shows that for every less seat of Long haul Standard Seatwill result in decrease in profit by $175.

Therefore the decrease in profit will be $65322.73 [($175)*(0.27273)] =

$65275.002.

Question D:

I) A fall in price of each Business Short Haul Seat to $666.

- 5 -


6/23


The answer report indicates that allowable decrease for the Business Short Haul Seatsis 172.64. Hence the fall in price to $666 is allowed as it shows change of 34 ($700-$666). We can see from the Sensitivity Report above that, for each reduced dollar, thedecrease in profit contribution is 80.

Therefore the Profit would decrease by (34*$80) = $2720, so the optimal profit

would be now $65322.73-$2720=$62602.73.

Reduction in price in Business Long Haul Seats is not profitable.

II) A fall in price of each Business Short Haul Seat to $566.

The answer report indicates that allowable decrease for the Business Short Haul Seatsis 172.64. Hence the fall in price to $566 is allowed as well as it shows change of 134

($700-$566). And as the Sensitivity Report above shows that, for each reduced dollar,the decrease in profit contribution is 80.

Therefore the Profit would even decrease more by (134*$80) = $10720, so the

optimal profit would be now lowered to $65322.73-$10720=$54602.73.

Reduction in price in Business Long Haul Seats is not profitable.

III) A rise in price of each Advanced Long Haul Seat to $673

The associated shadow prices from the Sensitivity Report show that for everyadditional dollar of Advanced Long Haul Seat that could be obtained the increase in

profit contribution is $40. The increase of 37($673-$636) in price is acceptable asallowable increase for Advanced Long Haul Seat is unlimited (Infinity).

Therefore profit contribution could be increased by 37*$40= $1480, which makes

the optimal profit $65322.73+$1480=$66802.73

Increase in price in Advanced Long Haul Seats is profitable.

IV) A rise in price of each Advanced Long Haul Seat to $650

As mentioned above, the Sensitivity Report shows that for every additional dollar ofAdvanced Long Haul Seat that could be obtained the increase in profit contribution is$40. And the increase of 14($650-$636) in price is acceptable as allowable increasefor Advanced Long Haul Seat is unlimited (Infinity).

Hence profit contribution could be increased by 14*$40= $560, which makes the

optimal profit $65322.73+$560=$65882.73

Increase in price in Advanced Long Haul Seats is profitable.

Question E:

- 6 -


7/23


8/23


$B$31 Rods 45289.8182 $B$31=$C$33 Binding 0

$B$34 Short haul Business 80 $B$34>=$C$34 Not Binding 80

$B$35 Long haul Family 0 $B$35>=$C$35 Binding 0

$B$36 Short haul Family 140 $B$36>=$C$36 Not Binding 140

$B$37 Long haul Advanced 39.6363636 $B$37>=$C$37 Not Binding 39.6363636

$B$38 Short haul Advanced 0 $B$38>=$C$38 Binding 0$B$39 Long haul Standard 0 $B$39>=$C$39 Binding 0

$B$40 Short haul Standard 0 $B$40>=$C$40 Binding 0

$B$22 No of Advanced Long haul Seats 39.6363636 $B$22>=$C$22 Not Binding 9.63636364

Sensitivity Report:

Adjustable Cells



$B$6 Long haul business 0 0 568 767.2 1E+30

$B$7 Short haul Business 80 0 284 1E+30 102

$B$8 Long haul Family 0 0 412 236 1E+30

$B$9 Short haul Family 140 0 206 1E+30 102

$B$10 Long haul Advanced 39.63636364 0 286 160.285714 111

$B$11 Short haul Advanced 0 0 142 92 1E+30

$B$12 Long haul Standard 0 0 175 111 1E+30

$B$13 Short haul Standard 0 0 -88 374 1E+30

$B$14 Military 143 0 0 1E+30 1E+30

Constraints




$B$19 No of Military Seats 143 -1508 143 1.82758621 0.068965517


$B$21 No of Business Long Haul Seats 0 0 70 1E+30 70

$B$41 Military 143 0 0 143 1E+30

$B$23 No of Advanced Long haul Seats 39.63636364 0 40 1E+30 0.363636364

$B$24 No of Total Advanced Seats 39.63636364 0 70 1E+30 30.36363636

$B$25 No of Total Standard Seats 0 0 198 1E+30 198

$B$26 No of Total Family Seats 140 102 140 26.5 1


$B$28 Fireproof Material 800 10.2 800 151.428571 5.714285714

$B$29 Steel 32994.36364 0 50000 1E+30 17005.63636

$B$30 Bolts 9850 26 9850 4 106

$B$31 Rods 45289.81818 0 60000 1E+30 14710.18182$B$33 Long haul business 0 -767.2 0 2.36607143 0


$B$35 Long haul Family 0 -236 0 6.23529412 0


$B$37 Long haul Advanced 39.63636364 0 0 39.6363636 1E+30

$B$38 Short haul Advanced 0 -92 0 11.7777778 0

$B$39 Long haul Standard 0 -111 0 9.63636364 0

$B$40 Short haul Standard 0 -374 0 9.63636364 0

$B$22 No of Advanced Long haul Seats 39.63636364 0 30 9.63636364 1E+30

Result:

- 8 -


9/23


The new answer report shows the new maximum profit as $62896. This means profithas reduced by $2426.7272 ($65322.7272-$62896). And the new optimal mix showsreduction in total seats by 14 (416-402) along with the lowered utility of steel by285(33279-32994) and rods by 297(45586-45289).

This shows it is no longer profitable to reduce the number of Bolts.

II) A fall in number of bolts to 9700.

Computer Output:

Answer Report:

Target Cell (Max)

Cell Name

Original

Value Final Value$B$2 Objective Function 0 58354.85714

Adjustable Cells

Cell NameOriginalValue Final Value


$B$7 Short haul Business 0 73.71428571

$B$8 Long haul Family 0 3.55271E-15




$B$12 Long haul Standard 0 0



Constraints


$B$18 Total No of Seats 386.7142857 $B$18


10/23


$B$35 Long haul Family 3.55271E-15 $B$35>=$C$35 Binding 0

$B$36 Short haul Family 140 $B$36>=$C$36 Not Binding 140

$B$37 Long haul Advanced 30 $B$37>=$C$37 Not Binding 30


$B$39 Long haul Standard 0 $B$39>=$C$39 Binding 0


$B$22 No of Advanced Long haul Seats 30 $B$22>=$C$22 Binding 0

Sensitivity Report:

Adjustable Cells



$B$6 Long haul business 0 0 568 1420 1E+30

$B$7 Short haul Business 73.71428571 0 284 76.5 102

$B$8 Long haul Family 3.55271E-15 0 412 483.7142857 1E+30

$B$9 Short haul Family 140 0 206 1E+3043.7142857

$B$10 Long haul Advanced 30 0 286 160.2857143 1E+30


$B$12 Long haul Standard 0 0 175 271.2857143 1E+30

$B$13 Short haul Standard 0 0 -88 534.2857143 1E+30

$B$14 Military 143 0 0 1E+30 1E+30

Constraints



$B$18 Total No of Seats 386.7142857 0 550 1E+30163.285714

$B$19 No of Military Seats 143 -2353.143 143 8.896551724

0.7586206

9

$B$20 No of Total Business Seats 73.71428571 0 110 1E+3036.2857143


$B$41 Military 143 0 0 143 1E+30

$B$23 No of Advanced Long haul Seats 30 0 40 1E+30 10



$B$26 No of Total Family Seats 14043.714286 140 129 11

$B$27 No of Family Long Haul Seats 3.55271E-15 0 50 1E+30 50

$B$28 Fireproof Material 737.1428571 0 800 1E+3062.8571429

$B$29 Steel 32166 0 50000 1E+30 17834

$B$30 Bolts 970040.571429 9700 44 516

$B$31 Rods 44311.42857 0 60000 1E+3015688.5714

$B$33 Long haul business 0 -1420 0 10.53061224 0

$B$34 Short haul Business 73.71428571 0 0 73.71428571 1E+30

$B$35 Long haul Family 3.55271E-15 -483.7143 0 30.35294118 0



$B$38 Short haul Advanced 0 -223.1429 0 40 0

$B$39 Long haul Standard 0 -271.2857 0 46.90909091 0

$B$40 Short haul Standard 0 -534.2857 0 46.90909091 0

$B$22 No of Advanced Long haul Seats 30 -160.2857 30 10 4

- 10 -


11/23


Result:

Reduction in number of Bolts by 300 shows the even more reduction in optimal profitas $6938.27273 as new optimal profit is $58354. The new answer report shows the

change in number of seats as 30(416-386) and the utility of steel, rods and fireproofmaterial is also decreased by 1113(33279-32166), 1276(45586-44311) and 63(800-737) respectively.

This shows it is certainly not profitable to reduce the number of Bolts.

III) A rise in the number of units of fireproof material to 1000 units.

The change in number of units of fireproof material is 200, which is acceptable as allowableincrease is 208. The shadow price of one unit of fireproof material is $17, so the effect on profit

is a net increase of $3400($17*200).

That implies a maximum profit will be $61922.27 ($65322.27273-$3400).

Increase in number of unit of fireproof material is not profitable as it increase

the cost.

IV) A rise in the number of units of fireproof material to 1100 units.

The Sensitivity Report above shows that we could profitably utilise additional 208numbers of units of fireproof material (Allowable Increase). And in the givenexample, the change in number of units of fireproof material is exceeding the

allowable increase.

1100-800=300

This means that as the Bolts constraint is shifted outwards, the Feasible Regionexpands thus increasing the profit until eventually another corner of the feasibleregion is hit and another constraint will become binding.

To find new optimal product mix we need to replace the fireproof material

constraint and reset the decision variables to zero and Re-Solve.

Computer Output:

Answer Report:

Target Cell (Max)


$B$2 Objective Function 0 69856

Adjustable Cells


$B$6 Long haul business 0 1.66533E-16

- 11 -


12/23


13/23


$B$14 Military 143 0 0 1E+30 1E+30

Constraints




$B$41 Military 143 0 0 143 1E+30

$B$20 No of Total Business Seats 110 102 110 0 9.142857143

$B$21 No of Business Long Haul Seats 1.66533E-16 0 70 1E+30 70

$B$19 No of Military Seats 143 -1508 1430.79310345 1.103448276

$B$23 No of Advanced Long haul Seats 34.18181818 0 40 1E+30 5.818181818

$B$24 No of Total Advanced Seats 34.18181818 0 70 1E+30 35.81818182


$B$26 No of Total Family Seats 140 102 140 11.5 16


$B$28 Fireproof Material 1100 0 1100 1E+30 0

$B$29 Steel 34169.81818 0 50000 1E+30 15830.18182

$B$30 Bolts 10000 26 10000 64 46$B$31 Rods 47387.09091 0 60000 1E+30 12612.90909

$B$33 Long haul business 1.66533E-16 -808 0 1.0952381 0


$B$35 Long haul Family 0 -236 02.70588235 0


$B$37 Long haul Advanced 34.18181818 0 034.1818182 1E+30

$B$38 Short haul Advanced 0 -92 05.11111111 0

$B$39 Long haul Standard 0 -111 04.18181818 0

$B$40 Short haul Standard 0 -374 0

4.1818181

8 0

$B$22 No of Advanced Long haul Seats 34.18181818 0 304.18181818 1E+30

Result:

The new answer report shows the new maximum profit as $69856. This means profithas increased by $4533.273 ($69856-$65322.27273). And the new optimal mix showsincrease in total seats by 11(427-416) along with the additional utility of steel by391(34169-33279) and rods by 1800(47387-45586).

Increase in number of unit of fireproof material is profitable.

- 13 -


14/23


Question F:

I) A fall in number of military seats to 130

The reduction in number of military seats from 143 to 130 calculates the change of13. This is acceptable as the allowable increase is 31.29. The shadow cost inSensitivity report above is 922.7 (which is negative). Which means a reduction in

each number of military seats will make profit of $922.7.

This will make additional profit of $11995.1 ($922.7*13) and the optimal profit

will be $77317.83($65322.7272+$11995.1).

Decrease in number of military seats is welcome as it will be profitable for

airlines.

II) A new Boeing 787A with less total number of seats of 300.

The Sensitivity Report above shows that we could profitably utilise reduced 137.727numbers of total seats (Allowable Decrease).And in the given example, the changein total number of seats is exceeding the allowable decrease.

550-300=250

This means that as the Total Number of Seats constraint is shifted, the FeasibleRegion changes thus decreasing the profit until eventually another corner of thefeasible region is hit and another constraint will become binding.

To find new optimal product mix we need to replace the total number of seatsconstraint and reset the decision variables to zero and Re-Solve.

Computer Output:

Answer Report:

Target Cell (Max)



Adjustable Cells

- 14 -


15/23





$B$8 Long haul Family 0 36.94117647

$B$9 Short haul Family 0 10.05882353

$B$10 Long haul Advanced 0 30$B$11 Short haul Advanced 0 0




Constraints


$B$18 Total No of Seats 300 $B$18


16/23


$B$8 Long haul Family 36.9411765 0 412 236 11.7142857

$B$9 Short haul Family 10.0588235 0 206 50.5714286 8.2

$B$10 Long haul Advanced 30 0 286 4.82352941 1E+30




$B$14 Military 143 0 0 1E+30 1E+30

Constraints



$B$18 Total No of Seats 300 157.529412 300 93 8.14285714

$B$19 No of Military Seats 143 -860.35294 143 11.6296296 4.11111111



$B$22 No of Advanced Long haul Seats 30 -4.8235294 30 10 30

$B$23 No of Advanced Long haul Seats 30 0 40 1E+30 10


$B$25 No of Total Standard Seats 0 0 198 1E+30 198$B$26 No of Total Family Seats 47 0 140 1E+30 93

$B$27 No of Family Long Haul Seats 36.9411765 0 50 1E+30 13.0588235


$B$29 Steel 30773.1176 0 50000 1E+30 19226.8824

$B$30 Bolts 10000 12.1176471 10000 171 628

$B$31 Rods 39520.9412 0 60000 1E+30 20479.0588



$B$35 Long haul Family 36.9411765 0 0 36.9411765 1E+30

$B$41 Military 143 0 0 143 1E+30


$B$38 Short haul Advanced 0 -124.58824 0 14.25 0$B$39 Long haul Standard 0 -115.82353 0 17.1 0


$B$36 Short haul Family 10.0588235 0 0 10.0588235 1E+30

Result:

From the new answer report, the new optimal product mix to produce is,


10.05 seats for short haul family class,

36.94 seats for long haul family class



The new answer report shows the new maximum profit as $48591.88235.

This means profit has decreased by $16730.84 ($65322.7272-$48591.88235).

- 16 -


17/23


Question G:

To analyze the impact of newly arranged Standard Cost on Boeing we need to formnew Objective Function as:

Z =565*X1+265*X2+365*X3-13*X4+200*X5+26*X6+15*X7-202*X8+0*X9

Where an average of all cost is taken and deducted by the each revenue. All theconstraints will remain same.

To find new optimal product mix we need to form new objective function with same

constraints and reset the decision variables to zero and Re-Solve.

Computer Output:

Answer Report:



Report Created: 24/04/2010 08:55:22

Target Cell (Max)



Adjustable Cells




$B$8 Long haul Family 0 33.61904762


$B$10 Long haul Advanced 0 40$B$11 Short haul Advanced 0 0




Constraints


$B$18 Total No of Seats 296.619048 $B$18=$C$41 Not Binding 143

$B$20 No of Total Business Seats 80 $B$20


18/23


$B$23No of Advanced Long haulSeats 40 $B$23


19/23


$B$27 No of Family Long Haul Seats33.619047

6 0 50 1E+30 16.380952

$B$28 Fireproof Material 800 14.3333333 800 300 491.42857

$B$29 Steel 30792 0 50000 1E+30 19208

$B$30 Bolts 10000 17.3809524 10000 344 706

$B$31 Rods

39104.761

9 0 60000 1E+30 20895.238

$B$33 Long haul business 0 -372.66667 015.75892

9 0

$B$19 No of Military Seats 143 -1008.0952 14312.17241

4 5.9310345

$B$35 Long haul Family33.619047

6 0 033.61904

8 1E+30

$B$36 Short haul Family 0 -82.52381 0131.4117

6 0


$B$38 Short haul Advanced 0 -130.42857 0 30 0

$B$39 Long haul Standard 0 -176.19048 064.18181

8 0

$B$40 Short haul Standard 0 -393.19048 0

64.18181

8 0$B$34 Short haul Business 80 0 0 80 1E+30

Result:

Although everything else is same, the total number of seats has been lowered by119.6537(416.27273-296.619). Hence the utility of the Steel, Bolts and rods alsolessen. This has reduced profit by $23851.77 ($65322.7272-$41470.95238)

Question H:

To analyze the impact of new segment Student Backpacker on Boeing we need toform new Objective Function as:

Z = 568*X1 + 284*X2 + 412*X3 + 206*X4 + 286*X5 +142*X6 + 175*X7 88*X8+ 0*X9+445*X10

Where X10 is new Decision variable Student Backpacker with subject toConstraint X10 0.

All other constraints will remain same except three given below:

Steel84*X1+49*X2+63*X3+31*X4+54*X5+20*X6+20*X7+124*X8+158*X9+19*B15 50000

Bolts49*X1+7*X2+21*X3+4*X4+11*X5+9*X6+11*X7+11*X8+58*X9+22*B15 10000

Rods

112*X1+79*X2+82*X3+64*X4+50*X5+60*X6+21*X7+11*X8+196*X9+38*B15 60000

- 19 -


20/23


To find new optimal product mix we need to add the Student backpacker constraint

and reset the decision variables to zero and Re-Solve.

Computer Output:

Answer Report:



Report Created: 22/04/2010 07:07:02

Target Cell (Max)



Adjustable Cells




$B$8 Long haul Family 0 0







$B$15 Student Backpackers 0 6.636363636

Constraints


$B$19 Total No of Seats 409.6363636 $B$19


21/23


$B$32 Rods 45560.18182 $B$32=$C$42NotBinding 143

$B$35 Short haul Business 80 $B$35>=$C$35NotBinding 80

$B$36 Long haul Family 0 $B$36>=$C$36 Binding 0

$B$37 Short haul Family 140 $B$37>=$C$37NotBinding 140

$B$38 Long haul Advanced 40 $B$38>=$C$38NotBinding 40


$B$40 Long haul Standard 0 $B$40>=$C$40 Binding 0


$B$23 No of Advanced Long haul Seats 40 $B$23>=$C$23NotBinding 10

Sensitivity Report

Microsoft Excel 10.0 Sensitivity ReportWorksheet: [B2.xls]Sheet5

Report Created: 22/04/2010 07:07:02

Adjustable Cells



$B$6 Long haul business 0 0 568 508.581818 1E+30

$B$7 Short haul Business 80 0 284 1E+30142.409091

$B$8 Long haul Family 0 0 412 137.863636 1E+30

$B$9 Short haul Family 140 0 206 1E+30

125.09090

9$B$10 Long haul Advanced 40 0 286 1E+30 63.5




$B$14 Military 143 0 0 1E+30 1E+30

$B$15 Student Backpackers 6.636363636 0 445 127 95

Constraints



$B$19 Total No of Seats 409.6363636 0 550 1E+30

140.36363

6

$B$20 No of Military Seats 143 -1173.18182 143 2.5172413885.7777778


$B$43 Student Backpackers 6.636363636 0 0 6.63636364 1E+30



$B$24 No of Advanced Long haul Seats 40 63.5 40 13.2727273 10



$B$27 No of Total Family Seats 140 125.0909091 140 36.5 140



$B$30 Steel 33140.09091 0 50000 1E+3016859.9091

- 21 -


22/23


$B$31 Bolts 10000 20.22727273 10000 3088 146

$B$32 Rods 45560.18182 0 60000 1E+3014439.8182

$B$42 Military 143 0 0 143 1E+30


$B$36 Long haul Family 0 -137.863636 0 8.58823529 0

$B$37 Short haul Family 140 0 0 140 1E+30$B$38 Long haul Advanced 40 0 0 40 1E+30

$B$39 Short haul Advanced 0 -40.0454545 0 16.2222222 0

$B$40 Long haul Standard 0 -47.5 0 13.2727273 0


$B$23 No of Advanced Long haul Seats 40 0 30 10 1E+30

Result:

From the new answer report, the new optimal product mix to produce is,


140 seats for short haul family class,


6.6363 seats of student backpackers class,


Hence the total occupied seats are 409.6363which gives $65953.18182 maximum

contributions to profit.

Note: Addition of New class has reduced total number of seats by however profit

has been increased by $630.45462

Question I:

Limitations of the model:

Errors: Initial input figures may be found to be wrong.

What If? Management would like to know what the effects are of changing one ofthe input parameters e.g. what is effect on profit of a 5% rise in price or a 10%increase in the supply of raw materials?

Looks at the range of values where the current optimal solution remains optimal.

Does not change feasible region.

Changes the slope of the profit or cost line

Binding Constraint:

-Changes the size of the feasible region.-For constraints, rise in RHS value allows

feasible region to become larger, leading tonew corner points.

- 22 -


23/23


-For constraints fall in RHS value meansfeasible region will become smaller.

Simply decreases/increases the level of slack (the former up to a point where the

constraint becomes binding. (FDA lecture slides, 2010)

Reference:

Meyer H. (2010) Financial Decision Analysis Lecture Slides, Leeds MetropolitanUniversity

fda assignment

Documents