Lab Exercise 7. Location Selection Problems
pdf
keyboard_arrow_up
School
University of Illinois, Chicago *
*We aren’t endorsed by this school
Course
355
Subject
Industrial Engineering
Date
Dec 6, 2023
Type
Pages
4
Uploaded by AgentChinchillaPerson113
Lab
Exercise
7
Location
Selection
Problems
‘When
selecting
a
location
for
a
new
facility,
planners
need
some
metric
for
evaluating
how
good
any
particular
location
is.
One
of
the
most
widely-used
metrics
is
distance,
which
most
commonly
measures
the
distance
from
the
new
location
to
its
customers,
its
suppliers,
or
both.
Another
option
is
using
gravity
models
that,
essentially,
measure
the
total
"attractive
force"
of
a
potential
location
to
the
entire
set
of
customers.
In
this
exercise,
we
will
apply
three
different
models
to
location
selection
problems.
These
models
all
assume
a
known
set
of
demand
data,
which
are
contained
in
the
"DemandData.xlsx"
file.
7.1
Distance
Models
We'll
start
with
distance
models
that
measure
the
total
distance
between
a
business
location
and
the
distribution
of
expected
demand.
These
models assume
that
distance
is
a
strong
factor
in
customer
choice;
travel
costs
would
be
incurred
when
customers
travel
to
the
facility,
or
service
providers
travel
to
customers,
or
a
physical
product
is
delivered
to
customers.
In
all
cases,
the
goal
is
to
minimize
these
total
travel
costs,
which
vary
depending
on
whether
travel
is
unrestricted
or
limited
to
a
rectangular
road
grid.
Distance
models
may
be
implemented
with
weighted
or
unweighted
customer
demand.
In
the
unweighted
models,
customers
are
considered
to
be
undifferentiated
and,
accordingly,
the
location
with
the
smallest
sum
of
distances
is
considered
to
be
the
best.
In
the
weighted
models,
customers
are
differentiated.
For
example,
some
might
buy
more
often,
in
greater
quantities,
or
more
profitable
items.
The
weighted
approach
is
also
suitable
for
aggregate
demand,
like
the
total
customers
in
a
zip
code.
The
two
distance
models
we
consider
are:
o
Euclidean:
This
model
uses
the
total
straight-line
distances
between
all
customers
and
the
business
location.
For
a
customer
at
(x.
y.)
and
a
potential
operation
location
(xo,
),
the
distance
between
them
is
\/(x,
—
X0)?
+
(¥
—
¥0)?.
The
Euclidean
model
is
appropriate
when
customers
can
travel
reasonably
directly
to
the
business.
e
Metropolitan
model:
This
model
uses
the
distances
between
the
x-
and
y-coordinates
of
each
customer-location
pair.
For
a
customer
at
(xc,yc)
and
a
potential
operation
location
(xo,
¥o),
the
distance
between
them
is
simply
|
x.
—
xo
|
+
|
yc
—
¥o
|.
The
metropolitan
model
is
appropriate
when
all
travel
is
confined
to
east-west
or
north-south
roads,
which
is
reasonably
accurate
for
many
cities
in
the
United
States.
Additionally,
more
sophisticated
models
(not
considered
here)
might
also
consider
travel
time
variations,
alternate
travel
modes
(like
walking
or
using
public
transportation),
or
route
selection
(as
the
most
direct
route
might
not
be
the
fastest).
Still,
the
general
principle
of
choosing
the
location
with
the
lowest
total
travel
costs
remains
the
same.
7.1.1
Euclidean
Models
Open
the
DemandData
file
to
the
"Euclidean"
worksheet.
There
are
1200
customer
demand
data
points
(which
might
be
derived
from
past
purchase
activity,
demographic
predictors
of
139
shopping
behavior,
or
other
sources).
Each
data
point
represents
a
household
with
location
coordinates
and
income.
We’ll
use
Euclidean
models
to
evaluate
a
potential
facility
location
whose
coordinates
are
given
in
the
range
Q3:R3.
The
various
data
points
will
be
grouped
into
zones
and
spending
will
be
estimated
as
a
function
of
distance
(from
the
zone
centers
to
the
new
store
location)
and
household
income.
1.
In
cell
Q5
(under
the
label
"Distance")
enter
the
formula
"=SQRT((O5-$Q8$3)"2+(P5-
$R$3)"2)".
2.
Select
RS
and
use
autofill
to
copy
the
formula
first
over
the
range
Q6:Q13.
This
fills
out
the
distances
from
the
store
location
to
the
zone
centers.
3.
In
the
next
few
steps,
we’ll
use
VLOOKUP
functions
to
obtain
household
zone
codes.
In
cell
D3,
enter
the
formula
"=VLOOKUP(B3,$K
$4:5L$6,2)".
A
VLOOKUP
function
will
compare
a
cell’s
value
against
a
table
and
return
a
value
from
a
specified
row
and
column
in
that
table.
The
column
is
specified
in
the
function
(in
the
previous
function,
2
means
the
second
column).
The
row
is
found
by
comparing
the
cell’s
value against
the
left
column
of
the
table.
If
the
cell’s
value
is
less
than
the
first
row,
it
will
generate
an
error.
Otherwise,
VLOOKUP
checks
the
next
table
value.
If
that
value
exceeds
the
table
value,
VLOOKUP
will
return
the
value
from
the
first
row
(and
specified
column).
Otherwise,
the
process
continues
until
a
table
value
is
greater
(and
VLOOKUP
returns
the
value
from
the
previous
row
and
specified
column)
or
the
cell’s
value
exceeds
the
last
table
value
(and
the
value
from
the
last
row
and
specified
column
is
returned).
4.
Incell
E3,
enter
the
formula
"=VLOOKUP(C3,$K$9:8L.$11,2)",
then
copy
the
formula
to
cell
H4.
5.
Incell
F3,
enter
the
formula
"=D3+3*E3".
This
combines
the
X and Y
codes
into
a
single
value.
Then
select
D3:F3
and
use
autofill
to
copy
their
formulas
down
to
D1202:F1202.
6.
Now
we’ll
use
another
VLOOKUP
to
convert
income
brackets
to
estimated
household
income
values.
In
cell
G3,
enter
the
formula
"=VLOOKUP(G3,$K$15:$1.$19,2)".
Use
autofill
to
copy
this
formula
down
to
G1202.
Our
worksheet
already
has
a
formula
in
place
for
the
relationship
between
distance
and
sales.
Naturally,
any
given
scenario could
have
a
different
formula
depending
on
factors
like
weather,
the
prevalence
of
car
ownership,
and
the
distribution
of
competitors.
7.
Now
we’ll
combine
household
income,
distance,
and
propensity
to
spend
on
groceries
(the
third
column
in
the
income
code
table)
into
estimated
sales.
In
cell
I3,
enter
the
formula
"=H3*VLOOKUP(G3,8K$15:5M$19,3)*VLOOKUP(F3,SN$5:3R$13,5)",
then
copy
it
down
to
11202
using
autofill.
8.
Incell
R15,
enter
the
formula
"=SUM(I3:11202)".
This
gives
the
estimated
total
sales.
Discuss:
Suppose
your
firm
was considering
four
new
store
locations:
(40,
40),
(40, 60), (60, 60),
and
(60, 40).
Using
this
model,
which
would
be
predicted
to
have
the
highest
sales?
140
7.1.2
Metropolitan
Models
Open
the
"Metropolitan”"
worksheet,
which
has
the
same
demand
data
points.
Now
we’ll
calculate
distances
using
the
metropolitan
model
and
using
direct
distances
to
households
rather
than
zones.
1.
In
cell
E3
(under
the
label
"Distance")
enter
the
formula
"=ABS(B3
-
$0$3)+ABS(C3-
$P$3)".
Use
autofill
to
copy
the
formula
down
to
E1202.
2.
Suppose
we’ve
modeled
that
spending
decreases
by
a
factor
of
e
for
every
20
distance
units.
Then,
we
can
use
the
same
distance
formula
to
find
what
fraction
of
spending
would
happen
at
our
new
location.
In
cell
F3
(under
the
label
"Factor"),
enter
the
formula
"=1/EXP(E3/20)".
Use
autofill
to
copy
the
formula
down
to
F1202.
3.
In
cell
G3
(under
the
label
E(Sales)),
enter
the
formula
"=D3*F3".
This
is
the
product
of
relevant
household
spending
and
the
distance-based
sales factor:
the
expected
sales
from
the
household.
Use
autofill
to
copy
this
formula
to
G1202.
Note
that
the
estimate
of
total
sales
again
appears
in
cell
P15.
4.
Now,
let’s
make
a
simple
two-way
data
table
(using
a
column
of
options
for
one
variable
and
a
row
of
options
for
another)
to
consider
different
location
options
at
the
same
time.
In
cell
N7,
enter
the
formula
"=P15".
5.
Our
column
entries
will
be
the
X
coordinates
for
our
locations.
In
N8,
enter
the
value
40
and
in
N9,
enter
the
value
60.
6.
Our
row
entries
will
be
the
Y
coordinates
for
our
locations.
In
O7,
enter
the
value
40
and
in
P7,
enter
the
value
60.
7.
Select
the
range
N7:P9,
then
go
to
Data
->
What-If
Analysis
->
Data
Table.
In
the
pop-up
window,
enter
O3
for
the
column
input
and
P3
for
the
row
input,
then
click
OK.
Discuss:
Among
the
same
four
locations
at
the
end
of
7.1.1,
which
location
would
have
the
highest
sales
according
to
the
metropolitan
model?
7.2
Huff
Model
In
this
exercise,
we
will
use
the
Huff
model
(a
type
of
gravity
model
for
estimating
how
stores
attract
customers)
to
assess
five
stores
in
five
zones
from
7.1.
Gravity
models
define
attractive
force
between
each
customer-location
pair.
Unlike
the
two
distance
models,
here
the
location
with
the
highest
score
(indicating
the
strongest
attraction
to
the
most
customers)
is
the
best.
Like
gravitational
force,
attraction
decreases
with
distance.
We
assume
that
attraction
decreases
with
the
square
of
distance;
that
is,
doubling
the
distance
will
reduce
attraction
by
a
factor
of
four.
The
worksheet
"Huff"
gives
information
about
travel
times
and
customer
spending
(these
are
derived
from
the
data
used
in
7.1).
It
also
assigns
store
sizes,
which
we have
arbitrarily
assigned.
From
these
data,
we
will
calculate
store
attractions,
visit
probabilities,
and
sales
within
each
zone.
Then,
we
will
calculate
the
market
share
for
each
store
over
the
five
zones.
1.
Suppose
we
are
considering
opening
a
new
store
(labeled
E
in
the
worksheet)
in
an
area
with
four
main
competitors
(labeled
A
through
D).
We
are
evaluating
the
future
sales
for
a
700
square
meter
location
at
coordinates
(30,
65).
In
cells
N7,
M11,
and
N11,
respectively
enter
the
values
700,
30,
and
65.
141
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
2.
We'll
calculate
attractions
assuming
a
lambda
of
1.5
for
all
stores.
In
cell
P10,
enter
the
value
1.5.
In
cell
E3,
enter
the
formula
"=D3/C3~$P$10".
Then
use
autofill
to
copy
the
formula
over
the
range
E4:E7.
Then,
copy
the
range
E3:E7
to
the
ranges
E10:El4,
E17:E21, E24:E28,
and
E31:E35.
3.
Now,
let’s
calculate
the
visit
probability
for
each
store,
which
is
the
store’s
attraction
divided
by
the
sum
of
attractions
in
that
zone.
In
cell
F3,
enter
the
formula
"=E3/SUM(ES$3:E$7)".
Use
autofill
to
copy
the
formula
over
the
range
F4:F7.
4.
Incell
F10,
enter
the
formula
"=E10/SUM(E$10:E$14)".
Use
autofill
to
copy
the
formula
over
the
range
F11:F14.
5.
Incell
F17,
enter
the
formula
"=E17/SUM(ES$17:E$21)".
Use
autofill
to
copy
the
formula
over
the
range
F18:F21.
6.
In
cell
F24,
enter
the
formula
"=E24/SUM(E$24:E$28)".
Use
autofill
to
copy
the
formula
over
the
range
F25:F28.
7.
Incell
F31,
enter
the
formula
"=E31/SUM(E$31:E$35)".
Use
autofill
to
copy
the
formula
over
the
range
F32:F35.
8.
For
each
zone,
the
total
spending
is
already
given
in
column
A.
With
the
attraction
value,
we
can
calculate
each
store’s
sales
in
each
zone.
In
cell
G3,
enter
the
formula
"=F3*A$4".
Use
autofill
to
copy
this
formula
over
the
range
G4:G7.
9.
In
cell
G10,
enter
the
formula
"=F10¥*AS$11".
Use
autofill
to
copy
this
formula
over
the
range
G10:G14.
10.
In
cell
G17,
enter
the
formula
"=F17*A$18".
Use
autofill
to
copy
this
formula
over
the
range
G18:G21.
11.
In
cell
G24,
enter
the
formula
"=F24*A$25".
Use
autofill
to
copy
this
formula
over
the
range
G25:G28.
12.
In
cell
G31,
enter
the
formula
"=F31*A$32".
Use
autofill
to
copy
this
formula
over
the
range
G32:G35.
13.
We
now
have
enough
information
to
find
market
shares
over
the
entire
set
of
zones.
Across
zones,
a
store’s
market
share
is
the
sum
of
its
sales
divided
by
the
sum
of
the
zones’
total
sales
(within
a
zone,
a
store’s
market
share
is
its
visit
probability).
In
cell
O3,
enter
the
formula
"=SUM(G3,G10,G17,G24,G31)/SUM(AS$4,A$11,A$18,A$25,A$32)".
Copy
this
formula
over
the
range
O4:07
using
autofill.
These
are
the
stores’
market
shares.
Discuss:
Locating
a
store
near
a
zone
center
will
often
maximize
store sales
using
the
Huff
model
but not
necessarily
in
reality.
Can
you
think
of
a
few
practical
reasons
why
a
zone
center
might
not
be
a
good
retail
location?
What
alternative
models
might
be
used
to
address
this
weakness?
142