Question

How do you put this into excel QM? which method do you select?

Given the following distances between destination nodes, what is the minimum distance that connects all the nodes? Distance From To 1 2 100 1 3 200 2 3 100 2 4 150 2 5 200 150 3 4 300 3 5 250 4 5 4 6 200 6 100 LC

Answer 1

From	To	Path	Distance
1	2	12	100
1	3	13	200
2	3	23	100
2	4	24	150
2	5	25	200
3	4	34	150
3	5	35	300
4	5	45	250
4	6	46	200
5	6	56	100

We create a Allocation matrix , Distance Matrix , Total Distance matrix like this

AQ AR AP AO AN AM AL AK A. AI AH AG Sum 6 5 4 3 2 Allocated =SUM(AJ2:AN2) =SUM (AJ3:AN3) -SUM(AJ4: AN4) 1 1 0 0 0 1 1 2 C C 0 1 C 2 1 0 C 1 C C 3 =SUM(AJ5:AN5) 4 1 0 1 0 C 0 4 5 =SUM(AJ6:AN6) 1 1 C C 5 6 1 1 1 1 Sum 7 To 8 6 5 4 2 Distance 0 0 200 100 1 10 0 200 150 100 0 2 11 0 From 300 150 C C 3 12 200 250 0 4 13 100 C C 5 C 14 15 16 Sum 6 4 2 Total Distance 17 100 0 C 0 100 1 18 100 C C 0 100 C 2 19 150 150 C 20 250 250 C 4 C 21 100 100 C C 5 22 23 SUM(AJ18:AN22) Total 700 24 25

Total Distance matrix is obtained by multiplying Distance matrix with allocation.

Then we will put the following objective function & constraints in the solver :

Objective : To minimize the distance , i.e Total Distance should be minimum

Decision Variable : We change the allocation matrix

Constraints :

1. The Allocation matrix should be binary, i.e 0 or 1.

2. The sum of allocations in one row should be maximum of 1.

3. The sum of allocations in one column should be maximum of 1.

4. The sum of Allocated Distances should be greater than equal to 100 in each row.

The parameters used in the solver are shown below :

Solver Parameters $AN$24 Set Objective: OValue Of To: 0 Min Mаx By Changing Variable Cells: $AJ$2:$AN$6 Subject to the Constraints: $AJ$2:$AN$6= binary $AJ$7:$AN$7 = 1 $AO$18:$AO$22 100 $AO$2:$AO$6 = 1 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Evolutionary Options Method: 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. Solve Help Close X

Hope this helps