One of the primary purposes of budgeting and creating financial plans is to attempt to predict future outcomes based on assumptions being made in the present.

What-if analysis takes this a step further and attempts to perform analytics based on changing certain inputs in a financial model to attempt to predict outcomes under very specific circumstances. 

The technique is often deployed in conjunction with scenario planning, which is the process of analyzing potential scenarios and how they might impact future outcomes.

This is most commonly used by business leaders to help make decisions using “best-case” and “worst-case” scenarios as a guide. 

What Is What-If Analysis?

As the name implies, what-if analysis is used in financial modeling to assess possible outcomes due to certain events that might be favorable or unfavorable to a business.

This is most commonly performed in conjunction with various scenarios. Scenarios are first created based on possible business impacts. For example, a steep decline in demand, a supply chain disruption, or the outcome of a pending litigation matter.

At its core, what-if analysis is used to create and compare different financial plans and develop alternatives that can be used when a given condition changes. 

Whatever the various scenarios might be, what-if analysis attempts to quantify the impact of the various scenarios.

Therefore, what-if analysis is a technique often used in scenario planning whereby the outcomes of the what-if analysis become the analytical data points used to assess “best-case,” “base-case,” and “worst-case” scenarios. 

Why Is What-If Analysis Important?

Among the various benefits of performing what-if analysis, there are some very practical applications.

First, the outcomes help to develop different budgets based on changes in revenue as a result of some external force.

This allows FP&A analysts to create contingency plans that management can utilize under certain circumstances that help keep the organization moving towards its goals. 

Second, it allows business leaders to make decisions with better information at their disposal. 

What-if analysis can be used to see how changes in operating plans might impact future outcomes. Similarly, it can be used in times of uncertainty when the business environment is undergoing changes that might be out of management’s control. 

Finally, because what-if analysis is a thoughtful approach to predict various outcomes, it forces management to examine a number of facts and possible issues that impact the organization.

This process helps to improve visibility into the outcome of certain projects or initiatives, which creates a feedback loop that helps to progress projects toward the desired outcome.  

How To Perform What-If Analysis

Historically, financial analysts had to perform what-if analysis “by hand,” building large excel-based models that had various inputs changed. Microsoft Excel has since introduced the “what-if” suite of functions that help to perform what-if analysis a little more seamlessly. 

There are three primary functions under the what-if suite in Excel: Scenario Manager, Goal Seek, and Data Table. Each of these tools is used to assist in the execution of what-if analysis in a different way.

Data Tables and Scenarios take financial model’s inputs and project them forward to attempt to identify future outcomes, while Goal Seek is used as a backward looking tool that identifies what input is required to produce a desired outcome. 

How to Use Scenario Manager in Excel

Scenarios are a set of values that Excel can substitute and change automatically within a set of defined cells in a worksheet. This allows you to store multiple iterations of datasets in the same cells, which can then be compared or viewed individually.

  1. Click “what-if” drop down menu on the data tab in Excel

  2. Select “Scenario Manager”

  3. Click Add

  4. Name your scenario

  5. Select the cells in the financial model that you wish to toggle

  6. Add a comment if necessary

  7. Click “OK” and a new dialogue box will appear

  8. You will be prompted to enter a value for each of the cells you selected in Step 5. These are the variables you will change in the model to do your what-if analysis. For example, if you wish to test a drop in sales growth and your model input is a percentage of prior year’s sales, then enter a percentage value. 

  9. Click “OK”

  10. You can now add additional Scenarios by simply clicking “add.” The selected input cells will remain. 

How to use Data Table in Excel for What-If analysis

Data Table is a useful command in excel for situations where you have a formula that is dependent on multiple inputs.

In these cases, when performing what-if analysis, you might want to see how changing one, or both, inputs in the formula impacts the result. This is useful for comparing multiple outcomes at once

  1. Organize your data by setting any cell equal to the cell that contains the formula you would like to test. 
  2. Next, create a row of input values you wish to test. You can test in increments and make the inputs formula-driven to add equal increments.
  3. In a column, add the next input value you wish to test, similar to step two.
  4. On the data ribbon in Excel, select what-if analysis and navigate to Data Table.
  5. A dialogue will open and prompt you to select the row of inputs and the column of inputs.
  6. Select “OK.”
  7. A data table will be created. You can edit each variable independently to see how they interact and find the optimal mix of independent variables. 

How to use Goal Seek in Excel for What-If Analysis

Goal Seek is helpful in cases where you have a desired outcome and wish to know the inputs needed to achieve the desired outcome. 

  1. Navigate to the Data ribbon in Excel
  2. Select the What-If drop down
  3. Click “Goal Seek”
  4. The “Set Cell” command should be set to the cell that is calculating the output you wish to test.
  5. “To Value” is the value you wish the “Set Cell” formula to calculate.
  6. “By Changing Cell” is the independent variable you wish to change to achieve the desired outcome you set in step 5.
  7. Click “OK.”

Using Datarails to Build Your What-If Analysis

Every finance department knows how tedious performing what-if analysis can be. Regardless of the budgeting approach your organization adopts, it requires big data to ensure accuracy, timely execution, and of course, monitoring.

Datarails’ FP&A solution is an enhanced data management tool that can help your team create and monitor budgets faster and more accurately than ever before.

By replacing spreadsheets with real-time data and integrating fragmented workbooks and data sources into one centralized location, you can work in the comfort of excel with the support of a much more sophisticated data management system behind you.

This takes budgeting from time-consuming to rewarding.