How to Do Sensitivity Analysis in Excel with ChatGPT

Performing a sensitivity analysis is a fantastic way to understand how different variables impact your business outcomes, but doing it in Excel can feel like a clunky, manual chore, especially when you want to test more than two variables at once. By pairing the number-crunching power of Excel with the coding assistance of ChatGPT, you can automate this entire process. This guide will walk you through exactly how to do it, step by step.

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 Sensitivity Analysis? (The Simple Explanation)

At its core, sensitivity analysis, sometimes called "what-if" analysis, helps you answer the question: "If this number changes, how much does that change my end result?" It’s a method for figuring out which variables have the biggest impact on your final numbers.

Imagine you're launching a new online course. Your final profit depends on a few key factors:

• The price of the course.
• The amount you spend on ads (Cost Per Acquisition).
• The percentage of website visitors who buy (Conversion Rate).
• Your refund rate.

A sensitivity analysis would show you, for instance, that a 10% decrease in your conversion rate impacts your profit much more drastically than a 10% increase in ad spend. This kind of insight is invaluable because it tells you exactly where to focus your energy. Do you need to A/B test your landing page (to improve conversion rate) or find cheaper ad channels? This analysis points you in the right direction.

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 Old Way: Using Excel's Built-in Tools

Excel has a few native tools for sensitivity analysis, but they each have limitations that can slow you down. Understanding them helps appreciate why using an AI assistant is such a game-changer.

One-Variable Data Table: This is the simplest form. You can see how changing one input (like your course price) affects multiple outputs (like total revenue and profit) simultaneously. It's fast and easy for a single variable.

Two-Variable Data Table: This lets you test two inputs at a time, for instance, seeing how different combinations of price and ad spend affect your final profit. While more powerful, the output is a grid that only shows one final outcome, unlike the one-variable table.

Scenario Manager: This tool allows you to create and save multiple different scenarios with multiple input variables. You could create a "Best Case" scenario (high price, high conversion rate, low ad cost), a "Worst Case," and a "Most Likely." The issue is that it's less systematic than a data table, you're just comparing a few predefined scenarios rather than a range of possibilities.

The biggest problem? If you want to analyze the combined impact of three or more variables systematically (e.g., price, ad spend, and refund rate), you're out of luck with Excel's built-in tools. That’s when the process usually collapses into a mess of manually copying and pasting values, which is exactly the kind of task we can offload to an AI.

Step 1: Set Up Your Financial Model in Excel

Before you can bring ChatGPT into the picture, you need a clean and well-structured model in Excel. Your AI assistant isn’t a mind-reader, it needs a logical foundation to work from. Let's build a simple profit model for an e-commerce business selling a single product.

Organize your spreadsheet into three clear sections:

1. Inputs (or Assumptions): These are the variables you plan to change. Put each one in its own labeled cell. Don't embed them directly inside formulas! This is critical for the analysis to work.
2. Calculations: This is the middle part where you use your inputs to calculate intermediate metrics.
3. Outputs: This is the final result you want to measure. Like your inputs, this should be a single, clearly labeled cell.

Here’s what our e-commerce model might look like:

Example: E-commerce Profit Model

Let's say your spreadsheet looks like this:

• Cell B2: Product Price = 50
• Cell B3: Ad Spend = 2000
• Cell B4: Cost Per Click (CPC) = 1.50
• Cell B5: Conversion Rate = 3%
• Cell B6: Cost of Goods Sold (COGS) per unit = 15

These are our Inputs. Now, for the Calculations and Outputs:

• Cell B8: Website Clicks = =B3/B4 — This divides Ad Spend by CPC.
• Cell B9: Units Sold = =B8*B5 — This multiplies clicks by the conversion rate.
• Cell B10: Total Revenue = =B9*B2 — Units sold times product price.
• Cell B11: Total COGS = =B9*B6 — Units sold times COGS per unit.
• Cell B13: Gross Profit (Our Key Output) = =B10-B11-B3 — Revenue minus COGS and ad spend.

With this simple model, we now have a clear structure — inputs in cells B2:B6 and our single key output in B13. This setup is perfect for automation.

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: Crafting the Perfect ChatGPT Prompt

Your goal is to have ChatGPT write an Excel VBA (Visual Basic for Applications) macro for you. VBA is a programming language that expands Excel's capabilities, perfect for automating repetitive tasks. A well-written prompt is the difference between getting a perfect, working script and a frustrating, buggy mess.

Your prompt should include four key elements:

1. The Context: " I am building a financial model in Excel and I want to run a sensitivity analysis."
2. Your Model's Structure: " My inputs are Ad Spend in cell B3, Cost Per Click (CPC) in cell B4, and Conversion Rate in cell B5. My final profit is in cell B13."
3. The Goal of the Analysis: "I want to see how Gross Profit (B13) changes when I vary Ad Spend, CPC, and Conversion Rate by -20%, -10%, +10%, and +20% from their baseline values."
4. The Desired Format of the Output: "Please write the Excel VBA code for a macro that will generate a summary table of the results on a new worksheet named 'Sensitivity Analysis'."

