Question

I need help with this problem. I have done the first part, but I don't know if its correct or not.

Thanks

Ernie's Market is a small independent chain of neighborhood grocery stores. Emie's distributes high volume product to its stores from a company owned and operated warehouse. Additionally, the company is looking to start a back-hauling program with its suppliers in an effort to reduce its transportation costs. Kimberly-Clark operates a factory in the vicinity of Ernie's stores and warehouse and will be the first participant in a pilot back- hauling program. Figure 1 is a road map showing the locations of Kimberly-Clark and Emie's Warehouse and stores and the distances along each of the road segments. Here are your tasks: 1. Ernie's believes that its costs are directly proportional to the transportation distance. Determine delivery and back-haul routes that will minimize Ernie's total costs. 2. Now, assume that the reverse transportation costs are not directly proportional to the distance, but instead are a linear function of the distance. Table 1 shows the reverse route cost ratios which can be used as multipliers to calculate the relative transportation costs associated with traversing road segments in the reverse direction. Find the cost minimizing return routes from the Kimberly-Clark factory to each of Ernie's stores and Ernie's warehouse Warehouse Figure 1 - Road System showing Ernie's Warehouse, Stores, and Kimberly-Clark Factory

Answer 1

Create Excel model as follows:

C23 - fx =SUMPRODUCT(D2:D20, C2:C20) E H On Route Distance G Netflow A 1 From 2 Warehouse 3 Warehouse 4 Warehouse Nodes Warehouse Supply/Demand TTTTTTTTTTTTTTTTTT Kimberly-Clark 0 Kimberly-Clark Kimberly-Clark 18 Kimberly-Clark Total Distance = 0

EXCEL FORMULA:

=SUMPRODUCT(D2:D20,02:C20) F - fx B TOT А | в | On Route Distance C23 A 1 From 2 Warehouse 3 Warehouse 4 Warehouse А А B Supply/Demand B Nodes Netflow Warehouse =SUMIF($A$2:$A$20,F2, $C$2:$C$20)-SUMIF($B$2:$B$20, F2, $C$2:$C$20) =SUMIF($A$2:$A$20,F3,$C$2:$C$20)-SUMIF($B$2:$B$20,F3, $C$2:$C$20) =SUMIF($A$2:$A$20, F4, $C$2:$C$20)-SUMIF($B$2:$B$20, F4, $C$2:$C$20) =SUMIF($A$2:$A$20,F5, $C$2:$C$20)-SUMIF($B$2:$B$20, F5, $C$2:$C$20) =SUMIF($A$2:$A$20, F6, $C$2:$C$20)-SUMIF($B$2:$B$20, F6, $C$2:$C$20) =SUMIF($A$2:$A$20,F7,$C$2:$C$20)-SUMIF($B$2:$B$20, F7,$C$2:$C$20) =SUMIF($A$2:$A$20, F8, $C$2:$C$20)-SUMIF($B$2:$B$20, F8,$C$2:$C$20) =SUMIF($A$2:$A$20,F9,$C$2:$C$20)-SUMIF($B$2:$B$20, F9,$C$2:$C$20) H -SUMIF($A$2:$A$20,F10,$C$2:$C$20)-SUMIF($B$2:$B$20,F10,$C$2:$C$20) Kimberly-Clark I-SUMIF($A$2:$A$20,F11, $C$2:$C$20)-SUMIF($B$2:$B$20,F11, $C$2:$C$20) O JO JO = lo BT E = 0 = -1 E F E L H F G Kimberly-Clark Kimberly-Clark H Kimberly-Clark Total Distance = 1 =SUMPRODUCT(D2:D20,C2:C20) !

Enter Solver Parameters as follows:

IS Solver Parameters Set Objective: SC523 To: Max Min Value Of: 0 By Changing Variable Cells: SC$2:$C$20 Subject to the Constraints: SG$2:$G$11 = $1$2:$1$11 Add Change Delete Reset All Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems. Solve Close

Click Solve to generate optimal solution:

C23 @ fx =SUMPRODUCT(D2:D20,02:C20) J K L M N O P Q R S To Netflow Supply/Demand On Route Distance 1 F Nodes Warehouse А B 1 = А 1 From 2 Warehouse 3 Warehouse 4 Warehouse А Α Ι в B 0 0 = = 0 0 S Solver Results B D D D в F В ТЕ с Т Е Solver found a solution. All constraints and optimality conditions are satisfied. Reports Answer Keep Solver Solution Sensitivity Limits O Restore Original Values H Kimberly-Clark E -1 = 1 ELF E G Ε Ι Η Return to Solver Parameters Dialog O 0 OK | Cancel F G 0 Kimberly-Clark Kimberly-Clark H Kimberly-Clark Solver found a solution. All constraints and optimality conditions are satisfied. When Simplex LP is used, this means Solver has found a global optimal solution. o H 0 Total Distance - 193

The shortest route is indicated by On Route variable, having value 1

So, the shortest route is: Warehouse - A - B - E - F - Kimberly-Clark

Shortest distance = 19