© anita lee-post facility location part 2 by anita lee-post

© Anita Lee-Post

Facility LocationFacility LocationPart 2Part 2

Facility LocationFacility LocationPart 2Part 2

bybyAnita Lee-PostAnita Lee-Post

bybyAnita Lee-PostAnita Lee-Post

© Anita Lee-Post

Center-of-gravity methodCenter-of-gravity methodCenter-of-gravity methodCenter-of-gravity method

1.1. Establish relative distances between Establish relative distances between existing facilities by placing them on existing facilities by placing them on a coordinate grid system;a coordinate grid system;

2.2. Use the following formulas to find Use the following formulas to find the X and Y coordinates for the the X and Y coordinates for the location of the new facility:location of the new facility:

1.1. Establish relative distances between Establish relative distances between existing facilities by placing them on existing facilities by placing them on a coordinate grid system;a coordinate grid system;

2.2. Use the following formulas to find Use the following formulas to find the X and Y coordinates for the the X and Y coordinates for the location of the new facility:location of the new facility:

© Anita Lee-Post

Center-of-gravity method formulasCenter-of-gravity method formulas

C = d V

V x

ix i

i

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

C = d V

Vy

iy i

i

© Anita Lee-Post

Question: What is the best location for a new Z-Mobile warehouse/temporary storage facility considering only distances and quantities sold per month?

Question: What is the best location for a new Z-Mobile warehouse/temporary storage facility considering only distances and quantities sold per month?

XX

YY

Bowling GreenBowling Green(100,200)(100,200)

ElizabethtownElizabethtown(250,580)(250,580)

MoreheadMorehead(790,900)(790,900)

(0,0) (0,0)

Center of gravity method exampleCenter of gravity method example• Three automobile showrooms (Bowling Green, Elizabethtown, and Three automobile showrooms (Bowling Green, Elizabethtown, and

Morehead) are located according to the following grid which represents Morehead) are located according to the following grid which represents coordinate locations for each showroom in Kentucky. Monthly demand coordinate locations for each showroom in Kentucky. Monthly demand of Z-Mobiles at each showroom is also given below.of Z-Mobiles at each showroom is also given below.

Showroom No. of Z-Mobiles sold per month

Bowling GreenElizabethtownMorehead

125019002300

© Anita Lee-Post

Center-of-gravity exampleCenter-of-gravity example continuedcontinued

XX

YY

Bowling Green(100,200)

Elizabethtown(250,580)

Morehead(790,900)

(0,0)(0,0)

To begin, you must identify the existing facilities on a two-dimensional plane or grid and determine their coordinates.

You must also have the volume information on the business activity at the existing facilities.



125019002300

© Anita Lee-PostXX

YY

Bowling GreenBowling Green(100,200)(100,200)

ElizabethtownElizabethtown(250,580)(250,580)

MoreheadMorehead(790,900)(790,900)

(0,0) (0,0)

Center-of-gravity exampleCenter-of-gravity example continuedcontinued

C = 100(1250) + 250(1900) + 790(2300)

1250 + 1900 + 2300 =

2,417,000

5,450 = x 443.49

C = 200(1250) + 580(1900) + 900(2300)

1250 + 1900 + 2300 =

3,422,000

5,450 = y 627.89

You then compute the new coordinates using the formulas:

LexingtonNew location

© Anita Lee-Post

Transportation method using Excel SolverTransportation method using Excel Solver

1.1. Set up the transportation table with Set up the transportation table with n rows (factories) and m columns n rows (factories) and m columns (warehouse) showing:(warehouse) showing:

• Demand requirements of each Demand requirements of each warehouse or destination;warehouse or destination;

• Supply availability at each Supply availability at each factory or source;factory or source;

• Shipping costs per unit of Shipping costs per unit of goods from each source to goods from each source to each destination.each destination.

1.1. Set up the transportation table with Set up the transportation table with n rows (factories) and m columns n rows (factories) and m columns (warehouse) showing:(warehouse) showing:

• Demand requirements of each Demand requirements of each warehouse or destination;warehouse or destination;

• Supply availability at each Supply availability at each factory or source;factory or source;

• Shipping costs per unit of Shipping costs per unit of goods from each source to goods from each source to each destination.each destination.

© Anita Lee-Post

Transportation method using Excel SolverTransportation method using Excel SolverTransportation method using Excel SolverTransportation method using Excel Solver

