Navigation

Previous
Next
Homepage
FAQ
What's new
Sitemap

Topics

Excel
Office button
Home
Insert
Page Layout
Formulas
Data
Review
View
Developer

Excercises
Autosum
Chart - Insert a second axis
Create a half-moon chart
Create a demographics chart
Conditional formatting
Data table
Goal Seek
Pivot table
Scenario Manager
Solver

Tutorials

Word
Excel 2003
Excel
PowerPoint
Access

Others

Demonstration files
Texte en français

Contact

By e-mail
Join our Newsletter

Share this page

Excel 2010 - Exercise on Solver

Introduction
Before we start
Activate the Solver
Add constraints
The options
Solve the problem
Refences

Introduction

Microsoft Excel is known to be one most used application in the world because it's so versatile. Many companies have added options to Excel to make it even better. Those pieces of software are called add-ins. The Solver is an add-in you can use to optimize your model You can try to maximize your profits, minimize your losses or achieve a goal with the least amount of resources. Don't forget to add the constraints to your model. Otherwise, your résultat ira vers l'infini ! Otherwise, your result will go toward infinity! (And beyond if you believe Buzz Lightyear)

Before we start

For this exercise, you can use the file excel2007-2010-exercises.xlsx on the demonstrations files web page.

Select the Completed Model worksheet.

From the Manage area, select Excel Add-ins and press the Go button.

From the list of Add-ins, select Solver Add-in.
Press the OK button.

At the end of the Data tab you will now find the Solver tool.

The company wants to optimize its quarterly profits. But it also has some constraints that it must adhere. The monthly growth rate of the company (B21) must be between 15% and 150%. The sales for Product 100 (B3) must be between 45 000$ and 250 000$. The sales for Product 200 (B4) must be between 10 000$ and 125 000$. The sales for Product 300 (B5) must be between 5 000$ and 75 000$. With these informations, use the Solver to find the optimal solution.

Go to the Data tab.
Press the Solver button.

The Set Objective box is where you place the cell you want to optimize. For this example, it's the D19 cell that contains the sum profit for the quarter.Afterward, you determine what you want to achieve. You can either maximize, minimize or determine a goal value to attain.

The By changing variable cells box is where you select the cells that you allow the Solver to change to optimize the objective.Please note that changing cells must always be cells with only numbers, not formulas.

The B21 cell B21 are variable (growth rate) and B3, B4 and B5 (sales the first month).

Add constraints

Without constraints for each variable you select, the Solver will give a solution moving toward infinity.Constraints force the solver to consider the limits of your variable cells.

To accelerate the process, all the constraints have been added except those related to the B3 cell. We must determine the upper and lower limit for this cell.

Press the Add button and the constrait according to the image.

In the Cell box, enter B3.
In the Constraint box, enter B32.

Don't worry about the ''$'' sign. It won't affect the result.

Equal to

The contents of the cell must be equal to a fixed amount.

Greater or equal to

The contents of the cell is higher or equal to a fixed amount. It determines the minimal value for the cell since the value cannot be below that threshold.

Less or egale to

The content must be less than or equal to a fixed amount of the check constraint. It determines the maximum possible value because nothing can exceed it.

Int

The contents of the cell should give a whole number, with no fraction.

Bin

The content of the cell must either be TRUE or FALSE.

Dif

For this exercise, select the constraint >= .

The contents of cell B32 is the upper limit determined for that model. You can put a value in the cell. But it's not recommended. But this is not recommended. it's easier to change a boundary in a cell than having to change a constraint in the Solver window.

Press the Add button this last constraint.

In the Cell reference box, enter B3.
Select the constraint >=.
In the Constraint box, enter C32.
Press the OK button.

The solver offers several options to help you optimize your result. The degree of accuracy, tolerance and convergence helps you determine how much you will be close to the optimal solution depending on the model you have. The model assumed linear solver optimizes for simple models. You can also choose from several types of estimates, and derived types of research to find the optimal solution varies depending on the efficiency equations to solve.

The constraint precision box determines how close the Solver's result should be to the constraints before stopping.

Select the All Methods tab.
Activate Use Automatic scaling.

This is a very important option in this case because the range of each variables is so wide. The values for the growth rate (B21) needs only to move by 0.01 to have a great impact on the final result. But the same change for the other cells (B3 to B5) would barely be noticable. Automatic scaling ajusts the range to each variable cell instead of using a single scale for all the variables. You will not find the optimal solution if you don't activate this option.

You can place a maximum amount of time to find the optimal solution. You can also determine the number of trials, or iterations, the solver can take to find the solution. The Solver may not find the optimal solution if it doesn't have time or iterations to properly work your model. But it may take a lot of time if you don't but limits.

Select the GRG Nonlinear tab.

The convergence box determines how close to the optimal solution the Solver must be to stop searching.

The forward derivative will try to find the optimal solution by moving toward the solution. The central derivative may give a better solution but requires more time for its calculations.

The Use multistart option will try different starting points for each values to try to find a better solution. But it will take more time to try all the possibilities.

Select the Evolutionary tab.

The convergence dertimes how close to the optimal solution the Solver should be before stopping. The mutation rate determines how much of the variables should change from one iteration to the other. The value can change from 0 to 0.999. The higher the value, the more variation there will be between each iteration. That may take longer to find the optimal solution.

The Population Size is the number of data points the Solver keeps to help it find the optimal solution. The minimum is 10 and the maximum is 200.

The Solver will stop trying to find a better solution if it cannot improve on the current solution after the amount of time placed in this box. It may pass over the optimal solution if that value is too low and you have a complex model with many variables.

The Require Bounds on Variables should always be activated. But it requires that you enter the constraints for the upper and lower limits of each variable.

To return to the Solver window, press the OK button.

Solve the problem

Press the Solve button.

The Solver will do its calculations to find the optimal solution before showing the results on the worksheet. The solver window will also appear to give you some options.

The first option is to determine whether you wish to keep the Solver's solution or to return to the to original values. Depending on the type of method you used to find the solution, you may select from a number of reports that will generate new worksheets with the data you requested. You may even save the variables into a scenario and compare it to other scenarios.

Select the options you wish.
Press the OK button.