Graphed LogoGraphed
Login

How to Do Sensitivity Analysis in Excel with AI

Cody Schneider•

Thinking about how a small change in your ad spend could impact your overall revenue? Or how a sudden shift in material costs might affect your profit margins? That's the core of sensitivity analysis, a powerful way to understand how variables in your business model affect your outcomes. We’ll walk through how to perform this "what-if" analysis using Excel’s built-in tools and explore how new AI-powered approaches are making this process faster and more intuitive.

What is Sensitivity Analysis, Anyway?

Sensitivity analysis, often called "what-if" analysis, is a technique used to determine how different values of an independent variable will impact a particular dependent variable under a given set of assumptions. In simpler terms, it's about asking "what happens to my profit if...?"

For any business, this is a fundamental practice for:

  • Better Decision-Making: It helps you weigh the potential outcomes of different choices, like launching a new product or entering a new market.

  • Risk Assessment: You can identify which variables have the biggest impact on your bottom line and create contingency plans accordingly. If a 5% increase in your cost of goods sold craters your profit, that’s a significant risk you need to monitor.

  • Forecasting and Budgeting: By testing various scenarios (optimistic, pessimistic, and realistic), you can create more robust and flexible financial forecasts.

Instead of guessing, sensitivity analysis gives you a quantitative look at potential futures, allowing you to plan with much greater confidence.

Getting Started: Building Your Excel Model

Before you can analyze anything, you need a well-structured model in Excel. A good model has three clear components:

  1. Inputs (Variables): These are the numbers you'll be changing. They should be in their own cells, easy to find and modify. Never hard-code variables inside formulas.

  2. Outputs (Results): This is the outcome you want to measure, like Net Profit or Return on Investment (ROI). This cell should contain a formula.

  3. Formulas: This is the logic that connects your inputs to your outputs.

Let's use a simple example of a single product business. Here's our model:

Cell B1: Units Sold Value: 5,000Cell B2: Price per Unit Value: $40.00Cell B3: Cost per Unit Value: $15.00Cell B4: Marketing Costs (Fixed) Value: $25,000

Cell B6: Revenue Formula =B1B2Cell B7: Total Cost Formula =(B1B3)+B4Cell B8: Profit Formula =B6-B7

In this model, the Profit in cell B8 is our key output. The cells B1, B2, B3, and B4 are our inputs. We can now use Excel's "What-If Analysis" tools to see how changes to these inputs affect our Profit.

Method 1: Using a One-Variable Data Table

A one-variable data table shows you how changing one input in your model affects one or more output formulas. It's perfect for when you want to see the effect of a range of values for a single variable, like different price points.

Let's see how our profit changes if we vary the Price per Unit from $30 to $50.

Step-by-Step Guide:

  1. Set up your table structure. In a blank area of your sheet, list the different values for the variable you want to test down a column. For our example, in cells D2 through D6, we’ll type: $30, $35, $40, $45, $50.

  2. Link to your output formula. In the cell to the right and one row above your list of values (cell E1), you need to reference your key output. Type =B8 into cell E1 to link to our Profit formula. Your setup should look like this:

  • D2: $30

  • D3: $35

  • D4: $40

  • D5: $45

  • D6: $50

  • E1: =B8

  1. Open the Data Table tool. Select the entire range D1:E6.

  2. Go to the Data tab, click on What-If Analysis, then select Data Table...

  3. Specify the input cell. Since our list of new prices is organized in a column, use the "Column input cell" field. Select cell B2 (Price per Unit).

  4. Click OK. Excel will instantly populate the rest of the table, showing you the profit you'd make at each different price point.

Method 2: Using a Two-Variable Data Table

What if you want to see the impact of changing two variables at the same time? A two-variable data table lets you see how combinations of two inputs affect a single output. For example, how does Profit change as both Price per Unit and Units Sold fluctuate?

