What-If Analysis: Excel's 3 Tools, Scenario Planning and Testing a Budget Before You Spend It

What-if analysis is the practice of changing the inputs to a model, such as spend, price or volume, and watching how the output changes, so you can test a decision before you make it. In Excel it means changing input cells and reading the formulas that depend on them, using Scenario Manager, Goal Seek or a data table.

This guide covers each of Excel's three what-if tools, Scenario Manager, Goal Seek and data tables, with the steps and the limits Microsoft documents for each, and what Google Sheets can do with its Goal Seek add-on. It then runs one marketing budget question through two formulas, spend times average ROAS and a response curve that flattens as spend rises. In the illustrative example they agree at today's spend and sit $100,000 of weekly revenue apart at double it.

What is what-if analysis?

Microsoft defines what-if analysis as changing the values in cells to see how those changes affect the outcome of the formulas on the worksheet. You move the inputs to a model and read the outcome before you commit money, stock or headcount.

Every what-if analysis has three parts: the inputs you want to test, such as spend or price; the formula that turns them into an outcome; and the outcome itself, such as revenue or profit. Excel's tools change the inputs and read the outcome. None of them checks whether the formula in the middle describes how the business behaves.

What-if analysis in one example

Take an illustrative case. A marketing manager spends $50,000 a week on paid social and books $150,000 of revenue against it. Finance asks what happens if spend doubles. The quickest what-if analysis is a cell for spend, a cell for average ROAS of 3.0, and a formula that multiplies them. Change spend to $100,000 and the sheet answers $300,000. That is arithmetically correct and probably wrong, because the formula assumes every extra dollar earns what the average dollar earned.

The three questions a what-if analysis answers

• Forward: what happens to the output if an input changes? "What does revenue do if spend rises by a fifth?" A data table answers this across many input values at once.
• Backward: what input do I need to reach a target? "What spend gets us to $225,000 a week?" That is Goal Seek.
• Comparison: which of several named plans comes out best? "Base, push or cut?" That is Scenario Manager.

Where is what-if analysis in Excel?

Excel for Windows and Mac

In current versions of Excel for Windows, Microsoft places What-If Analysis in the Forecast group on the Data tab. The button opens a menu with Scenario Manager, Goal Seek and Data Table. Microsoft's data table page still mentions the Data Tools group, so look there on older builds. On a Mac, Microsoft's Goal Seek instructions give the path as the Data tab, then What-If Analysis.

What-if analysis in Excel for the web

Microsoft's service description for Excel for the web lists what-if analysis tools as not available in the browser. You can view what-if results that were produced on the desktop, but Goal Seek and data tables need the Excel desktop app. Microsoft names Solver in the same list, though Frontline Systems publishes a free Solver App that runs in Excel for the web.

Excel's three what-if tools at a glance

Microsoft lists three kinds of what-if analysis tools that come with Excel: Scenarios, Goal Seek and Data Tables. They answer different questions and change inputs in different ways.

Tool	Question it answers	Inputs it changes	What you get back	Documented limit
Scenario Manager	Which named plan comes out best?	Several cells, saved as named sets of values	Any one scenario loaded into the sheet, or a summary of all of them	Up to 32 variable values per scenario; reports do not recalculate
Goal Seek	What input value hits my target?	One cell	The input value that makes the formula equal your target	One variable input value only
Data Table	How does the output move across a range of inputs?	One or two cells, across many values	A grid of results, one per input value or pair of values	Two variables at most; recalculates with the sheet

For several inputs that move together under constraints, such as a budget split with a minimum spend per channel, use the Solver add-in.

How to use Scenario Manager

Scenario Manager stores named sets of input values and swaps them into the sheet on demand, which suits comparing a handful of complete plans. The steps use the sheet from the worked example below: B1 holds weekly spend, B2 holds average ROAS, and B3 holds the revenue formula, =B1*B2.

Step by step

1. Lay out the input cells and a result formula that depends on them.
2. Go to Data > What-If Analysis > Scenario Manager and select Add. Name the first scenario Base, set B1 as the changing cell and keep today's value, 50000. Showing a scenario overwrites the cells, so save Base first or you will have to retype today's values.
3. Add a scenario called Push with B1 at 100000, and one called Cut with B1 at 25000.
4. Pick a scenario in the list and select Show. Its values load into the sheet and B3 updates.
5. Select Summary, choose Scenario summary and enter B3 as the result cell. Excel writes a new sheet with every scenario side by side. Result cells are optional for this report and required for the PivotTable version.