An Example of a Great Prompt

Here’s a full prompt you could use for our e-commerce model:

"Hello, ChatGPT. I need your help writing an Excel VBA macro for a sensitivity analysis. I have a financial model on a worksheet named 'Model'. Here's the structure:

• Input 1: 'Ad Spend' is in cell B3.
• Input 2: 'Cost Per Click (CPC)' is in cell B4.
• Input 3: 'Conversion Rate' is in cell B5.
• My final output, 'Gross Profit', is in cell B13.

I want to run a sensitivity analysis where I test the impact of changing each of these three inputs independently. For each input, I want to see the effect on the Gross Profit when the input's value is changed to be 20% lower, 10% lower, 10% higher, and 20% higher than its current value in the model.

*Can you please write a VBA script that does the following:

1. Creates a new worksheet called 'Sensitivity Analysis'.
2. In this new sheet, create a summary table. The table should have columns for: 'Variable Name', 'Percentage Change', 'New Value', and 'Resulting Gross Profit'.
3. The script should loop through each of my three inputs (Ad Spend, CPC, Conversion Rate), calculate the scenarios, record the resulting profit for each, and then reset the variable back to its original value before moving to the next."*

This will help me see which variable has the most impact on my profit."

Step 3: Implementing and Running the VBA Script

ChatGPT will process your request and generate a block of VBA code. Don’t be intimidated if you’ve never seen it before, you don't need to understand it, just paste it in the right place.

How to Add the Macro to Your Excel File:

1. Open your Excel workbook.
2. Press Alt + F11 (or Option + F11 on Mac) to open the Visual Basic for Applications (VBA) editor.
3. In the editor menu, go to Insert > Module. A blank white window will appear.
4. Copy the entire code snippet provided by ChatGPT and paste it into this blank module window.
5. Close the VBA editor by clicking the 'X' in the top right corner. The code is now saved inside your workbook.

How to Run Your New Macro:

1. Return to your main Excel window.
2. Click the Developer tab in the ribbon. (If you don't see it, go to File > Options > Customize Ribbon and check the box for "Developer".)
3. Click on the Macros button. A new window will appear listing all available macros.
4. You should see your new macro listed (it will likely be named something like RunSensitivityAnalysis). Select it and click Run.

In just a few seconds, the script will execute. A new worksheet named "Sensitivity Analysis" will appear, containing a perfectly formatted table displaying exactly how your profit changes with each variation of your inputs. You just accomplished what would have taken 20 minutes of tedious manual work in less than a minute.

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

Step 4: Analyze Your Results and Go Deeper

Now you have your results table. Look for the variable that causes the biggest swing in your Gross Profit. You'll quickly see if a +-20% change in Conversion Rate makes your profit jump and dive more than a +-20% change in Ad Spend. This tells you which business lever is the most powerful.

The real beauty of using ChatGPT as a co-pilot, however, is that you can keep the conversation going.

• Ask for a Visualization: Say, "This is great, thank you. Now, can you write another macro that takes the data from the 'Sensitivity Analysis' sheet and creates a 'Tornado Chart' to visualize the sensitivity of each variable?" ChatGPT can generate the code to automatically build these powerful data visualizations.
• Add More Complexity: What if you want to test two variables simultaneously? You can refine your prompt: "Thanks. Now, could you modify the code to create a scenario where you test the combined impact of increasing Ad Spend by 10% and decreasing CPC by 10%?"
• Troubleshoot: If you get an error when running the macro, you don’t have to solve it alone. Copy the error message and paste it back into your chat: "When I ran the code, I got this error: '[paste error here]'. What does it mean, and how can I fix it?"

This iterative process turns a static reporting task into a dynamic conversation with your data, letting you explore new angles and answer follow-up questions in real-time, all without getting bogged down by the limitations of Excel's menus and toolbars.

Final Thoughts

Running a robust, multi-variable sensitivity analysis in Excel moves from being a frustrating manual task to a quick, automated process when you team up with ChatGPT. By setting up a logical model and writing a clear prompt, you can generate powerful VBA scripts that handle the repetitive work for you, freeing you to focus on what the numbers actually mean for your business.

While this approach significantly streamlines analysis within a spreadsheet, we know the real bottleneck for many marketers and founders is simply getting clean data aggregated in one place to begin with. We built Graphed to solve this frustration. Instead of manually pulling reports from Google Analytics, Shopify, Facebook Ads, and your CRM just to dump them into Excel, you can connect your sources to Graphed in a few clicks. From there, you just ask questions in plain English to build real-time dashboards and reports across all your platforms in seconds - no scripts, prompts, or spreadsheets required.