Microsoft Excel 12.

0 Answer Report
Worksheet: [Book1]Sheet1
Report Created: 3/21/2011 1:38:18 PM

Target Cell (Min)

Cell Name Original Value Final Value
$C$7 obj fun X12 0 1567400

Adjustable Cells
Cell Name Original Value Final Value
$B$5 X11 0 50000
$C$5 X12 0 10000
$D$5 X13 0 0
$E$5 X21 0 0
$F$5 X22 0 20000
$G$5 X23 0 0

Constraints
Cell Name Cell Value Formula Status Slack
$C$10 C1 X12 60000 $C$10<=$E$10 Binding 0
$C$11 C2 X12 20000 $C$11<=$E$11 Not Binding 20000
$C$12 C3 X12 50000 $C$12=$E$12 Not Binding 0
$C$13 C4 X12 30000 $C$13=$E$13 Not Binding 0
$C$14 C5 X12 0 $C$14=$E$14 Not Binding 0
Microsoft Excel 12.0 Answer Report
Worksheet: [Final OR.xls]Sheet1
Report Created: 3/21/2011 2:34:00 PM

Target Cell (Value Of)

Cell Name Original Value Final Value
$C$22 Obj Fun X112 0 22400

Adjustable Cells
Cell Name Original Value Final Value
$B$20 X111 0 34166.66667
$C$20 X112 0 15833.33333
$D$20 X211 0 0
$E$20 X212 0 0
$F$20 X121 0 0
$G$20 X122 0 10000
$H$20 X221 0 20000
$I$20 X222 0 0

Constraints
Cell Name Cell Value Formula Status Slack
$C$25 C1 X112 50000 $C$25=$E$25 Not Binding 0
$C$26 C2 X112 0 $C$26=$E$26 Not Binding 0
$C$27 C3 X112 10000 $C$27=$E$27 Not Binding 0
$C$28 C4 X112 20000 $C$28=$E$28 Not Binding 0
Microsoft Excel 12.0 Answer Report
Worksheet: [Final OR.xls]Sheet1
Report Created: 3/21/2011 4:00:43 PM

Target Cell (Min)

Cell Name Original Value Final Value
$C$36 Obj fun X12 0 3275

Adjustable Cells
Cell Name Original Value Final Value
$B$34 X11 0 0
$C$34 X12 0 4600
$D$34 X21 0 5000
$E$34 X22 0 800

Constraints
Cell Name Cell Value Formula Status Slack
$C$39 C1 X12 4600 $C$39=$E$39 Not Binding 0
$C$40 C2 X12 5800 $C$40=$E$40 Not Binding 0
$C$41 C3 X12 5000 $C$41=$E$41 Not Binding 0
$C$42 C4 X12 5400 $C$42=$E$42 Not Binding 0
Microsoft Excel 12.0 Answer Report
Worksheet: [Final OR.xls]Sheet1
Report Created: 3/21/2011 4:38:13 PM

Target Cell (Min)

Cell Name Original Value Final Value
$C$50 Obj fun X12 0 2651

Adjustable Cells
Cell Name Original Value Final Value
$B$48 X11 0 4700
$C$48 X12 0 0
$D$48 X13 0 0
$E$48 X21 0 0
$F$48 X22 0 0
$G$48 X23 0 0
$H$48 X31 0 3300
$I$48 X32 0 8700
$J$48 X33 0 0

Constraints
Cell Name Cell Value Formula Status Slack
$C$53 C1 X12 4700 $C$53<=$E$53 Not Binding 6700
$C$54 C2 X12 0 $C$54<=$E$54 Not Binding 7200
$C$55 C3 X12 12000 $C$55<=$E$55 Binding 0
$C$56 C4 X12 8000 $C$56=$E$56 Not Binding 0
$C$57 C5 X12 8700 $C$57=$E$57 Not Binding 0
$C$58 C6 X12 0 $C$58=$E$58 Not Binding 0
Microsoft Excel 12.0 Answer Report
Worksheet: [Final OR.xls]Sheet1
Report Created: 3/21/2011 4:49:52 PM

Target Cell (Min)

Cell Name Original Value Final Value
$C$66 Obj fun X12 0 4895.5

Adjustable Cells
Cell Name Original Value Final Value
$B$64 X11 0 2700
$C$64 X12 0 8700
$D$64 X13 0 0
$E$64 X21 0 5300
$F$64 X22 0 0
$G$64 X23 0 0