2.2. Set up a candidate solution table Set up a candidate solution table with n rows and m columns showing:with n rows and m columns showing:

• Total units supplied from each source;Total units supplied from each source;

• Total units shipped to each destination;Total units shipped to each destination;

• The volume of goods to be shipped The volume of goods to be shipped from each source to each destination from each source to each destination (i.e., the shipping (i.e., the shipping schedule/configuration).schedule/configuration).

2.2. Set up a candidate solution table Set up a candidate solution table with n rows and m columns showing:with n rows and m columns showing:

• Total units supplied from each source;Total units supplied from each source;

• Total units shipped to each destination;Total units shipped to each destination;

• The volume of goods to be shipped The volume of goods to be shipped from each source to each destination from each source to each destination (i.e., the shipping (i.e., the shipping schedule/configuration).schedule/configuration).

© Anita Lee-Post


3.3. Set up a cost calculation tableSet up a cost calculation table

• Detailed shipping costs from each Detailed shipping costs from each source to each destination;source to each destination;

• Total cost of the shipping schedule.Total cost of the shipping schedule.

3.3. Set up a cost calculation tableSet up a cost calculation table

• Detailed shipping costs from each Detailed shipping costs from each source to each destination;source to each destination;

• Total cost of the shipping schedule.Total cost of the shipping schedule.

© Anita Lee-Post

Transportation method exampleTransportation method exampleTransportation method exampleTransportation method example

Modified Center-of-Gravity exampleModified Center-of-Gravity example

• Monthly demand of Z-Mobiles at three existing Monthly demand of Z-Mobiles at three existing automobile showrooms (Bowling Green, Elizabethtown, automobile showrooms (Bowling Green, Elizabethtown, and Morehead) is:and Morehead) is:

• Monthly supply of Z-Mobiles at two new factories to be Monthly supply of Z-Mobiles at two new factories to be considered (Lexington, and Louisville) is: considered (Lexington, and Louisville) is:

Modified Center-of-Gravity exampleModified Center-of-Gravity example

• Monthly demand of Z-Mobiles at three existing Monthly demand of Z-Mobiles at three existing automobile showrooms (Bowling Green, Elizabethtown, automobile showrooms (Bowling Green, Elizabethtown, and Morehead) is:and Morehead) is:

• Monthly supply of Z-Mobiles at two new factories to be Monthly supply of Z-Mobiles at two new factories to be considered (Lexington, and Louisville) is: considered (Lexington, and Louisville) is:



125019002300

Factory No. of Z-Mobiles supplied per month

LexingtonLouisville

20001500

© Anita Lee-Post

Transportation method example Transportation method example continuedcontinuedTransportation method example Transportation method example continuedcontinued

• Monthly supply of Z-Mobiles at two existing factories Monthly supply of Z-Mobiles at two existing factories (Denver, and Detroit) is:(Denver, and Detroit) is:

• Cost to transport an Z-mobile from each factory to each Cost to transport an Z-mobile from each factory to each showroom is: showroom is:

• Monthly supply of Z-Mobiles at two existing factories Monthly supply of Z-Mobiles at two existing factories (Denver, and Detroit) is:(Denver, and Detroit) is:

• Cost to transport an Z-mobile from each factory to each Cost to transport an Z-mobile from each factory to each showroom is: showroom is:

Factory No. of Z-Mobiles supplied per month

DenverDetroit

25001450

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? Question: Which is a better location for a new Z-Mobile factory: Lexington or Louisville?

© Anita Lee-Post


1.1. Set up the transportation table for Set up the transportation table for LexingtonLexington

1.1. Set up the transportation table for Set up the transportation table for LexingtonLexington

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

What is the total transportation cost if the new factory is located in Lexington?

© Anita Lee-Post


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

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

A B C D E

7Candidate Solution Total Shipped

8 Denver =SUM(B8:D8)

9 Detroit =SUM(B9:D9)

10 Lexington =SUM(B10:D10)

11Total Supplied =SUM(B8:B10) =SUM(C8:C10) =SUM(D8:D10)

© Anita Lee-Post


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

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

A B C D E

13Cost Calculations

14 Denver =B8*B2 =C8*C2 =D8*D2

15 Detroit =B9*B3 =C9*C3 =D9*D3

16 Lexington =B10*B4 =C10*C4 =D10*D4

17 Total cost =SUM(B14:D16)

© Anita Lee-Post