With B2 at 3.0, the summary shows revenue of $150,000 for Base, $300,000 for Push and $75,000 for Cut. Scenarios that colleagues build in workbooks with the same cell layout can be pulled in with the Merge button.

Scenario Manager limits

• Each scenario can hold up to 32 variable values, though you can create as many scenarios as you like.
• Scenario reports do not recalculate when you edit a scenario. Generate a fresh report and delete the stale one, particularly if the old one is already in a deck.

How to use Goal Seek

Goal Seek is what-if analysis run backwards. You give it the result you want, and it changes one input until the formula produces that result.

Step by step

1. Go to Data > What-If Analysis > Goal Seek.
2. In Set cell, enter the cell that holds the formula, B3.
3. In To value, type the target, 225000.
4. In By changing cell, enter the input to adjust, B1.
5. Select OK. Excel iterates and reports the input that hits the target, which on this sheet is $75,000 of weekly spend.

The cell Goal Seek changes must be referenced by the formula in the Set cell box, or Goal Seek has nothing to move.

Goal Seek limits, and when to use Solver instead

Goal Seek works with one variable input value only. To hit a target by moving spend across several channels, with a floor or ceiling on each, Microsoft points to the Solver add-in, which handles several changing cells and constraints.

Goal Seek found $75,000 because the formula says every extra dollar earns $3. Give it the response curve from the worked example below and the same $225,000 target needs $150,000 of spend, twice the money. Goal Seek cannot tell which of the two formulas describes your market.

How to use a data table for what-if analysis

A data table runs one formula across a list of input values and writes every result into a grid, so you see how the output responds across a range instead of at one point.

One-variable data table

1. Type the input values in a column, for example weekly spend from 25000 to 200000 in D3:D8.
2. In the cell one row above and one column to the right of that list, E2, enter a formula that points at the result, =B3. That is the placement Microsoft's data table instructions require for a column of values.
3. Select D2:E8, the values plus the formula row.
4. Go to Data > What-If Analysis > Data Table. Leave Row input cell empty and set Column input cell to B1, the spend cell.
5. Select OK. Excel substitutes each value in D3:D8 into B1 in turn and writes the revenue beside it.

Two-variable data table

A two-variable table changes two inputs at once. Put spend values down a column, a second assumption across the top row, for example average ROAS of 2.5, 3.0 and 3.5, and the result formula in the corner cell where the row and the column meet. Select the whole block, open Data Table, and set Row input cell to B2 (ROAS) and Column input cell to B1 (spend). Each cell in the grid shows revenue for one pair of values.

Data table limits

• A data table cannot accommodate more than two variables. For three or more, Microsoft recommends scenarios.
• Data tables recalculate whenever the worksheet recalculates, even when none of their inputs changed, which slows heavy workbooks. Under File > Options > Formulas you can set the calculation options so the worksheet recalculates automatically but the data tables do not.

What-if analysis in Google Sheets

Google documents no built-in What-If Analysis menu, Scenario Manager or data table for Sheets. Goal Seek comes as an add-on. Google publishes a free Goal Seek add-on for Sheets in the Workspace Marketplace, which calculates the value a cell needs for a formula in another cell to reach a target. Install it from Extensions > Add-ons > Get add-ons, then open it from the Extensions menu.

Build the other two by hand, or, for data tables, install a third-party add-on from the Workspace Marketplace.

• Data table: put input values in column A, and in column B a formula that runs the model for that row's input, such as =A2*$E$1 for a linear sheet or =300000*A2/(A2+50000) for a response curve. Fill it down. Each extra assumption value needs another column.
• Scenario switch: make a small table with one row per scenario (Base, Push, Cut) and one column per input. Add a dropdown cell with data validation listing the scenario names, then pull each input into the model with INDEX and MATCH against that cell. Changing the dropdown swaps the whole plan, and nothing overwrites your base numbers.

What-if analysis vs scenario planning vs sensitivity analysis

HM Treasury's Green Book, the UK government's appraisal guidance, treats sensitivity analysis, scenario analysis and what-if questions as separate tools. It defines sensitivity analysis as testing how changes in key assumptions might affect a proposal's results (para 6.102), and treats scenario analysis as a way to analyse uncertainty across plausible futures. Of what-if questions it says: "For low-cost and low-risk proposals, practitioners may consider simple what-if questions about the assumptions that underpin the appraisal" (Green Book 2026, para 6.74).

