What is What-If Analysis in Google Sheets?

Ever wish you had a crystal ball to see how business decisions might play out before committing? What-if analysis is the closest you'll get, allowing you to tweak variables in a spreadsheet to see the impact on your bottom line. This article walks you through exactly how to perform powerful what-if analysis directly in Google Sheets, from simple formula tweaks to building your own dynamic scenario manager.

What is "What-If Analysis" Anyway?

At its core, what-if analysis is the process of changing the values in cells to see how those changes affect the outcome of formulas on a sheet. It’s a way to explore different scenarios and potential results without altering your original data. Instead of making decisions based on a single, static forecast, you can model a range of possibilities.

Think about a simple monthly budget. You have your income, and a list of expenses like rent, utilities, and groceries, which give you a final "Net Savings" amount. What-if analysis is simply asking questions like:

• What if my electricity bill is 20% higher this summer? You change the value in the "Utilities" cell and watch your "Net Savings" decrease.
• What if I get a 5% raise? You update your "Income" cell and see your savings potential grow.
• What if I cut my 'dining out' budget by $100? You reduce that expense and instantly see the positive effect on your net savings.

This same simple principle applies to complex business models. By changing assumptions for things like ad spend, conversion rates, shipping costs, or sales commissions, you can build a flexible model that helps you make proactive, data-informed decisions instead of reactive guesses.

Why is What-If Analysis so Valuable?

Moving beyond personal budgets, what-if analysis becomes an incredibly powerful tool for business planning, forecasting, and strategy. Here’s why so many managers and analysts rely on it:

• Better Decision-Making: It provides a clear, quantitative look at the potential consequences of a decision. You can compare the upside and downside of different strategies before committing resources. For example, you can model whether hiring a new salesperson or increasing your marketing budget is likely to have a bigger impact on revenue.
• Risk Assessment: By modeling worst-case scenarios, you can identify potential risks to your business and plan accordingly. What happens to your profitability if your main supplier increases their prices by 15%? What if a new competitor forces you to lower your prices by 10%? A what-if model gives you the answers immediately.
• Goal Setting and Planning: It helps you work backward from a goal to figure out what you need to do to achieve it. This is often called "goal seeking." If you want to achieve $1 million in revenue next year, you can model different combinations of product pricing, sales volume, and marketing investment to create a clear plan.
• Improved Forecasting Accuracy: Instead of a single, rigid forecast, you can present a range of outcomes - a pessimistic, realistic, and an optimistic scenario. This gives stakeholders a much clearer picture of the potential future and sets more realistic expectations.

Performing What-If Analysis in Google Sheets: Your Toolkit

Unlike Microsoft Excel, which has dedicated “What-If Analysis” tools like Scenario Manager and Data Tables, Google Sheets takes a more a-la-carte approach. But don't worry - you can still accomplish everything you need to with a few clever techniques and a useful add-on.

Let’s build a simple business model to use across our examples: an e-commerce store launching a new product.

Step 1: Set Up The Model Create a simple profit projection in your Google Sheet. It should have inputs (the variables you can change) and outputs (the results that are calculated with formulas).

Your inputs:

• Units to Sell
• Price Per Unit
• Cost Per Unit (COGS)
• Monthly Ad Spend

Your outputs (with formulas):

• Total Revenue: =B2*B3 (Units to Sell * Price Per Unit)
• Total Cost: =B2*B4 (Units to Sell * Cost Per Unit)
• Gross Profit: =B6-B7 (Total Revenue - Total Cost)
• Net Profit: =B8-B5 (Gross Profit - Monthly Ad Spend)

Now, let's explore how to analyze this model.

Method 1: The Manual Approach

The simplest form of what-if analysis is just manually changing the input values and seeing how the outputs change.

Ask a question, change a cell, and get your answer. For example:

Question: "What if we increased our price to $65 but only sold 900 units as a result?"

1. Change the "Price Per Unit" cell (B3) to 65.
2. Change the "Units to Sell" cell (B2) to 900.
3. Observe the new "Net Profit" in cell B9.

This method is fast, simple, and perfect for quick checks. However, it's not great for comparing multiple scenarios at once because you have to remember (or write down) the results each time you change the inputs.

Method 2: Using the Goal Seek Add-On

Often, you know the result you want, you just need to figure out how to get there. Instead of changing inputs until you hit your target output, you can use Goal Seek to do the work for you.

Goal Seek is an official Google-made add-on that you need to install first.

How to Install Goal Seek:

1. Go to Extensions > Add-ons > Get add-ons.
2. Search for "Goal Seek".
3. Click on the tool (it will be from Google) and click "Install".
4. Grant it the necessary permissions.

Now you can use it to work backward. Let's say our goal is to achieve a Net Profit of $10,000. We believe we can achieve this by increasing the number of units we sell, but we're not sure how many that'll take.

Step-by-step with Goal Seek:

1. Go to Extensions > Goal Seek > Open. A sidebar will appear on the right.
2. Set cell: Select the cell with your final output formula (in our model, this is B9, Net Profit).
3. To value: Enter the target value you want to achieve. Let's put 10000.
4. By changing cell: Select the single input cell you want to adjust to meet your goal. For this example, we’ll select B2 (Units to Sell).
5. Click Solve.

Goal Seek will now run hundreds of iterations in the background and will automatically populate cell B2 with the number of units you need to sell to hit exactly $10,000 in net profit. It's a massive timesaver for working backward to find a single missing variable.

Method 3: Build Your Own Scenario Manager

The real power of what-if analysis comes from being able to quickly switch between different, complex scenarios (e.g., "Worst Case," "Expected," and "Best Case") to compare them side-by-side. While Google Sheets doesn't have a built-in "Scenario Manager" like Excel, you can build your own dynamic version in about ten minutes using dropdown menus and a VLOOKUP formula. It’s a game-changer.

Here’s how to do it.

Step 1: Create a Scenarios Table Find some empty space on your sheet (or on a separate tab) and create a table that lists all your potential scenarios. For Price Per Unit, enter different prices for your "Best Case" and "Worst Case." For each input in our main model, create a corresponding value for each scenario.

Step 2: Create a Dropdown Menu to Select Scenarios Back in your main model, pick a cell where you want to select your scenarios from, for example, cell E1.

1. Select E1.
2. Go to Data > Data validation.
3. Under "Criteria," choose "List of items" and enter "Best Case, Expected, Worst Case."
4. Click Save.

Now you’ll see a dropdown in cell E1 with all the options from your scenarios table. You can even personalize your dropdown cells by giving them customized colors for each variable.

Step 3: Write a VLOOKUP Formula to Connect Everything The VLOOKUP formula is where the magic happens. We will replace the static input values in your main model (like "Units to Sell" and "Price Per Unit") with VLOOKUP formulas that pull in the correct values from your Scenarios Table based on the scenario selected in your dropdown menu.

For example, replace the "Units to Sell" cell value with this formula: =VLOOKUP($E$1, $H$4:$J$6, 2, FALSE)

This tells the VLOOKUP to look up the scenario name (e.g., “Best Case”) in the scenarios table and return the corresponding "Units to Sell" value. Similarly, replace the "Price Per Unit" cell value with: =VLOOKUP($E$1, $H$4:$J$6, 3, FALSE)

With these formulas, your model will automatically adjust based on the scenario you select, making it easy to compare side-by-side without manually changing inputs.