Access Excel SolverAccess Excel Solver

• Select Select ToolsTools and then and then SolverSolver from from the Excel menuthe Excel menu

Access Excel SolverAccess Excel Solver

• Select Select ToolsTools and then and then SolverSolver from from the Excel menuthe Excel menu

© Anita Lee-Post


• If If SolverSolver is not is not found at that found at that location, then:location, then:

• Select Select ToolsTools and and then then Add-InAdd-In from from the Excel menuthe Excel menu

• Select Select Solver Add-Solver Add-inin from the Add- from the Add-Ins WindowIns Window

• If If SolverSolver is not is not found at that found at that location, then:location, then:

• Select Select ToolsTools and and then then Add-InAdd-In from from the Excel menuthe Excel menu

• Select Select Solver Add-Solver Add-inin from the Add- from the Add-Ins WindowIns Window

© Anita Lee-Post


• Set parameters in Set parameters in the Solver the Solver Parameters Parameters windowwindow

• Target cell Target cell • Minimization Minimization

problemproblem• Changing cellsChanging cells• ConstraintsConstraints

• Click on the Click on the “Options” button “Options” button to set solver to set solver optionsoptions

• Set parameters in Set parameters in the Solver the Solver Parameters Parameters windowwindow

• Target cell Target cell • Minimization Minimization

problemproblem• Changing cellsChanging cells• ConstraintsConstraints

• Click on the Click on the “Options” button “Options” button to set solver to set solver optionsoptions

© Anita Lee-Post


• Set options in the Set options in the Solver Options Solver Options windowwindow

• Assume Linear Assume Linear ModelModel

• Assume Non-Assume Non-NegativeNegative

• Click “OK” to Click “OK” to return to the return to the Solver Parameter Solver Parameter windowwindow

• Set options in the Set options in the Solver Options Solver Options windowwindow

• Assume Linear Assume Linear ModelModel

• Assume Non-Assume Non-NegativeNegative

• Click “OK” to Click “OK” to return to the return to the Solver Parameter Solver Parameter windowwindow

© Anita Lee-Post


• Click “Solve” in the Click “Solve” in the Solver Parameters Solver Parameters window for solver window for solver resultsresults

• Select “Keep Solver Select “Keep Solver Solution” in the Solution” in the Solver Results Solver Results windowwindow

• Click “OK” to return Click “OK” to return to the spreadsheetto the spreadsheet

• Click “Solve” in the Click “Solve” in the Solver Parameters Solver Parameters window for solver window for solver resultsresults

• Select “Keep Solver Select “Keep Solver Solution” in the Solution” in the Solver Results Solver Results windowwindow

• Click “OK” to return Click “OK” to return to the spreadsheetto the spreadsheet

© Anita Lee-Post


1.1. Make a copy of the Make a copy of the Lexington worksheet by Lexington worksheet by selecting selecting EditEdit and then and then Move or Copy sheetMove or Copy sheet from from the Excel menuthe Excel menu

2.2. Make sure the “Create a Make sure the “Create a copy” option is checked copy” option is checked in the Move or Copy in the Move or Copy windowwindow

3.3. Click “OK”Click “OK”

1.1. Make a copy of the Make a copy of the Lexington worksheet by Lexington worksheet by selecting selecting EditEdit and then and then Move or Copy sheetMove or Copy sheet from from the Excel menuthe Excel menu

2.2. Make sure the “Create a Make sure the “Create a copy” option is checked copy” option is checked in the Move or Copy in the Move or Copy windowwindow

3.3. Click “OK”Click “OK”

What is the total transportation cost if the new factory is located in Louisville?

© Anita Lee-Post


1.1. Set up the transportation table for LouisvilleSet up the transportation table for Louisville

2.2. Access Excel Solver as before to solve the Access Excel Solver as before to solve the above transportation problemabove transportation problem

1.1. Set up the transportation table for LouisvilleSet up the transportation table for Louisville

2.2. Access Excel Solver as before to solve the Access Excel Solver as before to solve the above transportation problemabove 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

© Anita Lee-Post


Shipping schedule Louisville cost

Conclusion: It is cheaper to locate in Lexington (a saving of $166000 -150000 = $16000).

© anita lee-post facility location part 2 by anita lee-post

Documents

anita leepost center

anita leepost slide

anita leepost question

ith location slide

center of gravity c

y coordinates

center of gravity d

source supply availability