Introduction to optimization with the Excel Solver tool

Try Office 2010 In Excel 2010, many improvements have been made to the Solver add-in.
Read a blog post or try Office 2010!

Applies to
Microsoft Office Excel 2003
Microsoft Excel 2000 and 2002

Book cover This article was adapted from Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston.

This classroom-style book was developed from a series of presentations by Wayne Winston, a well known statistician and business professor who specializes in creative, practical applications of Excel. So be prepared—you may to put your thinking cap on.

Visit Microsoft Learning to buy this book.

In this article

What is optimization?

  • How can a large drug company determine the monthly product mix at their Indianapolis plant that maximizes corporate profitability?
  • If Microsoft produces Xbox consoles at three locations, how can they minimize the cost of meeting demand for Xbox consoles?
  • What price for Xbox consoles and games will maximize profit from Xbox sales?
  • Microsoft would like to undertake 20 strategic initiatives that will tie up money and skilled programmers for the next five years. They do not have enough resources to undertake all 20 projects. Which projects should they undertake?
  • How do bookmakers find the best set of "ratings" for NFL teams to set accurate point spreads?
  • How should I allocate my retirement portfolio among high-tech stocks, value stocks, bonds, cash, and gold?

In all these situations, we want to find the best way to do something. More formally, we want to find the values of certain cells in a spreadsheet that optimize (maximize or minimize) a certain objective. The Excel Solver tool helps you answer optimization problems.

Defining an optimization model

An optimization model has three parts: the target cell, the changing cells, and the constraints.

Target cell

The target cell represents the objective or goal. We want to either minimize or maximize the target cell. In the example of a drug company's product mix, the plant manager would presumably want to maximize the profitability of the plant during each month. The cell that measures profitability would be the target cell. The target cells for each situation described at the beginning of the article are listed in the following table.

Model Maximize or minimize Target cell
Drug company product mix Maximize Monthly profit
Xbox shipping Minimize Distribution costs
Xbox pricing Maximize Profit from Xbox consoles and games
Microsoft project initiatives Maximize Net present value (NPV) contributed by selected projects
NFL ratings Minimize Difference between scores predicted by ratings and actual game scores
Retirement portfolio Minimize Riskiness of portfolio

Keep in mind that in some situations, you might have multiple target cells. For example, Microsoft might have a secondary goal to maximize Xbox market share.

Changing cells

Changing cells are the spreadsheet cells that we can change or adjust to optimize the target cell. In the drug company example, the plant manager can adjust the amount produced for each product during a month. The cells in which these amounts are recorded are the changing cells in this model. The following table lists the appropriate changing cell definitions for the models described at the beginning of the article.

Model Changing cells
Drug company product mix Amount of each product produced during the month
Xbox shipping Amount produced at each plant each month that is shipped to each customer
Xbox pricing Console and game prices
Microsoft program initiatives Which projects are selected
NFL ratings Team ratings
Retirement portfolio Fraction of money invested in each asset class

Constraints

Constraints are restrictions you place on the changing cells. In our product mix example, the product mix can't use more of any available resource (for example, raw material and labor) than the amount of the available resource. Also, we should not produce more of a product than people are willing to buy. In most Solver models, there is an implicit constraint that all changing cells must be nonnegative. I'll discuss nonnegativity constraints in more detail in later chapters. Remember that a Solver model does not need not have any constraints. The following table lists the constraints for the problems presented at the start of the chapter.

Model Constraints
Drug company product mix

Product mix uses no more resources than are available

Do not produce more of a product than can be sold

Xbox shipping

Do not ship more units each month from a plant than plant capacity

Make sure that each customer receives the number of Xboxes they need

Xbox pricing Prices can't be too far out of line with competitors’ prices
Microsoft project initiatives Projects selected can't use more money or skilled programmers than are available
NFL ratings None
Retirement portfolio

Invest all our money somewhere (cash is a possibility)

Obtain an expected return of at least 10 percent on our investments

Installing and running Solver

To install Solver, click Add-Ins on the Tools menu, and then select the Solver Add-in check box. Click OK, and Excel will install the Solver. Once the add-in is installed, you can run Solver by clicking Solver on the Tools menu.

The following figure shows the Solver Parameters dialog box, in which you input the target cell, changing cells, and constraints that apply to your optimization model.

 Note   You'll see how to do this in more detail in each of the Solver model articles listed in the See also section of this article.

The Solver Parameters dialog box.

After you have input the target cell, changing cells, and constraints, what does Solver do? To answer this question, you need some background in Solver terminology. Any specification of the changing cells that satisfies the model's constraints is known as a feasible solution. For instance, in our product mix example, any product mix that satisfies the following three conditions would be a feasible solution:

  • Mix does not use more raw material and labor than is available.
  • Mix produces no more of each product than is demanded.
  • Amount produced of each product is nonnegative.

Essentially, Solver searches over all feasible solutions and finds the feasible solution that has the "best" target cell value (the largest value for maximum optimization, the smallest for minimum optimization). Such a solution is called an optimal solution. Some Solver models have no optimal solution and some have a unique solution. Other Solver models have multiple (actually an infinite number of) optimal solutions.

The best way to understand how to use Solver is by looking at detailed examples. In the See also section of this article, you can find links to additional articles that describe how to use the Solver to address several important business (and nonbusiness) problems.

Test yourself

For each situation described below, identify the target cell, changing cells, and constraints.

  • I am borrowing $100,000 for a 15-year mortgage. The annual rate of interest is 8 percent. I make monthly payments. How can I determine my monthly mortgage payment?
  • How should an auto company allocate its advertising budget between different advertising formats?
  • Where should a city locate a single hospital?
  • How should a drug company allocate sales-force effort to their products?
  • A drug company has $2 billion to allocate to purchasing biotech companies. Which companies should they buy?
  • The tax rate charged to a drug company depends on the country in which a product is produced. How can a drug company determine where each drug should be made?
 
 
Applies to:
Excel 2003