How to Do Sensitivity Analysis in Google Sheets with ChatGPT

Building a financial model is one thing, but understanding its breaking points is another. Sensitivity analysis lets you see exactly how changes in your key assumptions - like unit sales, ad costs, or pricing - will impact your bottom-line results like profit or revenue. This article will show you how to perform a sensitivity analysis directly in Google Sheets and how you can use ChatGPT as a copilot to make the process faster and more accessible.

What is Sensitivity Analysis Anyway?

At its core, sensitivity analysis, often called "what-if" analysis, is a financial modeling technique used to determine how different values of an independent variable will impact a specific dependent variable. In simpler terms, it's about answering questions like:

• "If our cost per unit increases by 15%, how much does our total profit decrease?"

• "How many new subscribers do we need to sign up each month to break even if we increase our marketing budget by $5,000?"

• "What happens to our return on investment if website traffic drops by 10%?"

Instead of relying on a single "best guess" for your forecast, sensitivity analysis gives you a range of possible outcomes. This helps you understand risks, identify the most critical drivers of your business, and make more informed decisions under uncertainty. You move from a single, static sales projection to a dynamic model that reveals how resilient your business is to change.

Step 1: Build Your Base Model in Google Sheets

Before you can test different scenarios, you need a basic model with your key inputs and outputs. You can build this for anything: a new product launch, a marketing campaign budget, or an entire business's profit and loss statement.

For this tutorial, let's create a simple profit/loss model for selling a single product.

Start by setting up your sheet with clear labels and initial values. In Column A, list your variables. In Column B, enter your current projections or "base case" values.

Here’s how it looks:

Input Variables (Your Assumptions):

• Cell A1: Unit Price

• Cell B1: $50

• Cell A2: Units Sold

• Cell B2: 1,200

• Cell A3: Cost Per Unit

• Cell B3: $20

• Cell A4: Fixed Costs

• Cell B4: $15,000

The Output (The Result Calculation):

Below your inputs, create your output formula. In our case, this is the Profit calculation.

• Cell A6: Profit

• Cell B6: Enter the formula:

Based on these numbers, your 'Profit' in cell B6 should calculate to $21,000. This is your baseline. Now, you’re ready to see how sensitive this profit number is to changes in your assumptions.

Step 2: Performing a One-Variable Sensitivity Analysis

A one-variable analysis shows how your profit changes when a single input, like 'Units Sold', goes up or down. Unlike Excel, Google Sheets doesn't have a built-in "What-If Analysis" Data Table feature, but we can easily build our own.

Let's see how profit changes if 'Units Sold' varies from 800 to 2,000.

Build the Data Table Structure

In a clear space on your sheet (e.g., column D and E):

• Create scenarios for 'Units Sold': In column D, starting from D2, list the different values you want to test for 'Units Sold'. You can type them manually or use a formula to create a series.

E.g., in D2 enter 800, in D3 enter 1000, in D4 enter 1200, and so on, up to 2000 in D8.

• Set up the columns headers: In D1: Units Sold | In E1: Resulting Profit

Your setup should look like this:

Writing the Formula

Now, we'll write a single formula in cell E2 and drag it down. This formula will recalculate the profit for each corresponding 'Units Sold' value from column D while keeping the other inputs ('Unit Price', 'Cost Per Unit', 'Fixed Costs') fixed.

In cell E2, enter:

Let's break that down:

• $B$1 is the locked 'Unit Price'.

• D2 is the variable 'Units Sold' from our list.

• $B$3 is the locked 'Cost Per Unit'.

• $B$4 is the locked 'Fixed Costs'.

Press Enter. Then, click on cell E2, grab the small blue square in the corner (the fill handle), and drag it down to cell E8. Google Sheets will automatically calculate the profit for each scenario!

Step 3: Leveraging ChatGPT as Your Spreadsheet Assistant

While building the table manually is great for understanding the process, ChatGPT can dramatically speed things up, reduce errors, and even help you think through your model.

A. Getting the Right Formula

Instead of figuring out the formula yourself, you can ask ChatGPT. Be specific about your cell references.

Example Prompt:

"I'm building a one-variable data table in Google Sheets. My scenarios for 'Units Sold' are in column D, starting at D2. My model's live inputs are in Sheet1: 'Unit Price' is in B1, 'Cost Per Unit' is in B3, and 'Fixed Costs' are in B4. Give me a formula for cell E2 that calculates the profit for each 'Units Sold' scenario, so I can drag it down."

