Comparing Methods of Optimization in Solver of Microsoft Excel 2007 and 2019: A Case Study of Statistical Models
Article Sidebar
Main Article Content
Abstract
The objective of this research was to compare the methods to solve optimization in benchmark problems among 29 objective functions of 11 statistical models. The comparison were carried out by these methods: Linear Programming (LP2007), Newton-Raphson (NR) and Conjugate Gradient (CG) in add-ins solver of Microsoft Excel 2007 and linear programming (LP2019), Generalized Reduced Gradient (GRG) and Evolutionary (EV) in Microsoft Excel 2019. The experimental group was divided into linear programming problem using LP2007 and LP2019 methods for efficiency comparison. Likewise, nonlinear model problem using NR, CG, GRG and EV for efficiency comparison. The two decision criteria are the minimizing Mean Absolute Error (MAEmin) and the minimizing mean time (ATmin) for determining the most effective method for finding the answer. The benchmark problems come with exact solutions, exhibiting 7 objective functions utilizing decision method by ATmin. The benchmark problems in relation to the stochastic-nonlinear model with 22 objective functions were performed with the decision method by MAEmin and ATmin. For each scenario that was repeated 100 times, for the benchmark problems with exact solution, LP2019, finding answers was faster than using the LP2007 with regard to traveling salesman problems. However, in cases of the white noise model, linear model, logarithmic model, exponential mode and power model that RMSE is objective function, NR and CG methods are found to work faster than GRG and EV methods in all of the problems. Meanwhile, for the benchmark problems of the stochastic-nonlinear model, the GRG and EV methods are the most effective respectively with decision by MAEmin. Conversely, the CG method and NR method are the fastest means with the decision process by ATmin.
Article Details
The articles published are the opinion of the author only. The author is responsible for any legal consequences. That may arise from that article.
References
[2] M. Somboonrojchai, “The productivity improvement on steel roughing mill,” M.S. thesis, Faculty of Engineering, King Mongkut's University of Technology North Bangkok, 2010 (in Thai).
[3] N. Sedtakomkul, “The applied Monte Carlo simulation method to reorder point and order quantity for purchasing under uncertainties of demand,” M.S. thesis, Faculty of Engineering, King Mongkut's University of Technology North Bangkok, 2011 (in Thai).
[4] A. Chamklin, “Optimization of packing on vehicles: A case study of Srithai Superware Ltd.,” ndependent study, School of Business, The University of the Thai Chamber of Commerce, 2013 (in Thai).
[5] P. Penpakkol and T. Intarakumthornchai, “Inventory management of spare parts under uncertain demand: A case study of particle board manufacturer,” The Journal of KMUTNB, vol 28, no.1, pp. 9–22, 2018 (in Thai).
[6] S. Wititpan, “Application of excel solver to determine optimized parameter for hydrological model,” M.S. thesis, Faculty of Engineering, King Mongkut’s University of Technology Thonburi, 2015 (in Thai).
[7] C. Theppakdee, “Pollution management guideline for medium-sized integarted close system broiler farm,” M.S. thesis, Faculty of Engineering, King Mongkut's Institute of Technology Ladkrabang, 2017 (in Thai).
[8] V.Y. Naimy, “Parameterization of GARCH(1,1) for Paris stock market,” American Journal of Mathematics and Statistics, vol 3, no. 6, pp 357–361, 2013.
[9] F. Farida, “A simplified approach to estimating parameter of the GARCH (1,1) model,” Applied Science and Engineering Progress, vol 12, no. 3, pp. 158–163, 2019.
[10] J. Vasilev, “Solving the traveling salesman problem with the alldifferent constraint in MS Excel,” in Proceedings 5th International Conference on Application of Information and Communication Technology and Statistics in Economy and Education (ICAICTSEE-2015), 2015, pp. 420–423.
[11] P. Minsan and W. Minsan, “Comparing methods of optimization in solver of Microsoft Excel 2007 and 2019,” UTK Research Journal, vol 13, no. 2, pp. 144–161, 2019 (in Thai).
[12] C.E. Miller, A.W. Tucker, and R.A. Zemlin, “Integer programming formulations and traveling salesman problems,”Journal of Association for Computing Machinery 7, pp. 326–329, 1960.
[13] T. Sawik, “A note on the Miller-Tucker-Zemlin model for the asymmetric traveling salesman problem,” Bulletin of the Polish Academy of Sciences Technical Sciences, vol. 64, no. 3, pp. 517–520, 2016.
