14. Goalseek and Solver

 
Subtitles Enabled
Replay Lesson

Next lesson: Run Scenarios Using Pivot Tables

Watch next lesson
100%

Overview

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

Lesson Notes

Install Solver

1. Go to File tab and select Options
2. Select Add-ins on the left menu
3. Select Solver Add-in and press Go
4. Make sure Solver Add-In is checked and press OK

How Solver and Goalseek work

- These tools allow you to select an output objective and input(s) to be changed
- Solver and Goalseek then find the input values that result in the output objective

Keyboard shortcuts

ALT + A , W , G: Open Goalseek

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.