How to Use What-If Analysis in Google Sheets with AI

Ever wonder what would happen to your profits if your ad spend increased by 20%? Or how many new customers you'd need to officially hit your ambitious quarterly revenue goal? These are not psychic predictions, they are calculated forecasts you can make yourself. This guide shows you exactly how to answer these kinds of questions right inside Google Sheets using a powerful technique called what-if analysis, and we’ll even explore how AI can make your predictions more accurate.

What Exactly Is What-If Analysis?

At its core, what-if analysis is simply the process of changing certain numbers in your spreadsheet to see how those changes affect your final results. Think of it less like a complex data science project and more like a financial playground. It allows you to create and test different scenarios to understand the potential impact of your decisions before you make them.

Instead of guessing, you can build a simple model to answer critical business questions like:

"What happens to our profit if our main supplier raises prices by 10%?"
"How many sales do we need this month to be profitable if we hire a new employee?"
"What's our best-case and worst-case revenue outcome if our big marketing campaign flops or succeeds beyond our wildest dreams?"

By building a small model with your key metrics, you can swap out a few variables and instantly see the ripple effect across your entire business forecast. It's about replacing uncertainty with informed decision-making.

Building Your First What-If Model in Google Sheets

Even the most complex financial models are built on a few simple components. Before you start forecasting, it's helpful to understand the three essential parts of any what-if analysis setup.

Let's map them out:

Inputs (Variables): These are the numbers you plan to change. They are the "what-if" parts of your questions. Good examples include marketing budget, website conversion rate, price per product, or raw material costs. It's a great habit to format these cells differently - like with a blue font or a light yellow background - so you and your team know these are the numbers meant for tinkering.
The Model (Formulas): This is the engine of your analysis. It’s a set of formulas that connect your inputs to your outputs. For example, a simple model might use the formula Revenue = Website Traffic * Conversion Rate * Average Order Value. Your model defines the logical relationship between all your variables.
Outputs (Results): These are the final calculated outcomes you're interested in. Outputs like Net Profit, Total Revenue, or Customer Lifetime Value change dynamically as you adjust your inputs. Seeing these numbers update in real-time is where the "aha!" moments happen.

Step-by-Step Example: Forecasting E-commerce Profitability

Talk is cheap, so let's build a real model. Imagine you run a small online store selling handmade candles. You want to build a what-if analysis to see how changes in traffic and conversion rates affect your gross profit.

1. Set Up Your Spreadsheet

Open a blank Google Sheet. In column A, list your labels. Your setup might look something like this:

Inputs:

Website Visitors
Conversion Rate (%)
Average Order Value ($)
Cost of Goods Sold (COGS as %)

Outputs:

Total Revenue ($)
Gross Profit ($)

Next to your labels in column B, enter some baseline numbers. Again, it is a great idea to make the input numbers stand out visually by changing the font color to blue.

2. Enter Your Formulas

Now, let’s build the model. This is where you connect your inputs to your outputs.

In the cell next to "Total Revenue" (B6 in our example), type the following formula. This calculates revenue based on your key inputs:

In the cell next to "Gross Profit" (B7), type this formula. This calculates profit by subtracting the cost of goods sold from revenue:

Your spreadsheet should now instantly calculate the revenue and profit based on the inputs you provided.

3. Test Scenarios Manually

You’re ready for analysis! You can now change any of the blue "Input" values to see how they affect the "Output" cells.

What if we get more traffic? Change "Website Visitors" from 5000 to 7500. Watch your Revenue and Gross Profit update automatically. Now it's not guessing, it becomes "if our SEO efforts boost traffic by 50%, we can expect our gross profit to jump from $X to $XX."
What if our conversion rate drops? Lower "Conversion Rate" from 2% to 1.5% to see the potential damage. This gives you a clear number to attach to the importance of website optimization. "If we neglect our checkout process and the conversion rate dips by half a percent, we'll lose $XXX in gross profit every month."

This simple act of plugging and playing in numbers is the foundation of all what-if analysis.

Automating Your Analysis with Google Sheets Tools

Manually changing inputs is a great starting point, but Google Sheets offers tools to make your analysis more precise, especially when you have a specific goal in mind.

Finding the "How" with Goal Seek

Sometimes, your question isn't "what happens if?" but rather "how do I get there?". For example, "we absolutely need to make $10,000 in gross profit next month. What conversion rate do we need to achieve to hit that target?" This is a job for Goal Seek. Goal Seek works backward, letting you set a desired output value, and it will calculate the required input value to achieve it for you. It's essentially an analysis in reverse.

Since Google Sheets does not have a native Goal Seek feature like Excel does, we need an add-on. But don’t worry, it’s free and easy to install:

In the menu, go to Extensions > Add-ons > Get add-ons.
Search for “Goal Seek” and install the one offered by Google. You’ll need to grant it permission to run.
Once installed, open it by going to Extensions > Goal Seek > Open.

To use it to solve our "make $10,000 in profit" problem, enter the following:

Set cell: Select the cell containing your Gross Profit formula (B7 in our example).
To a value: Enter 10,000.
By changing cell: Select the cell with your Conversion Rate (B2).

Click the 'Solve' button. Goal Seek will rapidly test dozens of possibilities until it finds the exact conversion rate you need to reach your goal.

Supercharging Your Scenarios with AI

The biggest challenge in what-if analysis is not building the formulas - it's coming up with realistic scenarios. What does a pessimistic conversion rate look like? Is an optimistic traffic projection a 20% increase or 50%? This is where AI becomes a game-changer. AI can help you brainstorm and generate more informed scenarios based on much broader context than you might have on your own.

Using AI to Generate Scenarios

You can use any AI tool, like ChatGPT or an add-on for Sheets, to generate some helpful inputs. You just need to give it some context. Here’s an example prompt: “I run a small e-commerce store selling handmade candles. My current baseline data for a month is: 5000 visitors, a 2% conversion rate, and a $50 average order value. Based on market trends and a planned social media campaign, give me three realistic scenarios (Pessimistic, Realistic, and Optimistic) for the next quarter. For each scenario, provide realistic potential values for website traffic and conversion rate.”

This approach will give you structured answers like this:

Pessimistic: Economic pressure and low conversion rates could result in 4500 visitors and a 1.6% conversion rate.
Realistic: Steady growth from marketing efforts leads to 5500 visitors and a 2% conversion rate.
Optimistic: The new social media campaign is a success, driving a surge in traffic. Expect 6000 visitors and a 3% conversion rate.

Now, instead of manually guessing, you have three data-backed scenarios to plug into your spreadsheet. You can see how gross profit changes in each situation, giving you clarity on potential risks and rewards for your ideas.

Creating a Scenario Comparison Table

To present your findings in a clear manner, create another simple table in your sheet that compares the results.

This will give you tangible snapshots of where things already stand or might end up. It's simple, effective, and adds depth to your strategic analysis.

Practical Tips for Better Analysis

With your basic model built and the right setup, even better results can be achieved. Use these additional tips to expand your analysis further:

Use Advanced Techniques: Avoid sticking with traditional methods and venture into advanced functions and scripts for in-depth analysis.
Invest in Training: If numbers and analysis are critical to your business, consider professional development in data analytics tools.
Continually Iterate: As you gain insights, refine your model to incorporate new data, evolving business elements, and market conditions.