How to Use What-If Analysis

Thinking about the future of your business always involves asking "what if?" What if our ad budget doubles? What if our conversion rate drops by 10%? What if we increased our product price? What-if analysis is the structured process of answering these questions by changing key variables in a model to see how the outcomes are affected. This article will show you what it is, why it's a great tool for decision-making, and how to perform it using the most common tools in Excel and Google Sheets.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Is What-If Analysis and Why Should You Care?

At its core, what-if analysis is a way to substitute different values into your formulas to explore a range of possible results. Instead of having a single static forecast, you can create a dynamic model that shows you the best-case, worst-case, and most-likely scenarios. It’s essentially a financial and strategic sandbox where you can test theories without any real-world consequences.

This process is incredibly valuable for a few key reasons:

• Smarter Financial Planning: It helps you understand the direct impact of financial decisions. You can see exactly how a 5% increase in your ad budget might translate to revenue, helping you allocate resources more effectively.
• Risk Management: By modeling potential negative scenarios - like a drop in website traffic or a rise in material costs - you can prepare contingency plans and build a more resilient business strategy.
• Better Goal Setting: Wondering if that aggressive sales target is achievable? What-if analysis lets you work backward to see exactly what marketing and sales performance you’d need to hit that number.
• Strategic Decision-Making: You can confidently assess the potential outcomes of major business decisions, like launching a new product, changing your pricing model, or entering a new market.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

The Three Musketeers of What-If Analysis in Spreadsheets

While the concept sounds complex, spreadsheets like Excel and Google Sheets have built-in tools designed specifically for this kind of analysis. The three primary tools in Excel are Scenario Manager, Goal Seek, and Data Tables. Google Sheets doesn't have identical native features but offers Goal Seek and provides easy ways to replicate the others.

Let's break down how to use these with some practical, everyday business examples.

1. Using Scenario Manager for Multiple Outcomes

Scenario Manager is perfect when you have several variables that might change and you want to compare different potential futures. It allows you to create and save sets of input values (scenarios) and then switch between them to see the results instantly.

Example: Let's plan a digital marketing campaign budget. Our success depends on several variables we don't fully control: Cost Per Click (CPC), Website Conversion Rate, and Average Order Value (AOV).

Step 1: Build Your Base Model

First, create a simple model in your spreadsheet with your inputs (variables) and outputs (results based on formulas).

• Inputs (Changing Cells):
• Outputs (Formulas):

Your base model shows one specific outcome. But what if things go really well, or really poorly?

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 2: Create Scenarios (Excel)

To use Excel's Scenario Manager:

1. Go to the Data tab, click What-If Analysis, and select Scenario Manager.
2. Click Add... to create your first scenario. Name it "Pessimistic."
3. For Changing cells, select the cells containing your inputs (CPC, Conversion Rate, AOV). Hold Ctrl (or Cmd on Mac) to select multiple cells.
4. Click OK. Enter the values for your pessimistic outlook (e.g., CPC up to $1.75, Conversion Rate down to 1.5%, AOV $70).
5. Repeat to add an "Optimistic" scenario (e.g., CPC down to $0.90, Conversion Rate up to 3.5%, AOV $95).

Now you can double-click any scenario in Scenario Manager to see your model update instantly. For a clean report, click Summary... to generate a comparison sheet of all scenarios.

Replicating Scenarios in Google Sheets

Google Sheets lacks a built-in Scenario Manager. To replicate, create columns for each scenario next to your base model. Copy your input labels and results, then manually enter values for "Pessimistic" and "Optimistic" cases, copying the formulas down. While less automated, this achieves similar comparison views.

2. Working Backwards with Goal Seek

Goal Seek is perfect for simple "what if" problems where you have a target value and need to find the input that achieves it.

Example: Your company wants $20,000 monthly revenue from a new subscription. You know the price per subscription, so you need to find how many subscribers you need.

Step 1: Set Up the Problem

• Inputs (Changing Cell): Number of Subscribers (start with an estimate, e.g., 150)
• Constant: Price Per Month ($99)
• Output (Goal Cell): Total Revenue: =NumberOfSubscribers * PricePerMonth

Step 2: Use Goal Seek

In both Excel and Google Sheets:

• Excel: Go to Data > What-If Analysis > Goal Seek
• Google Sheets: Use the free "Goal Seek" add-on from the Google Workspace Marketplace (Extensions > Goal Seek > Open)

Set parameters:

• Set cell: Total Monthly Revenue cell
• To value: 20000
• By changing cell: Number of Subscribers cell

Click OK. The tool iterates to find the exact number of subscribers needed (about 202), turning a guess into a precise goal.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

3. Analyzing Many Variables with Data Tables

Data Tables are the most flexible tool, allowing you to see how one or two variables influence an outcome across many scenarios—ideal for sensitivity analysis.

One-Variable Data Table

Example: You sell an online course. For every $10 increase in price, sales drop by 5. How does that affect total profit?

1. Build a profit model at a base price.
2. Create a list of prices (e.g., $99 to $299, in $10 increments).
3. In an adjacent cell, link to your profit formula.
4. Select the range (list of prices and results), go to Data > What-If Analysis > Data Table.
5. Use the "Column input cell" to specify the original price cell.
6. Click OK. The table displays profit for each price.

Two-Variable Data Table

Example: How do product price and ad spend jointly affect profit?

1. Set up your model.
2. List potential prices down the first column and ad spends across the top row.
3. Link the top-left cell to your profit formula.
4. Select the entire grid, go to Data > What-If Analysis > Data Table.
5. For the "Row input cell," select your ad spend key cell.
6. For the "Column input cell," select your price cell.
7. Click OK. The grid shows profit for each price and ad spend combo, guiding strategic decisions.

Final Thoughts

What-if analysis transforms static data into a dynamic planning tool. Using Scenario Manager, Goal Seek, and Data Tables, you can start predicting future outcomes and make smarter decisions.

One common hurdle is manual setup—exporting data from various platforms and shaping it into models. To simplify, we built Graphed to connect directly with your marketing and sales data sources in real time. You can ask questions like, "What if Facebook ad spend was $10,000 next month?" or "Compare projected sales with a 5% increase in conversion rate." We handle data connection and AI analysis so you spend less time on spreadsheets and more on exploring possibilities.