Inputs - Costs, Capacities, Demands
Demand Region
Production and Transportation Cost per 1,000,000 Units Fixed Low
Supply Region N. America S. America Europe Asia Africa Cost ($) Capacity
N. America 81 92 101 130 115 6,000 10
S. America 117 77 108 98 100 4,500 10
Europe 102 105 95 119 111 6,500 10
Asia 115 125 90 59 74 4,100 10
Africa 142 100 103 105 71 4,000 10
Demand 12 8 14 16 7
Decision Variables
Small Large
Demand Region - Production Allocation (1000 Units) Plants Plants
Supply Region N. America S. America Europe Asia Africa (1=open) (1=open)
N. America 0 0 0 0 0 0 0
S. America 12 8 0 0 0 0 1
Europe 0 0 0 0 0 0 0
Asia 0 0 4 16 0 0 1
Africa 0 0 10 0 7 0 1
Constraints
Supply Region Excess Capacity
N. America 0
S. America 0
Europe 0
Asia 0
Africa 3
N. America S. America Europe Asia Africa
Unmet Demand 0 0 0 0 0
Objective Function
Cost = $ 23,751 23,751
Fixed High
Cost ($) Capacity
9,000 20
6,750 20
9,750 20
6,150 20
6,000 20
Total
Plants
0
1
0
1
1
Some alternative scenarios to try
1. What if a plant must be built in Europe? In this case add the
constraint I16 ≥ 1 in Solver and rerun.
2. What if plants must be built in every market? In this case, we need t
constraints I14:18 ≥ 1 in Solver and rerun.
ope? In this case add the
y market? In this case, we need to add
run.
Problem 2
Sources/ $/Ton Mile Tons Coordinates
Markets Fn Dn xn yn
Gurgaon 0.90 500 700 1200
Sources Faridabad 0.95 300 250 600
Manesar 0.85 2700 225 825
Bhopal 1.50 225 600 500
Nashik 1.50 150 1050 1200
Markets Bengaluru 1.50 250 800 300
Chennai 1.50 175 925 975
Nagpur 1.50 300 1000 1080
0 0
Facility Location
X,Y system Lattitude and longitude
x= 0.0 ###
y= 0.0 ###
Cost = $ 4,730,833 ###
Page 4
Problem 2
dn Lattitude Longitude Distance
1389 28.459497 77.026634 ###
650 28.408913 77.317787 ###
855 28.361879 76.940117 ###
781 23.259933 77.412613 ###
1595 20.00053 73.782707 ###
854 12.971599 77.594566 ###
1344 13.08268 80.270721 ###
1472 21.1458 79.088158 ###
1400
1200
1000
800
Y
600
400
200
0
0 200 400 600 800 1000 1200
X
Page 5
Problem 2
Using Solver to Optimize Location for Steel Appliances
1. Using Data | Analysis | Solver, solve the model to obtain
location of facility represented by pink dot in chart below.
2. Change tonnage from St. Lous in Cell D7 to 1,700. How do
you expect location of facility to change? Try using Solver.
3. Change tonnage from St. Lous in Cell D7 to 2,700. How do
you expect location of facility to change? Try using Solver.
800 1000 1200
Page 6
Problem 2
Appliances
odel to obtain
n chart below.
o 1,700. How do
using Solver.
o 2,700. How do
using Solver.
Page 7
Nashik Chennai Faridabad Indore
Fixed cost 150000 250000 200000 100000
Variable cost 20 11 35 30
Graph
Units Nashik Chennai Faridabad Indore
0 150000 250000 200000 100000
15000 450000 415000 725000 550000
Cost-volume analysis
800000
700000
600000
$
500000
400000
300000
200000
100000
0
0 2000 4000 6000 8000 10000 12000 14000 16000
Units
Nashik Chennai Faridabad Indore
Pairwise Comparison Matrix for the Criteria and Consistency Metrics
Pairwise Comparison Matrix for the Criteria
Expert perception
Criteria F1 F2 F3
F1 1 0.5 3
F2 2 1 4
F3 0.3333333333 0.25 1
Sum 3.3333333333 1.75 8
Normalisation 0.3 0.285714286 0.375
of column
0.6 0.571428571 0.5
0.1 0.142857143 0.125
Sum 1 1 1
Consistency A X
check
F1 F2 F3
F1 1 0.5 3 0.3202380952
F2 2 1 4 0.5571428571
F3 0.3333333333 0.25 1 0.1226190476
# of Criteria 3
CI = 0.0091623971 0.018324794
RI = 0.58
CR = 0.0157972363 < 0.1 consistent
The consistency index (ci) measures the degree of logical consistency among pair-wise compariso
random index (ri) is the average CI value of randomly-generated comparison matrices using Saaty
preference scale sorted by the number of items being considered.
Consistency ratio (cr) indicates the amount of allowed inconsistency (0.10 or 10%). Higher numbe
mean the comparisons are less consistent. Smaller numbers mean comparisons are more consiste
CRs above 0.1 means the pair-wise comparison should be revisited or revised.
0.32 0.56 0.12
F1 F2 F3 Global weight
Jharkhand 0.13 0.38 0.3 0.2904
Telangana 0.24 0.29 0.24 0.268
Orissa 0.07 0.07 0.21 0.0868
Gujarat 0.56 0.26 0.25 0.3548
Row average
0.32
0.56
0.12
1
AX
MMULT
0.966666667 3.018587361
1.688095238 3.02991453
0.368650794 3.006472492
AVG (�max) 3.018324794
ency among pair-wise comparisons. The
comparison matrices using Saaty’s
d.
ncy (0.10 or 10%). Higher numbers
an comparisons are more consistent.
ed or revised.
Rank
2
3
4
1