Method	Question it answers	What you change	Output	Typical horizon	Typical tool
What-if analysis	What happens if this input changes?	One or a few inputs you choose	A result for each input value	The next decision or budget	Spreadsheet, MMM simulator
Sensitivity analysis	Which assumptions move the result most?	One assumption at a time, across a range	How far the result moves per assumption	Same as the plan being tested	Data table, tornado chart
Goal seeking (break-even)	What input reaches a target or flips the decision?	One input, solved for	A single required value	Same as the plan being tested	Goal Seek, Solver
Scenario analysis or planning	How does the plan hold up in different futures?	Many inputs together, as coherent stories	A small set of named outcomes	Medium to long, often several years	Scenario Manager, FP&A platforms, workshops
Monte Carlo simulation	How wide is the spread of possible outcomes?	Many uncertain inputs, drawn at random from ranges	A probability distribution of results	Any	Simulation software, Python or R

What is scenario planning?

Scenario planning is the long-range end of the family. A team writes a few coherent stories about how the outside world might develop, such as a recession, a new competitor or a jump in media prices, and tests its strategy against each one. The scenarios are not forecasts and usually carry no probabilities. The aim is a plan that holds up in several futures instead of one tuned to the most likely.

A forecast estimates the most likely path of a number, with an interval around it, and our guide to time series forecasting covers how those are built. Scenario planning asks what you would do if the path turned out differently.

Tools for scenario planning fall into three groups: spreadsheets with Scenario Manager or a scenario-switch table, FP&A planning platforms that hold driver-based versions of a plan, and, for marketing budgets, MMM simulators that apply fitted response curves to each scenario.

Where Monte Carlo simulation fits

When many uncertain inputs interact, a few hand-picked cases understate the range of outcomes. Monte Carlo simulation gives each uncertain input a distribution, draws a large number of random combinations and reports the spread of results. It is the step up from what-if analysis when the question moves from "what happens if" to "how likely is it".

A worked example: one marketing channel, three tools

The numbers in this what-if analysis are illustrative. The sheet models one channel with weekly figures in USD and no carryover between weeks, so each week's spend affects that week's revenue only.

The sheet

Cell	Contents	Value
B1	Weekly spend	$50,000
B2	Average ROAS	3.0
B3	Revenue, =B1*B2	$150,000

It is the simplest way to link a budget to revenue, and every Excel tool works on it.

Scenario Manager: three plans

Scenario	Weekly spend	Revenue on the linear sheet
Base	$50,000	$150,000
Push	$100,000	$300,000
Cut	$25,000	$75,000

Goal Seek: the spend for a target

Set B3 to 225000 by changing B1. Goal Seek returns $75,000 of weekly spend, so $25,000 more spend buys $75,000 more revenue.

Data table: sweeping spend

Weekly spend	Revenue on the linear sheet
$25,000	$75,000
$50,000	$150,000
$75,000	$225,000
$100,000	$300,000
$150,000	$450,000
$200,000	$600,000

Every row is three times the spend. At $200,000 a week the sheet predicts $600,000, and an extended column would keep climbing at the same rate forever.

The linear-assumption trap

Why spend times average ROAS grows forever

Average ROAS describes past spend as a whole. Multiplying a new spend level by it assumes the next dollar returns what the average dollar returned, at any scale. Google researchers Jin, Wang, Sun, Chan and Koehler (2017) wrote that "Advertising usually has lag effects and diminishing returns, which are hard to capture using linear regression." A spend-times-ROAS formula is cruder still, a straight line through zero.

In most channels the first dollars reach the most responsive people. Later dollars increasingly reach people who have already seen the ad or were never likely to buy, so past a point each extra dollar returns less than the one before. That is why budget should follow marginal ROAS, the return on the next dollar, as the ROAS guide works through.

Same question, response-curve formula

Replace the formula in B3 with a response curve: revenue = 300,000 x spend / (spend + 50,000), or =300000*B1/(B1+50000) in Excel. This Hill curve, with a slope of 1, approaches a ceiling of $300,000 a week and reaches half of it at $50,000 of spend. Both values are invented here. A real model fits them to your history.

At today's $50,000 the curve gives $150,000, the same as the linear sheet, which is why the linear sheet looks right. Run the same tools on both:

Question (illustrative)	Linear sheet	Response curve
Revenue at $50,000, today	$150,000	$150,000
Push to $100,000	$300,000, up $150,000	$200,000, up $50,000
Cut to $25,000	$75,000, down $75,000	$100,000, down $50,000
Goal Seek for $225,000	$75,000 of spend	$150,000 of spend
Revenue at $200,000	$600,000	$240,000
Average ROAS at $100,000	3.0	2.0
Marginal ROAS at $100,000	3.0	0.67