ChatGPT will provide the exact formula we created above, including an explanation of why the dollar signs are important.

B. Generating Scenarios

Don't want to manually type all your test values? Ask ChatGPT to create them for you.

Example Prompt:

"Generate a list of 12 scenario values for a sensitivity analysis. Start with 500 units and increase by 150 units for each step."

You can then just copy and paste the results directly into your Google Sheet.

C. Debugging and Troubleshooting

Caught on a #VALUE! error or an incorrect result? Paste your formula and describe your setup to ChatGPT.

Example Prompt:

"My formula =(B1 * D2) - (B3 * D2) - B4 is not changing when I drag it down. B1 is price, B3 is cost, and B4 is fixed cost. D2 is my unit scenario. What am I doing wrong?"

ChatGPT will immediately spot that you forgot to add the $ symbols to lock the references for B1, B3, and B4.

Step 4: Two-Variable Sensitivity Analysis

This is where things get really insightful. A two-variable analysis lets you see how your profit changes when two inputs vary simultaneously. For example, how does profit change when both 'Units Sold' and 'Unit Price' go up or down?

Building the 2D Matrix

The setup is a matrix where one variable runs across the columns and the other runs down the rows.

• Setup Rows (Variable 1): In a column, list your scenarios for 'Units Sold' (e.g., in cells G13:G19, list values from 800 to 2000).

• Setup Columns (Variable 2): Across a row, list your scenarios for 'Unit Price' (e.g., in cells H12:L12, list values like $40, $45, $50, $55, $60).

• In the corner where the row and column headers meet (G12), you can leave it blank or reference your 'Profit' formula by typing =B6. This isn't required but is good practice.

Your layout should look like this (with G12 being the top-left corner):

The Formula with Mixed References

Here, we use mixed cell references (e.g., $G$13 and H$12).

In cell H13 (top-left of the data grid), type the formula:

Let's unpack it:

• H$12: This is the 'Unit Price' scenario. The $ on the row locks it so it stays fixed when dragging down. When dragging across columns, it updates to I, J, K, etc.

• $G13: This is the 'Units Sold' scenario. The $ on the column locks it so it stays G when dragging across. When dragging down, it updates to G14, G15, etc.

• $B$3 and $B$4: These are our other inputs, fully locked.

Now, click on H13, and drag the fill handle right to L13. Then, with that whole row selected, drag the fill handle down to row 19. The entire table will populate with the correct profit for every combo of price and units sold.

Step 5: Visualize the Results with Charts and Heatmaps

Numbers in a table are useful, but visuals make the insights jump off the page.

For a One-Variable Table: Line Chart

1. Select your one-variable data table (both columns, including headers). In our example, D1:E8.

2. Go to Insert > Chart.

3. Google Sheets will likely default to a line chart, which is perfect for this. It clearly shows how profit trends up or down as your input variable changes.

For a Two-Variable Table: Heatmap

A heatmap is the best way to visualize a 2D matrix. It uses color scales to show highs and lows at a glance.

1. Select all the calculated profit data in your two-variable table (e.g., H13:L19).

2. Go to Format > Conditional formatting.

3. In the sidebar that appears, click the "Color scale" tab.

4. Choose a color combination. A "Green to Yellow to Red" scale is classic - it instantly shows you which combinations lead to high profit (green), mediocre profit (yellow), and losses (red).

5. Click "Done." Your table is now a powerful visual tool for decision-making.

Here again, you could ask ChatGPT: "How do I create a heatmap using conditional formatting in Google Sheets for the data range H13:L19? I want positive numbers to be green and negative numbers to be red." It will give you step-by-step instructions.

Final Thoughts

By building sensitivity analysis tables in Google Sheets, you elevate your planning from simple forecasting to strategic risk management. You gain a deeper feel for which business levers matter most and where your plan is most vulnerable. Teaming up with an AI assistant like ChatGPT can further streamline the process by generating formulas, creating scenarios, and helping you debug your model along the way.

Of course, building manual models in sheets is just one way to stay on top of your data. As your business grows, manually downloading CSVs and updating tables becomes a huge time sink. At that stage, we've found that having a single, unified view of your most important metrics from all your tools is game-changing. With Graphed, you can securely connect all your marketing and sales data sources - like Google Analytics, Shopify, and Facebook Ads - and use simple natural language to generate real-time reports and dashboards. Instead of spending hours in spreadsheets, you can get insights in seconds, allowing you to focus on strategy, not data wrangling.