# Optimization of the Weighted Multi-Facility Location Problem Using MS Excel

^{1}

^{2}

^{3}

^{4}

^{*}

## Abstract

**:**

## 1. Introduction

#### 1.1. Literature Review

#### 1.2. Main Aim of This Work

#### 1.3. Problem Formulation

## 2. Materials and Methods

#### 2.1. Evolutionary Method Excel-Solver

#### 2.2. Metaheuristic Algorithm

- Generate random solution.
- Set temperature to its maximum limit.
- Repeat points 4 to 6 for a predefined number of times.
- Transform current solution.
- Replace the current solution by the transformed solution with the probability given by the Metropolis criterion.
- Save the best solution if found.
- Cool the temperature.
- When the temperature is not below its lower limit, repeat the whole process in point 3.
- Terminate the algorithm and return the best solution found.

## 3. Performance Evaluation

#### 3.1. Hardware and Software Configuration

**Hardware configurations.**Calculations were performed on three computers with different hardware configurations:

- HW 1-CPU: AMD A10-9620P RADEON R5 2.50 GHz; memory 8.00 GB RAM.
- HW 2-CPU: INTEL CORE i7-7700 3.60 GHz; memory 32.00 GB RAM.
- HW 3-CPU: INTEL CORE i9-9900K 3.60 GHz; memory 32.00 GB RAM.

**Software configuration.**A 64-bit operating system, Windows 10 Enterprise LSTC, MS Excel 2016, 64 bit, part of the Microsoft Office Suite, with the Solver add-in installed, which performs calculations. In the basic version it allows only one method of calculation using evolutionary algorithms. The Excel environment is used to enter data, calculate intermediate results, and display the result of the entire process.

#### 3.2. Benchmark Instances

- Each group is solved separately by an evolutionary method in Excel as a Single Facility Location Problem (SFLP) task. The sum of the resulting values is then the solution of the MFLP problem with this topology (best result 360,186.787);
- The lowest value of 300 calculations by the metaheuristic method on three hardware configurations (the best result 360,186.7863)—as this method provided the best solution, it serves as a reference value.

- by Excel-Solver evolutionary method on HW1;
- by Excel-Solver evolutionary method on HW2;
- by Excel-Solver evolutionary method on HW3;
- by metaheuristic algorithm on HW1;
- by metaheuristic algorithm on HW2;
- by metaheuristic algorithm on HW3.

- minimal solution;
- average solution;
- standard deviation;
- coefficient of variation;
- difference between the reference and the minimal solutions in percent;
- difference between the reference and the average solutions in percent;
- histogram, classes are from 1 percent to 10 percent deviation from reference;
- graph with coordinates for result for the reference and the minimal solution.

#### 3.3. Results

#### 3.3.1. Histograms and Solutions for Benchmark A

#### 3.3.2. Histograms and Solutions for Benchmark B

#### 3.3.3. Histograms and Solutions for Benchmark C

#### 3.3.4. Other statistical values for the Evolutionary and Metaheuristic method

## 4. Discussion and Conclusions

## Supplementary Materials

## Author Contributions

## Funding

## Data Availability Statement

## Conflicts of Interest

## References

