Solver Add-in Research Articles

Tariff Optimization under Incentive Regulation of Public Services Using Simplex method

The tariffs of regulated public services under price cap regulation are variables defined in the tariff review process, subject to a major technical complexity and many restrictions. Given the complex scenario that involves the tariffs definition, the present paper seeks to verify the effectiveness of the implementation of a Linear Programming model to support decision-making in optimal tariffs definition. The Solver add-in of Microsoft Excel software was used to generate the optimal solution for the proposed model. The results indicated that the proposed model generated a reasonable result, incorporating the various restrictions for modeling and ensuring details at a sufficient level for modeling adjustments throughout the process, if necessary.

Getting Beyond the First Result of Solving a Vehicle Routing Problem

The simple vehicle routing problem (VRP) is a common topic of discussion in introductory operations research/management science courses. The VRP can be framed in a variety of ways, and it can be difficult to solve to optimality. For solution purposes, introductory textbooks demonstrate how Excel’s Evolutionary Solver (ES) add-in produces a routing. The ES utilizes a genetic algorithm with a heuristic stopping rule to produce a routing that is not guaranteed to be optimal. Beyond pointing out that search controls, such as maximum execution time, may be extended and followed by restart(s) of ES, textbook treatments do not offer alternative ways to continue the search for a possibly better routing. In this paper, a suite of ways is presented in which students may investigate beyond what ES produces or any other optimality-uncertain VRP solution method. The suite includes perturbation methods and other ways that function within an Excel spreadsheet environment that is popular with students and textbook writers. Because there is no demonstrable feature that confirms optimality, the student problem Solver must settle for a ‘best found’ result as unsettling as it may be. The incertitude is addressed.

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.

An iterative algorithm for groundwater pumping energy cost minimization

This article presents an algorithm for solving a very common water resources management problem. This involves finding the optimal solution to the problem of minimizing the cost of energy consumed during pumping and transporting water from a group of wells to a reservoir to satisfy the water needs of a residential area. This algorithm addresses the problem of minimizing the cost of the energy consumed by pumping groundwater through an iterative process. The results of the algorithm are verified by solving the same problem with the generalized reduced gradient algorithm, which is integrated into the Microsoft Excel Solver add-in. It is found that the algorithm accurately calculates the optimal solution of the problem, while the results of the solutions of similar mathematical programming problems with this algorithm can be used as guides for other more complicated problems.

Optimization of the Multi-Facility Location Problem Using Widely Available Office Software

Multi-facility location problem is a type of task often solved (not only) in logistics. It consists in finding the optimal location of the required number of centers for a given number of points. One of the possible solutions is to use the principle of the genetic algorithm. The Solver add-in, which uses the evolutionary method, is available in the Excel office software. It was used to solve the benchmark in 4 levels of difficulty (from 5 centers for 25 points to 20 centers for 100 points), and one task from practice. The obtained results were compared with the results obtained by the metaheuristic simulated annealing method. It was found that the results obtained by the evolutionary method are sufficiently accurate. Their accuracy depends on the complexity of the task and the performance of the HW used. The advantage of the proposed solution is easy availability and minimal requirements for user knowledge.

Distinguishing areas of organic, biodynamic and conventional farming by means of multispectral images. A pilot study

The aim of the present investigation was to develop a basic methodological scheme for distinguishing organic, biodynamic and conventional areas of winter wheat cultivation, based on the satellite images from Sentinel-2. The target areas are located in the experimental field of the Institute of Agriculture in Karnobat, Bulgaria. The plant condition was determined on a three-point rating scale according to the average plant height, percentage of projective cover of the aboveground plant parts and intensity of the green color of leaves during the end of tillering to complete ripeness. To determine the homogeneity of the target polygons, we used a small drone DJI Phantom 4, with 1/2.3 CMOS camera and Dronedeploy to compile flight plans for shooting and photogrammetric processing of the images. ArcGIS was used in GIS procedures and analyses. Microsoft Excel, with Solver ADD-IN was used to clusterize the target polygons in order to distinguish the polygons of organic, biodynamic and conventional farming by the multispectral indices (CRI 1, MSAVI, NDVI, NDWI, PSSRa, PSSRc, WDRVI). All selected indices were calculated in GIS environment. Ground observations showed that there were differences in the condition of plants in different cultivation systems during the selected observation phases. The obtained results reveal that the differences in dynamics and development during the growth and development stages of the wheat in the organic, biodynamic and conventional fields can be traced by spectral characteristics from satellite images and they can be used to distinguish them.

Optimasi Pemotongan Besi Tulangan Pada Pekerjaan Struktur Menggunakan Metode Linear Programming

