what if analysis
Scenario Planning

What-if Analysis in Sales Planning [How-to + Spreadsheet Template Download]

8 mins

What-if analysis in sales planning helps businesses explore scenarios and their potential outcomes. By assessing market changes or pricing adjustments, it enables data-driven decisions to improve your sales strategy. Below, you will learn how to implement what-if analysis effectively.

What Is a What-if Analysis?

What-if analysis is one of the most common practices in FP&A – financial planning and analytics. Used by decision-makers across all industries, it is often mystified and overcomplicated. But in fact, there is nothing too complicated about the concept.

 

What-if analysis is a risk assessment method in which you change inputs and investigate how the change affects the output.
 

Pretty simple, right?

Why Use What-if Analyses in Sales Planning?

You would probably check out a flight simulation game before piloting your first ACTUAL airplane. Because you want to test out how it would look without the actual dangers of crashing. However, just like there are differences between a flight simulator and flying a plane in real life, there are differences between making hypotheses and testing them in real life.

 

That is what what-if analysis in a business context roughly is: you do a thought experiment and try to analyze the results of the business initiative before actually flying up with unsuspecting passengers (other stakeholders in this case). You try to find out WHAT would happen IF…

What-if Analysis and Financial Modeling

Let us try and distinguish the two similar, most commonly used concepts: sensitivity analysis and scenarios. Different sources sometimes use the terms “what-if scenario”; “what-if analysis”; “scenario analysis” and “sensitivity analysis” interchangeably. They are close but not synonyms.

Sensitivity Analysis

This one is pretty simple: sensitivity analysis is changing the one (independent) variable and investigating how this affects the other (dependent) variable in the model for the same period. It is often also called a what-if analysis or simulation analysis.

sensitivity-analysis or what-if analysis in sales planning

Scenarios

Scenarios are a bit more complex to execute but not too hard to explain. In scenario analysis, you change one or more variables and investigate how the changes affect multiple dependent variables in the model.

scenario-analysis

What-if Analysis in Sales Planning in Excel - Definitions

As far as what-if analyses go, Excel has several built-in tools that can be extremely useful if used correctly. These tools give users a way to change the input values without changing the data itself. They are Scenarios, Data tables, Goal seek.

Scenarios

The scenario tool in Excel is used to store and show several versions of data in the same cells. Up to 32 to be more specific. Planners use base, best, and worst-case scenarios most commonly, though. According to Microsoft, a Scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet.

scenario-summary

Data Tables

The data tables tool is used when you have a formula with one or two variables and want to see all possible outcomes for different combinations of each variable. You can use one or two variables to create a data table what-if analysis.

 
Example: You plan to save $2000 at a 5% monthly compound interest rate and want a calculator to track your savings over time. The variables you can change are the initial amount you put in the account and the number of years you will save. With Data tables, you can generate a table that tells you your total savings balance if you change the initial amount you will put in your account.
compund-interest-calculator

It is possible to create a similar table using the time variable, but also using both time and the initial amount:

investment-table-time-variable

Goal Seek

Goal Seek is used if you know the result you would like to get from a formula, but you want to calculate its inputs. In other words, you backward engineer the formula’s inputs.

goal-seek

Example: A demand planner wants to generate $150 000 by selling four products in the portfolio. The Goal Seek tool can help them determine how much Product 4 they need to sell to get to the wanted revenue numbers if they already know the amounts for Products 1, 2, and 3.

 

The issue with Goal Seek is that it only supports one input and one output cell. This makes it somewhat limiting in solving more complex problems.

What-if Analysis in Excel - How to?

To see how planners use what-if analysis in sales planning, we created a simplified financial model of a hypothetical pharmaceutical company. Download it below for details and instructions.

In our hypothetical company, demand planners are making a 2022 sales strategy based on 2021 sales data. Management has directed them to increase the gross margin by 2%. Given the company’s operations, the planners can only adjust product prices and promo discounts.

Analyzing the Impact of Price and Discount Changes

They aim to explore:

  • How a 4% price increase for some products impacts the gross margin.
  • How a 3% reduction in discounts for some clients affects the gross margin.
  • How to combine these adjustments for maximum gross margin.

The sales report is central to the model, with all what-if analyses built around it:

pharma-company-sales-report

You can see the sales numbers for 2021 (the previous year). This report is built using slicers and pivot tables, so the planners can drill down and investigate the numbers for each client, brand, and product category in detail.

The magic happens in the What-if analysis tab. Demand planners can use Excel’s scenario manager tool to play with different combinations of price increases and discount decreases to get to the desired gross margin increase.

what-if-analysis in sales planning Excel template

This is the final result of our what-if analysis process. Here is how we got to it:

 

Combining Data for Key Model Calculations

First, we created a tab where we combined the client, product data, and transactions data and made some calculations to get a detailed 2021 sales report – Model Calculations tab. All crucial calculations for the entire model are done here.

farseer-model-calculations

For practical reasons, we used pivot tables and slicers to make a more user-friendly sales report with all the calculations needed to make a what-if analysis. It is easier to navigate the data and drill down into specific clients, products, and product segments.

 
After making the sales report for 2021, we used it as a base for what-if analysis. We know that two variables that we’re going to change are the price change and promo discounts, so we created a partly interactive what-if analysis tab with the comparison of actual sales from 2021 and the cells where we can manipulate the two independent variables. Here’s how it looked:
farseer-what-if-analysis in sales planning

