How to Do What-if Analysis in Excel: An Expert Guide

Forecasting is difficult, but critical. Excel allows you to rigorously test assumptions and quantify outcomes, rather than relying on intuition.

What you will learn

1. The methods: How to execute Scenarios, Goal Seek, and Data Tables
2. The application: Practical use cases like optimising sales forecasts and resource allocation
3. The result: Evidence-based decisions that advance your organisation

What-If Analysis allows you to change variables to instantly see the impact on your data.

How it works: You adjust input values in Excel to simulate different outcomes. It replaces guesswork with data-driven decision-making, helping you understand exactly how specific changes affect your final results.

Plan better, risk less. What-If Analysis equips you to forecast and strategise effectively across key performance indicators (KPIs). You mitigate risks and uncover opportunities using data, rather than relying on assumptions.

Real-world examples

• Sales teams: Test how adding headcount, boosting ad spend, or entering new markets impacts revenue
• Operations managers: Adjust resource allocation based on fluctuating demand forecasts

The bottom line: You get evidence-backed proposals and reduced uncertainty.

Excel offers three options for forecasting. Choose the one that matches your data complexity:

1. Scenarios: Test multiple versions of the future simultaneously.
   • Best for: Complex planning (like territory mapping) where you must weigh interconnected factors
2. Goal Seek: Work backward from a target.
   • Best for: Determining the exact sales figures needed to hit a specific revenue objective
3. Data Tables: Automate sensitivity testing across a range of possibilities.
   • Best for: Comparing metrics side-by-side, such as customer volume changes under different marketing strategies

For complex scenarios spanning multiple factors, manual Excel work hits a ceiling leverage our scenario planning tool to dramatically improve your throughput. For now, let's dive into the specifics of the three what-if analysis methods.

Let's discuss in detail the three ways you can perform a what-if Analysis in Excel.

1. Scenarios

Scenario Manager lets you toggle between different sets of data—like a "Best Case" vs. "Worst Case" budget—within a single worksheet.

The jargon

• Changing cells: The inputs you manipulate (e.g., revenue, expenses)
• Result cell: The outcome that updates automatically (e.g., profit)

How to do it

1. Click Data > What-If Analysis > Scenario Manager
2. Click Add and name your scenario (e.g., "Conservative Budget")
3. Select the Changing cells you want to adjust
4. Enter the specific values for that scenario
5. Repeat to create additional scenarios
6. Click Show to instantly switch between them

Click Summary to generate a report that compares all your scenarios side-by-side in a new table.

Watch out: The Summary Report is static. If you update your data later, you must generate a new report.

Pro tip: You can merge scenarios from other workbooks, allowing you to combine assumptions from different team members into one master sheet.

2. Goal seek: work backward from a target

Goal Seek is for when you know the answer you want (e.g., a specific monthly payment) but need to find the input required to get there.

The limitation: It only solves for one variable at a time.

How to do it

1. Select the cell containing your formula
2. Click Data > What-If Analysis > Goal Seek
3. Set cell: The cell with your formula
4. To value: The result you want to achieve (e.g., £500)
5. By changing cell: The single input Excel can adjust (e.g., Interest Rate)
6. Click OK

Real-world example: You know your loan amount and the max monthly payment you can afford ($500). You leave the interest rate blank. Goal Seek calculates exactly what interest rate you must negotiate to keep payments at $500.

Pro tip: Need to solve for more than one variable (e.g., changing both loan amount and term length)? Stop using goal Seek and use the solver add-in.

3. Data tables

Data Tables allow you to visualize how changing one or two variables affects a formula instantly.

Use this when: You want to find the "sweet spot" between two factors, like price vs. sales Volume, without running dozens of individual scenarios.

How to do it

1. Set up a grid with row inputs (e.g., different prices) and column inputs (e.g., different volumes)
2. Reference your formula (e.g., Revenue) in the top-left corner of the grid
3. Highlight the entire table range
4. Click Data > What-If Analysis > Data Table
5. Specify your Row and Column input cells

Excel populates the entire grid instantly. You can scan rows and columns to see exactly which combination yields the highest profit. The table is dynamic. Update your base assumptions, and every cell in the grid recalculates automatically, ensuring your strategy is always current.

Limitation: You are restricted to testing a maximum of two variables at once.

For territorial or route planning, manual spreadsheets break down. You cannot juggle rep workloads, travel distances, and customer classifications in a static grid.

The solution: eSpatial automates the chaos using integrated mapping intelligence.

At eSpatial, our integrated mapping solutions allow territory optimization through automated mapping and intelligence tools. We turn days of manual work into minutes of automated insight.

The capabilities

• Optimise layouts: Algorithms balance territories automatically, whether you are designing fresh or adapting to change
• Connect data: Tightly integrates with your CRM/ERP to keep KPIs real-time
• Drive action: Move from theoretical "what-ifs" to precise operational execution instantly

The bottom line: Stop manually calculating potential outcomes. Contact us to unlock competitive advantage through precise territory mapping.