In construction projects, reinforcing iron is a very important material in the implementation of construction work. The remaining iron material cannot be avoided in the field, so the figure for the remaining unused material affects the costs incurred by the contractor. Given that iron material is one of the materials with a high price on the market. One solution to reduce the amount of remaining iron material is to optimize iron cutting to get the number of pieces as needed with minimal cutting. The method used is Linear Programming with the help of the Solver Add-In program in Microsoft Excel. The research was conducted on a 3-storey building construction project, namely Puskesmas Kampung Dalam, Kab. Padang Pariaman. Works that reflect iron work on the upper and lower structures. The analysis results were obtained from 7.31% and cost Rp. 27.067.887,-. for the ironworks reviewed. So that the use of the Linear Programming method in the case study of this research is able to optimize the remaining iron material quite well and produce minimum remaining reinforcing iron.

Bir İhtisas Hastanesinde Ameliyathane Programının Optimizasyonu: Bir Vaka Uygulaması

The operations management in health services has recently become extremely important since hospitals’ main objective is to provide high-quality health services to patients while reducing their costs and improving their financial assets for their survival and growth. In this respect, operating rooms are of great interest to the hospitals since they are the hospital’s largest cost and revenue center. However, despite their criticality in health services, they are generally bottleneck resources in hospitals. Therefore, increasing the efficiency of operating rooms results in improved patient satisfaction and reduction in the cost of surgical operations. At this stage, operating room scheduling is the key to accomplish these two conflicting goals at a reasonable level simultaneously. It includes the timing of each operation as well as the assignment of surgical resources to each operation such as operating room and surgical teams over a few days or a week. Operating room surgical schedules often present logistical difficulties in terms of assigning doctors to specific operating rooms. Due to a wide variety of factors such as room availability, working hours in a week, doctor preferences, and operating room capabilities, surgical scheduling can prove to be challenging. In many hospital administrators manually modify the assignments on a case-by-case basis, which makes it difficult and time-consuming when dealing with surgical schedules. In this paper, we try to implement a linear programming procedure, which transforms the operating room schedule into a working schedule that dynamically changes weekly; and can be programmed to incorporate different scenarios within the hospital-based on specific hospital parameters. With the application of a binary linear programming model on illustrative problems by using Excel Solver Add-in, we demonstrate the advantages of using such an approach in optimizing static and dynamic surgical assignments weekly in order to meet a specific goal.

Estimated glomerular filtration rate in children: adapting existing equations for a specific population.

Creatinine-based glomerular filtration rate (GFR)-estimating equations frequently do not perform well in populations that differ from the development populations in terms of mean GFR, age, pathology, ethnicity, and diet. After first evaluating the performance of existing equations, the aim of this study was to demonstrate the utility of an in-house modification of the equations to better fit a specific population. Estimated GFR using 8 creatinine-based equations was first compared to 2-sample 51Cr-ethylenediaminetetra-acetic acid plasma clearance in non-cancer and cancer groups independently. The groups were then divided into development and validation sets. Using the development set data, the Microsoft® Excel SOLVER add-in was used to modify the parameters of 7 equations to better fit the data. Using the validation set data, the performance of the original and modified equations was compared. Two hundred fifty-six GFR measurements were performed in 160 children. GFR was overestimated in both groups (non-cancer 4.3-22.6ml/min/1.73m2, cancer 17.2-46.6ml/min/1.73m2). The root mean square error (RMSE) was 19.1-21.8ml/min/1.73m2 (non-cancer) and 18.6-20.8ml/min/1.73m2 (cancer). The P30 values were 49.1-73.0% (non-cancer) and 19.6-66.0% (cancer). Modifying the parameters of seven equations resulted in significant improvements in the P30 values in the non-cancer (65.0-85.0%) and cancer (79.6-87.8%) groups. Modifying the parameters of pediatric GFR estimating-equations using a simple Excel-based tool significantly improved their accuracy in both non-cancer and cancer populations. Graphical abstract.

Removal studies of Pb (II) ions by carbon powder prepared from pοlyethylene terephthalate (PET) bοttles