- Vazsonyi, A. Which Door Has the Cadillac: Adventures of a Real-Life Mathematician; Writers Club Press: Bloomington, IN, USA, 2002. [Google Scholar]
- Němec, P.; Stodola, P. Optimization of the Multi-Facility Location Problem Using Widely Available Office Software. Algorithms
**2021**, 14, 106. [Google Scholar] [CrossRef] - Fernandes, D.R.M.; Rocha, C.; Aloise, D.; Ribeiro, G.M.; Santos, E.M.; Silva, A. A Simple and Effective Genetic Algorithm for the Two-Stage Capacitated Facility Location Problem. Comput. Ind. Eng.
**2014**, 75, 200–208. [Google Scholar] [CrossRef] - Rahmani, A.; MirHassani, S.A. A Hybrid Firefly-Genetic Algorithm for the Capacitated Facility Location Problem. Inf.Sci.
**2014**, 283, 70–78. [Google Scholar] [CrossRef] - Chen, C.-H.; Ting, C.-J. Combining Lagrangian Heuristic and Ant Colony System to Solve the Single Source Capacitated Facility Location Problem. Transp. Res. Part E Logist. Transp. Rev.
**2008**, 44, 1099–1122. [Google Scholar] [CrossRef] - Guner, A.R.; Sevkli, M. A Discrete Particle Swarm Optimization Algorithm for Uncapacitated Facility Location Problem. J. Artif. Evol. Appl.
**2008**, 2008, 861512. [Google Scholar] [CrossRef][Green Version] - Zhang, H.; Liu, J.; Yao, X. A Hybrid Evolutionary Algorithm for Reliable Facility Location Problem. In Parallel Problem Solving from Nature–PPSN XVI. PPSN 2020. Lecture Notes in Computer Science; Bäck, T., Preuss, M., Deutz, A., Wang, H., Doerr, C., Emmerich, M., Trautmann, H., Eds.; Springer: Cham, Switzerland, 2020; Volume 12270. [Google Scholar] [CrossRef]
- Aras, N.; Yumusak, S.; Altmel, I.K. Solving the Capacitated Multi-Facility Weber Problem by Simulated Annealing, Threshold Accepting and Genetic Algorithms. In Metaheuristics, Operations Research/Computer Science Interfaces Series; Doerner, K.F., Gendreau, M., Greistorfer, P., Gutjahr, W., Hartl, R.F., Reimann, M., Eds.; Springer: Boston, MA, USA, 2007; Volume 39. [Google Scholar] [CrossRef]
- Manzour-al-Ajdad, S.M.H.; Torabi, S.A.; Eshghi, K. Single-Source Capacitated Multi-Facility Weber Problem—An Iterative Two Phase Heuristic Algorithm. Comput. Oper. Res.
**2012**, 39, 1465–1476. [Google Scholar] [CrossRef] - Amiri-Aref, M.; Shiripour, S.; Ruiz-Hernández, D. Exact and Approximate Heuristics for the Rectilinear Weber Location Problem with a Line Barrier. Comput. Oper. Res.
**2021**, 132, 105293. [Google Scholar] [CrossRef] - Tong, L.; Wang, Z.-T. Application of Plant Growth Simulation Algorithm on Solving Facility Location Problem. Syst. Eng. Theory Pract.
**2008**, 28, 107–115. [Google Scholar] [CrossRef] - Luis, M.; Ramli, M.F.; Lin, A. A Greedy Heuristic Algorithm for Solving the Capacitated Planar Multi-Facility Location-Allocation Problem. AIP Conf. Proc.
**2016**, 1782, 040010. [Google Scholar] [CrossRef] - Foltin, P.; Vlkovský, M.; Mazal, J.; Husák, J.; Brunclík, M. Discrete Event Simulation in Future Military Logistics Applications and Aspects. In Privacy Enhancing Technologies; Metzler, J.B., Ed.; Springer: Berlin/Heidelberg, Germany, 2018; Volume 10756, pp. 410–421. [Google Scholar]
- Stodola, P.; Mazal, J. Tactical Decision Support System to Aid Commanders in their Decision-Making. In Proceedings of the Modelling and Simulation for Autonomous Systems, Rome, Italy, 15–16 June 2016; pp. 396–406. [Google Scholar]
- Stodola, P.; Drozd, J.; Šilinger, K.; Hodický, J.; Procházka, D. Collective Perception Using UAVs: Autonomous Aerial Reconnaissance in a Complex Urban Environment. Sensors
**2020**, 20, 2926. [Google Scholar] [CrossRef] [PubMed] - Blaha, M.; Šilinger, K. Application support for topographical-geodetic issues for tactical and technical control of artillery fire. Int. J. Circuits Syst. Signal Process.
**2018**, 12, 48–57. [Google Scholar] - Hošková-Mayerová, Š.; Talhofer, V.; Otřísal, P.; Rybanský, M. Influence of Weights of Geographical Factors on the Results of Multicriteria Analysis in Solving Spatial Analyses. ISPRS Int. J. Geo-Inf.
**2020**, 9, 489. [Google Scholar] [CrossRef] - Mazal, J.; Rybanský, M.; Bruzzone, A.G.; Kutěj, L.; Scurek, R.; Foltin, P.; Zlatník, D. Modelling of the microrelief impact to the cross country movement. Int. Conf. Harb. Marit. Multimodal Logist. Modeling Simul.
**2020**, 66–70. [Google Scholar] [CrossRef] - Františ, P.; Hodický, J. Virtual reality in presentation layer of C3I system. In Proceedings of the MODSIM05—International Congress on Modelling and Simulation: Advances and Applications for Management and Decision Making, Proceedings 2005, Melbourne, Australia, 12–15 December 2005; pp. 3045–3050. [Google Scholar]
- Sekelova, M.; Korba, P.; Hovanec, M.; Mrekaj, B.; Oravec, M.; Szabo, S. Options of measuring the work performance of the air traffic controller. In Transport Means 2018; Kaunas University of Technology: Trakai, Lithuania, 2018; pp. 1476–1481. [Google Scholar]
- Vikhar, P.A. Evolutionary Algorithms: A Critical Review and Its Future Prospects. In Proceedings of the 2016 International Conference on Global Trends in Signal Processing, Information Computing and Communication (ICGTSPICC), Jalgaon, India, 22–24 December 2016; IEEE: Piscataway Township, NJ, USA, 2016; pp. 261–265. [Google Scholar] [CrossRef]
- ‘Solver’. Available online: https://www.solver.com/ (accessed on 1 June 2021).
- Byrd, R.H.; Mary, E.; Hribar, M.E.; Nocedal, J. An Interior Point Algorithm for Large-Scale Nonlinear Programming. SIAM J. Optimization
**1999**, 9, 877–900. [Google Scholar] [CrossRef] - Gill, P.E.; Murray, W.; Wright, M.H. Practical Optimization; Academic Press: London, UK, 1981. [Google Scholar]