Constraints
Cell Name Cell Value Formula Status Slack
$C$69 C1 X12 11400 $C$69<=$E$69 Binding 0
$C$70 C2 X12 5300 $C$70<=$E$70 Not Binding 1900
$C$71 C3 X12 8000 $C$71=$E$71 Not Binding 0
$C$72 C4 X12 8700 $C$72=$E$72 Not Binding 0
$C$73 C5 X12 0 $C$73=$E$73 Not Binding 0
 Supply of crude oil from Saudi Arabia and Brueni to Australia and Japan

X11 X12 X13 X21 X22 X23

50000 10000 0 0 20000 0

obj fun 1567400

Constraint
C1 60000 <= 60000
C2 20000 <= 40000
C3 50000 = 50000
C4 30000 = 30000
C5 0 = 0

Refinery operation at Australia and Japan to get total demand of Gasoline of 22400 b/d

X111 X112 X211 X212 X121 X122 X221 X222

34166.667 15833.33 0 0 0 10000 20000 0

Obj Fun 22400

Constraint
C1 50000 = 50000
C2 0 = 0
C3 10000 = 10000
C4 20000 = 20000

Supply of Gasoline b/d from Australia and Japan to New Zealand and Philippines

X11 X12 X21 X22

0 4600 5000 800

Obj fun 3275

Constraint
C1 4600 = 4600
C2 5800 = 5800
C3 5000 = 5000
 C4 5400 = 5400

Supply of Distillate from Australia, Japna and USA to New Zealand and Philippines

X11 X12 X13 X21 X22 X23 X31 X32 X33

4700 0 0 0 0 0 3300 8700 0

Obj fun 2651

Constraint
C1 4700 <= 11400
C2 0 <= 7200
C3 12000 <= 12000
C4 8000 = 8000
C5 8700 = 8700
C6 0 = 0

(As the cost of Distillate of Australia and Japan is not given)

If Supply of Distillate is only from Australia and Japan to New Zealand and Philippines

X11 X12 X13 X21 X22 X23

2700 8700 0 5300 0 0

Obj fun 4895.5

Constraint
C1 11400 <= 11400
C2 5300 <= 7200
C3 8000 = 8000
C4 8700 = 8700
C5 0 = 0
 Page 1
Solution of Global Oil Company

Here I suppose that Australia and Japan is refining 50000b/d and 30000b/d respectively and
supply is from Saudi Arabia and Brunei. Total cost of supply includes crude oil cost,
transportation cost and tanker use cost per barrel.

A. Supply of crude oil from Saudi Arabia and Brunei to Australia and Japan

Australia Japan
Crude Transport Tanker Total Crude Transport Tanker Total
oil Oil
Saudi 18.50 0.65 0.996 19.25 18.50 0.70 0.913 20.11
Brunei 19.90 0.15 0.0415 20.09 19.90 0.25 0.415 20.19

Objective function: To minimize the total cost of supply

Objective function: 19.25X11+20.11X12+0X13+20.09X21+20.19x22+0X23

Constraint: X11+X12+X13<= 60000 (Supply from Saudi, X13= Dummy)

X21+X22+X23<=40000 (Supply from Baunei, X23= Dummy)

X11+X21= 50000 (Austraila, demand)

X12+X22= 30000 (Brunei, Demand)

X13+X23= 0 (Dummy)

After Using solver following result is obtained

Supply of crude oil from Saudi Arabia and Australia and Brunei(b/d)
Australia (b/d) Japan (b/d) Dummy(b/d)
Saudi Arabia 50000 10000 0000
Brunei 0000 20000 0000
 Page 2
A. Refinery operation at Australia and Japan

/d respectively and According to case total demand of gasoline is 22400b/d from Australia, Japan, New Zealand and
es crude oil cost, Philippines. So here my objective function is get gasoline 22400b/d. The cost and out put of
refinery by high processing and low processing is given in the table 6.1.

apan Australia Japan

Saudi Arabia
n High Processing X111 X112
Tanker Total Low Processing X112 X212
Brunei
0.913 20.11 High Processing X211 X122
Low Processing X212 X222
0.415 20.19

Objective fun: 0.31X111+0.19X112+0.36X211+0.26X212+0.3X121+0.18X122+0.35X221+0.25X222

Objective function is get gasoline 22400b/d

Constraint: X111+X112= 50000

X211+X212= 000

X121+X122= 10000

X221+ X222= 20000

After using solver I got this result

X111 X112 X211 X212 X121 X122 X221 X222

34167 15833 0 0 0 10000 20000 0

