What is what-if analysis?

The analysis builds a baseline financial model for finance teams and helps them understand how different variables can change a company’s financial performance.

The process is important for businesses because the what-if analysis drives data-driven decision-making and accurate forecasting. It’s also useful for scenario analysis and sensitivity analysis. Furthermore, it is an asset to integrated financial planning and the risk management function.

Adaptability is becoming the key feature that sets financial planning and analysis (FP&A) teams apart. At the core of this adaptability is the shift toward artificial intelligence (AI) tools and machine learning (ML) models embedded into what-if analysis.

This technology-infused approach reaches beyond what traditional what-if analysis can do and uses AI for forecasting to extract unique and valuable insights, automate functions and drive informed decisions.

At a high level, what-if analysis works by examining potential positive or negative changes to one or more variables. They are also called assumptions and can be minor changes or major fluctuations, depending on the situation.

Some of these variables include total revenue, churn, total expenses, cost of goods sold (COGS), taxes, inflation, liabilities and interest rates. Modern organizations must consider these factors and more when developing a business plan.

A what-if analysis is done in a Microsoft Excel workbook, typically by navigating to the Data tab and selecting the appropriate tool from the what-if analysis drop-down menu.

Modern FP&A tools offer direct Excel spreadsheet integration, making it easy for organizations to clean and organize their data and connect with existing systems, such as enterprise resource planning (ERP) systems. These tools can also offer AI in financial modeling functions and finance automation tools to streamline the analysis process.

From there, finance teams can choose from various what-if analysis tools to create scenarios:

• Scenario manager: This function, accessible through a dialog box, enables users to create and save multiple sets of values (scenarios) and compare their outcomes. The scenario manager creates different scenarios without having to manually retype different values or side-by-side comparisons of best-case and worst-case scenarios.

• Goal Seek: This what-if analysis tool is a reverse calculation of a formula to find the input value required for a changing cell to achieve a specific outcome. For instance, if the target value in the result cells is USD 50,000 in profit, the finance team can use Goal Seek to determine the input needed. The input can also represent how many units it must sell to reach the target.

• Data tables: It is a structured arrangement of cells intersecting at rows and columns, often requiring a specific row input cell or column input cell to function. This tool is designed to store and display data in a uniform way. Finance teams can create multiple data tables with different input values through a specific cell reference to examine how those slight changes will impact a financial model.

There are two primary methods of what-if analysis: scenario analysis and sensitivity analysis.

Other specific approaches include Goal Seek analysis, simulation analysis and scenario planning.

The scenario analysis is most useful when evaluating the effects of multiple changes and unknown market variables. This process evaluates the impact of potential events or scenarios that can occur in the future. Scenario changes might include shifts in market conditions, changes in tax rates or increases in service costs.

In financial modeling, scenario analysis is used to estimate changes in business value and cash flow. The scenarios are typically divided into best-case, worst-case and base-case. Finance teams use this analysis to understand possible outcomes and evaluate wanted results.

A sensitivity analysis examines how independent variables affect a specific dependent variable under specific conditions. The approach is best suited for an organization wanting to focus on a single goal and how a single variable might affect the overall budget or annual financial goal.

Financial analysts will use this approach within defined boundaries determined by the input variables and evaluate its impact on an organization’s profit margin. Data analysis in Excel is a powerful tool for organizations looking to add credibility to their financial models.

A successful what-if analysis depends on strong execution and collaboration. While the process can differ slightly from one organization to the next, this step-by-step guide can be a good starting point.

A what-if analysis starts by identifying the factors most impactful to the organization’s bottom line.

Consult chief financial officers (CFOs) and other leaders during this beginning stage to provide insight into which factors are most important. Finance teams can use these factors to create what-if scenarios and begin developing an analysis strategy.

Most analyses begin with a baseline for comparison. Organizations must collect historical data and consolidate information on the overall business strategy before conducting the what-if analysis.

For instance, if the focus is on employee headcount, analysts will need to gather and organize this data accordingly. This step can be time-consuming and resource-intensive, but it’s an important stage of the process.

After establishing the baseline and deciding the factors, prioritize the variables with the most influence on financial outcomes.

Sometimes, new data can deemphasize certain factors, making them less relevant to the analysis. A way to avoid this situation is to use qualitative and quantitative approaches to determine the importance of the factors.

Finance teams must ask themselves questions like ‘Is there a better factor to represent the hypothesis?’ and ‘What is the goal in analyzing this factor?’.

After choosing the most relevant and important factors, the finance team is ready to build the what-if analysis. The exact next steps will depend on which FP&A tools are being used and whether it’s a scenario analysis or a sensitivity analysis.

For a scenario analysis, turn the factors into a scenario summary. Then, build a way to simulate and track the impact of the scenarios on the business-related variables. Most organizations use Excel for this step and if possible, integrate it directly into the FP&A software.

A sensitivity analysis follows similar steps but is focused on a single factor. For both, finance teams must build a plan to review the results and potential errors.

A good place to start the what-if analysis is by setting three different scenarios with different assumption sets.

For example, best-case, worst-case and base-case scenarios provide stakeholders with a good starting point for analyzing variables. Make sure to continuously document the reasoning behind each assumption so analysts have feedback and can make better decisions in new scenarios.

Finance teams must review all scenario results and look for patterns in the data that reveal which drivers are making the biggest difference.

Validate the outputs by comparing them to historical performance and be prepared to adjust assumptions in case the results are not realistic. Then, apply those findings and turn them into actionable reports for business leaders.

Resilient organizations are separating themselves from the competition. A well-run what-if analysis can provide FP&A teams with valuable insights for financial management and increase clarity in business planning:

• Bigger competitive advantage: Being proactive and considering future events puts organizations in a unique position of power. By conducting a what-if analysis, finance teams can make better decisions faster and have contingency plans in place before a situation arises. This agility keeps organizations one step ahead of issues and optimizes prices and profitability.

• Better risk management: Knowing the financial impact of change and having plans in place reduces operational risk. What-if analysis outcomes allow organizations to run their operation with peace of mind and from a place of strength. They can effectively estimate cash flow needs and navigate supply chain issues without compromising value. This approach provides a safeguard for a business’s risk profile and allows it to keep operating, even in a worst-case scenario.

• Better resilience: Performing a what-if analysis and forecasting potential scenarios surfaces an organization’s weaknesses. With this information, the organization becomes more resilient in the face of future events or unpredictable market shifts. A what-if analysis allows finance teams to evaluate every available course of action and decide which offers the best result.

Finance teams should follow general guidelines when running a what-if analysis. To help ensure data-driven insights and actionable results, teams should recognize common pitfalls when building budget scenarios:

• Set realistic assumptions: Base assumptions on real, historical data and industry benchmarks, not on unrealistic assumptions that are likely not attainable even under ‘perfect’ circumstances.

• Use simple models: Choose a couple of impactful variables to start and don’t overcomplicate the process. Complex models place greater workloads on finance teams and are error-prone. Keep things simple at first and document all work.

• Account for bias: Consider bias in a what-if analysis and know it will affect outcomes. Biases are sometimes unavoidable. Recognize these biases when conducting the analysis and be transparent about their influence on certain factors.

• Test multiple scenarios: Start with best-case, base-case and worst-case scenarios, then expand beyond them to capture a more realistic range of outcomes.

• Document findings: Keep a paper trail of all scenarios and assumptions. Be prepared to communicate the results to stakeholders and other departments. Visuals and dashboards can help communicate more technical findings and ensure that all interested parties understand them.