Parameter | Value |
---|---|

Max time | Unlimited |

Iterations | Unlimited |

Constraint precisions | 0.000001 |

Convergence | 0.0001 |

Population size | 100 |

Random seed | 0 |

Mutation rate | 0.075 |

Maximum time without improvement | 30 s |

Max subproblems | Unlimited |

Maximum feasible solutions | Unlimited |

Integer optimality | 1% |

Name of Term | Explanation |
---|---|

Reference | Optimal or reference solution for an instance. |

Minimal | Minimal value from set of 100 results |

Average | Average value from set of 100 results |

Dif. ref-min | Difference between reference and minimal value (%) |

Dif. ref-avg | Difference between reference and average value (%) |

Standard dev. | Standard deviation for set of 100 results |

Coefficient of variation | Relative standard deviation for set of 100 results |

Avg. time | Average time of computing one result in set of 100 results |

Name of Acronym | Explanation |
---|---|

EHW1 | Results of operations with evolutionary method on HW1 |

EHW2 | Results of operations with evolutionary method on HW2 |

EHW3 | Results of operations with evolutionary method on HW3 |

MHW1 | Results of operations with metaheuristic algorithm on HW1 |

MHW2 | Results of operations with metaheuristic algorithm on HW2 |

MHW3 | Results of operations with metaheuristic algorithm on HW3 |

Benchmark Instance | Number of Points | Number of Centers | Reference |
---|---|---|---|

A | 100 | 20 | 310,048.77 * |

B | 100 | 20 | 360,186.79 |

C | 100 | 20 | 3,399,273.00 |

Benchmark Instance | A | B | C |
---|---|---|---|

Reference value | 310,048.77 | 360,186.77 | 3,399,273.00 |

Minimal | 319,256.49 | 364,155.78 | 3,496,336.34 |

Average | 369,489.23 | 422,268.28 | 3,666,323.69 |

Dif. ref-min (%) | 2.97 | 1.10 | 2.86 |

Dif. ref-avg (%) | 19.17 | 17.24 | 7.86 |

Standard dev. | 24,062.23 | 42,651.21 | 91,422.78 |

Coefficient of variation | 0.0651 | 0.1010 | 0.0249 |

Benchmark Instance | A | B | C |
---|---|---|---|

Reference value | 310,048.77 | 360,186.77 | 3,399,273.00 |

Minimal | 314,559.20 | 360,530.87 | 3,407,806.82 |

Average | 343,051.52 | 397,906.86 | 3,513,082.48 |

Dif. ref-min (%) | 1.45 | 0.10 | 0.25 |

Dif. ref-avg (%) | 10.64 | 10.47 | 3.35 |

Standard dev. | 14,906.14 | 37,515.08 | 56,379.08 |

Coefficient of variation | 0.0435 | 0.0943 | 0.0160 |

Benchmark Instance | A | B | C |
---|---|---|---|

Reference value | 310,048.77 | 360,186.79 | 3,399,273.00 |

Minimal | 314,772.06 | 360,344.72 | 3,417,691.86 |

Average | 341,912.54 | 388,651.12 | 3,524,354.39 |

Dif. ref-min (%) | 1.52 | 0.04 | 0.54 |

Dif. ref-avg (%) | 10.28 | 7.90 | 3.68 |

Standard dev. | 12,411.75 | 37,700.39 | 59,072.10 |

Coefficient of variation | 0.0363 | 0.0970 | 0.0168 |

Benchmark Instance | A | B | C |
---|---|---|---|

