How to Run What-If Analysis in Sales Planning (With Examples)

What-if analysis in sales planning is a technique where you change one or more input variables such as price, discount rate, or sales volume. The goal is to model how those changes affect key outputs before committing to a course of action. It replaces guesswork with structured modelling, giving decision-makers a quantified view of risk and opportunity before a plan goes live.

This guide covers the concept, the Excel tools, a worked example, the limitations of spreadsheet-based analysis, and how purpose-built planning platforms handle the same problems faster and with fewer failure points.

Read more: Scenario Planning: How to Prepare Your Business for Uncertainty

What-if analysis is a decision support technique used in financial modeling and planning. You change one or more input variables and observe how those changes affect key outputs including revenue, gross margin, and cash flow. You do this before committing to a course of action.

The question it answers is simple: what would happen to the business if this variable changed? The analysis can be narrow (what if we raise the price of Product A by 4%?) or broad (what if the market contracts by 15% and we simultaneously lose our largest client?).

It is one of the most commonly used techniques in FP&A because it converts assumptions into numbers. Instead of debating whether a price increase is a good idea, the team can model three versions and compare the margin impact side by side.

These two terms are often used interchangeably. They are related but distinct.

Sensitivity analysis changes one variable at a time while holding all others constant. It shows how sensitive a result is to a single input. If you want to know how gross margin moves when you change the discount rate, sensitivity analysis gives you that answer in isolation.

Scenario analysis changes multiple variables simultaneously to model a coherent view of a possible future state. A best case might assume price holds, volume grows 10%, and a major new account closes. A worst case might assume price pressure forces deeper discounts, volume falls, and cost inflation squeezes margin. Both inputs move together.

In practice, most sales planning what-if work uses both. Sensitivity analysis identifies which variables matter most. Scenario analysis then models the realistic combinations of those variables.

Most guides jump straight to the tools. Three preparation steps determine whether the output is useful or misleading.

Step 1: Define your baseline. Every what-if analysis needs a stable starting point. For sales planning, this is typically prior year actuals or the approved annual budget. Without a clean baseline, you cannot measure the impact of any change you model.

Step 2: Identify and prioritise your variables. Not every input is worth modeling. Focus on variables that are both uncertain and material to the output you care about. In sales planning, high-priority variables typically include price points, discount rates, volume assumptions per product or channel, and promotional spend. Variables that are predictable or too small to move the outcome should stay at their baseline values.

Step 3: Map your dependencies. Every model has chains of cause and effect. A price change affects revenue, which affects gross margin, which affects contribution per unit, which affects break-even volume. Map these dependencies before building your scenarios. When you change an input, every output that should move in response must be connected in the model.

Skipping Step 3 is the most common source of flawed what-if analysis. A model that updates revenue when price changes but not margin or volume is giving false confidence.

Excel provides three built-in what-if tools, plus a fourth available via add-in. Each serves a different type of question.

Scenario Manager stores and switches between multiple sets of input values. Each scenario can have up to 32 changing cells. There is no hard cap on the number of scenarios, though planners typically work with three: base, best, and worst.

Each scenario saves a named set of input values that can be applied to the model with one click. The Scenario Summary report shows all scenarios side by side against a set of result cells you define, making comparison straightforward.

The limitation is that Scenario Manager outputs are static snapshots. Every time inputs change, the summary table must be regenerated manually.

Data Tables show all possible outcomes across one or two variables in a grid format. If you want to see how gross margin responds to every combination of price increase (from 0% to 8% in 1% steps) and discount reduction (from 0% to 5% in 1% steps), a two-variable Data Table generates the full matrix automatically.

Data Tables are excellent for sensitivity analysis. They reveal the shape of the relationship between inputs and outputs, including non-linear effects and thresholds.

Goal Seek works backwards from a target output to find the required input value. If you know you need a gross margin of 38%, Goal Seek can calculate the price point required to achieve it, assuming all other variables stay constant.

The limitation is that Goal Seek supports one input and one output cell. It cannot solve for the combination of two variables that jointly achieve a target.

Solver is an Excel add-in (available under the Data tab once enabled) that handles multi-variable optimisation. Where Goal Seek changes one input to hit one target, Solver adjusts multiple changing cells simultaneously, subject to constraints you define.

For sales planning, Solver can find the combination of price increase and discount reduction that maximises gross margin, while keeping price change below a ceiling set by commercial policy and volume above a floor set by operations. Goal Seek cannot do this in a single run.

For straightforward models, Solver is practical. For complex multi-product, multi-channel planning models, its limitations surface quickly. That is where dedicated planning platforms offer a meaningful advantage.

