Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

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 book 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 ...