What-If Analysis in Excel

What-if analysis in Excel is a powerful tool for running scenarios or solving for variables. Let’s walk through how to use both the scenario manager and goal seek functions within this tool

What Is What If

What-if analysis is a built-in tool for Excel that can save you a ton of time. It allows you to run scenarios or solve for variables without having to write multiple formulas or go through painstaking trial and error.

You can find what-if analysis on the data tab of the ribbon. Within what-if analysis, you will find both scenario manager and goal seek.

Scenario Manager

Let’s say you are running a hotel. You can adjust the room rate however you see fit. At higher room rates, fewer people will book. At lower room rates, more people will book. Of course, you have fixed costs to overcome like the maintenance and staffing of the front desk. And you have variable costs to service each room and maybe provide breakfast in the morning. This is a fairly common rate and volume analysis and can be laid out in Excel.

In this situation, your goal isn’t to fill up the hotel. Rather, it is to generate the most potential profit. Scenario builder will let us test each occupancy rate and show the profit for each.

First, open scenario builder as shown above. You should see a screen like this:

Hit the “+” or “Add” button depending on your version. Name the scenario based on the variable (60% scenario in this case) and select the cell that you want to test.

Then, you will need to input the value that you want to run.

Repeat these steps for each scenario you want to test.

Once completed, hit the summary button. Select the cell in which the result is calculated. In this case, it is the cell where profit sits.

After running the scenarios, Excel will output a table showing the result for each. In this case, the 80% occupancy scenario generates the highest profit based on overall room rate compared to expenses.

Goal Seek

Instead of running scenarios, you know the result you want and need to get the drivers for a specific result. This is very common with budgeting or project management. Back to the hotel example, your budget is $3,820 of profit per night. What occupancy rate do you need to shoot for.

Navigate back to What-If analysis on the data tab and select goal seek. Select the cell you want to find a value for, set the value, and select the target cell that needs to change. In this case, we are adjusting profit to $3,820 by changing occupany rate.

Excel will reiterate the formulas until its finds the target value. Note that if you get an error back, no variable was able to solve for the equation. In this case, Excel finds the occupany rate of 90%.

Want to learn more great tips and tricks for using Excel? Check out our list of the best Excel courses and start a deep dive into the world of Excel today!

Have any questions on what-if analysis in Excel? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.