How to Use What-If Analysis in Excel with ChatGPT

Ever wonder how a small price tweak could impact your yearly revenue, or how a dip in ad performance might affect your profit margin? These aren't just guesses, they're questions you can answer using What-If Analysis in Excel. This article will show you how to use Excel's powerful built-in tools for financial forecasting and how to bring ChatGPT into the mix as your expert assistant to make the process even faster and more insightful.

What Exactly is What-If Analysis?

At its core, What-If Analysis is the process of changing values in a spreadsheet to see how those changes affect the outcome of your formulas. It's a way to test different scenarios and understand the relationships between different inputs and outputs without having to manually create dozens of different spreadsheets. It allows you to move from asking "what happened?" to confidently asking "what could happen if...?"

Imagine you’re a content creator launching an online course. Your profit depends on a few key variables:

Your monthly ad spend
Your ad click-to-purchase conversion rate
The price of your course

You can build a simple model in Excel to calculate your profit. With What-If Analysis, you can instantly see how your profit changes if you increase your ad spend by 20% or if your conversion rate drops by half a point. Instead of just having one static projection, you have a dynamic model that helps you make smarter decisions.

Excel has three primary tools built specifically for this kind of work, found under the Data > What-If Analysis tab:

Scenario Manager: Lets you create and save different groups of input values (scenarios) and switch between them. It’s perfect for comparing "Best Case," "Worst Case," and "Most Likely Case" side-by-side.
Goal Seek: Works backward for you. You tell Excel what result you want from a formula, and Goal Seek figures out what input value you need to get there.
Data Tables: Shows you how changing one or two variables in your model affects a key outcome, all displayed in a simple table. It’s great for seeing a range of possibilities at a glance.

Step 1: Build Your Base Model in Excel

Before you can analyze anything, you need a clean, well-structured financial model. The key is to separate your inputs (the variables you'll change) from your calculations and outputs. This makes it easy for Excel’s tools - and for you - to understand what’s going on.

Let's use a simple example: a freelance videographer forecasting their annual income. Here’s a simple model setup:

Inputs (Assumptions):

Projects Per Month: The average number of projects you expect to complete. (Cell B3)
Rate Per Project (€): The average amount you charge per project. (Cell B4)
Monthly Expenses (€): Your fixed costs like software, insurance, etc. (Cell B5)

Calculations & Outputs:

Monthly Revenue:
Annual Revenue:
Monthly Profit:
Annual Profit:

With this simple model, you're ready to start exploring different scenarios.

Step 2: Exploring Scenarios with Excel's Native Tools

Let’s put these built-in tools to work on our freelance videographer model.

Using Goal Seek to Hit a Target

Our videographer has a goal: to make over €70,000 in annual profit. They can't do more than 4 projects per month, so they need to figure out what to charge per project. Goal Seek is perfect for this.

Navigate to Data > What-If Analysis > Goal Seek.
In the dialog box, enter the following:
- Set cell: F6 (This is our Annual Profit outcome)
- To value: 70000 (Our target profit)
- By changing cell: B4 (The Rate Per Project we need to determine)
Click OK. Excel will instantly calculate that they'd need to charge roughly €2,163.67 per project to hit their goal.

Using Scenario Manager to Compare Outcomes

Running a business means being prepared for different situations. What if bookings are great? What if they're slow? Scenario Manager helps you compare these possibilities.

Let’s create three scenarios:

Base Case: 4 projects/month at €1,500/project. (Our original model)
Worst Case: Only 2 projects/month at €1,200/project.
Best Case: A busy 5 projects/month at €1,750/project.

Here’s how to set this up:

Go to Data > What-If Analysis > Scenario Manager.
Click Add.... Give the first scenario the name "Worst Case."
For the Changing cells, select B3,B4 (the cells for Projects Per Month and Rate Per Project). Click OK.
Enter the values for the worst case: 2 for B3 and 1200 for B4. Click OK.
Repeat this process, adding a "Best Case" scenario with values 5 and 1750. You can also add a "Base Case" with the original values if you like.

Now, you can select any scenario in the manager and click Show to instantly update your model. For a nice overview, click Summary to have Excel create a new table breaking down every scenario side-by-side.

Using Data Tables for At-a-Glance Comparisons

Data tables are great for a more granular view of how one or two variables influence an outcome. We’ll create a Two-Variable Data Table to see how projected annual profit changes with different numbers of projects per month and rates per project.

Set up your table, including the correct formula in the cell that calculates the annual profit (here it's assumed to be F6).
Identify the cell that contains the rate per project (B4) for the data table's row input and projects per month for the column input.
Go to Data > What-If Analysis > Data Table.
In the dialog box:
- Row input cell: B4 (The rate per project)
- Column input cell: B3 (The number of projects per month)
Click OK.

Excel will instantly populate the table with variations in annual profit for each combination of variables.

Step 3: Supercharging Your Analysis with ChatGPT

Excel's built-in tools are fantastic. But when the models get complex? You want to know which variables to even consider? That's where ChatGPT comes in as your personal assistant.

Getting Help with Complex Formulas

Instead of struggling over complex tax calculations, ask ChatGPT for help. It can provide you with useful formula suggestions, reducing errors and frustration.

Brainstorming Predictions and Variables

What are the scenarios you have not considered? Ask ChatGPT for brainstorming prompts.

I am a freelance videographer creating a financial projection. What are the key variables I should include in a What-If analysis?

Final Thoughts

What-If Analysis with Excel lets you turn data into actionable insights. Tools like Scenario Manager and Goal Seek help you structure projections to fit a variety of outcomes. Incorporating ChatGPT helps you create a more comprehensive view of potential futures. While Excel remains essential for spreadsheet-based forecasting, the day-to-day reality often involves understanding what's already happened. That's why we created Graphed, a powerful analytics platform designed to help professionals make data-driven decisions. Instead of building manual spreadsheets, use insights and automations to drive your business forward.