Question

1. SHOW STEPS USING Excel & SOLVER-- Create and populate a matrix/spreadsheet that has three columns.
   1. Starting location (Always Pearlsburg)
   2. The other locations   
   3. Shortest route solution total time for that path [NOTE: MUST USE SHORTEST ROUTE METHOD - Show work]

Hint: You should have 12 rows.

Note:   use this numbering scheme

1	Pearlsburg
2	Kitchen Corner
3	Quarry
4	Morgan Creek
5	Stone House
6	Cedar Creek
7	Cutters Store
8	Blakes Crossing
9	Homer
10	McKinney Farm
11	Wellis Farm
12	Bottom Town
13	Holbrook

Segment	From Node	To Node	Time
Pearlsburg to Kitchen Corner	1	2	10
Pearlsburg to Quarry	1	3	15
Pearlsburg to Morgan Creek	1	4	12
Kitchen Corner to Cutter's Store	2	7	20
Kitchen Corner to Stone House	2	5	14
Kitchen Corner to Quarry	2	3	8
Quarry to Blake's Crossing	3	8	18
Quarry to Cedar Creek	3	6	9
Morgan Creek to Quarry	4	3	16
Morgan Creek to Cedar Creek	4	6	7
Morgan Creek to Homer	4	9	18
Morgan Creek to McKinney Farm	4	10	11
Stone House to Cutter's Store	5	7	10
Stone House to Blake's Crossing	5	8	6
Cedar Creek to Blake's Crossing	6	8	10
Cedar Creek to Wellis Farm	6	11	17
Cedar Creek to Homer	6	9	5
Cutter's Store to Blake's Crossing	7	8	12
Cutter's Store to Bottom Town	7	12	14
Blake's Crossing to Bottom Town	8	12	6
Blake's Crossing to Holbrook	8	13	15
Blake's Crossing to Wellis Farm	8	11	9
Homer to Wellis Farm	9	11	11
Homer to McKinney Farm	9	10	8
McKinney Farm to Wellis Farm	10	11	21
Wellis Farm to Holbrook	11	13	10
Bottom Town to Holbrook	12	13	12

Answer 1

Shortest route is determined using Excel Solver as follows:

116 Q fx SUMPRODUCT(D2:D28,E2:E28) To Time Flow 10 15 12 20 14 Node Netflow 1 Segment 2 Pearlsburg to Kitchen Corner 3 Pearlsburg to Quar 4 Pearlsburg to Morsan Creek 5 Kitchen Corner to Cutter's Store 6 Kitchen Corner to Stone House 7 Kitchen Corner to Quar 8 Quarry to Blake's Crossin 9 Quarry to Cedar Creek From RHS SSolver Parameters Set Objective: SJS16 Maxⓔ Min O Value Of: 0 By Changing Variable Cells: 18 SES2 SES28 Subject to the Constraints: 16 n Creek to Quar n Creek to Cedar Creek n Creek to Homer n Creek to McKinney Farnm 10 SHS2:SHS14 = SJS2:SJ514 Add 18 10 Change 12 13 10 14 Stone House to Cutter's Store 15 Stone House to Blake's Crossin 16 Cedar Creek to Blake's Crossin 17 Cedar Creek to Wellis Farm 1 Cedar Creek to Homer 9 Cutter's Store to Blake's Crossin 20 Cutter's Store to Bottom Town 21 Blake's Crossingto Bottom Town 22 Blake 's Crossingto Holbrook 23 Blake 's Crossingto Wellis Farm 24 Homer to Wellis Farm 25 Homer to McKinney Farm 26 McKinney Farm to Wellis Farm 27 Wellis Farm to Holbrook 28 Bottom Town to Holbrook Delete Total Time -44 10 Reset All 12 14 Make Unconstrained Variables Non-Negative 12 12 13 Select a Solving Method: Simplex LP Options 15 Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems. 10 10 13 13 21 10 12 Solve Close 12

EXCEL FORMULAS:

Node	Netflow		RHS
1	=SUMIF($B$2:$B$28,G2,$E$2:$E$28)-SUMIF($C$2:$C$28,G2,$E$2:$E$28)	=	1
2	=SUMIF($B$2:$B$28,G3,$E$2:$E$28)-SUMIF($C$2:$C$28,G3,$E$2:$E$28)	=	0
3	=SUMIF($B$2:$B$28,G4,$E$2:$E$28)-SUMIF($C$2:$C$28,G4,$E$2:$E$28)	=	0
4	=SUMIF($B$2:$B$28,G5,$E$2:$E$28)-SUMIF($C$2:$C$28,G5,$E$2:$E$28)	=	0
5	=SUMIF($B$2:$B$28,G6,$E$2:$E$28)-SUMIF($C$2:$C$28,G6,$E$2:$E$28)	=	0
6	=SUMIF($B$2:$B$28,G7,$E$2:$E$28)-SUMIF($C$2:$C$28,G7,$E$2:$E$28)	=	0
7	=SUMIF($B$2:$B$28,G8,$E$2:$E$28)-SUMIF($C$2:$C$28,G8,$E$2:$E$28)	=	0
8	=SUMIF($B$2:$B$28,G9,$E$2:$E$28)-SUMIF($C$2:$C$28,G9,$E$2:$E$28)	=	0
9	=SUMIF($B$2:$B$28,G10,$E$2:$E$28)-SUMIF($C$2:$C$28,G10,$E$2:$E$28)	=	0
10	=SUMIF($B$2:$B$28,G11,$E$2:$E$28)-SUMIF($C$2:$C$28,G11,$E$2:$E$28)	=	0
11	=SUMIF($B$2:$B$28,G12,$E$2:$E$28)-SUMIF($C$2:$C$28,G12,$E$2:$E$28)	=	0
12	=SUMIF($B$2:$B$28,G13,$E$2:$E$28)-SUMIF($C$2:$C$28,G13,$E$2:$E$28)	=	0
13	=SUMIF($B$2:$B$28,G14,$E$2:$E$28)-SUMIF($C$2:$C$28,G14,$E$2:$E$28)	=	-1
	Total Time	=	=SUMPRODUCT(D2:D28,E2:E28)

Shortest route is indicated by flow variable having value of 1

Therefore, shortest route from node 1 to 13 is: 1-4-6-8-13

Minimum time = 44