ABSTRACT In this study, an innovative low-cost carbon powder that is obtained by pοlyethylene terephthalate (PET) bοttles waste was investigated for the removal of Pb (II) ions from aqueous solution. For this, carbon sample was characterised by a liquid displacement method, point of zero charge pH (pHPZC), scanning electron microscopy (SEM) and Fourier transform infrared spectroscopy (FTIR). In addition to this, batch experiments were carried out to study the effect of the main parameters such as contact time, initial Pb (II) concentration, adsorbent dosage and pH of the medium on the adsorption process. As results, the maximum adsorption capacity reached 97.95 mg.g−1 with an initial Pb (II) concentration of 100 mg.L−1, pH = 6.0, sorbent concentration of 0.4 g.L−1, agitation speed of 360 rpm, and temperature of 20°C. Dynamic studies using the solver add-in with Microsoft Excel showed that the adsorption of Pb (II) is well described by a pseudo-second-order kinetic model, while its equilibrium isotherm analysed by Langmuir, Freundlich, Redlich–Peterson and Sips models confirmed that the Langmuir isotherm model is in good agreement with the experimental data of the process. From environmental and economic view, the results of the present study show that PET waste is advantageous and an effective adsorbent for the elimination of lead from aqueous solutions.

Оптимізаційні задачі в структурі освітнього процесу закладів вищої освіти з фізичної культури і спорту

Актуальність теми дослідження. Доведено, що виконання професійної діяльності сучасними фахівцями на високому професійному рівні в умовах розвиненого інформаційного середовища передбачає вільне воло- діння інформаційно-комунікаційними технологіями під час розв’язання професійно орієнтованих завдань. Науково-педагогічна спільнота зосереджена на проблемі формування інформаційно-технологічної компетент- ності здобувачів закладів вищої освіти з фізичної культури й спорту. Мета дослідження – розробити методику формування інформаційно-технологічної компетентності здобувачів закладів вищої освіти з фізичної культури та спорту на прикладі майбутніх фахівців із туризму в процесі моделювання туристичних потоків. Методи до- слідження – вивчення, аналіз і систематизація літературних джерел й узагальнення передового педагогічного досвіду, що проводились із метою окреслення важливості формування в здобувачів вищої освіти вміння за- стосовувати інформаційно-комунікаційні технології для виконання практично орієнтованих завдань; методи математичного програмування застосовували в процесі розробки завдань оптимізації. Результати роботи. Запропоновано вдосконалення змісту професійної підготовки здобувачів вищої освіти шляхом формування компетенції виконувати професійно орієнтовані завдання в процесі моделювання туристичних потоків. Сут- ність методики розглянуто в ході розв’язання класичної задачі комівояжера, що полягає в знаходженні замк- неного маршруту мінімальної довжини, котра поєднує всі пункти туристичної мережі. Висновки. Запропонований підхід реалізовується у формі лекційних і практичних занять. На лекційному занятті передбачено висвіт- лення ролі й місця інформаційно-комунікаційних технологій у сфері обслуговування споживачів туристських послуг, способів їх застосування для складання туристських маршрутів, огляд типових завдань оптимізації, формулювання постановки задачі комівояжера та представлення різноманіття методів знаходження її розвʼязку. На практичному занятті пропонуємо колективне обговорення маршруту прогулянки й визначення можливих способів пересування, алгоритм виконання завдання за допомогою надбудови «Пошук рішення MS Excel», де побудову матриці відстаней також здійснюємо з використанням карти Google, а також зображення знайденого маршруту.

Simulating the Influences of Soil Water Stress on Leaf Expansion and Senescence of Winter Wheat

Current crop models usually need a lot of input information to simulate crop leaf area index and have relatively large simulation errors under soil water stress, which need to be improved. In this study, based on experiments conducted in glass soil columns and field under rainout shelters in four years (2012-2016) in Yangling, Shaanxi Province in China, we attempted to establish a dynamic model for simulating leaf expansion and senescence of winter wheat under soil water stress. First, a temperature response function was established with four cardinal temperatures (base temperature, lower optimum temperature, higher optimum temperature, and maximum temperature). Then two soil water stress functions were established to quantify the effect of soil water stress on the processes of leaf expansion and leaf senescence of main stem per plant. The first order derivative of a logistic function was then modified with the temperature and soil water stress response function and was used to simulate the daily rate of leaf area expansion and senescence of main stem. The parameters of the new model were estimated using the Solver add-in in MS Excel and then validated based on the data of soil column experiments in 2014-2015 growing season. Then the new model was evaluated with another dataset of column experiments conducted in 2015-2016. Furthermore, the influence of soil water stress on wheat tillering was included in the new model based on open field experiments in 2012-2013 growing season. Then, the new leaf area index (LAI) simulation model was further verified with the data of open field experiments under rainout shelter (2013-2014) and rainfed condition (2004-2005, 2005-2006, and 2008-2009) in different sites. A comparison was conducted between new LAI model and the original LAI module of CERES-Wheat. The results showed that the leaf expansion rate was not affected when the relative soil water availability was greater than 0.7; water stress inhibited the leaf expansion when relative soil water availability was between 0.2 and 0.7; leaves withered and yellowed when relative soil water availability was less than 0.2. The overall average root mean square error (RMSE) and residual accumulation coefficient (CRM) were 9.78 cm2 plant−1, -0.03, and 6.51 cm2 plant−1; 0.05 for model calibration and validation, respectively. The RMSE of LAI decreased by an average of 47.89% compared with the original LAI module of CERES-Wheat. The results of this study can help to improve the current CERES-Wheat model for model applications in the arid and semi-arid regions of China.

