Facility Location Part 2

1. Facility LocationPart 2 byAnita Lee-Post

2. Center-of-gravity method • Establish relative distances between existing facilities by placing them on a coordinate grid system; • 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 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 examplecontinued 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.

6. Center-of-gravity examplecontinued You then compute the new coordinates using the formulas: Y Morehead New location (790,900) 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:

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: 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? • Set up the transportation table for Lexington

13. Transportation method using Excel Solver • 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:

14. Transportation method using Excel Solver • 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:

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? • Make a copy of the Lexington worksheet by selecting Edit and then Move or Copy sheet from the Excel menu • Make sure the “Create a copy” option is checked in the Move or Copy window • Click “OK”

22. Transportation method using Excel Solver • Set up the transportation table for Louisville • Access Excel Solver as before to solve the above transportation problem

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