ArticlePDF Available

Abstract and Figures

We illustrate the use of spreadsheet modeling and Excel Solver in solving linear and nonlinear programming problems in an introductory Operations Research course. This is especially useful for interdisciplinary courses involving optimization problems. We work through examples from different areas such as manufacturing, transportation, financial planning, and scheduling to demonstrate the use of Solver. Introduction Optimization problems are real world problems we encounter in many areas such as mathematics, engineering, science, business and economics. In these problems, we find the optimal, or most efficient, way of using limited resources to achieve the objective of the situation. This may be maximizing the profit, minimizing the cost, minimizing the total distance travelled or minimizing the total time to complete a project. For the given problem, we formulate a mathematical description called a mathematical model to represent the situation. The model consists of following components: • Decision variables: The decisions of the problem are represented using symbols such as X 1 , X 2 , X 3 ,…..X n . These variables represent unknown quantities (number of items to produce, amounts of money to invest in and so on). • Objective function: The objective of the problem is expressed as a mathematical expression in decision variables. The objective may be maximizing the profit, minimizing the cost, distance, time, etc., • Constraints: The limitations or requirements of the problem are expressed as inequalities or equations in decision variables.
Content may be subject to copyright.
USING EXCEL SOLVER IN OPTIMIZATION PROBLEMS
Leslie Chandrakantha
John Jay College of Criminal Justice of CUNY
Mathematics and Computer Science Department
445 West 59
th
Street, New York, NY 10019
lchandra@jjay.cuny.edu
Abstract
We illustrate the use of spreadsheet modeling and Excel Solver in solving linear and
nonlinear programming problems in an introductory Operations Research course. This is
especially useful for interdisciplinary courses involving optimization problems. We work
through examples from different areas such as manufacturing, transportation, financial
planning, and scheduling to demonstrate the use of Solver.
Introduction
Optimization problems are real world problems we encounter in many areas such as
mathematics, engineering, science, business and economics. In these problems, we find
the optimal, or most efficient, way of using limited resources to achieve the objective of
the situation. This may be maximizing the profit, minimizing the cost, minimizing the
total distance travelled or minimizing the total time to complete a project. For the given
problem, we formulate a mathematical description called a mathematical model to
represent the situation. The model consists of following components:
Decision variables: The decisions of the problem are represented using symbols
such as X
1
, X
2
, X
3
,…..X
n
. These variables represent unknown quantities (number
of items to produce, amounts of money to invest in and so on).
Objective function: The objective of the problem is expressed as a mathematical
expression in decision variables. The objective may be maximizing the profit,
minimizing the cost, distance, time, etc.,
Constraints: The limitations or requirements of the problem are expressed as
inequalities or equations in decision variables.
If the model consists of a linear objective function and linear constraints in decision
variables, it is called a linear programming model. A nonlinear programming model
consists of a nonlinear objective function and nonlinear constraints. Linear programming
is a technique used to solve models with linear objective function and linear constraints.
The Simplex Algorithm developed by Dantzig (1963) is used to solve linear programming
problems. This technique can be used to solve problems in two or higher dimensions.
42
In this paper we show how to use spreadsheet modeling and Excel Solver for solving
linear and nonlinear programming problems.
Spreadsheet Modeling and Excel Solver
A mathematical model implemented in a spreadsheet is called a spreadsheet model.
Major spreadsheet packages come with a built-in optimization tool called Solver. Now
we demonstrate how to use Excel spreadsheet modeling and Solver to find the optimal
solution of optimization problems.
If the model has two variables, the graphical method can be used to solve the model.
Very few real world problems involve only two variables. For problems with more than
two variables, we need to use complex techniques and tedious calculations to find the
optimal solution. The spreadsheet and solver approach makes solving optimization
problems a fairly simple task and it is more useful for students who do not have strong
mathematics background.
The first step is to organize the spreadsheet to represent the model. We use separate
cells to represent decision variables, create a formula in a cell to represent the objective
function and create a formula in a cell for each constraint left hand side. Once the
model is implemented in a spreadsheet, next step is to use the Solver to find the
solution. In the Solver, we need to identify the locations (cells) of objective function,
decision variables, nature of the objective function (maximize/minimize) and
constraints.
Example One (Linear model): Investment Problem
Our first example illustrates how to allocate money to different bonds to maximize the
total return (Ragsdale 2011, p. 121).
A trust office at the Blacksburg National Bank needs to determine how to invest
$100,000 in following collection of bonds to maximize the annual return.
Bond Annual
Return
Maturity Risk Tax-Free
A 9.5% Long High Yes
B 8.0% Short Low Yes
C 9.0% Long Low No
D 9.0% Long High Yes
E 9.0% Short High No
The officer wants to invest at least 50% of the money in short term issues and no more
than 50% in high-risk issues. At least 30% of the funds should go in tax-free investments,
and at least 40% of the total return should be tax free.
Creating the Linear Programming model to represent the problem:
Decision variables are the amounts of money should be invested in each bond.
X
1
= Amount of money to invest in Bond A
43
X
2
= Amount of money to invest in Bond B
X
3
= Amount of money to invest in Bond C
X
4
= Amount of money to invest in Bond D
X
5
= Amount of money to invest in Bond E
Objective Function:
Objective is to maximize the total annual return.
Maximize f(X
1
, X
2
, X
3
, X
4
, X
5
) = 9.5%X
1
+ 8%X
2
+ 9%X
3
+ 9%X
4
+ 9%X
5
Constraints:
Total investment:
X
1
+ X
2
+ X
3
+ X
4
+ X
5
= 100,000.
At least 50% of the money goes to short term issues:
X
2
+ X
5
>= 50,000.
No more than 50% of the money should go to high risk issues:
X
1
+ X
4
+ X
5
<= 50,000.
At least 30% of the money should go to tax free investments:
X
1
+ X
2
+ X
4
>= 30,000.
At least 40% of the total annual return should be tax free:
9.5%X
1
+ 8%X
2
+ 9%X
4
>= 40%(9.5%X
1
+ 8%X
2
+ 9%X
3
+ 9%X
4
+ 9%X
5
)
Nonnegativity constraints (all the variables should be nonnegative):
X
1
, X
2
, X
3
, X
4
, X
5
>= 0.
Complete linear programming model:
Max: .095X
1
+ .08X
2
+ .09X
3
+.09X
4
+ .09X
5
Subject to:
X
1
+ X
2
+ X
3
+ X
4
+ X
5
= 100,000.
X
2
+ X
5
>= 50,000.
X
1
+ X
4
+ X
5
<= 50,000.
X
1
+ X
2
+ X
4
>= 30,000.
9.5%X
1
+ 8%X
2
+ 9%X
4
>= 40%(9.5%X
1
+ 8%X
2
+ 9%X
3
+ 9%X
4
+ 9%X
5
)
X
1
, X
2
, X
3
, X
4
, X
5
>= 0.
Spreadsheet model and Solver implementation:
Implementing the problem in an Excel spreadsheet and Solver formulation produces the
following spreadsheet and Solver parameters. The cells B6 through B10 represent the
five decision variables. The cell C13 represents the objective function. The cells B11,
E11, G11, I11, B14, and B15 represent the constraint left hand sides. The nonnegativity
constraint is not implemented in the spreadsheet and it can be implemented in the
Solver. The complete set of constraints, target cell (objective function cell), variable cells
(changing cells) and whether to maximize or minimize the objective function are
identified in the Solver parameters box.
44
Figure 1: Spreadsheet implementation of example one
Figure 2: Solver implementation of example one
Figure 3: Spreadsheet with optimal solution of example one
45
Optimal money allocation:
Amount invested in Bond A = X
1
= $20, 339.
Amount invested in Bond B = X
2
= $20, 339.
Amount invested in Bond C = X
3
= $29, 661.
Amount invested in Bond D = X
4
= $0.
Amount invested in Bond E = X
5
= $29, 661.
The Maximum annual return is $8,898.00
Example Two (Nonlinear model): Network Flow Problem
This example illustrates how to find the optimal path to transport hazardous material (
Ragsdale, 2011, p.367)
Safety Trans is a trucking company that specializes transporting extremely valuable and
extremely hazardous materials. Due to the nature of the business, the company places
great importance on maintaining a clean driving safety record. This not only helps keep
their reputation up but also helps keep their insurance premium down. The company is
also conscious of the fact that when carrying hazardous materials, the environmental
consequences of even a minor accident could be disastrous.
Safety Trans likes to ensure that it selects routes that are least likely to result in an
accident. The company is currently trying to identify the safest routes for carrying a load
of hazardous materials from Los Angeles to Amarillo, Texas. The following network
summarizes the routes under consideration. The numbers on each arc represent the
probability of having an accident on each potential leg of the journey.
Figure 4: Network diagram of example two
1
2
3
4
6
5
8
7
10
9
Los Angeles
Amarillo
0
.003
0.002
0.004
0.002
0.010
0
.010
0.006
0.006
0.009
0.002
0.003
0.010
0.001
0.004
0.001
0.005
0.003
0.006
San Diego
Las Vegas
T
ucson
Flagstaff
Phoenix
Las
Cruces
Lubbock
Albuquerque
46
The objective is to find the route that minimizes the probability of having an accident, or
equivalently, the route that maximizes the probability of not having an accident.
Creating the mathematical model to represent the problem:
Each decision variable indicates whether or not a particular route is taken (they are
known as binary variables). We will define these variables in following way:
X
ij
= 1 , if the route from node i to node j is selected, and X
ij
= 0 otherwise.
Let Pij be the probability of having an accident while travelling from node i to node j
(1- Pij is the probability of not having an accident).
Objective function:
Minimize the probability of having an accident or equivalently, maximize the probability
of not having an accident. Note that this objective function is nonlinear.
Maximize f(X
12
, X
13
,….) = (1-P
12
*X
12
) (1-P
13
*X
13
) (1 – P
14
*X
14
) (1 – P
24
*X
24
) ………. (1 -
P
9.10
*X
9,10
)
Constraints:
We use the following strategy to construct constraints: That is, supply one unit at the
starting node and demand one unit at the ending node, and for every other node,
demand or supply is zero. We find the route in which the one unit travels.
Total supply = 1, and total demand = 1, so for each node,
Net flow (Inflow – Outflow) = demand or supply for that node (Balance of flow rule).
Then we have following set of constraints:
Node 1: - X
12
– X
13
– X
14
= -1
Node 2: + X
12
– X
24
– X
26
= 0
Node 3: + X
13
– X
34
– X
35
= 0
Node 4: + X
14
+ X
24
+ X
34
– X
45
– X
46
– X
48
= 0
Node 5: + X
35
+ X
45
– X
57
= 0
Node 6: + X
26
+ X
46
- X
67
– X
68
= 0
Node 7: + X
57
+ X
67
– X
78
– X
7,10
= 0
Node 8: + X
48
+ X
68
+ X
78
– X
8,10
= 0
Node 9: + X
79
– X
9,10
= 0
Node 10: + X
7,10
+ X
8,10
+ X
9,10
= 1
Complete nonlinear Programming model:
Maximize: (1-P
12
*X
12
) (1-P
13
*X
13
) (1 – P
14
*X
14
) (1 – P
24
*X
24
) ………. (1-P
9.10
*X
9,10
)
Subject to:
- X
12
– X
13
– X
14
= -1
+ X
12
– X
24
– X
26
= 0
+ X
13
– X
34
– X
35
= 0
47
+ X
14
+ X
24
+ X
34
– X
45
– X
46
– X
48
= 0
+ X
35
+ X
45
– X
57
= 0
+ X
26
+ X
46
- X
67
– X
68
= 0
+ X
57
+ X
67
– X
78
– X
7,10
= 0
+ X
48
+ X
68
+ X
78
– X
8,10
= 0
+ X
79
– X
9,10
= 0
+ X
7,10
+ X
8,10
+ X
9,10
= 1
All X
ij
are binary.
Spreadsheet model and Solver implementation:
Figure 5: Spreadsheet implementation of example Two
Figure 6: Solver implementation of example two
48
Figure 7: Spreadsheet with optimal solution of example two
The optimal path:
The route that minimizes the probability of having an accident is given below:
Los Angeles to Phoenix
Phoenix to Flagstaff
Flagstaff to Albuquerque
Albuquerque to Amarillo.
Conclusion:
Optimization problems in many fields can be modeled and solved using Excel Solver. It
does not require knowledge of complex mathematical concepts behind the solution
algorithms. This way is particularly helpful for students who are non math majors and
still want to take theses courses.
References:
1) Cliff T. Ragsdale, 2011, Spreadsheet Modeling and Decision Analysis, 6
th
Edition.
SOUTH-WESTERN, Cengage Learning.
2) Dantzig, G. B. 1963, Linear Programming and Extensions, Princeton University
Press, Princeton, NJ.
3) John Walkenbach, 2007, Excel 2007 Formulas, John Wiley and Sons.
49
... The simplest mathematical package is MS Excel, its use for solving problems of this type was proposed in (Borucka, Niewczas, 2019;Drašković, Malyaretz and Dorokhov, 2016). Also, the research of these means is given proper attention in (Chandrakantha, 2008;Ezeokwelume, 2016;Vats, KumarSingh, 2016). However, the small dimension of the problem set in this study is worth mentioning. ...
... For this reason, congruence (10) will be taken into account separately for objective functions of minimization of cost and risk. Such cases for statement (9) are regarded as periphery and integral. Therefore, first integral case completely denies objective function of risk. ...
Article
Full-text available
The object of this study is a multicriteria transport problem, being stated for availability of several means of cargo delivery, meaning a multimodal transport problem. The optimization criteria of the multimodal transport problem described above are two objective functions of minimizing total transportation costs and level of transport risks. Three types of transport were selected for research: automobile, rail and river (inland waterway). The results of the study lay the foundation for development of a new valid algorithm for solving multimodal transport problems like multi-criteria optimization ones. The main advantage of such an algorithm lies in its higher potential convergence rate compared to classical numerical optimization methods, which now are predominantly used to solve the problems of this type. This advantage may not be decisive, but it appears to be at least quite an important argument when choosing the method of realization for two-criteria multimodal transport problems earlier considered, especially, in case of a large dimension. Moreover, the algorithm described in the work can be applied to similar problems with any number of types of transport and optimization criteria.
... To describe the average reaction behavior observed, minimization was performed using Microsoft Excel's Solver. [72] Firstly, a stoichiometric mass balance was set up for each of the ores using the average SEM-EDS compositions according to the general reaction: ...
Article
Fifteen commercial concentrates that are consumed in ferrochrome pre-oxidative sintering pelletizer operations were characterized, and exposed to oxidative roasting environments. Significant variance was found in the thermogravimetric behavior of the ores, which was observed to be strongly correlated with the starting Cr:Al ratio of the spinel (r = 0.84, P < 0.001). The formation of the sesquioxide phase during roasting was studied through comprehensive XRD and SEM–EDS analysis. Counter-diffusion of Mg2+ and Fe2+ cations with only limited diffusion of Cr3+ and Al3+ was observed. The final mass fraction of sesquioxide present after conversion was determined by calculations using SEM–EDS analyses and was found to be between 35 and 54 pct after oxidation at 1200 °C for 5 minutes. The mass fraction of sesquioxide was found to be most strongly correlated with the total Fe content of the starting chromite spinel (r = 0.93, P < 0.001). The sesquioxide phase that forms was confirmed to be a solid solution between Al2O3–Cr2O3–Fe2O3 with no evidence of pure hematite (Fe2O3) or eskolaite (Cr2O3) precipitate found.
... The solution of the given problem could be done by applying model data which would be a preparatory stage for ultimate risk transportation function (2) or (6) that could be solved through program aids [4,6,14,15,17]. ...
Article
Full-text available
The paper regards a specific class of optimization criteria that possess features of probability. Therefore, constructing objective function of optimization problem, the importance is attached to probability indices that show the probability of some criterial event or events to occur. Factor analysis has been taken for the main method of constructing objective function. Algorithm for constructing objective function of optimization is done for criterion of minimization risk level in multimodal transportations that demanded demonstration data. The application of factor analysis in classical problem solution was shown to give the problem a more distinct analytical interpretation in solving it.
... It is an example of a spreadsheet model developed in Microsoft Excel. Such models can be solved through mathematical programming using Excel Solver to solve linear and non-linear models [3]. The farm model is based on mathematical programming and enables optimisation of production plan. ...
Conference Paper
Full-text available
This paper addresses the problem of farm planning considering production and price risk. The extent of risk, possibilities of risk reduction and also its efficiency were studied on a hypothetical semi-size farm, with a typical mixed production plan including different livestock activities, fodder production as well as cash crops. For this purpose a farm model based on LP (linear programming) and QRP (quadratic risk programming) has been developed. The risk performance of farm production was analysed in the expected value and variance E,V efficient set. Results show that there are different strategies how risk on such a farm could be mitigated. With a slightly changed production plan, it could increase the EGM (expected gross margin) by 10% at the same level of risk.
... Authors of [4] have discussed various methods and aspects of linear programming and its applications. In [5], Chandrakantha has provided a solution for optimization problems using the EXCEL solver option. Jonsson [1] have discussed logistics and supply chain management and the part which deals with direct costing in logistics was really helpful in optimizing the transportation system and reducing its cost. ...
Chapter
Full-text available
Transportation plays a vital role in every manufacturing industry as it is one of the major activities that binds the whole supply chain and accounts for customer satisfaction with the right delivery time. Hence, bringing in an optimized transport routing on the grounds of time taken and cost of transportation is very important. In this paper, a cost optimization model for transportation of goods of a flavors and fragrance company is presented. The problem was a linear programming problem and was solved using an EXCEL solver. A savings of Rs. 765,000 per annum was estimated comparing the cost of transportation in the new model to that of the previous model.
... Epidemiological data from field studies were entered into the Tsetse Plan software which automatically generates post-analysis results. The estimation of parameters was achieved using the least squares method in Excel solver [34], with a view to minimising summation of squared errors given by ∑(Y(t, p) − X real ) 2 subject to the AAT model (0.1)-(0.16) where X real is the field reported data, and Y(t, p) represents the solution of the model corresponding to the number of active cases divided by time t with the set of estimated parameters denoted by p. ...
Article
Full-text available
African animal trypanosomosis (AAT) is transmitted cyclically by tsetse flies and mechanically by biting flies (tabanids and stomoxyines) in West Africa. AAT caused by Trypanosoma congolense, T. vivax and T. brucei brucei is a major threat to the cattle industry. A mathematical model involving three vertebrate hosts (cattle, small ruminants and wildlife) and three vector flies (Tsetse flies, tabanids and stomoxyines) was described to identify elimination strategies. The basic reproduction number (R 0) was obtained with respect to the growth rate of infected wildlife (reservoir hosts) present around the susceptible population using a next generation matrix technique. With the aid of suitable Lyapunov functions, stability analyses of disease-free and endemic equilibria were established. Simulation of the predictive model was presented by solving the system of ordinary differential equations to explore the behaviour of the model. An operational area in southwest Nigeria was simulated using generated pertinent data. The R 0 < 1 in the formulated model indicates the elimination of AAT. The comprehensive use of insecticide treated targets and insecticide treated cattle (ITT/ ITC) affected the feeding tsetse and other biting flies resulting in R 0 < 1. The insecticide type, application timing and method, expertise and environmental conditions could affect the model stability. In areas with abundant biting flies and no tsetse flies, T. vivax showed R 0 > 1 when infected wildlife hosts were present. High tsetse populations revealed R 0 <1 for T. vivax when ITT and ITC were administered, either individually or together. Elimination of the transmitting vectors of AAT could cost a total of US$ 1,056,990 in southwest Nigeria. Hence, AAT in West Africa can only be controlled by strategically applying insecticides PLOS ONE
... 2), and 5th percentile value (5% CD,MLE ) was determined by Eq. 3. In Eqs. 2b and 2c, the parameters were determined using Excel solver [20][21][22]. Order statistics determined the 5th percentile value (5% CD,Order ) by choosing the 5th lower strength. The 5% CD,MLE and 5% CD,Order intended to simulate the case that grip failure specimens (unintended failure) were regarded as normal test results (tension failure), and these might be lower than 5% ideal, MLE or 5% ideal, Order . ...
Article
Full-text available
In structural timber tests, unintended failure mechanisms occur frequently in specimens and their results are called censored data. There are two censored data analysis: censored maximum likelihood estimation (CMLE) and Kaplan–Meier (KM) method. In this study, the precision of the censored data analysis was investigated to determine the characteristic value, 5th percentile value, of the structural timber. The results show that (1) the 5th percentile value was underestimated by ordinary data analysis methods; maximum likelihood estimation (MLE) and Order statistics. (2) CMLE with 30% lower tail censored data and KM method provided much more precise 5th percentile value. (3) The amount of under-measurement (5 MPa, 10 MPa, and 15 MPa in this simulation study) did not show significant effect on the 5th percentile determination in CMLE and KM method, but the proportion of censored data (percentage of unintended failure specimen; 10%, 20%, 30%, and 40%) affected the determination of 5th percentile value. (4) CMLE with 30% lower tail censored data and KM method showed good agreement in case that the data included unintended failure data up to 20%.
Article
Full-text available
This paper aims to improve the understanding of environmental and socioeconomic drivers on land use change (LUC) through public participation (PP), and provide recommendations for long-term policy making to support sustainable land use management. Public participation (PP) was necessary to help understand and address the problem and concerns of stakeholders within the study area. Through two collaboration workshops seven individual future land use scenarios were created. Using the FLUS (Future land use simulation) model, land use was projected up till 2060, after which logistic regression analysis took place to find the most significant driver. Results found that LUC within the baseline scenario and the ones chosen by stakeholders were very different, however concluded that Paddy field extent would decrease in the future to be replaced by more drought resilient agriculture; Perennials & Orchards and Field Crops. Outcomes from future scenarios propose that future LUC was driven by environment spatial factors such as elevation and climate, not soil suitability. With, first hand interviews suggesting it is indirect external factors such as, crop price that drive LUC. Overall the study provides steps towards dynamic LUC modelling where future scenarios have been tailored to details specified by the public through their participation.
Article
This research on Modeling and Application of Mono-Commodity Multi-Location Linear Programming Techniques For Determining Optimum Transportation Network was carried out at a Manufacturing Industry in Lagos, which comprises of two plants, three depots and twenty retailers axis. The model was analyzed using Micro Soft Excel Software. The analysis to determine the optimal transportation network was carried out in two phases by considering numbers of truckload transported and each commodity from plants to depots and depots to retailers and their optimals. It was discovered that the existing practices transportation cost for truckloads moving from plant to retailers is N3,544,000,000,000 and when optimized, cost is N1,932,650,000,000 while considering each product the optimized transportation cost is N1,871,065,369,000. This implies that the transportation network generated considering each product will yield 47.2% gain in profit than existing network. Hence, it is recommended that mono-commodity multi-location transportation network be used. Keywords: [EXCEL Software, Mono-Commodity Multi-Location Model, Transportation Cost, Transportation Model, Transportation Network.].
Article
Full-text available
PL: Celem tego artykułu jest pokazanie możliwości wykorzystania dodatku Solver do optymalizacji wielu procesów logistycznych. Przedstawiono trzy różne przypadki, które dotyczyły: ustalenia optymalnej trasy, alokacji zaplecza logistycznego oraz minimalizacji kosztów transportu. Każda z analizowanych sytuacji została wzbogacona o teoretyczny opis problemu, aby zminimalizować ryzyko zrozumienia tej publikacji jako zbioru niepowiązanych ze sobą zagadnień. Całość została napisana w oparciu o doświadczenie autora, literaturę polską i angielską. EN: The purpose of this article is to show the possibility of using the Solver add-in to optimize many logistics processes. Three different cases were presented, which concerned: determining the optimal route, allocation of a logistics facility and minimization of transport costs. Each of the analyzed situations has been enriched with a theoretical description of the problem in order to minimize the risk of understanding this publication as a set of disconnected issues. The whole was written based on the author's experience, Polish and English language literature.
ResearchGate has not been able to resolve any references for this publication.