Comparison of spreadsheet-based optimization tools applied to construction site layout problem

This paper compares different spreadsheet-based optimization tools applied to a practical example of a construction site layout problem. The objective of the optimization is to minimize the total time of material transportation by optimal positioning of tower crane and work facilities on the construction site with repetitive operations. Computer programs, such as MS Excel, LibreOffice Calc, and Google Sheets can be applied as modeling tools for a variety of construction optimization problems in addition to their usual functionalities. In this study, LibreOffice Calc Solver, MS Excel Solver, along with other MS Excel add-ins, i.e., OpenSolver, Evolver, and What’sBest, are analyzed. The capabilities of optimization tools mentioned above are examined on the problem of optimal positioning of tower crane and work facilities on the construction site. The results obtained by optimization tools are noted and discussed. The paper ends with conclusions and recommendations for further research.

Allocation of Relief Centre for Flood Victims Using Location Set Covering Problem (LSCP)

The frequency of natural disaster has been increasing over the years, resulting in loss of life, damage to properties and destruction of the environment. Compared to other natural disaster, floods are the most significant natural hazard in Malaysia that affect thousands of people. It becomes of great concern especially, during these recent floods, several relief centers were experiencing problems when most of the flood’s victims needed to move to the other relief centers after the existing shelters were also affected by flood water. Furthermore, the relief centers were congested due to the large number of flood evacuees that were over the capacity provided. This study attempts to adapt an advance mathematical location-allocation models in making decision to locate and allocate flood victims during flood event. The Location Set Covering Problem (LSCP), with capacitated constraint is considered and solved using Excel Solver. The determination of safe locations for relief centres with respect to prone area is proposed by adding three new constraints which are the distance from river, elevation from sea level and rainfall amount. A flood case of Kuala Kuantan, Pahang in 2013 was used as the main study area for the analysis as well as the whole Kuantan district. Two scenarios of split and non-split allowed were tested for allocation process and result showed that the percentage allocation and number of relief centers involved increases when the distance increases accordingly. Hence, the model can be considered for better evacuation plan.

Evaluation of the Microsoft Excel Solver Spreadsheet-Based Program for Nonlinear Expressions of Adsorption Isotherm Models onto Magnetic Nanosorbent

The sorption of basic dyes onto magnetic nanosorbent is commonly used as a novel material to combat powdered activated carbon, which is difficult to handle and separate during water and wastewater treatment. This adsorption method is commonly implemented in water and wastewater treatment due to its low cost and high performance. To explore the feasibility of adsorption, six different nonlinear isotherm models were applied and introduced to evaluate the performance and adsorption mechanisms via Microsoft Excel, and they were then compared to those of MATLAB and OriginPro. The Langmuir best provided the sorption of methylene blue adsorbed for a two-parameter isotherm model. The three-parameter model Toth gave a goodness of fit indicating a heterogeneous sorbent surface. Error function analysis found that the Residual Sum of Squares Error, Chi-square, Coefficient of determination, Average Relative Error, Hybrid Fractional Error Function, Marquardt’s Percent Standard Deviation and Root Mean Square Error provided the best overall results. In comparison, it could be concluded that six isotherm models showed the confidence interval that is simply the best fit at all experimental data points provided by the three software tools. All error function results indicated that the Microsoft Excel Solver function spreadsheet method satisfied all the statistic measures to predict the real variance of the probability of experimental data for all six isotherm models of adsorption of basic dye removal. One added benefit of this Microsoft Excel software tool is the built-in function associated with the desired type of application, which designates the desired type of error/statistical functions not mentioned in this research to the adsorbent materials used.

EVALUASI PENERAPAN EVOLUTIONARY ALGORITHM UNTUK PEMECAHAN TRAVELING SALESMAN PROBLEM

