Facility Location Part 2 by Anita LeePost Anita

Facility Location Part 2 by Anita Lee-Post © Anita Lee-Post

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: © Anita Lee-Post

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 © Anita Lee-Post

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 Morehead (790, 900) Bowling Green Elizabethtown Morehead Elizabethtown (250, 580) Bowling Green (100, 200) (0, 0) No. of Z-Mobiles sold per month 1250 1900 2300 X Question: What is the best location for a new Z-Mobile warehouse/temporary storage facility considering only distances and quantities sold per month? © Anita Lee-Post

Center-of-gravity example continued 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. © Anita Lee-Post Y Morehead (790, 900) Elizabethtown (250, 580) Bowling Green (100, 200) (0, 0) X Showroom No. of Z-Mobiles sold per month Bowling Green Elizabethtown Morehead 1250 1900 2300

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) © Anita Lee-Post X

Transportation method using Excel Solver 1. 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. • © Anita Lee-Post

Transportation method using Excel Solver 2. 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). © Anita Lee-Post

Transportation method using Excel Solver 3. Set up a cost calculation table • Detailed shipping costs from each source to each destination; • Total cost of the shipping schedule. © Anita Lee-Post

Transportation method example Modified Center-of-Gravity example • Monthly demand of Z-Mobiles at three existing automobile showrooms (Bowling Green, Elizabethtown, and Morehead) is: Showroom Bowling Green Elizabethtown Morehead • No. of Z-Mobiles sold per month 1250 1900 2300 Monthly supply of Z-Mobiles at two new factories to be considered (Lexington, and Louisville) is: Factory Lexington Louisville © Anita Lee-Post No. of Z-Mobiles supplied per month 2000 1500

Transportation method example continued • Monthly supply of Z-Mobiles at two existing factories (Denver, and Detroit) is: Factory No. of Z-Mobiles supplied per month Denver Detroit • 2500 1450 Cost to transport an Z-mobile from each factory to each showroom is: 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? © Anita Lee-Post

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 © Anita Lee-Post

Transportation method using Excel Solver 2. Set up a candidate solution table with formulae to compute the total units of Zmobile 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(B 8: D 8) 9 Detroit =SUM(B 9: D 9) 10 Lexington =SUM(B 10: D 10) 11 Total Supplied © Anita Lee-Post =SUM(B 8: B 10) =SUM(C 8: C 10) =SUM(D 8: D 10)

Transportation method using Excel Solver 3. 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 13 Cost Calculations 14 Denver =B 8*B 2 =C 8*C 2 =D 8*D 2 15 Detroit =B 9*B 3 =C 9*C 3 =D 9*D 3 16 Lexington =B 10*B 4 =C 10*C 4 =D 10*D 4 17 © Anita Lee-Post Total cost E =SUM(B 14: D 16)

Transportation method using Excel Solver Access Excel Solver • Select Tools and then Solver from the Excel menu © Anita Lee-Post

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 Addin from the Add. Ins Window © Anita Lee-Post

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 © Anita Lee-Post

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 © Anita Lee-Post

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 © Anita Lee-Post

Transportation method using Excel Solver Shipping schedule © Anita Lee-Post Lexington cost

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” © Anita Lee-Post

Transportation method using Excel Solver 1. Set up the transportation table for Louisville 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 2. Access Excel Solver as before to solve the above transportation problem © Anita Lee-Post

Transportation method using Excel Solver Shipping schedule Louisville cost Conclusion: It is cheaper to locate in Lexington (a saving of $166000 150000 = $16000). © Anita Lee-Post