What-if analysis is a method of testing how changes to one or more inputs affect an outcome in a model, spreadsheet, or plan. You adjust assumptions like sales volume, interest rates, or costs, then observe how those changes ripple through to the bottom line. It’s used widely in financial planning, budgeting, investment decisions, and project management to evaluate risk and compare possible futures before committing to a course of action.
How What-If Analysis Works
The core idea is simple: you have a model with inputs and outputs, and you want to know what happens to the output when you change an input. A mortgage calculator is a basic example. You plug in a loan amount, an interest rate, and a term length, then adjust one of those variables to see how your monthly payment changes. The same logic scales up to complex financial models where dozens of variables interact.
What makes this more than just tinkering with numbers is structure. Rather than randomly changing cells in a spreadsheet, what-if analysis uses defined techniques to systematically explore outcomes. The three main approaches are scenario analysis, sensitivity analysis (often done through data tables), and goal seeking. Each answers a slightly different question.
Scenario Analysis
Scenario analysis adjusts multiple variables at once to simulate distinct situations. The classic setup involves three cases: a best case, a worst case, and a base case (your most realistic estimate). For example, if you’re projecting next year’s profitability, your best-case scenario might assume 15% revenue growth and stable costs, while your worst case assumes flat revenue and a 10% increase in raw material prices. The base case sits somewhere in between.
This approach is especially useful when several assumptions are likely to move together. In a recession, for instance, sales volume, pricing power, and customer retention might all decline at once. Scenario analysis lets you model that cluster of changes as a single coherent picture rather than tweaking variables in isolation. In Excel, the Scenario Manager tool lets you save multiple sets of input values and quickly switch between them to compare results. Each scenario can include up to 32 different variables.
Data Tables and Sensitivity Analysis
Sensitivity analysis asks a narrower question: how much does the outcome change when you move just one or two inputs? This tells you which variables matter most. If a 1% change in your interest rate assumption swings projected profit by $50,000 but a 1% change in your tax rate assumption only moves it by $2,000, you know where to focus your attention.
Excel’s data table feature is the main tool for this. A one-variable data table lets you see how different values of a single input (say, interest rates ranging from 4% to 8%) change the result of one or more formulas. A two-variable data table goes a step further, showing you the combined effect of two inputs in a grid format. You could, for example, build a table showing how monthly loan payments change across different combinations of interest rates and loan amounts, with every result visible at once.
The key limitation: data tables max out at two variables. If you need to test three or more inputs simultaneously, you’ll need to use scenario analysis instead.
Goal Seek
Goal Seek works in reverse. Instead of asking “what happens if I change this input?” it asks “what input do I need to hit a specific target?” You already know the result you want, and the tool calculates the value required to get there.
Say you need $100,000 in gross profit and you know your costs and margins, but you’re not sure how many units you need to sell. Goal Seek can solve for that sales volume directly. Or if you’re planning a loan and know you can afford $1,500 per month, it can tell you the maximum loan amount or the interest rate you’d need to secure. It’s particularly handy for backing into break-even points, target pricing, or required growth rates without manually guessing and checking.
Where Businesses Use It
In corporate finance, what-if analysis shows up in nearly every major decision. Capital budgeting teams use it to stress-test project returns under different cost and revenue assumptions before approving large investments. Treasury departments model how changes in interest rates or exchange rates would affect debt service costs. Sales leaders run scenarios to set quotas, testing how different growth rates would translate into headcount needs and commission expenses.
Budgeting is another natural fit. When building an annual budget, finance teams typically present leadership with multiple scenarios so decision-makers can see the tradeoffs. A hiring plan that looks affordable under 10% revenue growth might become unsustainable if growth comes in at 5%. Seeing those outcomes side by side makes the risks concrete.
On a personal level, the same logic applies to decisions like buying a home, saving for retirement, or evaluating a career change. Any situation where you’re making assumptions about the future and want to understand the range of possible outcomes is a candidate for what-if analysis.
Where What-If Analysis Goes Wrong
The biggest risk is treating outputs as predictions rather than possibilities. A what-if model is only as reliable as the assumptions you feed it. If your inputs are guesses dressed up as data, the results will look precise but mean very little.
A subtler problem is confusing correlation with causation when choosing which inputs to test. A Forbes analysis highlighted a real-world example: a company discovered that salespeople who attended more training sessions had higher sales numbers, so leadership invested heavily in mandatory training for the entire team. The results were disappointing. It turned out that high-performing, ambitious salespeople were simply more likely to sign up for training voluntarily. Training was a reflection of their drive, not the cause of their success. The what-if model (if we increase training, sales will rise) looked logical but was built on a flawed assumption.
Before acting on what-if results, ask whether there’s a plausible reason why changing a particular input would actually cause the output to change. If the connection seems real, design a small test to validate the assumption before scaling up. A what-if model can show you that raising prices by 5% would increase revenue by $200,000 if demand stays constant, but it can’t tell you whether demand will actually stay constant. That judgment still requires experience, market knowledge, and sometimes real-world experimentation.
Getting Started in Excel
All three what-if tools live under the Data tab in Excel, grouped under the “What-If Analysis” dropdown. Here’s a quick guide to choosing the right one:
- Scenario Manager: Use this when you want to compare several complete sets of assumptions. Name each scenario (optimistic, pessimistic, realistic), define the changing cells, and switch between them to see how results shift.
- Data Tables: Use this when you want to see how one or two variables affect a formula across a range of values, all displayed in a single table. Best for quickly identifying which inputs have the most impact.
- Goal Seek: Use this when you have a target number in mind and need to find the input that gets you there. Set the cell containing your formula, specify the target value, and tell Excel which input cell to adjust.
For more complex modeling with dozens of interrelated variables, dedicated tools like Monte Carlo simulation software can run thousands of randomized scenarios at once. But for most business planning and personal finance decisions, Excel’s built-in tools cover the ground you need.