How to Make a Sensitivity Analysis Graph in Excel

Building a forecast or financial model is one thing, but understanding its breaking points is another. Sensitivity analysis helps you see how your final results change when key assumptions fluctuate, and a graph makes those insights immediately clear. We'll walk you through exactly how to set up your model, generate the data, and create a sensitivity analysis graph in Excel.

What Exactly Is Sensitivity Analysis?

At its core, sensitivity 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 a "what-if" analysis. It helps you answer critical business questions like:

• "If our cost per click (CPC) increases by 15%, how much will our profit per acquisition fall?"

• "How many new customers do we need to sign up each month to break even if our monthly software fees go up?"

• "What happens to our project's net present value (NPV) if our projected revenue for year three is 10% lower than expected?"

By testing the sensitivity of your key inputs, you're not just guessing, you're quantifying uncertainty. This allows you to identify the most critical variables in your model – the ones that have the biggest impact on your bottom line. Visualizing this analysis with a graph makes the relationships between your variables undeniably clear, helping stakeholders understand risks and opportunities at a glance.

Step 1: Set Up Your Basic Financial Model in Excel

Before you can analyze anything, you need a model to work with. A sensitivity analysis is only as good as the underlying model it's testing. For this tutorial, we’ll build a simple profitability model for selling a single product.

Open a new Excel sheet and set up your model like this. Feel free to use your own business's numbers.

Inputs (Your Assumptions)

These are the variables you control or forecast. List the labels in one column and their corresponding values in the next. These are your "base case" assumptions.

• In cell A2: Unit Price

• In cell B2: 50

• In cell A3: Variable Cost Per Unit

• In cell B3: 20

• In cell A4: Units Sold

• In cell B4: 1000

• In cell A5: Fixed Costs

• In cell B5: 15000

Outputs (Your Calculations)

These cells contain the formulas that make your model work. This is where you calculate revenue, total costs, and your final profit based on the inputs above.

• In cell A7: Total Revenue

• In cell B7, enter the formula: =B2*B4

• In cell A8: Total Variable Costs

• In cell B8, enter the formula: =B3*B4

• In cell A9: Total Costs

• In cell B9, enter the formula: =B8+B5

• In cell A11: Profit

• In cell B11, enter the formula: =B7-B9

Our "base case" profit in cell B11 should be $15,000. Now that the model is built, we can start testing our assumptions.

Step 2: Create a Two-Variable Data Table

The magic behind this analysis in Excel is the Data Table feature, which is part of the "What-If Analysis" toolkit. It automatically calculates the results of a formula based on a range of different inputs. We'll create a two-variable table to see how Profit (our output) changes based on both Units Sold and Unit Price.

1. Set Up Your Table Structure: First, you need to lay out the framework for your data table in a new area of your spreadsheet.

   • The Top-Left Corner: This is the most crucial part. In a cell like D13, you must create a link to your final output formula. Type =B11 in cell D13. This tells Excel that "Profit" is the value we want to calculate inside the table.

   • Column Inputs (Variable 1): Down the first column of your table (starting in cell D14), list the different values for one of your variables. Let's use Units Sold. Enter values like 600, 800, 1000, 1200, and 1400 in cells D14 through D18.

   • Row Inputs (Variable 2): Across the first row of your table (starting in E13), list the different values for your other variable. Let's use Unit Price. Enter values like $40, $45, $50, $55, and $60 in cells E13 through I13.

Your table frame should now be set up with your output formula reference in the corner, "Units Sold" values running down the side, and "Unit Price" values running across the top.

1. Generate the Data: Now, we'll tell Excel to fill in the table.

   • Select your entire table range, including the formula reference and the input headers. In our example, this would be the range D13:I18.

   • Go to the Data tab on the Ribbon.

   • In the Forecast group, click What-If Analysis, then select Data Table... from the dropdown.

   • A small dialog box will pop up with two fields:

     • Row input cell: This is where Excel should substitute the values from your top row. Our top row lists different Unit Prices, so click in this box and then select the original Unit Price input cell from your model, which is $B$2.

     • Column input cell: This is where Excel should substitute the values from your side column. Our side column lists different numbers for Units Sold, so click in this box and then select the original Units Sold input cell, which is $B$4.

   • Click OK. Excel will instantly populate the table, showing you your calculated profit for every single combination of Unit Price and Units Sold.

