How to Use What-If Analysis in Excel with AI

Ever wish you had a crystal ball to see how business decisions might play out? What if you raise your prices by 10%? What happens to your profit if your main supplier increases costs? Answering these questions is the core of what-if analysis, a powerful feature in Excel that helps you substitute different values into your formulas to see the potential outcomes. This guide will walk you through how to use Excel's classic what-if tools and explain how modern AI is making this process even smarter and faster.

What Exactly is What-If Analysis?

At its heart, what-if analysis is the process of changing variables in a model to see how those changes affect the end result. It’s like a financial or operational simulator built inside your spreadsheet. Instead of just looking at the numbers you have, it lets you explore the numbers you could have under different circumstances.

It's incredibly useful for things like:

Budgeting and Forecasting: See how changes in sales, costs, or other variables impact your bottom line.
Decision Making: Compare the potential outcomes of launching a new product, changing marketing spend, or hiring more staff.
Risk Assessment: Model "worst-case scenarios" to understand your business's vulnerabilities and plan accordingly.

Excel offers an entire suite of tools specifically for this, hidden under the Data tab, in a menu called What-If Analysis.

Excel’s Built-in What-If Analysis Tools

Let's break down the three primary tools Excel gives you for this type of analysis: Scenario Manager, Goal Seek, and Data Tables. Each one is designed for a slightly different purpose.

For these examples, let's imagine we run a small online store selling custom T-shirts. Here’s our super simple model in Excel:

1. Goal Seek: Working Backward to a Target

Goal Seek is the most straightforward of the tools. It’s perfect for when you know the result you want, but you’re not sure what input you need to get there.

The Question: "We made $8,750 in profit this month, but we want to know how many units we’d need to sell to reach a $12,000 profit."

Instead of manually plugging in different numbers for "Units Sold" until you get close, Goal Seek does it for you instantly.

How to Use Goal Seek:

Navigate to Data > What-If Analysis > Goal Seek.
A small dialog box will appear with three fields:
- Set cell: This is the cell with the outcome you want to change. In our case, it's the Profit cell (E9).
- To value: This is your target. We want our profit to be 12000.
- By changing cell: This is the single input cell you want Excel to adjust. For us, it’s the Units Sold cell (E2).
Click OK. Excel will instantly run through the calculations and find the exact number of units you need to sell. In this case, it's 1,225 units.

Goal Seek is fast and easy, but its main limitation is that it can only change one input variable at a time.

2. Scenario Manager: Comparing Different Futures

Scenario Manager is what you use when you want to see how an entire set of different input variables affects your results. You can save multiple "scenarios" and switch between them to see the outcomes.

The Questions: "What if our advertising costs go up and our material costs go down? What does a 'best-case' vs 'worst-case' month look like for our profit?"

How to Use Scenario Manager:

First, define your base model as the "Current Month" scenario. This locks in your starting point.
Go to Data > What-If Analysis > Scenario Manager.
Click Add... to create your first scenario. Name it "Best-Case Scenario."
For Changing cells, select all the input cells you want to adjust. For our T-shirt store, this might be E2 (Units Sold), E3 (Price per Unit), E4 (Material Cost), and E5 (Ad Spend).
Enter the values for this best-case future: e.g., sell 1,500 units, drop ad spend to $1,000, find a cheaper material supplier ($9 per unit).
Again, click Add... to create a "Worst-Case Scenario." Keep the same changing cells. For this one, assume only 600 units sold and material costs jump to $12.
Click OK. Your scenarios now appear in the Scenario Manager window. You can select any scenario and click Show to update your spreadsheet instantly.
For a report, click Summary... and Excel generates a new sheet with a side-by-side comparison of all scenarios.

Scenario Manager is excellent for presenting different possibilities to stakeholders clearly.

3. Data Tables: Visualizing the Impact of Two Variables

Data Tables are designed for seeing how changes in one or two variables affect your final output across a range of values. The result is a table that lets you view dozens of potential outcomes at a glance.

The Question: "How does our profit change at different price points and for different units sold?"

Goal Seek handles one variable, Scenario Manager models many variables in discrete sets, but Data Tables can visualize the spectrum of outcomes for two key variables simultaneously.

How to Use a Two-Variable Data Table:

Setup your table structure:
- In one column, list a range of possible Units Sold (e.g., 500, 600, 700... 1200).
- In a row across the top, list a range of possible Price per Unit values (e.g., $18, $20, $22, $24).
In the top-left cell of the table (intersection of row and column), link your output formula: type =E9 to connect to your profit cell.
Highlight the entire table, including headers and the linked formula.
Go to Data > What-If Analysis > Data Table.
For Row input cell, select the cell that corresponds to the row variable (Price per Unit), e.g., E3.
For Column input cell, select the cell that corresponds to the column variable (Units Sold), e.g., E2.
Click OK. Excel will fill the table with profit values for every combination of price and units sold.

You can further enhance this with Conditional Formatting to visually highlight the most profitable options.

Enter AI: Supercharging Your Excel Analysis

As powerful as Excel’s tools are, they rely entirely on your manual inputs and assumptions. You have to establish scenarios yourself, it can't evaluate their realism based on data.

This is where AI transforms the process. AI tools, especially large language models like ChatGPT, can act as a strategic partner to help you brainstorm and test assumptions.

Using generative AI to develop and analyze different scenarios

Think of AI as an analytics-savvy brainstorming partner. Before opening Scenario Manager, you can give an AI context about your business and ask it to suggest scenarios.

Workflow example:

Provide Context to AI: Instead of vague questions, give details, e.g.,
"I run an online T-shirt business. Current monthly data: Units Sold=800, Price=$25, Material Cost=$10, Ad Spend=$1,500. Profit=$8,750. Based on market trends, brainstorm three scenarios: one optimistic, one pessimistic, and one growth experiment. For each, suggest new variable values and explain briefly."
Build AI-Generated Scenarios: The AI may suggest ideas like raising prices while doubling ad spend to target a premium market.
Use Excel for Calculation: Input these suggestions into Scenario Manager as usual.
Use AI for Deeper Insight: After generating results, ask the AI to interpret. For example:
"The 'growth experiment' shows the highest profit but needs high ad costs. What risks and rewards should I consider?"

This combination leverages AI’s creativity and Excel’s precise calculations, helping you develop data-informed hypotheses rather than guesswork.

Final Thoughts

What-if analysis turns spreadsheets into strategic tools. Using Goal Seek, Scenario Manager, and Data Tables helps you model alternative futures, enabling smarter decisions. When combined with AI’s brainstorming capabilities, you move beyond simple forecasts into comprehensive strategic modeling.

While spreadsheets are powerful, they often require manual setup. At Graphed, we automate this process by connecting directly to your live sales and marketing data. This allows you to ask natural language questions, like "Show me my Shopify revenue versus Facebook Ads spend," and get immediate, data-backed answers — simplifying complex analysis into a conversation.