Skip to main content

Frac CFO

By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated October 26, 2026

Sensitivity analysis in Excel means changing one or two assumptions in your model and watching how a result, such as profit or ending cash, responds. The fastest way is a Data Table (Data tab, What-If Analysis, Data Table). Use a Scenario for more than two inputs, and a tornado chart to rank which assumptions matter most. Below is one small model, built step by step, so you can copy the method into your own workbook.

Fraccfo
Bring More Clarity to Your Forecast
Frac CFO helps businesses build financial models and forecasts from the spreadsheets they already use, supporting clearer decisions about cash flow and key assumptions.

Visit Frac CFO

TL;DR

  • Build the model first, with every assumption in its own input cell and the output driven by formulas. A data table can only flex cells that the output actually depends on.
  • Use a one-variable data table for a single driver and a two-variable table for a pair, such as price and volume. For more than two inputs, use Scenarios.
  • Rank your drivers with a tornado chart: flex each input one at a time, record the swing in the output, sort it, and plot it.
  • Test a few assumptions that really matter, with ranges you can defend, rather than ±10% on everything.
  • Pair the sensitivity view with a variance chart so you can see where actual results drifted from plan.

Table of Contents

What sensitivity analysis tells you

Sensitivity analysis looks at how different values of an independent variable affect a specific dependent variable under a given set of conditions. In plain terms: your forecast is a bet on a handful of assumptions, and this exercise shows you which of those bets can hurt you.

Excel groups its tools under What-If Analysis, which is the process of changing cell values to see how formula results change. There are three tools in that menu: Scenarios, Goal Seek and Data Tables. Each does a different job:

Tool Best for Limit
Data Table Seeing a result across a range of values for one or two inputs Two inputs at most
Scenario Manager Saving named sets of inputs (base, upside, downside) Each scenario holds up to 32 changing cells
Goal Seek Finding the input needed to hit a target result One input, one target

The limits above come from Microsoft’s own guidance: Data Tables cannot exceed two input variables, and Scenarios are recommended beyond that. Goal Seek works backward from a known result to the input that produces it.

Set up the model before you test it

A sensitivity table is only as good as the model underneath it. Before you touch the Data tab, check four things:

  1. Every assumption sits in one input cell. No numbers typed inside formulas. Price, volume, unit cost and fixed costs each get their own labeled cell.
  2. The output is a formula that depends on those cells. If someone hardcoded the profit line, the table will show the same number everywhere.
  3. Inputs are visually distinct. Giving assumptions their own font color, as Corporate Finance Institute suggests, helps you and your colleagues see what can be changed.
  4. Input cells live on the same sheet as the table. Excel’s data table needs the row or column input cell on the sheet where the table sits, a common stumbling block that modeling trainers report as an “input cell reference is not valid” error. Put a small assumptions block on the same sheet as your tables, or link the model’s inputs to it.

The example model we will use

Here is a deliberately simple monthly model for a made-up online shop. The numbers are illustrative, not benchmarks.

Cell Assumption Value
B2 Average selling price $40
B3 Units sold per month 1,000
B4 Variable cost per unit $18
B5 Fixed costs per month $12,000
B7 Monthly profit =B3*(B2-B4)-B5

Base-case profit is $10,000 a month. The question we want to answer: how fragile is that number?

Choose what to test

The temptation is to flex everything. Resist it. Test only the most important assumptions, the ones where you are genuinely unsure and where a miss would change a decision.

A few ways to pick them:

  • Ask what you would regret. Which number, if wrong by a fifth, would make you hire late, overspend on inventory or miss payroll?
  • Trace the root cause. Root-cause analysis is one of the better ways to decide which assumptions drive your results.
  • Check where the number came from. An input pulled from an old market estimate deserves more scrutiny than one from last quarter’s invoices. If your assumptions rest on outside data, dating every source keeps stale figures from sneaking into the model.
  • Use realistic ranges. Pick low and high cases from history or a defensible estimate, not a reflexive ±10%. Both Dartmouth’s Tuck School of Business and SpreadsheetCharts.co.uk note that a parameter-specific range or percentile bounds such as P10 and P90 can be more honest than a uniform percentage.

For our shop, price, volume, unit cost and fixed costs are all fair game. Ranges: price $36 to $44, volume 800 to 1,200 units, which is plausible for a seasonal business.

One-variable sensitivity analysis with a data table

This answers: “What happens to profit if only price changes?” Microsoft describes the setup this way: list the input values in one column or row and put the formula in the cell diagonally adjacent, then run the Data Table command.

