A sensitivity table is a grid that shows how changes in one or two input variables affect an outcome you care about. If you’re building a financial model and want to know how your projected profit shifts when sales volume drops by 10% or material costs rise by 15%, a sensitivity table lays out every combination in a single, scannable view. It’s one of the most widely used tools in financial modeling, project evaluation, and business planning.
How a Sensitivity Table Works
The core idea is simple: you start with a base case, which is your best estimate of how things will play out. Then you ask “what if?” by substituting different values for one or two key inputs and recording what happens to your result. The table organizes all of those what-if scenarios into rows and columns so you can spot patterns at a glance.
Say you’re modeling the profitability of a new product. Your base case assumes you’ll sell 10,000 units at $50 each with a production cost of $30 per unit. A sensitivity table might vary the selling price across the top (from $40 to $60) and production cost down the side (from $25 to $35), with each cell showing the resulting profit. Instead of running dozens of separate calculations, you get the full picture in one place.
One-Variable vs. Two-Variable Tables
A one-variable (or one-way) sensitivity table changes a single input while holding everything else constant. You’d use this when you want to isolate the effect of one factor, like interest rate on monthly loan payments, or unit sales on total revenue. The result is a single row or column of outcomes that’s easy to read and chart.
A two-variable (or two-way) table changes two inputs simultaneously, creating a matrix of results. This is more realistic because business conditions rarely shift one variable at a time. For example, a real estate developer might vary both the rental rate and vacancy percentage to see how each combination affects annual cash flow. Two-variable tables are the most common format in financial modeling because they pack a dense amount of insight into a compact grid.
Why Sensitivity Tables Matter for Decisions
The real value of a sensitivity table isn’t just seeing different numbers. It’s identifying which variables have the biggest impact on your outcome, so you know where to focus your attention and risk management.
In project evaluation, sensitivity tables help answer four practical questions: which inputs drive the most variation in your results, what happens under realistic worst-case changes, whether those changes are severe enough to alter your decision, and what actions could cushion you against downside scenarios. A variable with a high sensitivity indicator, meaning a small percentage change in that input causes a large percentage swing in your outcome, deserves more scrutiny than one where even big swings barely move the needle.
For instance, in one project evaluation example, a 10% drop in price caused a 67% drop in the project’s net value, making price by far the most critical variable. A 20% increase in investment costs, meanwhile, caused a 266% change in net value. Findings like these tell decision-makers exactly where the risk lives and how much margin for error they have before a project stops being viable.
Building a Sensitivity Table in Excel
Excel has a built-in feature designed specifically for this. It’s found under the Data tab, in the What-If Analysis menu, labeled “Data Table.” Here’s how the process works in practice.
For a one-variable table, you list the values you want to test in a single column (or row). In the cell one row above and one column to the right of your list, you place the formula whose output you want to track. Then you select the entire range, go to Data, choose What-If Analysis, select Data Table, and tell Excel which cell in your model should be replaced with your test values. Excel fills in the results automatically.
For a two-variable table, you place one set of input values across a row and the other down a column. The formula goes in the cell where the row and column headers intersect (the top-left corner of the grid). After selecting the full range, you specify both a row input cell and a column input cell. Excel then populates every cell in the grid with the result of that specific combination.
A few practical tips: leave empty rows and columns around your table so Excel doesn’t confuse it with adjacent data. Make sure your workbook’s calculation mode is set to Automatic, or the table won’t update when you change inputs. And if your model is large and slow, know that data tables recalculate every time anything in the workbook changes, which can drag performance. You can switch to manual recalculation and press a shortcut to refresh the table only when needed.
What Sensitivity Tables Can’t Tell You
Sensitivity tables are powerful, but they have blind spots worth understanding.
The most significant limitation is that traditional sensitivity analysis changes one variable at a time. In reality, variables often move together: when raw material costs rise, shipping costs usually do too. A sensitivity table that tests each variable in isolation can miss these correlated effects. Two-variable tables help, but they still max out at two inputs.
Sensitivity tables also show you what could happen, not how likely it is. A table might reveal that a 30% cost increase would wipe out your profits, but it doesn’t tell you whether a 30% cost increase is a realistic possibility or a near-impossibility. Every scenario in the table gets equal visual weight regardless of its probability.
Finally, the quality of a sensitivity table depends entirely on the quality of your base case. If your starting assumptions are off, the table will show you how wrong things could go from an already-wrong starting point. Flawed inputs produce misleading insights no matter how neatly they’re organized.
For these reasons, sensitivity tables are often paired with complementary techniques. Scenario analysis lets you change many variables at once to model specific situations (best case, worst case, recession). Monte Carlo simulations run thousands of randomized trials to attach probabilities to different outcomes. A sensitivity table is typically the starting point: it tells you which variables matter most, and then these other methods help you explore those variables more deeply.