Determination of the Hansen solubility parameters and the Hansen sphere radius with the aid of the solver add-in of Microsoft Excel

This paper describes the use Excel’s Solver of Microsoft and desirability function for the determination of Hansen’s solubility parameters (HSPs). The Excel’s solver is powerful while at the same time being user-friendly and easy to comprehend. Results of HSPs for different polymers and oils were evaluated and compared with the results obtained by the use of other tools; no significant differences were found between the Microsoft Excel solution and either the professional program HSPiP or other software platform used by different authors. Improvements introduced in the Excel book developed by Steven Abbott for this purpose, by the inclusion of some restrictions and functions, allow simultaneously optimizing the diameter of the Hansen sphere together with the determination of Hansen’s parameters, so that a tool, available to professional and researcher, can be used for these determinations with a high degree of precision, using any of the nonlinear optimization algorithms available in the Solver add-in. Finally, it is illustrated how the use of programming in Excel VBA can lead to a friendly interface for these determinations.

Beta Chemical’s Journal Budget Dilemma

In this short and hypothetical case study, we present a management problem of optimization. With the backdrop of COVID-19, we explore how Beta Company should optimize its cost for international medical journal exposure to ensure its brand’s credibility in an international pharmaceutical market. The supplementary section accompanies MS excel models with data. By playing this excel based optimization game; the players would be able to appreciate the use of Solver Add-in to simulate real-life linear programming problems. The users would learn about the application of sum of product excel function and finally get accustomed about media allocation models in marketing under budget constraints.

IMPLEMENTASI ALGORITMA GREY WOLF OPTIMIZER (GWO) DI TOKO CITRA TANI JEMBER

Generally, optimization is defined as the process of determining the minimum or maximum value that depends on the function of the goal, even now there are many problems regarding optimization. One of them is the problem regarding the selection of goods to be included in a limited storage medium called Knapsack problem. Knapsack problems have different types and variations. This study will solve the problem of bounded knapsack multiple constraints by implementing the Grey Wolf Optimizer (GWO) algorithm. The problem of bounded knapsack multiple constraints has more than one subject with the items that are inserted into the dimension storage media can be partially or completely inserted, but the number of objects is limited. The aim of this study is to determine the results of using the Grey Wolf Optimizer (GWO) algorithm for solving the problem of multiple constraints bounded knapsack and compare the optimal solutions obtained by the simplex method using the Solver Add-In in Microsoft Excel. The data used in this study is primary data. There are two parameters to be tested, namely population parameters and maximum iteration. The test results of the two parameters show that the population parameters and maximum iterations have the same effect, where the greater the value of the population parameters and the maximum iteration, the results obtained are also getting closer to the optimal value. In addition, based on the results of the final experiment it is known that the comparison of the results of the GWO algorithm and the simplex method has a fairly small percentage deviation which indicates that the GWO algorithm produces results that are close to the optimal value.
 Keywords: GWO algorithm, Knapsack, Multiple Constraints Bounded Knapsack.

A hybrid optimization approach for parameter estimation of confined and leaky confined aquifers

Abstract The main objective of this study is to propose a linked simulation–optimization approach for solving aquifer parameter estimation problems from pumping test results. In the simulation part of the proposed approach, the drawdowns at given monitoring points and times are calculated by considering the Theis and Hantush approaches for confined and leaky confined aquifers, respectively. This simulation part is then integrated to a newly proposed hybrid optimization approach, namely DE–Solver, which integrates the differential evolution (DE) algorithm and generalized reduced gradient (GRG) method of the spreadsheet Solver add-in. The performance of the proposed approach is evaluated by considering two pumping test data for confined and leaky confined aquifers. Identified results indicate that the proposed approach provides better results than those obtained by using different approaches in the literature.

Chip seal design framework with life-cycle cost analysis for local agencies

