Solver in Excel

Optimize Financial Decisions using Analytics

Created by: Calliope Cortright ’23

SOLVER IN EXCEL

Intro to Solver

Solver is an add-in for Microsoft Excel that allows the user to optimize the value in a

particular cell by allowing the program to change values in other cells. This is useful to

make decisions in an environment where the decision maker wants to maximize profit or

minimize costs, for example. The program is able to adapt to a variety of situations, and

it is useful in many financial situations.

Solver is a tool for data analysis. It gives the user peace of mind with the assertion that

a particular decision is the optimal one with respect to a defined output. This tutorial will

first give a broad overview of Solver and then explain some use cases within the realm

of financial decision-making.

1. Accessing Solver……………………….…………………………………...……...pg. 2

2. Solver Functionality……………………………………………………….……..... pg. 3

3. Solver Outputs……………………………………………………………..………..pg. 5

4. Use Case: Solver in Capital Budgeting…………………………………………..pg. 6

5. Use Case: Solver in Pricing Decisions…………………………………………...pg. 8

Much of the information in this tutorial and the examples shown are from Professor

Terry Reilly, an Associate Professor in the Mathematics, Analytics, Science, and

Technology Division. The material in this tutorial is covered in great detail in Profesor

Reilly’s Financial Simulations class. However, Solver is introduced in other classes at

Babson, including AQM and Optimization Methods.

SOLVER IN EXCEL

Accessing Solver

Solver is an Excel add-in, so the first step in using Solver is to ensure that it is

loaded on your computer. The Solver option is shown in the Data Tab. Click on the data

tab, and look in the area at the far right of the ribbon (shown here in the yellow circle). If

Solver is listed here as an option, skip to the next section. If Solver is unavailable, follow

the following steps to install the add-in.

Click on “File” and then on “Options.” This

will bring up the menu shown to the right. Along

the left of the pop-up window is a menu

containing options. Select the “Add-ins” option

(highlighted in yellow). From here, Select the

“Go…” button (highlighted in orange).

Once you have selected this, a new

window will appear

with the add-in

options (shown to the

left). Check the box

next to “Solver Add-in” (highlighted in yellow). Once this box

is checked, select “OK” (highlighted in orange).

Once you have completed these steps, Solver

will show up as an option on your ribbon. Navigate to the

“Data” tab and look in the area to the far right of the ribbon.

You will find “Solver” (see below).

SOLVER IN EXCEL

Solver Functionality

To get acquainted with Solver,

click on this “Solver” option in the

“Data” tab. The pop-up menu in the

image to the right will appear. Before

diving into use cases, we must

become familiar with each of these

inputs. This section will go through

each of the inputs highlighted on the

image to the right.

Objective Cell: Highlighted in orange is

the objective cell. In this box, you will

link the cell that you want to optimize.

For example, if we want to maximize

total profit, we will put the cell for total

profit in this box. If we want to

minimize risk, we will add the cell that

quantifies risk in this cell. Keep in mind

you can only optimize one cell. For

example, you can not maximize profit while also minimizing risk.

To: Highlighted in green is the area where you must designate whether you want the

cell noted above to be maximized or minimized. You also have the option to set that cell

to a specific value that you will type into the box to the far left. Keep in mind that you

can only select one option here.

Variable Cells: The objective cell cannot change without some of its inputs changing.

Variable cells are inputs that you will let Solver change to optimize the objective cell. For

example, to maximize profit, you may let Solver change the number of units we

produce, so the number of units produced would be a variable cell. In this box, cells

must be linked that do not contain a formula (they

must be constants) so that Solver has full range to

change these cells.

Constraints: Often, in optimization scenarios, the

situation is bounded by constraints. For example, in

the units produced example, we may only be able to

produce a specific number of units. Therefore, we

must add these constraints by clicking on the “Add” button. The pop-up to the right will

appear. On each side of this inequality, cells must be linked. You can also change the

SOLVER IN EXCEL

inequality in the middle to be less than or equal to, greater than or equal to, equal to, or

