Loading documents preview...
CASE: JCG Global Air Services Quantitative Techniques- II Assignment: - 04 Section: - F Group number: 7
Submitted To: Prof. Bhuvanesh Pareek Submitted By: Akhilesh Kumar Singh Gaurav Panda Indroneel Das Mugdha Kabra Nikhil Murthy Swetapadma Mahapatra Shibani Shankar Ray
Given Information:
Fuel Costs per gallons: Airport KMLI KBOS KTEB KDAL
Price ($ per Gallons) 3.97 8.35 7.47 6.01
Ramp fee Waivers on purchase of fuel Airport
Ramp Fee
Min Gallons for fee Waiver
KMLI KBOS KTEB KDAL
800 450 400
500 300 350
Assumptions: Let the amount of fuel purchased at each airport be as follows: X1: Fuel gallons at airport KMLI before journey X2: Fuel gallons at airport KBOS X3: Fuel gallons at airport KTEB X4: Fuel gallons at airport KDAL X5: Fuel gallons at airport KMLI after Journey Before are the binary variables that identify the use of ramp fee waivers: Y1: Waiver at KBOS Y2: Waiver at KTEB Y3: Waiver at KDAL Y1, Y2 and Y3 are binary variables with values only 0 and 1. X1, X2, X3, X4 and X5 are integer Variables Objective Function: Objective is to minimize the costs incurred in the flight for the planned travel Minimize 3.97*X1 + 8.35*X2 + 7.47*X3 + 6.01*X4 + 3.97*X5 + Y1*800 + Y2*450 + Y3*400 Subject to Constraints:
Fuel tank Constraints X1 + 7000 <= 13000 7000 + X1 – 4800 + X2 <= 13000 7000 + X1 – 4800 + X2 – 2000 + X3 <= 13000 7000 + X1 – 4800 + X2 – 2000 + X3 – 5300 + X4 <= 13000 7000 + X1 – 4800 + X2 – 2000 + X3 – 5300 + X4 – 3100 + X5 <= 13000 Minimum Fuel Constraints 7000 + X1 – 4800 >= 2400 7000 + X1 – 4800 + X2 – 2000 >= 2400 7000 + X1 – 4800 + X2 – 2000 + X3 – 5300 >= 2400 7000 + X1 – 4800 + X2 – 2000 + X3 – 5300 + X4 – 3100 >= 2400 Maximum Ramp Weight Constraints 22200 + 7000 + X1 + 2*200 <= 36400 22200 + 7000 + X1 – 4800 + X2 + 4*200 <= 36400 22200 + 7000 + X1 – 4800 + X2 – 2000 + X3 + 8*200 <=36400 22200 + 7000 + X1 – 4800 + X2 – 2000 + X3 – 5300 + X4 + 8*200 <= 36400 Maximum Landing Weight Constraints 22200 + 7000 + X1 – 4800 + 2*200 <= 31800 22200 + 7000 + X1 – 4800 + X2 – 2000 + 4*200 <= 31800 22200 + 7000 + X1 – 4800 + X2 – 2000 + X3 – 5300 + 8*200 <= 31800 22200 + 7000 + X1 – 4800 + X2 – 2000 + X3 – 5300 + X4 -3100 + 8*200 <= 31800 Relation between X and Y 500 * 6.7 * (1- Y1) <= X2 300 * 6.7 * (1-Y2) <= X3 350 * 6.7 * (1-Y3) <= X4
Solving The LP using simplex we get
Microsoft Excel 15.0 Answer Report Worksheet: [288508646-Sectiond-Group14-Jcg-Global-Airservice.xlsx]CE 750 Formulation Report Created: 12/7/2015 2:32:08 AM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0 Seconds. Iterations: 20 Subproblems: 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative
Objective Cell (Min) Cell $B$14
Variable Cells Cell $B$5 $C$5 $D$5 $E$5 $F$5 $B$9 $B$10 $B$11
Constraints
Name Minimize KMLI
Name Fuel to be bought Fuel to be bought Fuel to be bought Fuel to be bought Fuel to be bought KBOS KMLI KTEB KMLI KDAL KMLI
KMLI KBOS KTEB KDAL KMLI
Original Value 11645.16 418
Final Value 11645.164 18
Original Value 6000 0 2010 2590 4600 1 0 0
Final Value 6000 0 2010 2590 4600 1 0 0
Integer Integer Integer Integer Integer Integer Binary Binary Binary
Cell
Name
$A$18
Relation between X and Y –
$A$19
Relation between X and Y –
$A$20
Relation between X and Y –
$A$22
Fuel Tank constraints –
$A$23
Fuel Tank constraints –
$A$24
Fuel Tank constraints –
$A$25
Fuel Tank constraints –
$A$26
Fuel Tank constraints –
$A$28
$A$35
Minimum Fuel Constraints – Minimum Fuel Constraints – Minimum Fuel Constraints – Minimum Fuel Constraints – Minimum Fuel on arrival at KMLI Maximum Ramp Weight -
$A$36
Maximum Ramp Weight -
$A$37
Maximum Ramp Weight -
$A$38
Maximum Ramp Weight -
$A$40
Maximum Landing weight -
$A$41
Maximum Landing weight -
$A$42
Maximum Landing weight -
$A$43
Maximum Landing weight -
$A$29 $A$30 $A$31 $A$33
$B$5:$F$5=Int eger $B$9:$B$11= Binary
Cell Value
Formula
0 $A$18<=$ B$18 2010 $A$19<=$ B$19 2345 $A$20<=$ B$20 13000 $A$22<=$ B$22 8200 $A$23<=$ B$23 8210 $A$24<=$ B$24 5500 $A$25<=$ B$25 7000 $A$26<=$ B$26 8200 $A$28>=$ B$28 6200 $A$29>=$ B$29 2910 $A$30>=$ B$30 2400 $A$31>=$ B$31 7000 $A$33>=$ B$33 35600 $A$35<=$ B$35 31200 $A$36<=$ B$36 32010 $A$37<=$ B$37 29300 $A$38<=$ B$38 30800 $A$40<=$ B$40 29200 $A$41<=$ B$41 26710 $A$42<=$ B$42 26200 $A$43<=$ B$43
Status Binding
Sla ck 0
Binding
0
Not Binding Binding
245
Not Binding Not Binding Not Binding Not Binding Not Binding Not Binding Not Binding Binding
480 0 479 0 750 0 600 0 580 0 380 0 510
Binding
0
Not Binding Not Binding Not Binding Not Binding Not Binding Not Binding Not Binding Not Binding
800
0
0
520 0 439 0 710 0 100 0 260 0 509 0 560 0
Sensitivity Analysis: Variable Cells
Cell $B$ 5 $C$ 5 $D$ 5 $E$ 5 $F$ 5 $B$ 9 $B$ 10 $B$ 11
Name Fuel to be bought KMLI Fuel to be bought KBOS Fuel to be bought KTEB
Fin al Val ue 600 0 0
Reduce d Cost
Allowab le Increase
0
Objectiv e Coeffici ent 0.592537 313 1.246268 657 1.114925 373 0.897014 925 0.592537 313 800
0.005970 149 0.110447 761 0.304477 612 370
0.110447 761 0.217910 448 0.005970 149 0.592537 313 800
0 0.110447 761 0
0.304477 612 1E+30
Allowabl e Decreas e 1E+30
KBOS KMLI
201 0 259 0 460 0 1
KTEB KMLI
0
12
450
1E+30
12
KDAL KMLI
0
400
400
1E+30
400
Fin al Val ue 0
Shadow
Constrai nt R.H. Side 0
Allowab le Increase 3350
Allowabl e Decreas e 1E+30
0
510
245
0
1E+30
245
0.304477 612 0
13000
245
510
13000
1E+30
4800
0
13000
1E+30
4790
0
13000
1E+30
7500
Fuel to be bought KDAL Fuel to be bought KMLI
0 0
Constraints
Cell
Name
$A$ 18
Relation between X and Y –
$A$ 19
Relation between X and Y –
201 0
$A$ 20 $A$ 22
Relation between X and Y – Fuel Tank constraints –
234 5 130 00
$A$ 23 $A$ 24 $A$ 25
Fuel Tank constraints –
820 0 821 0 550 0
Fuel Tank constraints – Fuel Tank constraints –
Price 0.238805 97 0.217910 448 0
$A$ 26 $A$ 28 $A$ 29 $A$ 30 $A$ 31 $A$ 33 $A$ 35 $A$ 36 $A$ 37 $A$ 38 $A$ 40 $A$ 41 $A$ 42 $A$ 43
Fuel Tank constraints – Minimum Fuel Constraints – Minimum Fuel Constraints – Minimum Fuel Constraints – Minimum Fuel Constraints – Minimum Fuel on arrival at KMLI Maximum Ramp Weight Maximum Ramp Weight Maximum Ramp Weight Maximum Ramp Weight Maximum Maximum Maximum Maximum -
Landing weight Landing weight Landing weight Landing weight
700 0 820 0 620 0 291 0 240 0 700 0 356 00 312 00 320 10 293 00 308 00 292 00 267 10 262 00
0
13000
1E+30
6000
0
2400
5800
1E+30
0
2400
3800
1E+30
0
2400
510
1E+30
0.304477 612 0.592537 313 0
2400
4600
245
7000
6000
4600
36400
1E+30
800
0
36400
1E+30
5200
0
36400
1E+30
4390
0
36400
1E+30
7100
0
31800
1E+30
1000
0
31800
1E+30
2600
0
31800
1E+30
5090
0
31800
1E+30
5600
Limits Report:
Cell $B$ 14
Objective Name Minimize KMLI
Value 11645.16 418
Variable Cell $B$ 5 $C$ 5
Name Fuel to be bought KMLI Fuel to be bought KBOS
Value 6000 0
Low er Lim it 600 0 0
Objectiv e Result 11645.16 418 11645.16 418
Upp er Lim it 600 0 260 0
Objectiv e Result 11645.16 418 14885.46 269
$D$ 5 $E$ 5 $F$ 5 $B$ 9 $B$ 10 $B$ 11
Fuel to be bought KTEB Fuel to be bought KDAL Fuel to be bought KMLI KBOS KMLI
2010
1
201 0 259 0 460 0 1
KTEB KMLI
0
0
KDAL KMLI
0
0
2590 4600
11645.16 418 11645.16 418 11645.16 418 11645.16 418 11645.16 418 11645.16 418
Hence Optimal Fuel to be bought are: Airport KMLI KBOS KTEB KDAL KMLI
Fuel ( in Gallons) 6000 0 2010 2590 4600
The Ramp fee waiver is used at Airport KBOS.
640 0 819 0 106 00 #N/ A #N/ A #N/ A
16539.68 657 16668.44 776 15200.38 806 #N/A #N/A #N/A