On the curve, the extra $50,000 of the push buys $50,000 of revenue, an incremental ROAS of 1.0 where the sheet promised 3.0. The $225,000 target needs $150,000 of spend, twice the sheet's answer. Ask Goal Seek for $300,000 and the curve has no answer at all, because it approaches that ceiling without reaching it. The linear sheet returns $100,000 without complaint.

The marginal figures show the saturation before the totals do. At $50,000 the curve's marginal ROAS is 1.5 against an average of 3.0, and at $100,000 it is about 0.67 against 2.0. Google's Meridian documentation treats that gap as a warning sign, because an mROI far below ROI means the channel is starting to saturate at its historical spend level.

Linear sheets overstate cuts too

Cutting spend to $25,000 saves $25,000. The linear sheet says the cut costs $75,000 of revenue, $3 per dollar saved. The curve says $50,000, or $2 per dollar. A team that judges cuts by average ROAS will overprice the cut and protect spend that sits on the flat part of the curve. On a concave curve like this one, average ROAS overstates both the gain from adding money and the loss from removing it.

What Excel's what-if analysis tools cannot check

Goal Seek, data tables and Scenario Manager solve whatever relationship you write and never ask whether it is true. A formula fails when it is built wrongly or when it is built correctly on the wrong model.

The first is common. In Raymond Panko's summary of spreadsheet field audits, the five methodologically strongest studies, published between 1995 and 2000, examined 55 operational spreadsheets and found serious errors in 91% of them. The second is the linear trap above. Checking the formulas will not catch it, because the arithmetic is correct, and catching it needs the model tested against data. A formula written by a spreadsheet copilot needs the same checks, and the guide to AI for data analysis sets out how to test one.

What a marketing what-if formula needs

Before a what-if analysis of spend is worth acting on, the formula behind it should have:

• Diminishing returns, so each extra dollar can return less than the last.
• Carryover (adstock), so this week's spend can still lift sales in later weeks.
• A split between base and incremental sales, so the formula moves only the part that media drives.
• Controls for seasonality, price and promotion, so a seasonal peak is not credited to whichever channel spent into it.
• Parameters fitted to your own history.
• Validation against held-out periods or experiments such as incrementality tests.
• A known tested spend range, so you can see where the model is interpolating and where it is guessing.

Fitting a curve by hand through two data points is still guessing. Estimating these pieces together from several years of data is the work a marketing mix model does, which is why a marketing what-if analysis belongs on the model rather than in a standalone sheet.

Running a marketing budget what-if analysis on an MMM simulator

What a simulator does that a spreadsheet cannot

An MMM simulator is a what-if tool built on a fitted model. You enter a spend plan, and it applies each channel's fitted response curve, with adstock and saturation, to return the incremental sales the plan should produce, for every channel at once. A spreadsheet cannot do this at scale. A data table stops at two variables, a scenario holds up to 32 variable values, and neither fits a curve to your data. Each evaluates whatever formula you type, as the response-curve sheet above shows.

Each Excel tool has a simulator counterpart.

• Scenario Manager becomes side-by-side spend plans, each scored with the fitted curves.
• Goal Seek becomes an optimiser that solves for the channel mix that reaches a growth target.
• A data table becomes the response curve itself, the model's predicted sales across a sweep of spend for one channel.

Read marginal ROAS by channel

Read two outputs: incremental sales by channel and in total, ideally with an interval, and each channel's marginal ROAS at the planned spend. The total tells you whether the new plan beats the base case. Marginal ROAS tells you where the next dollar should go, which is from the channel with the lowest marginal return to the one with the highest. Turning that result into a flighted plan is a media planning job.

Stay inside the tested spend range

A fitted curve is evidence only across the spend levels the model has seen. Meridian's documentation warns that response-curve points above historical spend involve extrapolation, and that the risk increases the further out you go. Check whether your simulator marks each channel's tested range and warns when a scenario crosses it. If it does not, check planned spend against each channel's historical maximum before sign-off. A plan that doubles a channel beyond anything it has spent is an experiment, and should be budgeted and measured as one.

How to run a good what-if analysis

The Green Book's test for sensitivity analysis, how changes in key assumptions might affect the result, is a fair standard for any what-if analysis.

