Microsoft Office Tutorials and References
In Depth Information
Chapter 35: The Solver Add-In—Optimizer
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.
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:
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)
P j ¼
the NPV or profit associated with project j.
the number of projects.