In our previous tip, we showed you how to get a preferred result by using Goal Seek with a single input value. The solver command is also commonly known as the what-if analysis tool. In this week’s exercise we’ll use the example of an accountant using the solver command to determine how much revenue should be generated in order to achieve a set profit value within a given constraint.
With solver, you can find an optimal value for a formula that is subject to limitations. Solver works with variable cells that participate in creating formulas for the final result within the set limits. The values in the variable cells will be adjusted to meet the set limits and produce the preferred result.
In this week’s tip, we explain how to use the solver command to achieve a preferred result with flexible multiple values.
Note: You are welcome to download the example workbook titled, ‘the workbook’ to practice.
Applies To: Microsoft® Excel® 2010 and 2013.
1. Select solver in the analysis group under the Data tab.
2. If solver is not installed, click on the link below for instructions on adding the solver command.
3. Set the objective cell to B21 (Net profit), and then set the value of to $150,000 (Net profit value to be achieved).
4. The variable cell to be changed is the revenue, B5 (How much do we generate to achieve a net profit of $150,000).
5. Select the Add button. Set the constraints to B7 (Cost of Sales less than or equal to 180,000).
6. Select OK. The screen shot below will be displayed.
7. Select Solve, then under Reports, select answer, sensitivity and limits. Then click OK.
As you can see the Net profit of $150,000, which is our preferred result, has been achieved. The result has been adjusted within the set limitations. For analysis, the answer, sensitivity and limits reports have been generated to the left of the current worksheet.