Step-by-Step Guide:

  1. Set up the table structure. One set of inputs goes down a column, and the other goes across a row. Let's test "Units Sold" from 3,000 to 7,000 (cells D3:D7) and "Price per Unit" from $30 to $50 (cells E2:G2).

  2. Link to your output formula. In the corner cell where your row and column of variables meet (cell D2), link to your output formula by typing =B8.

  3. Open the Data Table tool. Select the entire matrix, from your linked output formula to the end of your rows and columns (D2:G7).

  4. Go to Data > What-If Analysis > Data Table...

  5. Specify both input cells.

    • Row input cell: reference the original input for the data in your rows (B2 - Price per Unit).

    • Column input cell: reference the original input for the data in your columns (B1 - Units Sold).

  6. Click OK, and Excel will fill in the profit for every combination of units sold and price.

Method 3: Using Scenario Manager

Data tables are great for one or two variables, but what if you want to test a whole set of changes at once? Scenario Manager lets you store and compare entire groups of input values. It’s perfect for creating "Best Case," "Worst Case," and "Most Likely" outcomes.

Let's create two scenarios: a "Worst Case" where units sold fall and costs rise, and a "Best Case" where the opposite happens.

Step-by-Step Guide:

  1. Go to Data > What-If Analysis > Scenario Manager.

  2. Click Add... to create your first scenario.

  3. Give it a name, like "Worst Case." For "Changing cells," select B1, B3, B4 (use Ctrl+Click to select non-adjacent cells). Click OK.

  4. Enter the values for your "Worst Case" situation:

    • B1 (Units Sold): 3500

    • B3 (Cost per Unit): $18

    • B4 (Marketing Costs): $30000

  5. Click Add... again to create the next scenario. Name it "Best Case," keep the same changing cells, and click OK.

  6. Enter the "Best Case" values:

    • B1: 7000

    • B3: $12

    • B4: $22000

  7. Click OK. You'll see both scenarios listed. To see the impact on your model, double-click one of the scenarios, and Excel updates your input cells.

  8. For a clean overview, click Summary.... Ensure your result cell B8 is selected, and click OK. Excel generates a new sheet with a summary table comparing inputs and profit for each scenario.

A New Approach: Using AI for Sensitivity Analysis

Excel's tools are undeniably powerful, but they require manual setup. Building data tables and defining scenarios take time, and exploring new "what-if" questions on the fly means starting the process over.

Modern AI-native analysis tools change this entirely. Instead of becoming a button-clicking data architect, you become a conversational strategist. The focus shifts from building the model to simply asking it questions.

The process looks much simpler:

  1. Connect Your Data Directly: Instead of building a static model from a CSV export, these tools connect live to your sources, such as Google Sheets, Shopify, or HubSpot.

  2. Ask "What-If" in Plain English: You can now use natural language to run your analysis. Manual data table setup is replaced by a simple prompt.

  3. Iterate and Explore Instantly: The real power is in the follow-up. After your initial analysis, you can drill down, segment, and pivot by asking additional questions.

Let's look at some examples of how this plays out.

  • Instead of building a one-variable data table for ad spend, you'd ask:"Show me the projected ROI if our Facebook ad spend increases from $5,000 to $10,000 in $1,000 increments."The AI would generate the table or chart instantly.

  • Instead of using Scenario Manager for marketing forecasts, you'd describe scenarios:"Compare two scenarios for Q4 marketing performance: a 'Conservative' case where clicks decrease by 15% and CPC goes up by 10%, vs an 'Aggressive' case where we double our budget and see a 5% drop in CPC."The platform would create the comparison for you.

  • For real-time exploration:"Okay, based on that last chart, what sales volume do we need to hit to break even if our material costs rise by another 8%?"No need to rebuild the model—just keep the conversation going.

Final Thoughts

Sensitivity analysis is a highly valuable activity that turns guesswork into strategic planning. Excel offers a robust, if manual, set of tools with data tables and Scenario Manager to test various outcomes and assess your plans' resilience.

When analysis becomes conversational, the process is remarkably faster. Instead of meticulously setting up data tables and models each time you have a new question, you can connect directly to your data sources and simply ask. With Graphed, you can perform complex sensitivity analyses related to your ad performance, sales pipeline, or website traffic just by describing what you want to see. This approach takes you from question to real-time insight in seconds.