What-if analysis: a beginner's guide to scenarios + goal seek in Excel

What is a what-if analysis?

A what-if analysis, as the name suggests, is a way to consider and react to future changes in a business plan. It creates a baseline financial model and allows a planner to manipulate variables to show how it changes the outcome.

We use what-ifs in daily life without even realizing it. 

We might look at the weather and think, “It’s warm today, but there’s a good chance of rain.” The possibility leads us to choose an umbrella over a raincoat to stay cool and dry. 

What-if analysis in financial modeling works on the same basic principles, using concrete numbers to model possible outcomes. In a fast-changing business environment, considering possible changes to your current assumptions helps you create accurate forecasting and build contingency plans. 

How does what-if analysis work?

What-if analyses look at potential positive or negative changes to one or more variables (also called assumptions). These analyses can consider a single, small change, or major fluctuations across multiple variables simultaneously:

• Total revenue
• Churn
• Total expenses
• Cost of goods sold (COGS)
• Earnings per share
• Taxes
• Inflation
• Liability costs
• Interest rates

Types of what-if analysis

There are two methods of performing a what-if analysis: sensitivity analysis and scenario analysis: 

Sensitivity analysis examines changes to one variable in a plan. For instance, the modeler might ask, “What would happen if we trimmed our materials spending by 5%?” This single change affects many other variables, such as COGS, profit, and revenue. 

Sensitivity analysis can be used to get the business closer to a specific goal or understand how a likely change will affect budget and annual planning.  

Scenario analysis shows the effects of multiple, simultaneous changes. It looks at the influence of market conditions as a whole. Scenario changes may be based on likely or known changes (such as upcoming changes in tax rates or service prices) or unknown market variables. 

Scenario analysis can be used to explore best-case and worst-case scenarios (also called upside and downside cases), or fluctuations featuring outlier assumption changes across many variables.

So which what-if analysis tools are the best? It depends on the purpose of your query, plus the complexity and time horizon you’re examining. 

• For simple analysis, such as asking, “If we cut expenses by 15%, will it sufficiently extend our runway?” a sensitivity analysis may work.
• For longer-term or more complex queries, for instance, using what-if analysis in financial forecasting activities, scenario planning will likely provide better insights.

What are the advantages of what-if analysis?

While no one has a crystal ball, what-if and scenario analysis give your FP&A team valuable insight to run the business, no matter what the future holds.

What-if analysis creates clarity in your business planning by helping teams visualize the effects of change:

• Highlights the impact of key variables on the goals of the business.
• Compares options for reaching a set business objective under changing conditions.
• Promotes creative thinking in the FP&A process by surfacing useful insights.
• Shows the relationship between different components of the business plan.

Benefits of what-if analysis 

What-if analysis makes your planning more resilient. It creates more visibility in your FP&A practice which makes it easier for you to adapt as new situations arise. By conducting regular sensitivity analysis and scenario planning, you’ll become better informed about the financial drivers of the business. 

You’ll know where to prioritize efforts as things change and how new data will impact the business. 

Competitive advantage: Companies that build contingency plans recover from impacts faster than those that don’t. By considering potential future events, you know how to react as situations change. This agility can help forward-thinking companies get ahead of issues faster, avoid costs, and mitigate negative impacts better than their competition. 

Better risk management: Understanding the financial impacts of change reduces key areas of operational risk. It allows companies to:

• Effectively estimate cash needed in capital reserve
• Navigate supply chain issues while retaining value
• Ride out fluxes in commodities prices, stocks, etc.
• Maintain salaries and prevent turnover during downturns

All of these operational safeguards reduce the risk profile for businesses, allowing them to continue nominal operations even if worst-case scenarios come to pass.

Stronger decision-making: When you understand what’s possible, you can decide on what’s preferable. Scenario planning allows you to look at all available courses of action. From there, you can decide on the course that offers the best result while considering associated costs, risks, and timelines. 

How to do what if analysis in Excel

Excel gives you options for creating what-if analysis right within the software. It offers several options for creating scenarios and understanding the impacts of change.

What are the steps in what-if analysis?

At a high level, what-if analysis is pretty simple.

1. Isolate your variables.
2. Use the Goal Seek function to determine which input value outputs your desired target value.

From there, you look at the range of possible input values that give you the future values that create the best outcome for you.

...Alternatively, now you know which input values help you prevent the future you want to avoid.

