Graphed LogoGraphed
Login

How to Do Sensitivity Analysis in Google Sheets with AI

Cody Schneider•

Your business plan or marketing budget spreadsheet is built on a set of assumptions. But what happens if your cost per click is 15% higher than expected, or your sales conversion rate is 5% lower? Understanding how these changes impact your bottom line is the key to making smarter, more resilient plans. This article walks you through exactly how to perform a sensitivity analysis in Google Sheets to stress-test your assumptions - first with the classic manual approach and then with some help from AI.

What is Sensitivity Analysis, Really? (And Why Do It?)

Sensitivity analysis is just a formal name for a "what-if" analysis. It’s a technique used to determine how different values of an independent variable (an input or assumption) will impact a specific dependent variable (the outcome or result).

In simple terms, you build a model with a final output - like Net Profit or Return on Ad Spend - and then you purposefully change your key inputs to see how sensitive your output is to those changes. Do small adjustments to your ad budget cause massive swings in profit, or is it fairly stable? The answer tells you where to focus your attention and which risks matter most.

For example, a freelance marketer building a financial forecast has a few key assumptions:

  • Average number of projects per month

  • Average revenue per project

  • Monthly business expenses (software, marketing, etc.)

A sensitivity analysis would show them how their total annual profit changes if they land one fewer project per month or if their software costs increase by 20%. This insight moves them from simply having a forecast to understanding the risks and opportunities within it.

Step 1: Build Your Base Model in Google Sheets

Before you can test your model’s sensitivity, you need a model to test. Let’s build a simple e-commerce profitability model for a company that sells one product via online advertising.

First, set up your key assumptions and label them clearly. Clear labels are your best friend.

  1. Open a new Google Sheet.

  2. In column A, list your input labels: "Price Per Unit," "Cost Per Unit," "Ad Spend," "Cost Per Click (CPC)," and "Conversion Rate."

  3. In column B, enter your initial assumptions for each. These are your best guesses.

Next, build the calculations based on those inputs.

  1. Visitors from Ads: This is calculated by dividing your total Ad Spend by your Cost Per Click. The formula would be =B3/B4.

  2. Total Sales (Units): This is your number of visitors multiplied by your conversion rate. The formula is =(B3/B4)*B5.

  3. Total Revenue: The total units sold multiplied by the price per unit. The formula is =((B3/B4)*B5)*B1.

  4. Total Cost of Goods Sold (COGS): The total units sold multiplied by the cost per unit. The formula is =((B3/B4)*B5)*B2.

  5. Net Profit: This is your key output. The formula is Total Revenue - Total COGS - Ad Spend. =((B3/B4)*B5)*(B1-B2)-B3.

Organize everything so it’s easy to read. Adding clear labels for your calculations is just as important as labeling your inputs.

With our base case Net Profit calculated at $1,050, we're ready to see how fragile this number is.

Step 2: Manual Sensitivity Analysis with One Variable

The most common approach is to see how your profit changes when you modify just one assumption at a time. Let’s find out how sensitive our profit is to changes in our website’s Conversion Rate.

While Excel has a built-in "Data Table" feature for this, Google Sheets does not. But don’t worry, we can create one manually without much fuss.

  1. Set up your table structure. Somewhere else on your sheet, create a column with a range of possible values for your Conversion Rate. For example, in column E, list values from 1.0% to 5.0% in increments of 0.5%.

  2. Create the formula column. In the column next to it (column F), you'll write a new version of the profit formula that references the changing conversion rate instead of your original assumption cell.

You need to make sure the formula still pulls the other assumptions (like Price, Cost, etc.) from their original cells. Use absolute references, marked by the dollar sign ($), to ensure Sheets "always look in this specific cell, even when you drag this formula down."

  1. Write the formula. In cell F2, type the following formula:

Formula Breakdown:

  • $B$3/$B$4: Total Visitors from Ads. The dollar signs lock these references.

  • *E2: This multiplies by the conversion rate in your new data table (cell E2), not the original B5 cell. This is a relative reference, so it will change to E3, E4, etc., as you drag the formula.

  • *($B$1-$B$2): Calculates Gross Profit per unit. Locked references.

  • - $B$3: Subtracts our locked Ad Spend.

  1. Drag the formula down. Click the small blue square in the bottom-right corner of cell F2 and drag it down to the last row of your conversion rate values. Google Sheets will automatically adjust E2 in the formula for each new row, giving you the Net Profit for each hypothetical conversion rate.

Your one-variable data table is complete!