Steps for a column-oriented table:

  1. In D3 to D7, type the prices: 36, 38, 40, 42, 44.
  2. In E2, one row above the first value and one cell to the right, enter =B7. This links the table to your profit formula.
  3. Select D2:E7.
  4. Go to Data > What-If Analysis > Data Table.
  5. Leave Row input cell empty. In Column input cell, enter B2 (the price).
  6. Click OK.

Excel fills E3:E7 with a single array formula, {=TABLE(,B2)}. You cannot edit one cell of it, which is normal. Our result:

Price Monthly profit
$36 $6,000
$38 $8,000
$40 $10,000
$42 $12,000
$44 $14,000

Every $2 of price moves profit by $2,000 at 1,000 units. That is a clean, linear relationship, and now you can see it rather than assume it.

To test several outputs at once, put more formulas across the top row (profit, margin, break-even units) and select the whole block. Each column becomes its own sensitivity.

Data table formulas mapped to sensitivity columns

Two-variable sensitivity table in Excel

A two-variable table shows how pairs of assumptions interact, which is what lenders and investors tend to ask about: price against volume, growth against margin, or purchase price against exit assumptions in a property model. You can also read more on testing financial assumptions with data tables in that development context.

The layout differs in one important way: the formula goes in the top-left corner of the grid.

  1. In D10, enter =B7.
  2. Across E10:I10, type prices: 36, 38, 40, 42, 44.
  3. Down D11:D15, type units: 800, 900, 1000, 1100, 1200.
  4. Select D10:I15.
  5. Open Data > What-If Analysis > Data Table.
  6. Row input cell: B2 (price, because prices run across the row). Column input cell: B3 (units, because they run down the column).
  7. Click OK.

The row input cell must match the values across the top, and the column input cell must match the values down the side. Swapping them is the most common mistake and the numbers still look plausible, so check one cell by hand. Ours should show $10,000 where $40 meets 1,000 units.

Units Price $36 $38 $40 $42 $44
800 $2,400 $4,000 $5,600 $7,200 $8,800
900 $4,200 $6,000 $7,800 $9,600 $11,400
1,000 $6,000 $8,000 $10,000 $12,000 $14,000
1,100 $7,800 $10,000 $12,200 $14,400 $16,600
1,200 $9,600 $12,000 $14,400 $16,800 $19,200

How to read the grid

  • Find the base case and look around it. A 10% price cut with a 20% volume drop (the $36, 800 cell) leaves $2,400 of profit, a 76% decline.
  • Look for the diagonal that holds your target. If you need at least $8,000 a month, the grid shows which price and volume combinations get there. A $38 price needs 1,000 units; a $36 price needs more than 1,100.
  • Apply conditional formatting. Shade cells below your minimum acceptable profit red. A grid with a red corner tells you where the plan breaks.

If your workbook feels slow, set calculation to Automatic Except for Data Tables under Formulas > Calculation Options, and press F9 when you want a refresh.

Scenario Manager for more than two inputs

When price, volume, cost and fixed costs all move together, a data table cannot show it. Use Scenarios: Microsoft says a scenario can hold up to 32 values and you can create as many as you want.

  1. Go to Data > What-If Analysis > Scenario Manager > Add.
  2. Name it “Downside” and set the changing cells to B2:B5.
  3. Enter the downside values (for example $37, 850 units, $19 unit cost, $12,500 fixed costs).
  4. Repeat for Base and Upside.
  5. Use Summary to produce a side-by-side report of the result cells.

If you prefer something more transparent, put a scenario number in one cell and use CHOOSE to pick the matching input. The Excel Modelling Handbook recommends CHOOSE with a single scenario cell for switching, and it has an advantage: you can wrap a data table around the scenario cell and test all cases at once.

Build a tornado chart in Excel

Build a tornado chart in Excel — overview diagram

A data table shows a range; a tornado chart ranks drivers. Dartmouth’s Tuck School describes it as ranking input parameters by their impact on an output. The method, per SpreadsheetCharts.co.uk, is to change one input at a time while the rest stay at base case, record the output, and calculate each result’s deviation from base.

For our shop, we flex each input by 10% either way:

Input Low case High case Profit at low Profit at high Swing
Price $36 $44 $6,000 $14,000 $8,000
Units sold 900 1,100 $7,800 $12,200 $4,400
Variable cost $19.80 $16.20 $8,200 $11,800 $3,600
Fixed costs $13,200 $10,800 $8,800 $11,200 $2,400

(For costs, the “low” case is the unfavorable one, so the left side of the chart always means worse.)