Which what-if analysis tool is the best option? It depends on the number of changes you want to make and the complexity of the question you’re trying to answer.

If you're interested in only one or two variables, you should use a data table.

On the other hand, if you're interested in more than two variables, then you should use the scenario manager.

(For Google Sheets users: What-if analysis can be performed with the Goal Seek for Sheets add-on published by Google.)

You can find options for performing what-if analysis in the Data tab of Excel. Let’s start with Goal Seek. 

Goal Seek analysis

The Goal Seek feature lets you identify a specific goal (say, a certain revenue number or profit margin). You can adjust a variable to see if the goal in question is achievable.

This is how to create a Goal Seek what-if analysis in Excel:

From the Data Tab, select What-If Analysis, then Goal Seek.

Determine what needs to change. In this example, a business owner wants to increase the profit margin on a product from 60% to 70%. 

In the Goal Seek menu, select the goal you want to seek. In this case, the current profit margin of 60%. In the “To value” field, enter your new goal of 70% (expressed as a decimal).

Select the cell you will change to affect the goal. Here, we will adjust the cost per item. Select “OK” to run your goal seek analysis.

Excel will automatically run the goal seek analysis and (if possible) present a result. As seen below, the analysis results in a required cost decrease of $5 per item to achieve the desired margin of 70%.

Scenario manager

What if your calculations are a bit more complex? Or if there are several avenues to consider. The what-if Scenario Analysis function can help you make decisions by showing the impact of changing one or more variables.

Let’s go back to our profit margin example. There are a few ways to improve the profit margin of an item:

1. Increase the price
2. Decrease labor costs
3. Decrease materials costs

The scenario manager allows you to see the impact of these changes on your profit margin. 

1. Start by selecting Scenario Manager from the What-if Analysis tool under the Data tab.

2. Create a new scenario by selecting the + symbol.

3. Begin entering the variables for your scenario. You can change multiple variables by selecting cells while holding down the ALT key.

4. Once selected, enter the desired change for each cell. Hit “OK” when completed to return to the scenarios list.

5. To view the results of your scenarios, select the desired scenario from the menu and hit “show” to display the changes in your table. Here, we show the “Increase Price/Reduce Costs” scenario to understand how taking all three possible actions impacts our profit margin.

Scenario Summary Report

To view all of your scenarios in a data table for easy comparison, select “Summary” from the Scenario Manager dialog box. Select the result cells you’d like to see displayed (in this case, we chose Total Revenue, Total Cost, Profit, and Profit Margin). 

A scenario summary report will populate in a new tab of your workbook. You can work further within this table, arrange columns, etc. and even create different scenarios from it to further your analysis.

What-if analysis with data tables

If you're interested in a single condition changing (or if you have less advanced business models to work from), you can perform a what-if analysis with one or two variables in a data table.

A data table lets you see all the outcomes in a single place. So if you have multiple formulas that all rely on the same variable, a data table lets you examine the range of possible outcomes at a glance.

How to create a one-variable data table (with a formula)

First, type the list of values you want to substitute in the input cell in a column. (You can also type them in a row.) You should leave a few empty rows and columns on either side.

Next, type the formula in the cell one row above and to the right.

(If you chose to type your variables in a row instead, you should enter this formula one cell to the left and down.)

Next, select the range of cells containing the formulas and values you want to substitute.

Click What-If Analysis and then Data Table.

Finally, enter the cell reference for the input value in the Column input cell box. (And if you wrote your variables in a row, use the Row input cell box.)

Congrats! You just created a one-variable data table.

How to create a two-variable data table in Excel (with a formula)

You'll want to follow the exact same steps as above, but this time you'll fill out both the row and the column.

Additionally, you need a formula that has two input values.

Power your what-if analysis with spreadsheet-native FP&A

Now you know everything you need to get started with what-if analysis.

You know how to create a data table with one or two variables, how to work with the scenario manager, and how to find desirable input values with goal seek.

But you might be wondering...how do you get your actuals into Excel in the first place?

That's one of the things world-class FP&A teams use Cube for. Cube is the first CFO-founded spreadsheet-native FP&A platform that connects with your source systems (like your ERP), cleans and organizes your data, and lets you easily manipulate it in Microsoft Excel or Google Sheets.

Interested? Request a free, quick demo and we'll show you how Cube can help you keep what you love about spreadsheets and automate what you don't.

(And the best part? Most implementations finish within two weeks.)

Request a demo here: