Build a two-driver sensitivity table in Excel

A two-driver sensitivity table calculates one output for every combination of two inputs while holding the rest of the model fixed. Build it from a working output formula, label the input units and calculate a few cells independently. Use the grid to find a decision boundary, then check whether its volumes and costs are operationally possible.

Set up one small price-and-volume model

Use a single worksheet for this example. Monthly price is $50, volume is 800 units, variable cost is $30 per unit and included fixed operating cost is $12,000 per month. All figures are assumed; the result excludes any costs outside those definitions.

The base result is ($50 − $30) × 800 − $12,000 = $4,000. Price and variable cost are per unit; fixed cost and volume cover the same month. A grid mixing annual fixed cost with monthly volume would answer a different question.

Build the grid using ordinary formulas

Put volumes 600, 700 and 800 in E9:G9. Put prices 45, 50 and 55 in D10:D12. In E10 enter =($D10-$B$4)*E$9-$B$5, then copy across and down through G12. The row price moves down; the column volume moves across; cost assumptions remain fixed.

The base intersection is G11: price $50 and volume 800, giving $4,000. The low-price, low-volume corner E10 is (45 − 30) × 600 − 12,000 = −$3,000. If the copied grid does not produce both values, inspect the dollar signs and references before interpreting the colors or totals.

Use Excel's two-variable Data Table for a linked model

When the output depends on a larger model, desktop Excel's Data Table can substitute input combinations through that model rather than reproduce the entire calculation in every grid cell. Microsoft's two-variable Data Table instructions explain the row and column input fields.

1. To build the Data Table version instead, clear the calculated cells E10:G12 and keep the volume and price headings. Put =$B$6 in D9, where the input row and column meet.
2. Select D9:G12, including the output reference and both sets of input headings.
3. Choose Data → What-If Analysis → Data Table.
4. Set Row input cell to B3 because the row contains volumes. Set Column input cell to B2 because the column contains prices.

Swapping those inputs feeds 600 into price and 45 into units at the first corner, creating results for the wrong combinations. If values stay unchanged after inputs move, inspect calculation settings: data-table recalculation can be excluded from normal automatic calculation. Microsoft documents selecting the table formulas and pressing F9 to recalculate them.

Read a boundary rather than only the best cell

The zero at $50 and 600 units is operating break-even under the included costs. At $45 it takes 800 units. Moving across a row isolates volume; moving down a column isolates price. The table does not estimate how customers respond to a price change or assign probabilities to the nine combinations.

For example, comparing the base $4,000 with $45 and 700 units gives −$1,500, a $5,500 reduction. Separate the changes to understand it: dropping volume from 800 to 700 at $50 loses $2,000; then dropping price by $5 on 700 units loses $3,500. Their sum matches the $5,500 change along that path.

Recalculate beyond a capacity step

Suppose serving more than 700 units requires an extra $2,000 shift. The 800-unit column must include that cost: its results become −$2,000, $2,000 and $6,000 at prices $45, $50 and $55. Update the main output formula as well as any direct grid formula; a Data Table should follow the same cost logic.

For the direct formula grid, the additional deduction is -IF(E$9>700,2000,0) before copying across. That condition uses the column's volume. If extra capacity cannot be obtained, label those combinations infeasible instead of treating them as forecast options.

The break-even and capacity guide develops that constraint. Use the downside scenario guide for connected events and responses, and the DCF guide for a cash-flow valuation that can be examined with the same two-input method.