Running a successful business requires seeing around corners and anticipating the unexpected. Forecasting the future isn't easy, but contemplating various possibilities is critical for smart planning.
Excel gives users powerful options for rigorously testing assumptions and quantifying outcomes. This article will guide you on how to do a what-if analysis in Excel, exploring the three main methods: Scenarios, Goal Seek, and Data Tables.
You'll learn how each type works through practical use cases, such as optimizing sales forecasts or resource allocation. By becoming familiar with these tools, you can develop more robust projections and make evidence-based decisions to advance your organization's objectives.
What is a what-if analysis?
A what-if Analysis is a process that enables you to analyze the potential impact of changing one or more variables on the outcome of a formula or a set of data. When performing a what-if analysis, Excel and other planning tools allow you to manipulate input values to see how these changes affect the final result. This process of manipulating values and observing their simulated impact supports more informed and data-driven decision-making.
What is the goal of a what-if analysis?
The primary goal of a what-if analysis is to equip businesses with the ability to plan, forecast, and strategize more effectively. Considering multiple simulated scenarios provides a dynamic perspective across different business KPIs. You can mitigate risks, identify hidden opportunities, and make decisions based on solid data rather than assumptions alone.
For instance, a sales team might use a what-if analysis on their annual targets. They could scrutinize the profit implications of bringing on additional headcount, boosting ad spend, or entering new markets. An operations manager might evaluate resource allocation under various demand forecasts.
The goal in each case is the same — to make evidence-backed proposals and minimize uncertainty.
The three types of what-if analysis tools
When performing an extensive what-if analysis, choosing the right tool for the job is essential. As mentioned earlier, Excel provides three main options:
- Scenarios: Allows testing of multiple forecasting or planning scenarios simultaneously. It’s particularly useful for mapping applications like territorial planning, which often require weighing options across many interconnected factors simultaneously.
- Goal Seek: Useful for determining unknown variables needed to achieve objectives, like calculating sales targets required in new territories.
- Data Tables: Helpful in automating sensitivity testing across a range of known possibilities for mapping-related metrics, such as predicted customer volumes under different marketing approaches. Data tables efficiently generate result grids for comparing territory layouts or route plans side-by-side.
For the most comprehensive scenarios spanning multiple interrelated factors, you can 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.
How to do what-if Analysis in Excel: 3 ways
Let's discuss in detail the three ways you can perform a what-if Analysis in Excel.
1. Scenarios
Excel's Scenario Manager is a powerful tool for creating and managing multiple scenarios within a single worksheet. This feature is particularly useful when exploring the impact of changing various input values on your desired outcome.
A fundamental concept within the Scenario Manager is the designation of "changing cells," which are the input cells that contain the values you want to manipulate to generate different scenarios. The "result cell" displays the outcome based on the values in the changing cells.
Scenarios application
Consider the following as a what-if analysis example using the Scenario Manager: you are creating a budget for your business and want to explore two scenarios: a conservative (worst-case) and an optimistic (best-case) projection. You can use the Scenario Manager to set up these two scenarios.
To get started, select the cells containing the values you want to manipulate, then navigate to the Data tab and click on "what-if Analysis." Choose "Scenario Manager" from the dropdown menu.
Next, identify the changing cells in your worksheet — perhaps the revenue, expenses, and investment figures. Then, you would specify the values for each cell under the conservative and optimistic scenarios.
When you switch between the scenarios, the result cell (e.g., the projected profit or loss) will automatically update to reflect the different set of input values.
One of the Scenario Manager's key advantages is its ability to consolidate multiple workbooks into a single scenario. If you have team members who have their own versions of the budget with unique assumptions, you can combine all of those scenarios in your master worksheet.
After modeling various possibilities through what-if scenario analysis, you can also generate a Scenario Summary Report. This report displays all the scenario information in a table, providing a concise overview of your explored possibilities. It's important to note that the Scenario Summary Report does not automatically update when you change a scenario's values. If you make any updates, you'll need to create a new summary report to reflect the changes.
2. Goal seek
Sometimes, you may know the desired result you want to achieve but not know what input value is required to reach that goal. Excel's Goal Seek feature comes into play in such cases.
Goal seek application
To use Goal Seek, first, select the cell that contains the formula you want to manipulate. Then, go to the Data tab, click on "what-if Analysis," and choose "Goal Seek" from the dropdown menu.
Let's say you plan to take out a loan and know the amount you need to borrow, the repayment period, and the maximum monthly payment you can afford. However, you need to determine the interest rate needed to meet your loan goals. That’s where Goal Seek can help.
In your worksheet, you would have cells containing the loan amount, term length, and target monthly payment. The formula in another cell would calculate the required interest rate based on these inputs.
By using Goal Seek, you can select the cell that displays the calculated interest rate and tell Excel the desired result you want to achieve (e.g., the maximum monthly payment you can afford). Excel will then determine the interest rate that would produce that target monthly amount, allowing you to identify the optimal loan terms.
It's important to note that Goal Seek is limited to working with a single variable input. If you need to determine more than one input value, such as the loan amount and the monthly payment, you should consider using the Solver add-in instead.
3. Data tables
While the Scenario Manager and Goal Seek tools are great for exploring the impact of changing one or two variables, Excel's Data Table feature allows you to analyze the effects of multiple variables on a formula or set of formulas.
Data tables application
Here’s an example of what-if analysis using Data Tables: Imagine you're running a sales forecasting model and want to understand how changes in product price and sales volume would affect your revenue and profitability. You can set up a Data Table to visualize the impact of these two variables side by side.
In your worksheet, you would have the formulas for calculating revenue and profit, with the product price and sales volume as the input values. As we've done previously, you can access the what-if analysis Data Table feature by selecting these cells and navigating to the Data Tab.
In the Data Table dialog box, you can specify the cells containing the product price and sales volume as the column and row inputs, respectively. Excel will then populate the table with the corresponding revenue and profit values for each combination of price and sales volume.
Having all this information laid out in a Data Table allows you to quickly identify the optimal pricing and sales strategies, as you can see the impact of various price-volume scenarios at a glance. The beauty of Data Table what-if analysis is that it automatically recalculates when you update the input values, ensuring you always have the most up-to-date information.
While Data Tables are limited to a maximum of two variables, they offer a highly efficient way to analyze the interplay between multiple factors and their impact on your business outcomes. Their ability to efficiently test variable interactions makes Data Tables valuable for strategic decision-making, forecasting, and scenario planning.
Mapping Solutions for Businesses from eSpatial
For territorial or route planning, it takes more than knowing how to do what-if analysis in Excel. When juggling factors like representative workloads, travel distances, and customer classifications, manually running even the most streamlined what-if scenarios becomes untenable.
At eSpatial, our integrated mapping solutions allow territory optimization through automated mapping and intelligence tools. You can effortlessly design geographies, flag constraints, and let sophisticated algorithms generate optimized layouts — whether establishing new territories or evaluating ongoing adaptations. Comparative analyses that formerly took days can be explored in minutes.
With deeper reporting and tightly integrated CRM/ERP connections, decision quality improves even further. Key leaders stay informed on KPI fluctuations and their true drivers across situations, real or imagined. Most importantly, the insights guide precise action.
So, in brief — ask those vital "what if?" questions and take control of potential outcomes. Start leveraging what-if analysis tools today through our precise territory mapping and planning solutions to unlock competitive advantage and drive informed business decision-making. Contact us to get started!