We can immediately see that our profit is extremely sensitive to conversion rate changes. A dip from our assumed 3.5% to 2.5% erases our profit entirely, pushing us into the red.

Step 3: Creating a Two-Variable Sensitivity Analysis

Things get really interesting when you test two variables at once. This shows you how assumptions interact with each other. For our business, let's analyze how Ad Spend and Cost Per Click (CPC) jointly affect Net Profit.

This creates a matrix where the rows represent one variable and the columns represent the other.

  1. Set up the matrix. List potential Ad Spend values down a column (e.g., cell E8 to E13). Then, list potential CPC values across a row (e.g., cell F7 to J7).

  2. Write the master formula. The formula for a two-variable table is a little trickier. You need to use mixed references, where you lock either the column or the row but not both.

  • For the variable in the column (Ad Spend), you'll use $E8. This locks the column (E) so it won't change as you drag the formula right, but allows the row (8) to change as you drag it down.

  • For the variable in the row (CPC), you'll use F$7. This locks the row (7) so it won't change as you drag it down, but the column (F) will change as you drag it across.

In cell F8 (the top-left cell of your matrix), type this formula:

Formula Breakdown:

  • $E8/F$7: This calculates Visitors, using our mixed references for Ad Spend (column E) and CPC (row 7).

  • *($B$1-$B$2): Multiplies by Gross Profit per unit. These two are original assumptions and fully locked ($B$1, $B$2).

  • * $B$5: Multiplies by the Conversion Rate, another locked-in original assumption.

  • - $E8: Subtracts the Ad Spend from its value in column E.

  1. Fill the entire matrix in one move. Click on cell F8, then drag the blue square handle to the right to fill the first row. While the cells are still selected, drag the handle downwards to fill the rest of the table.

You’ll now have a powerful matrix showing your potential Net Profit across a wide range of advertising scenarios.

Step 4: Using AI to Accelerate the Process

Building these tables manually is an excellent way to learn the logic. But once you have it down, you can use built-in AI tools like Gemini in Google Sheets to speed things up considerably.

AI is especially brilliant for these three tasks:

1. Automating Table and Template Creation

You can use natural language prompts to have the AI build the entire structure for you. Highlight your base model (A1:B10) and then open the Gemini side panel. Try a prompt like:

“Using the selected data as context, create a two-variable sensitivity analysis table on a new sheet. The table should calculate Net Profit. The row variable should be 'Ad Spend' from $2000 to $4000 in increments of $500. The column variable should be 'Cost Per Click (CPC)' from $0.80 to $2.00 in increments of $0.30.”

Gemini will often generate the entire matrix, saving you the time of setting up the rows, columns, and trying to get your mixed references right.

2. Helping You Write Complex Formulas

Struggling to remember where the dollar signs go in a mixed reference? Just ask the AI. Click on an empty cell where you want your formula to go and give a prompt like:

“Write a formula for this cell that calculates net profit. Use Ad Spend from cell E9, CPC from cell G8, and get all other required inputs from the model located in cells B1:B5.”

The AI will analyze the structure of your sheet and generate the precise formula you need, saving you a lot of trial and error.

3. Visualizing and Summarizing Your Data

Turning a wall of numbers into a visual story is key. Tables with many numbers can be overwhelming, but a chart or a colored "heat map" makes the insights pop instantly. It helps you see where the high-risk (low-profit) and high-reward (high-profit) zones are.

Select your completed two-variable data matrix and tell the AI:

“Apply conditional formatting to the selected range. Bad outcomes should be red, and good outcomes should be green.”

This simple command transforms a boring table into an insightful visual heat map.

This visual makes it crystal clear that if our CPC rises over $1.40, our profit depends heavily on maintaining a high ad spend to compensate. That's a powerful, actionable insight obtained by working with AI.

Final Thoughts

Running a sensitivity analysis takes your Google Sheets models from simple calculators to powerful decision-making tools. By methodically changing your key inputs, you can identify the business drivers that matter most, pinpoint significant risks, and build more robust strategies. Whether you build the tables manually to learn the logic or get help from AI to get it done faster, this process is essential for anyone who relies on a spreadsheet to make business decisions.

Building these what-if scenarios in Google Sheets is a great step, but it often involves pulling static data or making manual updates. What if you could ask these kinds of questions directly to your live data from Shopify, Google Analytics, and Facebook Ads all at once? That’s why we designed Graphed to help. Instead of manual data tables, we let you use plain English to ask things like, "Create a dashboard showing how revenue is affected by ad spend across all platforms for the last quarter." It’s an easier way to get to the same deep insights, with a lot less setup.