Google Sheets what-if analysis: compare operating scenarios

Google Sheets what-if analysis compares the profit produced by different price, volume and cost assumptions. Build one calculation, feed it from a documented scenario table, and compare the outputs with actual results before using them in a planning discussion.

Define the operating calculation

A US business selling one product wants to compare approved monthly operating assumptions with the same month's actuals. Use units sold, realized price per unit, variable cost per unit and fixed operating cost. All money is USD, and all rows cover the same month.

Revenue = Units × Realized price per unit

Operating profit = Units × (Realized price per unit - Variable cost per unit) - Fixed operating cost

This is a simplified operating calculation, before financing and taxes. Its fixed cost includes the expenses the team chose for this planning scope. Keep that definition constant across scenarios and actuals; gross profit margin covers the separate revenue-and-COGS measure.

The finance owner should record where each assumption came from: the sales plan for units, net sales divided by units for realized price, and the operating ledger for costs. A scenario is a documented conditional case, not a prediction just because its name says Base.

Enter assumptions in a scenario table

On a sheet named Assumptions, put these headers in A1:E1 and values in rows 2:5:

The Actual row represents completed-period results in this worked example. In your workbook, keep the sales and cost exports supporting it, and record who approved the operating assumptions. Do not overwrite the approved Base row when an assumption changes.

For the source comparison before planning, follow Google Sheets data analysis. The Excel scenario-analysis guide covers the related workbook method for teams working there.

Create a scenario selector with a dropdown

On a sheet named Selected, use B1 for the scenario name. Choose Data > Data validation > Add rule, select a dropdown from the range Assumptions!A2:A5, and keep invalid entries rejected. Google's dropdown instructions document the range-based option and invalid-entry controls.

Put labels Units, Price, Variable cost and Fixed cost in A3:A6. Enter these formulas in B3:B6:

=INDEX(Assumptions!$B$2:$B$5,MATCH($B$1,Assumptions!$A$2:$A$5,0))
=INDEX(Assumptions!$C$2:$C$5,MATCH($B$1,Assumptions!$A$2:$A$5,0))
=INDEX(Assumptions!$D$2:$D$5,MATCH($B$1,Assumptions!$A$2:$A$5,0))
=INDEX(Assumptions!$E$2:$E$5,MATCH($B$1,Assumptions!$A$2:$A$5,0))

Google's INDEX documentation shows combining INDEX with MATCH to retrieve a corresponding value. The 0 argument selects an exact match, as Google's MATCH documentation specifies. Use unique scenario names so a repeated label cannot select the wrong row.

Put Revenue, Variable cost total, Contribution and Operating profit in A8:A11. Enter these formulas in B8:B11, respectively:

=B3*B4
=B3*B5
=B8-B9
=B10-B6

Check all scenarios on the same calculation

Select each scenario and record its outputs. Every amount below follows from the assumption row above it; amounts are whole dollars with no rounding.

For example, Downside profit is 900 × (48 - 32) - 12,000 = $2,400. Actual profit is 950 × (49 - 31) - 12,000 = $5,100.

Add a check outside the output range:

=B8-B9-B6-B11

It must be zero for every scenario. Also match the Actual row's revenue, variable cost and fixed cost with the source-period operating report. An arithmetic identity can pass even when the assumptions are unsupported.

Add a small two-input sensitivity grid

A grid answers a narrower question: what happens if units and price vary while variable cost stays at $30 and fixed cost at $12,000? On a Sensitivity sheet, enter those held-constant costs in F1 and F2. Put prices 48, 50 and 52 in B1:D1, and volumes 900, 1,000 and 1,100 in A2:A4.

In B2 enter the following and fill across and down:

=$A2*(B$1-$F$1)-$F$2

The center is 1,000 × (50 - 30) - 12,000 = $8,000, matching Base. The grid's 900-unit, $48-price result differs from Downside because this grid holds unit cost at $30 while Downside uses $32. For more on reading such tables, see Google Sheets sensitivity analysis.

Explain actual performance against Base

Actual profit is 5,100 - 8,000 = -$2,900 below Base. A sequential bridge proves the movement: volume contributes (950 - 1,000) × (50 - 30) = -$1,000; price contributes 950 × (49 - 50) = -$950; and unit cost contributes -950 × (31 - 30) = -$950.

The effects sum to -1,000 - 950 - 950 = -$2,900. Fixed cost did not change. This bridge uses actual volume for the price and cost steps; state that order so another reader can reproduce the allocation.

Where it goes wrong

• Assumptions have no owner or evidence. Keep a source, approval date and reporting period beside them.
• Actuals and scenarios use different costs. Reconcile their definitions before comparing profit.
• Scenario labels imply probabilities. Describe the assumptions; assign probabilities only with a justified method.
• A grid is mistaken for a complete scenario. Label held-constant inputs, especially costs that could change with volume.

Covirage is not affiliated with Google or Google Sheets.

Check the historical figures in Covirage

Bring the Google Sheet or operating exports supporting your scenario assumptions. Covirage maps the columns, its tools compute and check the historical operating comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See FP&A reporting with Covirage for the supporting workflow.