The paper introduces a systematic framework for local agencies to determine their project’s design based upon McLeod rational methodology in an easy and simple approach. The framework provides an optimum design of material types (aggregate and binder), their required application rates, and estimated performance guaranteeing the lowest life-cycle cost. A prototype is developed using MS Excel with solver Add-In package to illustrate the approach. The prototype offers a simple user-friendly platform to promote the use of “scientific and engineering” concepts to reach an optimum design. The approach could serve as a planning tool since it estimates chip seal projects’ LCC, design lifetime, probability of survival, and estimated performance at the end of the selected analysis period. The prototype consists of four modules that summarise the whole process, which are (1) temporary database, (2) permanent database, (3) systems builder/assessment, and (4) optimisation engine. The first module stores project’s related information and agency preferences. The second module stores construction materials’ alternatives, technical properties, traffic considerations, road factors, cost data, economic data, and performance data. The third module builds different design alternatives and calculates their application rates based upon McLeod performance measures. The fourth module suggests an optimum design identified as the best value option with the lowest long-term cost that satisfies the performance objective. A case study of a chip seal project is presented to show the effectiveness of using such prototype by comparing between the prototype optimum design and the agency design. Findings reveal that the prototype had suggested an alternative design that offered materials cost savings reaching up to 46%. Eight case studies of chip seal projects’ application rates were back-calculated using the tool and compared to the agency empirical rates. Findings showed that there are material variations reaching up to 38%. The use of unnecessary materials does not only affect costs but also affects performance. The prototype can be found very useful to local agencies as it is an easy to develop tool that does not only ensure the project’s design economic value but also assures performance quality.

Seleção de projetos de investimentos correntes em uma ferrovia através de programação por metas

Selecionar alternativas de investimentos correntes de acordo com recursos financeiros disponíveis configura-se como um problema clássico de otimização e dentre as diversas técnicas existentes destaca-se a Programação por Metas, uma técnica de programação por objetivos múltiplos. Esse artigo irá apresentar um modelo de programação por me-tas ponderada, aplicado na seleção de uma carteira de projetos de investimentos ferro-viários, utilizando o suplemento Solver do Microsoft Excel. Os resultados obtidos mos-traram que o modelo é uma importante ferramenta de apoio a tomada de decisão e que, com os ajustes necessários, pode ser utilizado para selecionar projetos de outras naturezas.

Development composite polymer matrix tablets with a preset time of isosorbide dinitrate release by the method of mathematical modeling

The kinetics of release of isosorbide dinitrate from coated and uncoated granules with a diameter of 0.5–0.8 mm and 0.8–1.25 mm used for the preparation of matrix tablets was studied. A statistical analysis of the results is made. For the same time value and different pH values, the mean values of release and empirical values of the Shapiro – Wilk criterion were calculated. For a series of series at the same time and different pH, the empirical values of the Cochren test and the non-parametric Cruskell – Wallis test are calculated considering the repeating groups and the Iman – Davenport correction for the number of elements in the sample. A low pH effect on release kinetics has been established. When modeling the release of pellet-based tablets, the characteristics of the normal distribution for each of the granule types and each time value are used. The amount of each type of granules that make up the tablet core is calculated to achieve the desired release profile in Excel using the Search Solver add-in. For the mean values of release for the granules of three types by the least square’s method, we obtained the mass values of the total content of isosorbide dinitrate. For experimental verification of the model, a batch of matrix tablets was prepared using coated and uncoated granules with a diameter of 0.5–0.8 mm. A study of the kinetics of release of isosorbide dinitrate from tablets at pH 1.2; 4.5; 6.8. A statistical analysis of the obtained data and a comparison of the results with calculations by the model were carried out. It has been established that the approach used makes it possible to describe the release of isosorbide dinitrate from tablets with an acceptable accuracy. The accuracy criterion is established - the experimental profile of release of the developed composition falls within the boundaries between the maximum and minimum desired profile. As a result of the research, a composite drug of prolonged action Dikor Long based on isosorbide dinitrate has been developed. The drug has passed state tests, its industrial production has been adjusted in Research and Production Company "Microkhim".

Teaching decomposition forecasting models using an Excel-based spreadsheet application with Solver and randomisation

This article describes an Excel-based application to help teach forecasting with additive and multiplicative decomposition models using the standard Solver add-in to identify intercept, linear trend, and seasonality parameters. By using the application, students can see how easy it is to apply Solver's GRG nonlinear solving method in a relatively familiar forecasting context. To give additional opportunities to practice and encourage academic integrity, the software randomly generates data and layouts and disables copying and pasting between windows. Homework results suggest it provides a challenge despite offering extensive feedback and help. Students reported that it helped them learn forecasting and Excel. Those who correctly completed the assignment have had mixed results on a variety of forecasting exam questions. Even when using innovative technologies such as this application, additional resources, motivation, and opportunities may be necessary to facilitate breadth and depth of learning beyond the essentials required to complete assignments.