Komputasi dalam memecahkan Traveling Salesman Problem (TSP) dihadapkan pada kombinasi solusi yang meningkat secara eksponensial seiring dengan bertambahnya node pada rute. Oleh karena itu, diperlukan pengembangan metode dalam memecahkannya. Penelitian ini akan menggunakan Evolutionary Algorithm (EA), salah satu algoritma heuristik untuk menangani beban komputasi tersebut. Walaupun demikian algoritma heuristik selalu dihadapkan dengan tantangan konvergensi. Konvergensi solusi yang diperoleh dari algoritma eksak seperti Linear Programming (LP) tidak selalu bisa dicapai oleh algoritma heuristik. Solver merupakan add in program Microsoft Excel yang mampu memecahkan masalah komputasi TSP melalui pendekatan EA (heuristik) dan simplex LP (eksak). Tujuan penelitian ini adalah pertama mendapatkan gambaran mengenai kinerja suatu metode heuristik EA dalam memecahkan TSP. Kedua, mendapatkan gambaran mengenai kompleksitas yang dihasilkan akibat penambahan jumlah node pada TSP. Sampel yang digunakan adalah jarak gudang dan 14 gerai minimarket terdekat di Kota Bandung. TSP untuk 15 node tersebut dipecahkan menggunakan EA dan kemudian dibandingkan dengan luaran dari hasil komputasi LP. Hasil perbandingan dianalisis secara deskriptif yaitu dengan melihat pola-pola yang dihasilkan oleh kedua metode. Semua komputasi baik EA dan LP menggunakan fitur yang disediakan oleh Microsoft Excel Solver. Hasil penelitian menunjukkan bahwa EA memiliki kualitas dalam mendekati konvergensi LP dengan baik dan layak digunakan dalam mendapatkan solusi pada jumlah node yang banyak. Hal ini ditunjukkan oleh pola luaran dari kedua metode dan linearitas yang dihasilkannya. Selain itu juga ditunjukkan pada node yang cukup banyak pada kasus ini, LP menghasilkan solusi tidak layak, di pihak lain solusi tersebut masih bisa diberikan oleh EA.

LOGÍSTICA PARA ROTAS DE DISTRIBUIÇÃO: UM ESTUDO DE CASO

Neste artigo é apresentado um método e um programa computacional para resolver o problema do caixeiro viajante (PCV) com o objetivo de minimizar a distância percorrida nas rotas de distribuição de uma companhia distribuidora de produtos para escritório, papelaria, informática, higiene, limpeza e descartáveis. O software utilizado para resolução do problema é o suplemento Evolutionary do Microsoft Excel Solver, que tem um algoritmo genético pré-programado. O programa computacional foi testado em rotas de distribuição da companhia em São Paulo e Minas Gerais, e os resultados obtidos foram melhores que os anteriormente estabelecidos pela empresa em todos os testes realizados, exigindo tempos de cálculo sempre inferiores a dois minutos.

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

This article presents the possibilities in solving the Weighted Multi-Facility Location Problem and its related optimization tasks using a widely available office software—MS Excel with the Solver add-in. To verify the proposed technique, a set of benchmark instances with various point topologies (regular, combination of regular and random, and random) was designed. The optimization results are compared with results achieved by a metaheuristic algorithm based on simulated annealing principles. The influence of the hardware configuration on the performance achieved by MS Excel Solver is also examined and discussed from both the execution time and accuracy perspectives. The experiments showed that this widely available office software is practical for solving even relatively complex optimization tasks (Weighted Multi-Facility Location Problem with 100 points and 20 centers, which consists of 40 continuous optimization variables in two-dimensional space) with sufficient quality for many real-world applications. The method used is described in detail and step-by-step using an example.

OPTIMIZATION OF LAMPUNG BATIK PRODUCTION USING THE SIMPLEX METHOD

The Production and Profit optimization application in this Batik company was built to solve their problems. This application has the ability to process raw material data, working time data, production requirements data and load data for optimization calculations. The basis for calculations carried out includes optimization calculations using the Simplex Method and the tools used are Excel Solver and Python Programming. The output of this application is that how many Batik are produced based on available resources, the total cost of expenses incurred during the production process as well as gross profit and net of sales. This application can increase company profits and the percentage level of profit depending on the price comparison of sales between one product to another