What-if analysis is a critical tool used in decision-making processes. It allows individuals and organisations to evaluate the potential outcomes of different scenarios. Altering variables helps predict how changes can impact results. The analysis is straightforward and useful in finance, project management, and strategic planning. Understanding its benefits can lead to better-informed decisions and increased confidence in planning.
What is a What-if Analysis?
What-if analysis helps organisations assess how different factors can affect their strategy, finances, and productivity. It is crucial for effective forecasting, providing business leaders with strong data to make informed decisions. This leads to better choices and a more flexible business strategy that can adapt to challenges and seize opportunities.
Types of What-if Analysis
There are two ways to conduct a what-if analysis: sensitivity analysis and scenario analysis.
Sensitivity Analysis
Sensitivity analysis looks at how changes to one variable impact a plan. For example, a modeller might ask, “What if we cut our materials spending by 5%?” This change can affect other factors like COGS, profit, and revenue.
Sensitivity analysis helps businesses reach specific goals and understand how changes will impact their budget and annual planning.
Scenario Analysis
Scenario analysis shows the effects of multiple changes happening at the same time. It examines the overall influence of market conditions. These changes can be based on known factors like upcoming tax rate adjustments or service price hikes, or they can involve unknown market variables.
This analysis helps explore both best-case and worst-case situations, looking at different outcomes based on shifting assumptions across many factors.
Why Is What-If Analysis Important?
What-if analysis can bring the following benefits:
Competitive advantage
What-if analysis offers a competitive advantage to organisations. It allows companies to make informed decisions based on potential outcomes. This approach reduces risks by identifying how changes can affect results. With better insights, businesses can respond effectively to market challenges. Planning becomes easier when leaders understand the potential impact of their choices. This clarity leads to stronger strategies and improved performance over time.
Better risk management
What-if analysis helps organisations manage risk more effectively. It allows teams to foresee potential problems and devise strategies to address them. Understanding different scenarios leads to informed choices. This understanding helps businesses avoid pitfalls and seize opportunities, ultimately contributing to greater stability and success.
Stronger decision-making
What-if analysis supports stronger decision-making. It provides clear insights into potential outcomes. Leaders make choices based on solid data, reducing uncertainty and leading to better strategies. Teams can respond quickly to changes, improving overall effectiveness. In turn, this fosters a culture of informed decision-making within the organisation.
How Does the What-if Analysis Work?
What-if analysis functions through a systematic approach. Analysts first define the variables involved in a scenario. Next, they modify these variables to see how outcomes change. They input different values into models or formulas. This process allows them to observe the potential implications of each change. The results are then evaluated to guide decision-making. Finally, the analysis supports leaders in crafting effective strategies based on clear data. This method simplifies complex situations and aids in better planning.
How to Conduct a What-if Analysis?
1. Team Kickoff
The team leader guides the team through the What-if Analysis step by step. They may use a detailed equipment diagram and any operating guidelines that have been prepared. Safety guidelines should also be included to determine acceptable safety levels.
2. Generate What-if Questions
The team creates What-if questions for each step of the experiment and each component. This helps identify possible sources of errors and failures.
Consider these factors when making questions:
- Human errors
- Failures in equipment components
- Changes from the planned critical parameters (like temperature, pressure, time, and flow rate).
3. Evaluate and Assess Risk
The team reviews the list of What-if questions one at a time to identify possible errors. They evaluate the likelihood of each error and consider the consequences.
4. Develop Recommendations
Unacceptable risk: If the team decides corrective action is needed, they will make a recommendation.
Acceptable risk: If the chance of occurrence is low, the consequences are minor, and the corrective action is costly and time-consuming, the team may choose not to make a recommendation.
5. Prioritize and Summarize Analysis
The team summarised and prioritised the analysis.
6. Assign Follow-up Action
Assign responsibilities for follow-up actions. Add a column to your What-if Analysis form to show who is responsible for each corrective action.
How do you perform a What-if Analysis in Microsoft Excel?
Goal Seek Analysis
The Goal Seek feature helps you find a specific target, like a revenue number or profit margin. You can change a variable to check if you can reach that target.
Here’s how to use Goal Seek in Excel:
- Go to the Data Tab, select What-If Analysis, then choose Goal Seek.
- Identify what needs to change. For example, a business owner wants to raise the profit margin on a product from 60% to 70%.
- In the Goal Seek menu, select the current profit margin of 60% as your goal. Enter 70% (as a decimal) in the “To value” field.
- Choose the cell to change that will affect the goal and calculate the desired result.
Excel will run the analysis and show the result, if possible. In this example, you need to reduce the cost by $5 per item to reach the 70% margin.
Scenario Manager
What if your calculations get more complicated or if you have several options 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 revisit the profit margin example. There are a few ways to improve the profit margin of an item:
- Increase the price
- Decrease labor costs
- Decrease materials costs
The scenario manager lets you see how these changes affect your profit margin.
- Select Scenario Manager from the What-if Analysis tool in the Data tab.
- Create a new scenario by clicking the + symbol.
- Enter the variables for your scenario. You can change multiple variables by selecting cells while holding the ALT key.
- After selecting, enter the desired change for each cell. When finished, click “OK” to return to the scenarios list.
- To see the results, choose your scenario from the menu and click “show” to display the changes in your table. For instance, we show the “Increase Price/Reduce Costs” scenario to see how all three actions affect the profit margin.
To view all your scenarios in a data table for easy comparison, select “Summary” from the Scenario Manager dialogue box. Choose the result cells you want to display, such as Total Revenue, Total Cost, Profit, and Profit Margin.
A scenario summary report will appear in a new tab of your workbook. You can modify this table, rearrange columns, and create different scenarios to continue your analysis.
What-if Analysis With Data Tables
If you want to analyse the change of a single condition or have simpler business models, you can do a what-if analysis with one or two variables in a data table. A data table shows all outcomes in one place. It helps you see the range of possible results if multiple formulas depend on the same variable.
To create a one-variable data table with a formula, first, list the values you want to use in a column (or a row). Leave some empty rows and columns on the sides. Then, type the formula in the cell one row above and to the right of your values. If you listed your variables in a row, place the formula one cell to the left and down.
Select the range of cells with the formulas and values. Click on What-If Analysis, then choose Data Table to change cells and calculate the results. Enter the cell reference for the input value in the Column input cell box. If you wrote your variables in a row, use the Row input cell box. You have now created a one-variable data table.
To create a two-variable data table in Excel, follow the same steps as above but fill out both the row and the column. You will also need a formula that requires two input values.
How to Use FP&A Software for What-if Analysis?
Organisations of all sizes use what-if analysis to assess their business strategies and plan for different scenarios. Many still rely on spreadsheets and manual work to conduct this analysis. While you can use Excel, most trained planners prefer not to use spreadsheets. Instead, they opt for FP&A platforms. These tools gather data from throughout the organisation and create dynamic dashboards. This allows users of all skill levels to create and simulate scenarios with much less manual effort than other methods.
FAQs
What are the disadvantages of what-if analysis?
Some disadvantages of this type of analysis include:
- What-if analysis can produce inaccurate results if the input data is wrong.
- It often relies on assumptions that may not reflect reality.
- Without proper oversight, it can lead to poor decision-making.
- It may require significant time and resources to set up.
- Results can be difficult to interpret, especially for complex scenarios.
- Users need a good understanding of the underlying data and models to use it effectively.
Which tool helps better for what-if analysis?
The best tool for what-if analysis often depends on specific needs. You can do a what-if analysis in Excel, which is user-friendly for simple scenarios and quick calculations. For more complex analyses, dedicated FP&A software is better. These platforms streamline data management and allow for easier scenario simulation without extensive manual input.
What are examples of what-if analysis?
You can use What-If Analysis to create two budgets based on different revenue levels. You can also set a desired result for a formula and find the values that will achieve that result.