Facility Location

1
Facility LocationPart 2
byAnita Lee-Post
2
Center-of-gravity method

1. Establish relative distances between existing
   facilities by placing them on a coordinate grid
   system
2. Use the following formulas to find the X and Y
   coordinates for the location of the new facility

3
Center-of-gravity method formulas
Cx X coordinate of center of gravity Cy X
coordinate of center of gravity dix X
coordinate of the ith location diy Y
coordinate of the ith location Vi volume of
goods moved to or from ith location
4

• Center of gravity method example
• Three automobile showrooms (Bowling Green,
  Elizabethtown, and Morehead) are located
  according to the following grid which represents
  coordinate locations for each showroom in
  Kentucky. Monthly demand of Z-Mobiles at each
  showroom is also given below.

Y
Showroom No. of Z-Mobiles sold per month
Bowling Green Elizabethtown Morehead 1250 1900 2300
Morehead
(790,900)
Elizabethtown
(250,580)
Bowling Green
(100,200)
(0,0)
X
Question What is the best location for a new
Z-Mobile warehouse/temporary storage facility
considering only distances and quantities sold
per month?
5
Center-of-gravity example continued
Y
To begin, you must identify the existing
facilities on a two-dimensional plane or grid
and determine their coordinates.
Morehead
(790,900)
Elizabethtown
(250,580)
Bowling Green
(100,200)
(0,0)
X
You must also have the volume information on the
business activity at the existing facilities.
Showroom No. of Z-Mobiles sold per month
Bowling Green Elizabethtown Morehead 1250 1900 2300
6
Center-of-gravity example continued
You then compute the new coordinates using the
formulas
Y
Morehead
(790,900)
New location
Lexington
Elizabethtown
(250,580)
Bowling Green
(100,200)
(0,0)
X
7
Transportation method using Excel Solver

• Set up the transportation table with n rows
  (factories) and m columns (warehouse) showing
• Demand requirements of each warehouse or
  destination
• Supply availability at each factory or source
• Shipping costs per unit of goods from each
  source to each destination.

8
Transportation method using Excel Solver

• Set up a candidate solution table with n rows and
  m columns showing
• Total units supplied from each source
• Total units shipped to each destination
• The volume of goods to be shipped from each
  source to each destination (i.e., the shipping
  schedule/configuration).

9
Transportation method using Excel Solver

• Set up a cost calculation table
• Detailed shipping costs from each source to each
  destination
• Total cost of the shipping schedule.

10
Transportation method example

• Modified Center-of-Gravity example
• Monthly demand of Z-Mobiles at three existing
  automobile showrooms (Bowling Green,
  Elizabethtown, and Morehead) is
• Monthly supply of Z-Mobiles at two new factories
  to be considered (Lexington, and Louisville) is

Showroom No. of Z-Mobiles sold per month
Bowling Green Elizabethtown Morehead 1250 1900 2300
Factory No. of Z-Mobiles supplied per month
Lexington Louisville 2000 1500
11
Transportation method example continued

• Monthly supply of Z-Mobiles at two existing
  factories (Denver, and Detroit) is
• Cost to transport an Z-mobile from each factory
  to each showroom is

Factory No. of Z-Mobiles supplied per month
Denver Detroit 2500 1450
From/To Bowling Green Elizabethtown Morehead
Denver 25 30 40
Detroit 50 25 25
Lexington 30 50 30
Louisville 40 35 50
Question Which is a better location for a new
Z-Mobile factory Lexington or Louisville?
12
Transportation method using Excel Solver
What is the total transportation cost if the new
factory is located in Lexington?

1. Set up the transportation table for Lexington

A B C D E
1 From/To Bowling Green Elizabethtown Morehead Supply
2 Denver 25 30 40 2500
3 Detroit 50 25 25 1450
4 Lexington 30 50 30 2000
5 Requirements 1250 1900 2300
13
Transportation method using Excel Solver

1. Set up a candidate solution table with formulae
   to compute the total units of Z-mobile shipped to
   each showroom and the total units of Z-mobile
   supplied from each factory

A B C D E
7 Candidate Solution Total Shipped
8 Denver SUM(B8D8)
9 Detroit SUM(B9D9)
10 Lexington SUM(B10D10)
11 Total Supplied SUM(B8B10) SUM(C8C10) SUM(D8D10)
14
Transportation method using Excel Solver

1. Set up a cost calculation table with formulae to
   compute the detailed shipping costs from each
   factory to each showroom and the total shipping
   costs of the entire shipping schedule

A B C D E
13 Cost Calculations
14 Denver B8B2 C8C2 D8D2
15 Detroit B9B3 C9C3 D9D3
16 Lexington B10B4 C10C4 D10D4
17 Total cost SUM(B14D16)
15
Transportation method using Excel Solver

• Access Excel Solver
• Select Tools and then Solver from the Excel menu

16
Transportation method using Excel Solver

• If Solver is not found at that location, then
• Select Tools and then Add-In from the Excel menu
• Select Solver Add-in from the Add-Ins Window

17
Transportation method using Excel Solver

• Set parameters in the Solver Parameters window
• Target cell
• Minimization problem
• Changing cells
• Constraints
• Click on the Options button to set solver
  options

18
Transportation method using Excel Solver

• Set options in the Solver Options window
• Assume Linear Model
• Assume Non-Negative
• Click OK to return to the Solver Parameter
  window

19
Transportation method using Excel Solver

• Click Solve in the Solver Parameters window for
  solver results
• Select Keep Solver Solution in the Solver
  Results window
• Click OK to return to the spreadsheet

20
Transportation method using Excel Solver
Shipping schedule
Lexington cost
21
Transportation method using Excel Solver
What is the total transportation cost if the new
factory is located in Louisville?

1. Make a copy of the Lexington worksheet by
   selecting Edit and then Move or Copy sheet from
   the Excel menu
2. Make sure the Create a copy option is checked
   in the Move or Copy window
3. Click OK

22
Transportation method using Excel Solver

1. Set up the transportation table for Louisville
2. Access Excel Solver as before to solve the above
   transportation problem

A B C D E
1 From/To Bowling Green Elizabethtown Morehead Supply
2 Denver 25 30 40 2500
3 Detroit 50 25 25 1450
4 Louisville 40 35 50 1500
5 Requirements 1250 1900 2300
23
Transportation method using Excel Solver
Louisville cost
Shipping schedule
Conclusion It is cheaper to locate in Lexington
(a saving of 166000 -150000 16000).