To designate the products and clients whose prices we are going to tweak in the what-if analysis, we created two additional tabs for SKUs and clients, respectively. Download the spreadsheet to learn more about these two tabs.

 
Next, we created a Scenario sales report but with calculated values for the two variables, which we’ll use in the what-if analysis. We copied the sales reports from 2021 and added several calculated columns with what-if prices and discounts:
farseer-model-scenario-calculations

Setting Up Scenarios with Excel’s Scenario Manager

We know that our scenarios are 4% product price increase, 3% client discount decrease, and the combination of the two. So we go to Data>What-if analysis>Scenario manager to define the scenarios:

scenario-manager

Next, we define the price increase scenario. The changing cell is the cell we need to use to define the price increase percentage:

edit-scenario

After this, we need to set the value of 4% for our first scenario, like this:

And we do the same for the second scenario:

In the third scenario we change 2 variables simultaneously:

edit-scenario-prevent-changes

Here is the final result with all 3 scenarios defined:

farseer-what-if-analysis in sales planning

Now we can select each scenario and show them in the cells.

We can compare the 2021 actuals and 2022 scenarios in the sales report table.
The only thing left to do is to show the scenario results in the Scenario Summary table:

Comparing Scenario Results with Actuals

We can compare the 2021 actuals and 2022 scenarios in the sales report table.

The only thing left to do is to show the scenario results in the Scenario Summary table:
scenario-summary-farseer

Our hypothetical demand planners can use the what-if dashboard to tweak the variables as they please, depending on the business goals and conditions. The downside of this approach is that the scenario summary reports aren’t interactive and need to be generated manually for every new scenario.

Problems With Excel What-if Analysis in Sales Planning

Although Excel is a powerful tool, there are many disadvantages in using it as only financial planning and what-if analysis tool, compared to dedicated financial planning tools. Excel can be a lot of work – users need to edit too many variables manually to get results. The model we built is a simplified example of what companies use. Even simplified, it is not easy to compare different versions of data using it – we would “drown” in pivot tables trying to make best and worst-case versions of plan values in the model. The work would double if we tried to make changes in one of the versions on the go, as planners often need to do. Every change in the model requires multiple re-calculations and pivot tables and data slicer refreshes.

Our modeler spent three days building the model and scenario analyses. In his own words, it was sometimes “nerve-racking” trying to connect everything and make it presentable and intuitive to use. We did our best to make the reports and tables as interactive as possible. However, the technical capabilities of the Excel surroundings made it difficult.

 
Also, when working with complex models in Excel, you need to be ready to face some illogical situations, because in our sales report table, Excel automatically adds all gross margin percentages. We can override that by building additional tables and calculations, adding the absolute values, and transforming them into percentages, but it is simply frustrating.

What-if Analysis in Sales Planning - in Farseer

There’s a more efficient way to perform what-if analysis in sales planning. We designed Farseer for this exact purpose. Unlike Excel, where setting up a full financial model with interactive dashboards, slicers, and what-if analyses can take days, Farseer completes the process in under three hours.

Faster work with automation

Farseer enables users to automate master data import and mapping. They can model using custom templates and advanced formulas which cannot be deleted. There is also no need to use VLOOKUP and similar functions because Farseer has an advanced database. Most calculations are automated and happen in the background.
 
In Farseer, what-if scenarios are managed in a custom Dashboard linked to the model and database, ensuring data syncs automatically. This allows for seamless collaboration, as all users view the latest data instantly. 
 
We recreated our what-if analyses from the spreadsheet in the Farseer dashboard:
farseer-sales-report-gif

Here is the first scenario, 4% product price increase:

farseer-sales-report-change-gif

The second scenario, lowering the client promo discount:

farseer-sales-report-promo-gif

The third scenario, combining the 2 variables:

farseer-sales-report-price-change-gif

As you can see, as soon as we change the what-if variables, the sales report table and charts automatically refresh. Also, by using slicers, it is easy to drill down to any client and SKU, and see how the scenario affected them:

farseer-sales-report-brand-change-gif

Every element on the Dashboard is interactive and easily customizable. Users can add new columns to the Sales report table without having to connect them to the model. Unlike in Excel, Farseer charts refresh because they sync with the Farseer database automatically, and the engine does the work quickly in the background.

Conclusion

What-if analysis in sales planning lets you play around with different scenarios—like tweaking prices or adjusting targets—to see how they might impact your sales. It’s a simple way to spot potential risks and opportunities, so you can make smarter decisions, without the guesswork. 

Once you’ve mastered sales planning, then discover how to take control of your stock levels and improve efficiency with data-driven inventory replenishment! 

FROM THE BLOG

Related articles

primary limitations of the balance sheet, what they are and how to overcome them

What Are Primary Limitations of the Balance Sheet (and How to Overcome Them)

07 November 2024
cash ratio definition

Cash Ratio Definition – A Simple Guide to Liquidity + Examples

06 November 2024
liquidity and solvency ratios

Liquidity and Solvency Ratios – Metrics for Your Business’ Survival

05 November 2024