nei(b/d)
Total Output of gasoline(b/d)
mmy(b/d)
Processing Crude Oil Gasoline Yield Australia Japan
00 X111 34167 0.31 10592
00 X112 15833 0.19 3008
X211 0 0.36
X212 0 0.26
X121 0 0.30
X122 10000 0.18 1800
X221 20000 0.35 7000
X22 0 0.25
Total 13600 8800

Australia has gasoline 13600b/d and Japan 8800b/d.

X22 0 0.25
Total 13600 8800

Australia has gasoline 13600b/d and Japan 8800b/d.

 Page 3
Output of Distillate (b/d)
Processing Crude Oil Distillate Yield Australia Japan
, New Zealand and X111 34167 0.61 20842
cost and out put of X112 15833 0.73 11558
X211 0 0.58
X212 0 0.69
X121 0 0.62
X122 10000 0.74 7400
X221 20000 0.59 11800
X22 0 0.70
Total 32400 19200

Australia has distillate 32400 b/d and Japan 19200 b/d.

5X221+0.25X222
A. Marketing of gasoline and Distillate from Australia, Japan and USA

Gasoline
Country Demand Surplus Total
Australia 9000 4600 13600
Japan 3000 5800 8800
New Zealand 5000 0000 5000
Philippines 5400 0000 5400

Distillate
Country Demand Surplus Total
21 X222
Australia 21000 11400 32400
00 0
Japan 12000 7200 19200
New Zealand 8000 0000 8000
Philippines 8700 0000 8700
USA 0000 12000 12000
Japan

Note: In the case cost of gasoline and distillate is not given in Australia and Japan so
taking only variable shipment cost and tanker requirement cost.

1800
7000

8800
8800
 Page 4
Supply of gasoline from Australia and Japan to Ne w Zealand and Philippines
Japan
Cost (variable shipment cost and tanker requirement cost b/d)
New Zealand Philippines
Australia 0.208 0.316
Japan 0.299 0.408

7400
11800 Objective function: 0.208X11+0.316X12+0.299X21+0.408X22

19200 Minimizing objective function

Constraints: X11+X12= 4600

X21+X22= 5800

X11+ X21= 5000

SA X12+ X22= 5400

After using solver I got this result

Total
13600 New Zealand Philippines
8800 Australia 000 4600
5000 Japan 5000 800
5400

Supply of Distillate from Australia, Japan and USA to New Zealand and Philippines

Total Here important to note cost of distillate of Australia and Japan is not given but cost of dis
32400 given $20.70 per barrel. In calculation I am ignoring the cost of USA distillate and tak
19200 shipment cost and tanker requirement cost of all country i.e. Australia, Japan and USA.
8000
8700 Cost (variable shipment cost and tanker requirement cost b/d)
12000 New Zealand Philippines Dum
Australia 0.208 0.316 000
Japan 0.299 0.408 000
Australia and Japan so I am USA 0.109 0.151 000

Objective function:

0.208X11+0.316X12+0X13+0.299X21+0.408X22+0X23+0.109X31+0.151X23+0X33

Minimization objective function

Constraint: X11+X12+X13 <= 11400 (Supply from Australia)

X21+X22+X23 <= 7200 (Supply from Japan)

X31+X32+X33<=12000 (Supply from USA)

Minimization objective function

Constraint: X11+X12+X13 <= 11400 (Supply from Australia)

X21+X22+X23 <= 7200 (Supply from Japan)

X31+X32+X33<=12000 (Supply from USA)

 Page 5
nd and Philippines X11+X21+X31= 8000 (Demand of New Zealand)

requireme nt cost b/d) X12+X22+X31= 8700 (Demand of Philippines)

Philippines
0.316 X13+X23+X33= 0 (Demand of Dummy)
0.408
After using solver I got following result

2 Supply of Distillate
New Zealand Philippines
Australia 4700 000
Japan 000 000
USA 3300 8700

If we consider only Australia and Japan as Supplier of Distillate because they

the demand of New Zealand and Philippines the I have following result

New Zealand Philippines

Australia 2700 8700
Japan 5300 000
Philippines
4600
800

Zealand and Philippines

is not given but cost of distillate of USA is

of USA distillate and taking only variable
ustralia , Japan and USA.

requirement cost b/d)

nes Dummy
000
000
000

9X31+0.151X23+0X33
ge 5
w Zealand)

lippines)

y)

f Distillate
Philippines Dummy
000 000
000 000
8700 000

of Distillate because they sufficient surplus to fulfill

ve following result

Philippines Dummy
8700 000
000 000
X111 X112 X211 X212 X121 X122 X221 X222
34167 15833 0 0 0 10000 20000 0