Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 125 Solver
CHAPTER 14 SOLVER
If you have a result that you are trying to reach, multiple values that
can change, and multiple constraints for each of these values, then
Solver is exactly what you need to solve your problem. Solver
adjusts the values in the cells you specify to produce the result you
want from the formula.
Possible uses for Solver are:
Production – What is the most profitable mix of items
to produce, considering the limitations of inventory and
machines?
Shipping – How can the cost of shipping goods from
different warehouses be minimised while meeting the
demands of clients and not exceeding the capabilities
of warehouses?
Scheduling – What is the minimum number of staff
required to meet service expectations and union
regulations?
Investment – How can the return on capital be
maximised considering risk management guidelines?
In this session you will:
gain an understanding of how Solver can be used to
solve complex problems
learn how to install the Solver add-in
learn how to set the base Solver parameters
learn how to add constraints to Solver
learn how to use Solver to solve a modelling problem
learn how to run and use Solver reports.
INFOCUS
WPL_E844
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 126 Solver
UNDERSTANDING HOW SOLVER WORKS
1
Solver is used to resolve optimisation problems
where at least two alternatives are available, and
where the goal is to either maximise a return or
profit, or minimise expense or effort. There are
generally three components to a problem like this:
the goal; the parameters within which you have to
work and; the variables that you can juggle. The
following explores a simple Solver example.
Solver Components: An Advertising Model
The Problem:
Advertising for a coming event is required. The goal is to calculate the minimum cost of advertising.
The constraints are that the advertising must attract at least 28 million female viewers and 24 million
male viewers. The variables are the numbers of each ad type that you purchase. The model
incorporates the following information. Each news ad is seen by 7 million women and 2 million men.
Each sports ad is seen by 2 million women and 12 million men. Each news ad costs $50,000 and each
sports ad costs $100,000.
2
The Solution:
Microsoft Excel Solver takes the calculations and constraints and calculates an answer. In this case, the
minimum advertising expense for the required impact can be achieved by running 4 news ads and 1
sports ad.
The cells that can change, or
variables, are the quantities
of each ad type. These are
the “by changing cells”.
The constraints affect the
totals in these cells. Females
must be at least 28,000,000
and Males 24,000,000.
The goal is to minimise the
value of the Total cost. This
cell is called the target cell.
The model uses formulas
in these cells to calculate
how many female and
male viewers see each ad
and how much they cost.
The quantities are varied until the
constraints are satisfied and the
minimum possible cost achieved.
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 127 Solver
INSTALLING THE SOLVER ADD-IN
Try This Yourself:
Before starting this exercise
you MUST ensure that
Excel has started...
Click on the File tab of the
Ribbon and select
Options, to display the
Excel Options dialog box
Click on Add-Ins to display
a list of active and inactive
add-ins
Click on Solver Add-In and
click on [Go…] to display
the Add-Ins dialog box
Click on Solver Add-In
until it appears with a tick,
then click on [OK] – Excel
will now ask if you wish to
install it
Click on [Yes]
The installation process will
now commence. It may
take a few minutes to
complete so be patient. The
tool will appear on the Data
tab of the Ribbon once
installed
For Your Reference…
To install the Solver add-in:
1. Click on the File tab, select Options, then
click on Add-Ins
2. Click on Solver Add-In and click on [Go…]
3. Click on Solver Add-In until it appears ticked
and click on [OK]
Handy to Know…
The Add-Ins area of the Excel Options
dialog box shows both active and inactive
add-ins. If you are not sure whether an add-
in has been installed or where it is found on
the Ribbon, open the Excel Options dialog
box and click on the Add-Ins option to
display the list of add-ins.
2
3
Solver is an Add-In – a program that can be
added when you need it, rather than being
included as part of the standard installation of
Microsoft Excel. If Solver has already been
installed it will appear in the Analysis group on the
Data tab of the Ribbon. If it doesn’t appear in the
Ribbon then it will need to be installed using the
Excel Options dialog box.
5
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 128 Solver
SETTING SOLVER PARAMETERS
Try This Yourself:
Open
File
Before starting this exercise
you MUST open the file E844
Solver_1.xlsx...
Spend a few moments
studying the worksheet and its
formulas
Click on F9 – this is the target
cell in which we want to have a
result
Click on the Data tab of the
Ribbon and click on Solver
, in the Analysis group, to
display the Solver
Parameters dialog box
The selected cell appears in
Set Objective…
Click on Min in To
Click on the range selection
tool for By Changing
Variable Cells
Click on C6 and drag down to
C7 to select the two cells
Click on the range accept tool
to return to the Solver
Parameters dialog box
Keep the Solver Parameters
dialog box open for the next
exercise
For Your Reference…
To set the base Solver parameters:
1. Click on Solver in the Analysis grouping
on the Data tab
2. Specify the target cell, what it should be
equal to, and the changing cells
Handy to Know…
You can also type in the range and cell
location, in the Solver Parameters dialog
box, rather than using the range selection
and range accept tools.
3
6
Once you have located a problem to solve it is a
matter of starting Solver and entering the base
information. This information includes nominating
the target cell, the cells that need to be changed
to arrive at a workable solution, and a scope for
the target cell – in other words what the target cell
should equal at the end of the calculation.
7
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 129 Solver
ADDING SOLVER CONSTRAINTS
Try This Yourself:
Same
File
Ensure that the Solver
Parameters dialog box appears
from the previous exercise...
Ensure that the settings in the
Solver Parameters dialog box
are as shown
Click on [Add] to display the
Add Constraint dialog box
Type D9 in Cell Reference,
then click on the drop arrow
and click on >=
Type 28000000 in Constraint
This rule constrains Solver by
telling it that cell D9 must be
greater than or equal to
28,000,000 at the end of the
Solver operation…
Click on [Add] to save this
constraint and add it to the
Solver Parameters dialog box
Repeat the above steps to
create a second constraint as
shown
Click on [OK] to return to the
Solver Parameters dialog box
where the constraints will be
listed
Keep the Solver Parameters
dialog box open for the next
exercise
For Your Reference…
To add constraints to Solver:
1. From the Solver Parameters dialog box,
click on [Add]
2. Type the constraint details and click on
[Add]
Handy to Know…
You can use the [Change] and [Delete]
buttons in the Solver Parameters dialog box
to make changes to constraints and delete
them after they have been created.
1
4
Solver constraints are like rules that constrain
what can be changed in the model. Constraints
are created from the Solver Parameters dialog
box and are listed in the dialog box for reference.
While you can have as many constraints as you
like, the more constraints you impose, the harder it
will be for Solver to find a solution.
6
7
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 130 Solver
PERFORMING THE SOLVER OPERATION
Try This Yourself:
Same
File
Ensure that the Solver
Parameters dialog box
appears from the
previous exercise...
Ensure that the settings
in the Solver
Parameters dialog box
are as shown
Click on [Solve] to
perform the Solve
operation
If the Solver operation
can be performed the
results will appear in the
worksheet and the Solver
Results dialog box will
appear…
Click on [OK]
Cool! To reach a target of
28 million female viewers
and 24 million male
viewers, you’ll need to
spend 180,000 for 4 one
minute ads in the news,
and 140,000 for a 1
minute sports time ad
For Your Reference…
To perform the solve operation:
1. Ensure that all of the parameters have been
set
2. From the Solver Parameters dialog box
click on [Solve]
Handy to Know…
The Solver Results dialog box allows you to
keep ([OK]) or discard ([Cancel]) the results
of the solve operation. You can also keep the
original figures and the new, solved figures
by saving the results as a scenario using
[Save Scenario].
1
2
Once all of the parameters have been set you are
ready to use Solver to try and resolve the
mathematical problem. Solver actually reiterates
through the formulas and constraints in the model
until a satisfactory solution is found. The results are
then displayed in the model and a dialog box
appears with options allowing you to keep or
discard the results.
3
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 131 Solver
RUNNING SOLVER REPORTS
Try This Yourself:
Same
File
Continue using the
previous file with this
exercise, or open the file
E844 Solver_2.xlsx...
On the Data tab on the
Ribbon, click on Solver
, in the Analysis
group, to see the Solver
Parameters dialog box
Click on [Solve] to
perform the Solve
operation and see the
Solver Results dialog
box
Click on Answer in
Reports, then click on
Sensitivity and Limits
to select all three
Click on [OK] to create
the reports which will
appear as worksheet
tabs at the bottom of the
window
Click on the Answer
Report 1 worksheet tab
to see the answer and
setting details
Click on the other tabs
and examine the reports
For Your Reference…
To create reports using Solver:
1. Run Solver until the Solver Results dialog
box appears
2. Select Answer, Sensitivity, and Limits in
the Solver Results dialog box and click on
[OK]
Handy to Know…
The case study example we use is relatively
simple with only one possible solution. As a
result the Sensitivity and Limits reports do
not provide much in the way of additional
information. These reports however come
into their own with more complex Solver
models.
3
5
To record Solver’s results and settings you can
create reports. There are three types of reports:
Answer, Sensitivity and Limits. Answer reports
the original and final values for the target, as well
as the settings. Sensitivity refers to how sensitive
the solution is to small changes in the formula.
Limits lists the possible upper and lower values
between which a solution can be reached.
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 132 Solver
NOTES:
1
4
