Question

ABC is going to build a new manufacturing plant to serve five market regions. ABC has four supply sources. The coordinates of location, the demand of each market region, maximum units from each supply source and the shipping cost from each supply source or to each market region are shown below.

Shipping Cost (S/Ton Mile) 1.5 Supply (Tons) 180 х Supply Y Coordinate Coordinate Sources Si 450 820 S2 1 220 280 900 S3 2 140 600 400 S4 2 170 850 700 Shipping Cost Demand (S/Ton Mile) х Y Markets (Tons) Coordinate Coordinate 100 100 MI 1 110 М2 1.5 70 200 300 1000 М3 2.5 85 100 М4 3 90 600 1100 M5 2 65 800 900

a) Where should ABC locate its plant? (Hint: Use Gravity Model and Excel Solver).

b) Identify some of the influencing factors that affect ABC’s network design decision.

Answer 1

The excel model is shown below

A1 Supply A Supply L M N O P Q A 1 1 x fc B C Shipping Supply 1.5 180 220 2 140 2 170 X 450 51 S2 1 H Distance Cost 935.36089291403.04134 942.5497334 942.549733 721.1102551 1442.22051 1101.135777 2202.27155 820 900 400 700 280 S3 600 850 S4 Cost 110 100 300 Markets Shipping Demand M1 1 M2 1.5 70 M3 2.5 85 M4 3 90 2 Distance 141.4213562 360.5551275 1004.987562 1252.996409 1204.159458 100 200 1000 600 800 10 11 100 1100 900 141.421356 540.832691 2512.46891 3758.98923 2408.31892 M5 65 Cost 15352.1142 15 Location 0 O ... Sheet1436 Sheet1437 Sheet1438 Sheet1439 READY 0 0 -- + 115%

The formulas are shown below

Al x fc Supply : А Supply H 1 E Y Shipping Supply Cost 180 450 280 820 900 400 700 220 140 170 Distance =SQRT((D2-D$15)^2+(E2-E$15)^2) =SQRT((D3-D$15)^2+(E3-E$15)^2) =SQRT((D4-D$15)^2+(E4-E$15)^2) =SQRT((D5-D$15)^2+(E5-E$15)^2) =G2*B2 =G3*B3 =G4*B4 =G5*B5 S3 S4 600 850 Shipping Demand Cost 110 100 1.5 300 Markets M1 M2 M3 M4 M5 100 200 1000 600 800 Distance =SQRT((D8-D$15)^2+(E8-E$15)^2) =G8*B8 =SQRT((D9-D$15)^2+(E9-E$15)^2) =G9*B9 =SQRT((D10-D$15)^2+(E10-E$15)^2)=G10*B10 =SQRT((D11-D$15)^2+(E11-E$15)^2) =G11*B11 =SQRT (D12-D$15)^2+(E12-E$15)^2) =G12*B12 85 ma 100 1100 900 Cost =SUM(H2:H5)+SUM(H8:H12) Location 0 24 ... Sheet1436 Sheet1437 Sheet1438 READY | HH | | | - -+ 115%

The solver parameters are shown below

Supply Solver Parameters - A Supply x B Shipping 1.5 1 fc C Supply 180 220 140 Set Objective: SH$15 450 280 S2 Y 820 900 400 700 Distance Cost 935.36089291403.04134 942.5497334 942.549733 721.1102551 1442.22051 1101.135777 2202.27155 To: O Max Min value of: 600 170 850 By Changing Variable Cells: SD$15:$E$15 Subject to the Constraints: Y 1 100 Add 1.5 200 Markets Shipping Demand M1 110 M2 70 M3 2.5 85 M4 M5 Distance 141.4213562 360.5551275 1004.987562 1252.996409 1204.159458 Cost 141.421356 540.832691 2512.46891 3758.98923 2408.31892 Change 100 300 100 1100 900 10 1000 90 600 Delete 800 Reset All Cost 15352.1142 Load/Save 15 Location 0 0 ✓ Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close ... Sheet1436 Sheet1437 Sheet1438 Sheet1439 ENTER 115%

The result is shown below

A1 Supply I J K L M N O P Q A A Supply 1 x B Shipping 1.5 1 fc C Supply 180 220 140 450 280 E Y 820 900 400 700 G H Distance Cost 227.4565202341.18478 414.5197819414.519782 300.9485288 601.897058 208.0351371 416.070274 S2 600 Solver Results 170 850 Y 1 Solver found a solution. All Constraints and optimality conditions are satisfied. Reports Answer Keep Solver Solution Sensitivity Limits O Restore Original Values 100 Markets Shipping Demand M1 110 M2 70 M3 2.5 85 M4 M5 2 1.5 Distance Cost 807.0617205 807.06172 594.7696552 892.154483 696.98972951742.47432 404.1784648 1212.53539 256.4629863 512.925973 200 1000 600 10 100 300 100 1100 900 90 Return to Solver Parameters Dialog Outline Reports 800 OK Cancel Save Scenario... 14 15 Location Cost 6940.82379 641.974 698.007 Solver found a solution. All Constraints and optimality conditions are satisfied. When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution. 24 ... Sheet1436 Sheet1437 Sheet1438 Sheet1439 READY D -- + 115%

Apart from the cost and the distance factors, the organization should also focus on various other factors such as lead time, lot size, etc.