How to Add What-If Analysis in Excel

Building a forecast or budget in Excel is a great start, but what about answering "what if?" questions? What if your costs increase by 10%? What happens if a new marketing channel brings in 20% more customers? Manually changing those numbers to check each outcome can be tedious and error-prone. This article details using Excel's built-in What-If Analysis tools - Scenario Manager, Goal Seek, and Data Tables - to make forecasting easier.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly is What-If Analysis?

What-If Analysis is a set of tools in Excel that lets you substitute different values into your formulas to see how the changes affect the outcome. Instead of creating multiple versions of your spreadsheet or manually editing cells repeatedly, you can test different possibilities within a single model. It helps you move from static reporting to dynamic forecasting, turning your spreadsheet into a powerful decision-making tool.

Excel provides three main tools under the "What-If Analysis" umbrella, each designed for a different purpose:

• Scenario Manager: Perfect for when you want to compare a few distinct outcomes. You can create different groups of input values (scenarios) like "Best Case," "Worst Case," and "Most Likely" and switch between them instantly. Think of this as comparing a handful of complete, alternative universes for your data.
• Goal Seek: Works backward. If you know the result you want, Goal Seek helps you find the single input value needed to achieve it. For example, if you need to hit a profit target of $100,000, Goal Seek can tell you exactly how many units you need to sell to get there.
• Data Tables: Ideal for seeing how changes in one or two variables affect your final result across a wide range of possibilities. It creates a table showing all potential outcomes at once, like a lookup chart for every possibility.

How to Use Scenario Manager to Compare Multiple Outcomes

Scenario Manager is your go-to tool when you need to store and compare different sets of input values. It’s perfect for creating clear, defined situations like optimistic, pessimistic, and expected business forecasts.

Let's create a simple event budget model. Our profit depends on three things: Ticket Sales, Venue Cost, and Marketing Spend. The profit is calculated with the formula: Profit = Ticket Sales - (Venue Cost + Marketing Spend).

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Set Up Your Model

First, create a simple table in Excel with your input cells and formula cell. For better readability, it’s a good practice to name your cells. You can do this by selecting a cell and typing a name (e.g., "Ticket_Sales") into the Name Box to the left of the formula bar.

• B2 (Total Revenue): 50,000
• B3 (Venue Cost): -15,000
• B4 (Marketing Spend): -5,000
• B6 (Total Profit): =SUM(B2:B4)

In this initial setup (our "Most Likely" scenario), the total profit is $30,000.

Step 2: Access Scenario Manager and Add Your First Scenario

Navigate to the Data tab, click on What-If Analysis in the Forecast group, and select Scenario Manager.

In the dialog box, click the Add... button to create your first scenario.

1. Scenario name: Let's call this one "Most Likely Case".
2. Changing cells: Select cells B2, B3, and B4. If they aren't contiguous, hold down Ctrl while you click each one.
3. Click OK.

A new window will appear, asking you to enter the values for your "Changing cells". Since our sheet already has these values, we can just click OK. Or, click Add to immediately create another scenario.

Step 3: Add "Best Case" and "Worst Case" Scenarios

Now, let's add two more scenarios.

For the "Best Case" scenario:

• Click Add.
• Name it "Best Case".
• The same "Changing cells" (B2:B4) should still be selected. Click OK.
• Enter optimistic values: maybe revenue increases to $70,000, venue cost drops to $12,000, and marketing becomes more efficient at $4,000. Click OK.

For the "Worst Case" scenario:

• Click Add again.
• Name it "Worst Case".
• Click OK.
• Enter pessimistic values: revenue drops to $35,000, the venue costs $18,000, and marketing spend has to increase to $8,000. Click OK.

You should now see all three scenarios listed in the Scenario Manager window.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: View Your Scenarios and Create a Summary Report

To see how each scenario affects your model, simply select a scenario name and click the Show button at the bottom of the window. You'll see the values in cells B2, B3, B4, and B6 update automatically.

But the real power comes from the Summary feature. Click the Summary... button. In the new window:

• Ensure "Scenario summary" is selected.
• For the Result cells, select B6 (our 'Total Profit' cell).
• Click OK.

Excel will instantly generate a new, cleanly formatted worksheet that compares all your scenarios side-by-side, showing the inputs and final profit results. This report is fantastic for presentations and making quick comparisons without having to click back and forth between scenarios.

How to Use Goal Seek to Solve for a Target Outcome

Goal Seek is simpler than Scenario Manager but incredibly useful. You use it when you already know the result you need from a formula, but you're not sure which input value you need to get there. It essentially reverses the calculation.

Imagine a student trying to calculate the grade they need on their final exam. Their final grade is a weighted average: Assignments (30%), Midterm (30%), and the Final Exam (40%).

Step 1: Set Up the Model

Create a simple table with the grades and weights:

• B2 (Assignments): 85
• B3 (Midterm): 78
• B4 (Final Exam): [leave this blank for now, or enter a placeholder]
• B6 (Final Grade): =(B20.3)+(B30.3)+(B4*0.4)

The student wants to achieve a Final Grade of at least 80. What score do they need on their final exam? Let's use Goal Seek to find out.

Step 2: Open and Configure Goal Seek

Go to the Data tab > What-If Analysis > Goal Seek. A simple dialog box will appear with three fields:

• Set cell: B6 (this is the cell containing our final grade formula).
• To value: 80
• By changing cell: B4 (this is where we'll enter the final exam score needed to achieve the target grade).

Excel runs its calculations and tells you what score you'll need on the final exam to reach a Final Grade of 80.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Create Data Tables for Quick Sensitivity Analysis

Data tables are a fantastic tool that shows you how changing one or two variables in a formula impacts the final result. We'll walk through an example of a loan payment calculation using a one-variable data table.

Using a One-Variable Data Table

To find out how monthly payments on your loan change with interest rates, we can create a data table. Here's a simplified setup:

Step 1: Set Up the Formula

First, create a formula to calculate the monthly payment of a loan using Excel's PMT function. For example, if you're calculating payments for a car loan, set it up as follows:

• B1 (Interest Rate): Enter the interest rate applicable for the loan.
• B2 (Loan Term in Months): Enter the total number of payment periods (e.g., 72 months for a 6-year loan).
• B3 (Loan Amount): Enter the total loan amount.
• B6 (Monthly Payment): Use the formula =PMT(B1/12, B2, -B3)

Now you can fill a series of interest rates in one column and link them to B1 to see how the monthly payments are affected.

Using Two-Variable Data Table

To explore how changes in both interest rate and loan term impact monthly payments, implement a two-variable data table. Place various interest rates in one direction and different loan terms in the other, letting Excel calculate the payment for each combination automatically.

Final Thoughts

Excel's What-If Analysis tools are invaluable for transforming static spreadsheets into dynamic financial models. These tools allow you to quickly explore scenarios, solve for desired outcomes, and analyze sensitivity, providing great insights for each scenario.

We made Graphed to assist with complex calculations and data automation. It helps get accurate results quickly and efficiently, allowing you to focus on strategic decision-making. Linked with platforms like Shopify and HubSpot, you get real-time updates and a streamlined experience.