To see how these tools work in practice, consider a hypothetical pharmaceutical company planning its sales strategy for the coming year based on prior year actuals. Management has set a target: increase gross margin by 2%. The commercial levers available to the planning team are product price adjustments and promotional discount rates.

The team wants to model three things:

1. How a 4% price increase on selected products affects gross margin.
2. How a 3% reduction in client promo discounts affects gross margin.
3. How the combination of both adjustments compares to either action in isolation.

The model is built in four stages. First, client, product, and transaction data are consolidated into a single Model Calculations tab where all key formulas live. Second, pivot tables and slicers are used to create a navigable sales report that allows drill-down by client, brand, and product category. Third, a Scenario Calculations tab is added with columns for what-if prices and discounts alongside the actual prior year values. Fourth, Excel’s Scenario Manager is used to define the three scenarios, and a Scenario Summary table is generated to compare the outcomes side by side.

The result is a working what-if dashboard where the planning team can select each scenario and read the gross margin impact directly. However, the scenario summary table is a static snapshot. Every new scenario requires the process to be repeated.

Excel is a powerful general-purpose tool, but it has structural limitations in sales planning contexts.

1. Every change to a scenario requires manual variable updates, formula refreshes, and pivot table rebuilds. In a model with multiple products, clients, and channels, this compounds quickly.
2. Version control is a persistent problem. When multiple planners work on the same model, maintaining a single source of truth requires discipline that spreadsheets do not enforce.
3. Gross margin percentages in pivot tables aggregate incorrectly by default. Fixing this requires additional calculation layers that add complexity and fragility to the model.
4. As model complexity grows, the connections between tabs become harder to audit. A formula error in one sheet can propagate silently through the analysis.

The model described in this article took three days to build in its simplified form. Adding additional products, channels, or scenario dimensions multiplies the build time and the maintenance burden.

Farseer was designed for exactly this type of analysis. For the sales planning model described in this article, which covers price and discount scenario analysis across a product portfolio, the same three scenarios can be built and run in under three hours, compared to three days in Excel.

The key differences are structural. In Farseer, scenario inputs are connected directly to the underlying data model and dashboard. When a variable changes, every output updates automatically. There is no pivot table to refresh, no formula to trace, no version to reconcile.

Collaboration works differently too. All users access the same live model and see the same data instantly. There is no risk of someone working from a version that was emailed two days ago.

The drill-down capability is also fundamentally different. In Excel, drilling from a summary to a client-level or SKU-level view requires a separate pivot table or a manual filter. In Farseer, slicers are connected to the model, so the same what-if scenario can be viewed at any level of the hierarchy without rebuilding the analysis.

Modeling too many scenarios. More scenarios do not mean better decisions. Three to five well-constructed scenarios with clear rationale are more useful than twenty permutations. When every variable is tested across every possible value, the output becomes noise rather than insight.

Confusing sensitivity with scenario analysis. Sensitivity analysis changes one variable at a time. Scenario analysis changes several simultaneously. Using sensitivity analysis for a question that requires a multi-variable answer understates the true range of outcomes.

Anchoring best and worst cases too close to the base. Analysts tend to build alternative scenarios as small variations around the base case. In volatile markets, the worst case often needs to be more severe than feels comfortable. The value of what-if analysis is precisely in testing outcomes that feel unlikely but are genuinely possible.

Ignoring model dependencies. Changing a price assumption without updating associated volume, cost, or margin formulas produces an internally inconsistent model. Always verify that every output dependent on a changed input has been recalculated correctly.

Presenting scenarios without action plans. A scenario is not useful unless paired with a response plan. For each scenario, document the trigger conditions: when does this scenario become the working plan, and what actions are required. Without this step, what-if analysis remains an analytical exercise rather than a planning tool.

A recent survey found that 42% of CFOs are now conducting high-frequency proactive scenario planning in response to economic uncertainty. This shift is being enabled partly by AI-assisted planning tools that reduce the time required to build and update scenario models.

The traditional Excel cycle involves adjusting inputs, refreshing pivots, and rebuilding reports. Each iteration takes time. AI-assisted platforms reduce this by automating the recalculation layer and surfacing which variables have the highest sensitivity to the target metric, so planners know where to focus their modelling effort.

For sales planning specifically, AI tools can ingest CRM pipeline data and flag when deal velocity or win rates shift enough to trigger a scenario update, rather than waiting for the monthly planning cycle. Research from Salesforce shows that 83% of B2B sales teams using AI experienced revenue growth in 2024, compared to 66% of teams without AI. The gap is widening, and what-if analysis is one of the planning disciplines where AI adds the most immediate value.