You’ve just automated hundreds of calculations. Now, let’s make that data easy to interpret with a graph.

Step 3: Build the Sensitivity Analysis Graph

A table full of numbers can be hard to read. A graph immediately shows the relationship between your variables. A line chart is one of the clearest ways to visualize this kind of two-variable sensitivity analysis.

Creating the Line Chart

1. Select Your Data: Highlight only the calculated results inside your data table, along with the row and column headers that contain your input values. In our example, select the range D13:I18. It's often easier to first highlight just the data (E14:I18), then add handles later, but selecting it all can work too.

2. Insert the Chart: Go to the Insert tab. In the Charts section, select the icon for "Insert Line or Area Chart" and choose the Line with Markers chart type.

3. Review the Initial Graph: Excel will generate a chart. Each line on the chart will represent one of the Unit Price scenarios. The horizontal (X) axis will represent the Units Sold. The vertical (Y) axis will show the resulting Profit.

Formatting Your Graph for Maximum Clarity

An unformatted chart tells a partial story. A little effort here makes your analysis professional and compelling.

• Add a Title and Axis Labels: This is non-negotiable. Click on the chart, then click the green "+" button that appears to the top right. Check the boxes for Chart Title, Axis Titles.

  • Change the Chart Title to something descriptive like "Profit Sensitivity Analysis".

  • Label the horizontal (X) axis "Units Sold".

  • Label the vertical (Y) axis "Profit ($)".

• Clean Up the Legend: The legend should clearly explain what each line represents. Excel usually does a decent job, but if it's not clear, you can fix it.

  • Right-click the chart and choose Select Data.

  • Under Legend Entries (Series), click on a series (e.g., "Series1") and click Edit.

  • In the "Series name" field, you can type a descriptive name like "Price: $40" or link directly to the cell in your data table (cell E13 in our example).

  • Repeat this for each series to make your legend clear and professional.

• Adjust Styling: Change line colors to make them distinct, adjust the scale of the Y-axis if needed (e.g., to clearly show the break-even point at $0), and resize the chart for better visibility.

Your finished graph now tells a clear story. You can immediately see how steeper the profit growth is at higher price points and how many more units you have to sell to break even at lower prices.

Alternative View: The Tornado Chart

What if you want to compare the impact of more than two variables? The Tornado Chart is perfect for this. It ranks different variables by how much impact a change (+/- 10%, for example) has on your final output. It's named for its shape - the longest bars at the top get progressively shorter, resembling a tornado.

While more involved to build manually than a line chart, the high-level process is:

• Determine a base case value for your output (our $15,000 profit).

• Create a table where you test the impact of a set percentage change (e.g., +/- 20%) for each key input variable one at a time, while keeping others at their base values.

• Calculate the difference in profit for each of these upside and downside scenarios compared to your base case.

• Use a formatted stacked bar chart to visualize these differences, ordering them with the most impactful variable at the top.

This graph instantly tells you which levers you should pay the most attention to. Is your profit more sensitive to a change in price, variable costs, or units sold? A tornado chart will answer that question immediately.

Final Thoughts

Mastering sensitivity analysis in Excel transforms your spreadsheets from static models into dynamic decision-making tools. By using the Data Table feature and creating clear visualizations like line charts or tornado charts, you can effectively test your assumptions, identify your biggest risks, and communicate complex scenarios with absolute clarity.

Building these models and creating data tables can still be time-consuming, especially when your data sits in different platforms. We created Graphed to simplify this entire analytics process. Instead of downloading CSVs and building models manually, you can connect live data sources and just ask for the analysis you need - like, "Compare Facebook Ad spend vs. revenue by campaign," or "Show me what happens to sales if website traffic drops 20%." We generate interactive, real-time dashboards for you, so you can focus entirely on the crucial insights, not the spreadsheet mechanics.