1. Set the base case from actuals. Use last period's actual spend and results, reconciled to finance. A wrong base carries its error into every case, and on a response curve it also changes where each case sits on the curve.
2. Write down every assumption and its source. One line each: the value, the source (model, test, contract or judgement) and the date. Unrecorded assumptions cannot be checked or rerun next quarter.
3. Use realistic ranges, then build base, upside and downside cases. Take ranges from historical minimums and maximums, test results and contracted floors. Expect lopsided cases, because a channel near saturation has more room to lose than to gain.
4. Change one input at a time before combining. One-at-a-time runs show which assumptions drive the result. Combine them into scenarios after that.
5. Find the break-even input. Use Goal Seek to find the value at which the decision flips, such as the marginal ROAS below which a push loses money. The Green Book calls this a switching value (para 6.103). If it sits inside your realistic range, the decision is fragile and worth testing before you commit.
6. Check the answer against history. Run the model on a past period it was not fitted to. If it misses, fix the model before trusting its view of next quarter.

Common mistakes in a budget what-if analysis:

• Extrapolating a linear ROAS to spend levels the business has never run.
• Judging cuts by average ROAS, which overstates what a cut costs wherever marginal ROAS has already fallen below the average, as it has on a saturating channel.
• Pushing a scenario past the tested spend range without flagging it.
• Editing scenarios after the summary has gone into a deck, when the report does not recalculate.
• Keeping assumptions in someone's head instead of next to the numbers.

Frequently asked questions

What are the three what-if analysis tools in Excel?

Excel comes with three: Scenario Manager, Goal Seek and Data Tables, all under Data > What-If Analysis. Scenario Manager swaps named sets of input values into the sheet and summarises them side by side. Goal Seek finds the one input value that makes a formula hit a target. A data table runs a formula across many values of one or two inputs and returns a grid of results.

Can you do what-if analysis in Excel online?

Not fully. Microsoft's Excel for the web service description lists what-if analysis tools as not available in the browser. You can view what-if results produced on the desktop, but Goal Seek and data tables need the Excel desktop app. For Solver in the browser, Frontline Systems publishes a free Solver App for Excel for the web. If the What-If Analysis button is missing from your ribbon, check whether you are in the browser version.

Is there what-if analysis in Google Sheets?

Partly. Sheets has no What-If Analysis menu, but Google publishes a free Goal Seek add-on that finds the value a cell needs for a formula in another cell to reach a target. For a data table, list input values in one column and fill the model formula down the next. For scenarios, use a dropdown cell with INDEX and MATCH over a small table of named cases.

What is the difference between what-if analysis and sensitivity analysis?

What-if analysis is the general practice of changing inputs and reading the result. Sensitivity analysis is a systematic form of it. HM Treasury's Green Book defines it as testing how changes in key assumptions might affect a proposal's results (para 6.102). In practice it is usually run one assumption at a time across a range. A single what-if question is not yet a sensitivity analysis.

When should I use a data table instead of Scenario Manager?

Use a data table to see how one output responds across many values of one or two inputs, such as revenue at every spend level from $25,000 to $200,000. Use Scenario Manager when each case changes several inputs together and you want named plans compared side by side. A data table cannot accommodate more than two variables, so anything wider belongs in scenarios.

Why does my spreadsheet what-if analysis disagree with my MMM?

Usually because the spreadsheet multiplies spend by average ROAS, a straight line, while the MMM applies a response curve that flattens as spend rises. The two agree near current spend and split as spend moves. In this guide's illustrative example, doubling spend adds $150,000 of weekly revenue on the sheet and $50,000 on the curve. The law of diminishing returns is why the curve bends.

How Aryma runs what-if scenarios

Aryma Labs has built marketing mix models since 2019. Budget questions such as what happens if money moves from TV to Meta, what a cut would cost or how far a channel can scale are what-if questions, and a fitted model is the place to answer them.

eMMMy, the reasoning assistant inside ArymaEdge, Aryma's MMM platform, is where those questions get asked in plain language. Describe a spend plan, such as "What will $100K on TV, $50K on Meta and $30K on Branded Paid Search deliver?", and eMMMy moves the MMM simulator to those values, with expected sales updating instantly. It also reads saturation curves, answering questions like how far Meta Video can scale before returns start to fall, and it explains the budget optimiser's recommendations when someone asks whether a suggested cut is wise.

Aryma Nebula, our agentic spend optimiser, turns MMM outputs into weekly and monthly budget allocation decisions. Aryma Intellisheet is an Excel add-in that cleans and reshapes MMM data before modelling and charts the outputs afterwards, with no coding. Aryma Dimension is our AI meeting analyst. In Dimension, eMMMy joins the call, runs budget scenarios live from your own model's results, and asks before running an optimiser.

For help running what-if scenarios on a marketing mix model, get in touch with us.