Microsoft Office Tutorials and References

In Depth Information

**Chapter 35: The Solver Add-In—Optimizer**

CHAPTER
35

The Solver Add-In—Optimizer

The Solver is an Excel Add-In used for optimizations. For one, it is an optimization

tool that helps decision makers to maximize profit given limited resources. These

resources can be raw materials, time, people, money, or anything else that is known

to be limited (scarce resources). The optimal solution can maximize profit, minimize

cost, or achieve similar goals.

It could also be an optimization tool that helps the decision maker to minimize

cost subject to certain minimum requirements.

There are many books on management science, operations management, and

operations research that explain the different uses for the tool. I could list hundreds

of topics. In finance: working capital management, capital budgeting, and portfolio

optimization. In manufacturing: job shop scheduling and blending. Distribution and

networks routing will look for solutions to loading and scheduling constraints,

which can also be optimized with the Solver.

This topic is about mastering the Excel software; therefore, I am going to explain

how to manipulate the Solver in Excel, so that when you want to explore the other

techniques, the Excel part will be familiar.

SOLVER EXAMPLE

Consider the capital budgeting example in the Chapter 35 file under the sheet named

Solver. In this example, you have 10 different projects to evaluate as possible

investments. Each project has an investment associated with it (the cost). Also, each

investment has a different yield, defined as the Net Present Value (NPV, the expected

profit.) As in a real-life situation, there are only finite resources, limiting the number

of projects you can invest in, the available budget being an unavoidable constraint.

Your task is to decide which projects to invest in.

This is the information you have about the capital budget model:

B

¼

the capital available for investment.

C
j
¼

the capital needed for each project.

X
j
¼

the decision variables. They can take either the value of 1 (invest)

or 0

(don

’

t invest).

P
j
¼

the NPV or profit associated with project j.

¼

n

the number of projects.