To chart it:

  1. Add two columns: low profit minus base ($10,000) and high profit minus base.
  2. Sort the rows by swing, largest first.
  3. Select the two deviation columns and insert a 2-D clustered bar chart.
  4. In Format Data Series, set Series overlap to 100% and reduce the gap width.
  5. Format the vertical axis and tick Categories in reverse order so the biggest bar lands at the top, as Risk Publishing also advises.

The result reads at a glance: price is the lever that moves profit most, then volume, unit cost, and fixed costs. Read the left and right halves separately to see downside versus upside exposure.

What a tornado chart cannot tell you

It flexes one input at a time, so it does not show interactions. Vose Software points out that tornado rankings work best when the input-output relationship is roughly monotonic; nonlinear effects and correlated inputs can mislead. In our shop, price and volume are surely linked, since raising price probably cuts units. That is why the two-variable grid and the tornado belong together.

Variance charts for actuals versus plan

Sensitivity analysis looks forward. A variance chart looks back and tells you which assumption actually moved. Add a variance column (actual minus budget), then chart budget and actual as clustered columns, with the variance as a separate bar or a column below. Format negative variances in red and positive in green with a custom number format so a miss is obvious.

The habit that makes this useful: when a line is off, check your tornado chart. If price was the biggest driver and price slipped, you have found the cause in minutes. The same logic applies in a driver-based budget, where each line is tied to a handful of inputs you can flex. A 12-month business budget gives you the plan to compare against.

For reporting to owners or investors, three charts cover most needs: a sensitivity grid for valuation or profit impact, a tornado chart for ranking drivers, and a budget variance bar chart for performance. The Excel Modelling Handbook describes data tables as the tools that turn a static calculator into a decision aid.

Our perspective

Most small businesses we meet do not need a bigger system. They need their existing spreadsheet to answer “what if?” without a rebuild. We see the same pattern repeatedly: a workbook with a revenue line, a cost line and a profit figure, where nobody can say which assumption deserves worry.

Our rule is to fix the structure first. Put assumptions in one block, make every output a formula, and only then add sensitivity tables. A grid on top of a messy model gives you precise-looking numbers that mean nothing. After that, we keep the test list short: usually price, volume, a key cost and the timing of cash. That is where the decisions are.

We also push owners to treat a sensitivity table as a conversation tool, not a forecast. The value is in agreeing, before a bad month, what you will do if the red cell happens: cut spend, pause hiring, call the bank. A tornado chart that sits in a folder changes nothing. One that sits next to a trigger plan changes behavior.

– frac

Get help building it in your own workbook

If you would rather not wire the tables yourself, we build them inside the spreadsheets you already use. Our Excel templates include structured models, such as a three-statement financial model that is ready for sensitivity tables. For a tailored build, a one-time finance project covers forecasts, budgets, break-even and pricing analysis. The Launch Kit is a $650 one-off, and our ongoing fractional CFO plans start at $600 per month on Starter.

Investor Pitch Model Excel Template

Common mistakes to avoid

  • Hardcoded outputs. If a table returns one repeated value, the formula is not linked to the input cell.
  • Reversed input cells. Check one corner of a two-variable grid by hand.
  • Testing too many inputs. A tornado chart with 20 bars hides the three that matter.
  • Ranges without a reason. Use history, quotes or a stated estimate for low and high cases.
  • Forgetting links between inputs. If volume depends on price, say so in the notes next to the chart.
  • Mixing time periods. Keep monthly and annual assumptions in clearly labeled units. For cash-focused tests, a 13-week cash flow forecast is a good base model, and a revenue forecast helps set realistic volume ranges.

FAQ

How do I run a one-variable sensitivity analysis in Excel?

List your input values in a column, put the output formula one row above and one cell to the right, and select the block. Open Data > What-If Analysis > Data Table, enter the input cell in Column input cell, and click OK. Excel fills in the results.

Can I do sensitivity analysis in Google Sheets?

Google Sheets has no built-in Data Table command. You can get the same result by building a grid where each cell recalculates the formula with its own row and column values, or by using named scenario cells with a CHOOSE or INDEX formula. It takes more setup than Excel, but it works for small models.

How many variables can a data table handle?

Two. Microsoft’s guidance says a data table cannot exceed two input variables, and recommends Scenarios when you need more. For many inputs, use a tornado chart to rank them first, then run a two-variable table on the top two.

What is the difference between sensitivity analysis and scenario analysis?

Sensitivity analysis changes one or two inputs and shows how the result moves. Scenario analysis changes several inputs together into named cases such as base, upside and downside. Use sensitivity to find what matters and scenarios to plan for combinations.

About the author

Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.

Sources

Leave a Reply