Reference value | 310,048.77 | 360,186.79 | 3,399,273.00 |

Minimal | 310,048.77 | 360,186.79 | 3,399,273.00 |

Average | 312,878.10 | 360,214.37 | 3,420,905.56 |

Dif. ref-min (%) | $2\times {10}^{-7}$ | $2\times {10}^{-7}$ | 0 |

Dif. ref-avg (%) | 0.91 | 0.01 | 0.64 |

Standard dev. | 5747.78 | 58.32 | 17,009.00 |

Coefficient of variation | 0.0184 | 0.0002 | 0.0050 |

Benchmark Instance | A | B | C |
---|---|---|---|

Reference value | 310,048.77 | 360,186.79 | 3,399,273.00 |

Minimal | 310,048.77 | 360,186.79 | 3,399,823.32 |

Average | 312,993.06 | 360,484.42 | 3,419,586.89 |

Dif. ref-min (%) | $2\times {10}^{-7}$ | 0 | 0.02 |

Dif. ref-avg (%) | 0.95 | 0.08 | 0.60 |

Standard dev. | 5639.52 | 2671.73 | 13,667.40 |

Coefficient of variation | 0.0180 | 0.0074 | 0.0040 |

Benchmark Instance | A | B | C |
---|---|---|---|

Reference value | 310,048.77 | 360,186.79 | 3,399,273.00 |

Minimal | 310,048.77 | 360,186.79 | 3,400,034.97 |

Average | 313,718.26 | 360,207.91 | 3,422,185.23 |

Dif. ref-min (%) | $2\times {10}^{-7}$ | $2\times {10}^{-7}$ | $6.22\times {10}^{-3}$ |

Dif. ref-avg (%) | 1.18 | $5.87\xb7{10}^{-3}$ | 0.66 |

Standard dev. | 6288.90 | 41.25 | 15,981.47 |

Coefficient of variation | 0.0200 | 0.0001 | 0.0047 |

Benchmark Instance | EHW1 | MHW1 |
---|---|---|

Dif. ref-min (%) | 2.86 | 0 |

Dif. ref-avg (%) | 7.86 | 0.64 |

Coefficient of variation | 0.0249 | 0.0050 |

Benchmark Instance | A | B | C |
---|---|---|---|

EHW1 | 2.97 | 1.10 | 2.86 |

EHW2 | 1.45 | 0.10 | 0.25 |

EHW3 | 1.52 | 0.04 | 0.54 |

Benchmark Instance | A | B | C |
---|---|---|---|

EHW1 | 19.17 | 17.24 | 7.86 |

EHW2 | 10.64 | 10.47 | 3.35 |

EHW3 | 10.28 | 7.90 | 3.68 |

Benchmark Instance | A | B | C |
---|---|---|---|

EHW1 | 0.0651 | 0.1010 | 0.0249 |

EHW2 | 0.0435 | 0.0944 | 0.0160 |

EHW3 | 0.0363 | 0.0970 | 0.0168 |

Benchmark Instance | A | B | C |
---|---|---|---|

EHW1 | 125 | 108 | 79 |

EHW2 | 55 | 54 | 50 |

EHW3 | 51 | 47 | 44 |

MHW1 | 22 | 25 | 27 |

MHW2 | 8 | 8 | 8 |

MHW3 | 6 | 7 | 7 |

Publisher’s Note: MDPI stays neutral with regard to jurisdictional claims in published maps and institutional affiliations. |

© 2021 by the authors. Licensee MDPI, Basel, Switzerland. This article is an open access article distributed under the terms and conditions of the Creative Commons Attribution (CC BY) license (https://creativecommons.org/licenses/by/4.0/).

## Share and Cite

**MDPI and ACS Style**

Němec, P.; Stodola, P.; Pecina, M.; Neubauer, J.; Blaha, M.
Optimization of the Weighted Multi-Facility Location Problem Using MS Excel. *Algorithms* **2021**, *14*, 191.
https://doi.org/10.3390/a14070191

**AMA Style**

Němec P, Stodola P, Pecina M, Neubauer J, Blaha M.
Optimization of the Weighted Multi-Facility Location Problem Using MS Excel. *Algorithms*. 2021; 14(7):191.
https://doi.org/10.3390/a14070191

**Chicago/Turabian Style**

Němec, Petr, Petr Stodola, Miroslav Pecina, Jiří Neubauer, and Martin Blaha.
2021. "Optimization of the Weighted Multi-Facility Location Problem Using MS Excel" *Algorithms* 14, no. 7: 191.
https://doi.org/10.3390/a14070191