If you’re working on an Excel sheet and maintaining multiple parameters, it must be challenging to track all of them simultaneously. Let’s say you have a product and wish to analyze the profit on its sales using different sales prices and units sold. How would you do that? This is precisely where a What-If analysis in Excel can save you a lot of time.
Still confused? Imagine you’re planning a road trip to a famous tourist destination. Naturally, you will consider the distance, fuel consumption, travel, and departure time for various commuting options, budgets, and alternatives. Before finalizing the plan, you will consider different scenarios and see which one suits you best. This is exactly how Excel’s what-if analysis helps in analyzing data.
As the name suggests, what-if analysis in Excel is simply finding answers to “what happens if we do this.” It is the process of observing or analyzing how changing cell values impacts the result. This powerful feature allows you to explore dynamic scenarios, like changing formulas, etc., and then note how these dependencies impact the worksheet’s outcome.
Almost every data analyst or expert works with Excel sheets to make better, quicker, and more accurate data decisions. The What-If analysis in Excel is very efficient and widely used in several applications like scenario evaluation, risk assessment, cost-benefit analysis, forecasting, and more. Let’s read about some of them in detail.
With the what-if analysis in Excel, analysts can input different values as “IFs” and see “What” happens in each scenario. This helps decision-makers to see the potential consequences of different scenarios.
Using this analysis, decision-makers can also assess potential risks. For instance, technical people working in oil rigs or anywhere in the oil and gas industry continually use the What-If analysis in Excel to see how different volumes and capacities impact oil and gas production.
What-if analysis in Excel can also assist in optimizing resource allocation by analyzing different scenarios. Using this analysis, decision-makers can identify resource requirements, evaluate cost implications, and anticipate investments based on their potential impact.
Analysts and decision-makers can use the various tools available for what-if analysis in Excel to forecast stock prices, future sales, and any other relevant factors.
Excel offers numerous tools that you can use for data analysis and gain insights, summarize unorganized data, perform statistical analysis, and much more. In this section, let’s explore some of the most used ones.
There are three main kinds of what-if analysis in Excel: Scenario, Goal Seek, and Data Tables.
The What-If analysis in Excel is also widely used in sensitivity analysis. Here’s how it is done.
By observing the sensitivity table, you can see how changing values impact the calculated results. You can use this information to make more informed decisions, identify areas of risk or uncertainty, and prioritize your focus on the most influential variables.
This section discusses the scenario what-if analysis in Excel.
Scenario Manager is a robust Excel tool widely used for what-if analysis. It allows you to create and handle several scenarios to see how changing variables impact your worksheet. Primarily, it compares multiple sets of input values to observe different outcomes.
Scenario Managers lets you generate a summary for each scenario. You can also generate a report or display the results visually in a separate worksheet.
Data tables are one of the most systematic ways to calculate results based on different combinations of inputs. Here’s how it is done.
Utilizing Data Tables for What-If Analysis allows you to quickly produce different results by changing the input values. This enables you to investigate many situations, evaluate your model, and base your judgments on the outcomes.
Let’s move on to see how Goal Seek works.
With Excel’s proficient Goal Seek tool, you can perform What-If Analysis by determining the input value required to reach a particular target or goal for a calculated result.
To determine how the modified input value affected the calculated result, analyze the results. You can also evaluate the viability of the Goal Seek solution.
In addition to the standard data analysis techniques, Excel also offers many advanced what-if analysis tools, like Solver, A/B Testing, Agent-based Modeling, and more. Here, we’re focusing on how Solver is used for complex what-if analyses.
Solver is a powerful Excel tool commonly used for mathematical modeling, optimization, and constraint analysis. Here’s how you can use Solver in Excel for complex scenarios.
Performing a what-if analysis in Excel with more than two variables works best using Scenarios. A scenario can have as many as 32 different values, and you can always create multiple scenarios. Here, an IF condition can have other IF conditions contained in it. These can simultaneously test multiple scenarios and return different results for each.
Contrasting it with Data Tables and Goal Seek, the former works best with one or two variables, and the latter will only perform with one variable.
Despite how beneficial a what-if analysis is, a few drawbacks and limitations restrict them. For starters, if you frequently work with worksheets, you know that worksheets do fail—and they fail a lot of times. Besides, there are a few other limitations; read on.
Despite the abovementioned limitations, you can still use Excel’s what-if analysis. Follow the practices below to maintain data integrity, minimize errors, and improve the reliability of your results.
Wrapping up, you can see that what-if analysis in Excel is very efficient in empowering analysts and decision-makers to explore scenarios, assess risks, allocate resources, and do much more. By incorporating multiple variables and using advanced techniques such as Solver, data tables, and scenario analysis, Excel enables users to gain valuable insights. Once you define the problem, set up all the constraints, and specify the suitable cells, you’ll get the most out of your what-if analysis in a few seconds.
To learn more about the same, you can look at some tutorials and blogs on Analytics Vidhya. It is a leading platform for data science, machine learning, and artificial intelligence and can be a valuable resource in leveraging what-if analysis and enhancing analytical capabilities.
A. The IF function holds a particular purpose. It is used to make logical comparisons and see how the outcome differs from what you expect.
A. What-if Analyses are called sensitivity analyses because they help you determine how sensitive the outcome is to changing variables.
A. The IFS function sees whether multiple IF conditions are met simultaneously, and a value corresponding to the initial TRUE condition is returned.
A. What-if analysis in Excel can be used for cost-minimization, cost-effectiveness, cost-utility, risk assessment, etc.