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)
Here are two views of the same worksheet: with the values or showing the formulas. In this case, all the revenus and charges depend on the growth rate located in cell B21.
Activate the Solver
Add-ins are usualy not activated when you use Excel. Follow the instructions below to activate Solver or any other add-ins you wish.
In Excel, press the Office button . OR
In Excel 2010, press the File tab.
Press the Options option.
From the left column, select the Add-Ins category.
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 is 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).
To select many cells, use the procedure below.
Select the first cell you wish to have in the box.
Press and hold the CTRL key.
Select the other cells while still holding the CTRL key.
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.
Solver allows you to use one of these six constraints:
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.
The contents of the cell should give a whole number, with no fraction.
The content of the cell must either be TRUE or FALSE.
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 is not recommended. But this is not recommended. It is 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.
Press the Options button.
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.
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.
You like what you read?
Share it with your friends.