Blog · Territory, capacity and quota planning · Finance and FP&A teams
Build a Google Sheets sensitivity grid that compares profit under explicit price and volume assumptions. Follow the cell layout, check each scenario, and prepare the calculated results for review alongside your own workbook.
Google Sheet sensitivity analysis works by recalculating a defined business outcome under alternative assumptions. An explicit formula grid lets a finance team compare price and sales volume while keeping unit cost and fixed costs visible. Start with a checked base case, then change the assumptions rather than typing new profit answers into the grid.
Use one period, one currency, and one definition of profit. The example is an illustrative US packaging company's monthly operating case in USD. It covers one product with constant variable cost per unit and unchanged fixed costs throughout the tested range.
The shared method is explained in scenario analysis in Excel. Here the implementation is an ordinary Google Sheets formula in every scenario cell. The sensitivity analysis glossary covers the underlying distinction between changing assumptions and observing actual results.
This grid measures the mechanical consequence of supplied inputs. It does not establish how customers will respond to price or whether operations can deliver the stated volume. Keep those business judgments beside the workbook, with an owner and a date.
Create this layout in columns A and B. Enter numeric values as numbers and use cell formatting to display currency. Google explains how to enter formulas and functions, beginning with an equals sign, in its own help documentation.
| Cell | Column A: input or result | Column B: value or formula |
|---|---|---|
| B2 | Selling price per unit (USD) | 50 |
| B3 | Units sold | 5,000 |
| B4 | Variable cost per unit (USD) | 30 |
| B5 | Monthly fixed costs (USD) | 60,000 |
| B6 | Operating profit (USD) | =(B2-B4)*B3-B5 |
The calculation is:
Operating profit = (Price per unit - Variable cost per unit) × Units sold - Fixed costs
Rebuild the base output line by line before adding alternatives.
| Step | Calculation | Amount (USD) |
|---|---|---|
| Revenue | 50 × 5,000 | 250,000 |
| Variable costs | 30 × 5,000 | 150,000 |
| Contribution | 250,000 - 150,000 | 100,000 |
| Fixed costs | Supplied monthly assumption | 60,000 |
| Operating profit | 100,000 - 60,000 | 40,000 |
Use the contribution calculation as an independent check against B6:
=B2*B3-B4*B3-B5-B6
It must return zero. If it does not, correct the base calculation before expanding it. These are operating assumptions for an internal business case; the table excludes interest and income taxes.
Put prices 48, 50, and 52 in F1:H1. Put volumes 4500, 5000, and 5500 in E2:E4. Enter this in F2:
=(F$1-$B$4)*$E2-$B$5
Copy F2 across through H2, then fill the row down through row 4. The price reference changes columns, the volume reference changes rows, and the unit cost and fixed costs stay anchored. These references are the whole calculation; there is no separate scenario-generation step.
| Units in column E | Price 48 (USD/unit) | Price 50 (USD/unit) | Price 52 (USD/unit) |
|---|---|---|---|
| 4,500 | 21,000 | 30,000 | 39,000 |
| 5,000 | 30,000 | 40,000 | 50,000 |
| 5,500 | 39,000 | 50,000 | 61,000 |
All grid outputs are monthly operating profit in USD. The results are exact whole dollars for these inputs; no rounding is needed. G3 is the base intersection because its price and volume match B2 and B3.
=G3-$B$6
That check must also return zero. Inspect H4 directly: (52-30)*5500-60000 = 61000. A copied formula can look plausible while pointing at the wrong heading, so check an off-center cell as well as the base.
Build a second table with headers in A10:E10: Scenario, Price, Units, Profit, and Change from base. Enter the supplied prices and units in columns B and C. In D11 and E11, use:
=(B11-$B$4)*C11-$B$5
=D11-$B$6
Fill both formulas down through row 14.
| Scenario | Price (USD/unit) | Units | Profit (USD) | Change from base (USD) |
|---|---|---|---|---|
| Base | 50 | 5,000 | 40,000 | 0 |
| Price down | 48 | 5,000 | 30,000 | -10,000 |
| Volume down | 50 | 4,500 | 30,000 | -10,000 |
| Both down | 48 | 4,500 | 21,000 | -19,000 |
The two individual declines do not simply add to the combined result. The interaction is (-2)*(-500) = 1000: the lower price applies to fewer units. Thus 40000-10000-10000+1000 = 21000. This check prevents an overstated combined decline.
Do not sum profits across the scenario rows. They are alternative versions of the same month, not transactions that occur together. For a broader comparison of assumption-driven cases, see Google Sheets what-if analysis.
At the base price, contribution per unit is 50-30 = 20, so break-even volume is 60000/20 = 3000 units. A guarded formula makes a nonpositive contribution visible:
=IF(B2<=B4,"No positive contribution",B5/(B2-B4))
Google's function reference documents IF and the other standard functions used to build explicit calculations. Covirage is not affiliated with Google or Microsoft.
Finance should validate the starting revenue and costs against the monthly workbook. Sales owns the price and volume ranges; operations confirms whether unit cost and capacity remain credible throughout them. If a volume requires another shift or additional equipment, create a separate cost case rather than extending this fixed-cost assumption silently.
Export the labeled scenario table with its period and USD units. Retain the original workbook, assumptions, and check cells. The export should contain readable calculated results; confirm the base row still shows $40,000 after export. For the broader task of reviewing an exported sheet, see Google Sheets data analysis.
Bring the calculated scenario workbook or Google Sheet with labeled assumptions and the base result. Covirage maps the columns, and its tools compute and check the comparisons; the external AI model explains the differences with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Excel and spreadsheet analysis for the supporting file-based workflow.
Yes. Put one assumption across column headings and the other down row headings. Use a formula that references both headings and locks the remaining inputs. Copy it across the grid, then check the base-case intersection.
No. Each cell answers what the supplied assumptions would produce. It does not estimate their likelihood. A finance owner should document the range, its evidence, and any relationship between assumptions before using the results in a decision.
Include a scenario label, the changed inputs, units, period, calculated output, and difference from the base case. Keep the calculation workbook separately so a reviewer can trace the exported values to their formulas and source assumptions.