14. Goalseek and Solver

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

These tools allow us back-solve for desired output values - providing you with the necessary input values to hit your output objectives.

Summary

  1. Installing Solver (00:04)

    Solver is a tool that automatically solves modeling problems given various inputs we specify. Solver is an add-in that you may need to install manually in Excel. To install Solver:

    • Select the File menu, then Options (or Excel Options)
    • Select Add-Ins on the left of the window
    • Select the Solver add-in and press Go
    • Check the box for Solver and press OK

    When Solver is installed correctly, it will appear on the right of the Data tab.

  2. Using Solver (00:51)

    To use Solver, we select the cell we want to solve, and open Solver from the data tab. In our case, we want to maximize additional profit, so the cell containing this amount is the cell we want to solve. In the Solver window, this cell is the Set Objective. We can choose to maximize or minimize this value, or set it to a specific amount.

    We then set the cell or cells that can be adjusted in the “By Changing Variable Cells” box. In our model, the booking limit is chosen as this cell, because we want to find the booking limit that maximizes profit. Finally, we can add constraints to the model. These are used where a cell in the model must have particular values. In our case, we set a constraint that the booking limit must be an integer.

    When the model is set up correctly, we select Solve, and Solver will identify the booking limit that maximises additional profit, subject to our constraints.

  3. Solving for a Specific Value (02:07)

    In addition to using Solver to maximize or minimize a particular value, we can also use it to set a cell to a specific value. For example, we use Solver to identify the voucher costs that would result in an additional profit of zero. This is useful in letting us know how high voucher costs would need to be for the overbooking policy to become a bad idea.

  4. Using Goalseek (03:03)

    Goalseek is another tool that can be used to set objectives and targets. Goalseek can be accessed from the What-If Analysis option in the Data tab, or using the shortcut Alt, A, W, G.

    Goalseek is slightly more limited than Solver. It lets us set a cell to a specific value by changing the value of another cell. It cannot be used to maximize or minimize the value of a cell. It also doesn’t support constraints. It’s useful when you want to perform a quick check on a target outcome, such as profit or return on investment. Solver can then be used for more complex problems involving constraints and optimization.

Transcript

In the last lesson, we found through sensitivity analysis that the optimal booking limit existed somewhere in the range of between 195 and 210 ticket sales per plane. While we could manually plug in values to see what exact booking limit gives the highest additional profit, Excel has a tool that calculates this automatically called Solver. To install Solver, we'll go to the File tab in the top left-hand corner and go to options. We'll then go to Add-ins, select the Solver add-in and press Go. Check the box for Solver Add-in and press OK. And when solver is installed correctly, you should see an icon for the function on the far right-hand side of the data tab. Next, we select the cell that we'd like to solve for, which is total additional profit and open solver. And our total additional profit cell is selected as the set objective. I'd like to find the maximum value of additional profit, so I'll select max and the variable's that I'd like to change will be the booking limit. So I'll select the booking limit cell. Solver also allows us to add constraints to the variable cells and the constraint will be that the value has to be an integer. So I'll add a constraint. The cell reference will be the booking limit and in the dropdown, I'll select Int. Which means it has to be an integer. I press OK and then Solve. And for a few seconds, Solver will run the model and check what booking limit provides us with the highest addition of profit. And once solver's finished, we can click OK to keep the solver solution which is 200 seats. So now we know for definite that given the current inputs, a booking limit of 200 seats provides the maximum additional profit. We can actually set additional profit to any value we want using solver. Let's say, for example, I'd like to see what voucher costs would give us a total additional profit of zero. So I set additional profit to the value of zero. And the variable cell that I'll change will be voucher costs. So I'll delete booking limit and select voucher costs. I'll remove the constraint by pressing Delete and then I'll Solve. And again after a few seconds, Solver provides us with a solution. And this solution which I'll accept tells us that voucher cost of 386 pounds result in a total additional profit of 0. In these two examples, we reached our objective by altering one variable. But if you wish, Solver allows you to also adjust multiple variables at the same time to reach a set objective. In addition to Solver, Excel also provides a similar tool called Goal Seek, which also allows you to set objectives or targets. It can be found under WHATIF Analysis and accessed using the shortcut, Alt + A, W, G. Goal Seek doesn't allow you to set maximum or minimum values and it doesn't allow you to set constraints. It only has a simple Set Cell To Value functionality. Let's say we'd like to hit additional profit of 800,000. So I'll set the To Value to 800,000 and I'll change the cell Voucher Costs. So I'll select the voucher cost cell and press OK. And again Goal Seek will take a couple of seconds to hit our target value but when it does, it gives us vouchers cost of 206 pounds. I tend to use Goal Seek when I want to perform a quick check on a target outcome. But when I want to add constraints and calculate max and min values, then I'll open up Solver. Normally if a project can't exceed a certain target additional profit or return on investment, it won't go ahead. Goal Seek and Solver allow you to back solve for these target outcomes and they tell you what your inputs and assumptions would need to be for this target to be reached. The insights generated from Goal Seek and Solver can vastly improve the decision-making process within companies making these tools a necessary addition to your modeling skillset.

Excel Excel for Business Analytics Learning Plan
Modeling for Business
Build Your First Model