“int” (meaning the cell to the left must be an integer), “bin” (meaning the cell to the left

must be binary - 0 or 1), or “dif” (meaning the cells to the left must all be different from

each other). Once you have filled out this constraint pop-up, click OK to close the

window and go back to the Solver window, or click Add to add the constraint and then

immediately add another one.

Non-negative toggle: The toggle highlighted in pink above, when selected, will force all

variable cells to be positive. Think about if this constraint applies to your situation.

Solving Method: You must choose a solving method in the drop-down menu highlighted

in red. “Simplex LP” can only be used when all constraints and the objective are linear

(i.e., no products, division, or excel functions). “GRG Nonlinear” should be the default

when this is not the case.

SOLVER IN EXCEL

Solver Outputs

Once you click “Solve” in the Solver

window, the following pop-up will

come up. Important insights can be

drawn from the Answer report and

Sensitivity report (obtained by

selecting these options in the

“Reports” field before clicking “OK.”)

Answer Report: The answer report

will tell you several things about the

optimal solution, including the

original and final value of all

variable cells and if the constraints

are binding. Binding constraints can

not change without changing the solution. If a constraint is non-binding, the answer

report also tells you the slack that exists on this constraint.

Sensitivity Report: The sensitivity report tells you how much the values of the variable

cells and the constraints would have to change to change the optimal solution. This is

useful in an environment where nothing is certain.

The Answer and Sensitivity Report will be covered in much greater detail in classes that

use Solver.

SOLVER IN EXCEL

Use Case: Solver in Capital Budgeting

Suppose you

are in charge of

making a decision in

which you must

decide which projects

to fund among a

group of nine

projects. You have a

budget for the next

two years, and you

hope to optimize the

NPV of the projects in aggregate. The information for each project and the budget are

included in the excel image above. Each project has a cost in year one and year two

and the NPV that it generates.

Setting up the Excel for Solver: You must first think of how to set up the Excel sheet in a

way that allows for an objective cell and variable cells. In this case, the variable cells

(highlighted in green) are created as binary variables to tell the user if these projects are

funded or not. Then, the total NPV (highlighted in orange) is calculated using a

sumproduct of the binary “funded?” variable and the NPV of the project. It is also

important to lay out the constraints in an organized manner. In this case, the constraints

(highlighted in blue) are that the costs of the projects selected (calculated as a

sumproduct of the year one cost and the binary “funded?” variable) are less than or

equal to the available budget.

Running Solver: Click on “Solver” in the Data tab. First, set the objective cell by linking it

to cell C10 (the Total NPV). Then click the “Max” toggle to ensure that NPV is

maximized. Now, define your variable cells as the green “funded?” cells. For

constraints, click “Add,” then select the used cells on the left side and the available cells

on the right side. Because these are both “<=,” these two constraints can be added

separately or together. Also, the variable cells must be binary, so add this as a

constraint. This is a linear problem, so we chose Simplex LP. Clicking “Solve” will give

SOLVER IN EXCEL

you the following solution. This tells us that we should fund projects 1, 3, 4, 6, and 9 to

optimize NPV.

SOLVER IN EXCEL

Use Case: Solver in Pricing Decisions

Often Solver can be used internally in a firm to decide the optimal price for a

product in terms of profit given a particular elasticity of demand. The excel below shows

this problem.

Setting up Excel:

Suppose that our

company sells suits. It

costs us $225 to make

each suit. This is not a

number we can change,

but we can change the

amount that we sell the

suits for. Therefore, the

price cell is highlighted in

green because it is a

decision variable. The elasticity is also set. The linear demand curve is defined using a

slope and intercept, the former is a function of the current price, current demand, and

elasticity, and the latter is a function of current price and current demand. We are

maximizing the profit obtained by suit sales (highlighted in orange).

Running Solver: Click “Solver” in the “Data” tab. The first step is to set the objective

value to the profit and

indicate that we are

trying to maximize this

number. Next, indicate

that our variable cell is

the Suit Price in E4.

There are no

constraints in this

model, so leave this

blank. This is a

nonlinear problem

because multiplication

and division is used to

influence the suit

profit, so GRG Nonlinear is selected. Click “Solve,” and the optimal suit price comes out

to $317.61.