How to Create a Sensitivity Report in Excel

Building a financial model or business plan involves making a lot of assumptions. But what a spreadsheet doesn’t tell you is which of those assumptions are most likely to make or break your success. This article will show you exactly how to perform a sensitivity analysis in Excel to test those assumptions, understand potential risks, and build more resilient business plans.

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 a Sensitivity Analysis?

A sensitivity analysis, also known as a "what-if analysis," is a technique used to see how changes in one variable affect a specific outcome. Think of it as a stress test for your spreadsheet model. Instead of relying on a single, static guess, you can test a range of possibilities to understand which inputs have the biggest impact on your final numbers.

For example, a marketing team planning a new ad campaign might build a model to project its return on ad spend (ROAS). Their key assumptions would be things like:

• Cost per Click (CPC)
• Website Conversion Rate
• Average Order Value (AOV)

A sensitivity analysis would answer questions like, "What happens to our ROAS if CPC is 15% higher than expected?" or "How much can our conversion rate drop before this campaign becomes unprofitable?" This process helps you identify the most critical levers in your plan, allowing you to focus your attention where it matters most and prepare for different outcomes.

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

First, Set Up Your Core Model

Before you can perform a sensitivity analysis, you need a basic model to work with. The key is to separate your inputs (your assumptions or variables) from your calculations and outputs (the final results). This structure makes it easy for Excel’s tools to test different input values.

Let's use a simple event planning model as an example. We want to calculate the potential profit from a workshop.

Here are the components of our model:

1. Inputs/Assumptions: These are the numbers we want to test. Let's put them at the top of our sheet.
2. Calculations & Output: These cells contain formulas that reference our input cells.

Your simple Excel model should look something like this. Notice how the "Projected Profit" is the final output we want to analyze.

With this simple model in place, we're ready to use Excel's analysis tools.

Method 1: Creating One- and Two-Variable Data Tables

Data Tables are the perfect tool for seeing how changes in one or two inputs impact your final output. It automatically calculates the outcome for every potential input value you provide, saving you from manually plugging in each number yourself.

How to Create a One-Variable Data Table

Let's see how our event's profit changes as the number of attendees fluctuates. Our model currently assumes 50 attendees, but we want to see the results from 30 up to 70.

Step 1: Set Up Your Table Structure

First, create a column with the different input values you want to test. In this case, it’s the number of attendees. In a column next to it, and one row above your first value, link to your final output cell – Projected Profit ($B$11).

Step 2: Open the Data Table Tool

Highlight the entire table range you just created (in our example, D4:E13). Navigate to the Data tab on the Ribbon, click on What-If Analysis, and select Data Table...

Step 3: Define Your Input Cell

A small pop-up window will appear. Because our variable (the number of attendees) is in a column, we need to tell Excel where the original input for that variable is located in our model. Click into the "Column input cell:" field and select the original "Number of Attendees" cell ($B$4).

Click OK. Excel will now automatically populate the table, showing you the projected profit for each different number of attendees.

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

How to Create a Two-Variable Data Table

This works just like the one-variable table, but lets you test two inputs at the same time. Let's see how our profit is affected by both the number of attendees and the ticket price.

Step 1: Set Up Your Table Structure

Create a table where one variable is listed down the first column (Attendees) and the second variable is listed across the top row (Ticket Price). In the top-left corner cell where the column and row meet, link your main output cell - Projected Profit ($B$11).

Step 2: Open the Data Table Tool and Define Inputs

Highlight the entire table (D4:H13). Go back to Data > What-If Analysis > Data Table.... This time, we need to fill in both fields:

• Row input cell: Select the original Ticket Price cell ($B$5).
• Column input cell: Select the original Number of Attendees cell ($B$4).

Click OK. Your table will instantly populate, showing the profit generated at every combination of attendees and ticket price. Now you have a complete matrix of potential outcomes!

Method 2: Using Scenario Manager for Multiple Inputs

The Scenario Manager is useful when you want to compare a few distinct sets of assumptions - like a "Worst Case," "Best Case," and "Most Likely" scenario. It’s perfect when you have three or more variables that change together.

Step 1: Open Scenario Manager

Navigate to Data > What-If Analysis > Scenario Manager.

Step 2: Add Your First Scenario

In the Scenario Manager window, click Add...

1. Give your scenario a name, like "Worst Case."
2. In the Changing cells: field, select all the input cells from your model that will change. For our example, let's test Attendees, Ticket Price, and Marketing Cost. (Hold CTRL to select multiple cells: $B$4, $B$5, $B$8).
3. Click OK.

A new window will appear, asking you to enter the values for this specific scenario. Let's input some pessimistic numbers (e.g., fewer attendees, lower price, higher marketing spend) and click OK or Add.

Step 3: Add More Scenarios

Repeat the process to add your other scenarios. For example, add a "Best Case" and a "Most Likely Case," each with its own set of input values.

Step 4: Generate a Scenario Summary Report

Once all your scenarios are set up, click the Summary... button in the main Scenario Manager window. In the dialog box, make sure "Scenario summary" is selected and that the Result cells: field points to your final output - in our case, the Projected Profit cell ($B$11). Click OK.

Excel will instantly generate a new sheet with a neatly organized summary report comparing the inputs and final profit for each defined scenario. This report gives you a clear snapshot of the range of possible outcomes.

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

Bonus Tip: Visualize Your Analysis

A table of numbers is useful, but a chart is often easier to interpret. You can quickly add visualizations to make your sensitivity analysis more impactful.

• Line charts: For a one-variable data table, highlight your results and create a line chart (Insert > Chart). This provides a simple, immediate visual showing how sensitive your output is to changes in that single input.
• Conditional Formatting: For a two-variable data table, you can create a heat map. Highlight the results inside your data table, then go to Home > Conditional Formatting > Color Scales. This instantly uses color to show you the best- and worst-performing combinations, making your insights pop off the page.

Final Thoughts

Mastering tools like Data Tables and Scenario Manager in Excel helps move your analysis from simple calculation to strategic foresight. By testing your key assumptions, you get a clearer picture of potential risks and opportunities, enabling you to build stronger, more reliable business strategies grounded in data.

We know that getting your model set up in the first place is often the biggest hurdle, especially when your data lives in different places like Shopify, Google Ads, or Salesforce. At Graphed, we automate the process of connecting these sources so you can get straight to the insights. You can use simple, natural language to build dashboards and ask questions, saving the time you'd otherwise spend formatting CSVs and getting you back to the "what-if" analysis that drives your business forward.