What-If Analysis in Excel: Techniques, Tools & Tips for Better Decision Making

In today‘s fast-paced business environment, the ability to analyze data, model scenarios and make informed decisions is more critical than ever. One of the most powerful tools at your disposal for this is what-if analysis in Microsoft Excel.

What-if analysis allows you to change the values in cells to see how those changes will affect the outcome of formulas on the worksheet. It‘s a way to explore different scenarios and test out various assumptions without having to manually enter new data each time.

Whether you‘re a financial analyst building a budget model, a project manager evaluating different resourcing plans, or a sales rep creating sales projections, what-if analysis is an essential skill to have in your toolkit. In this guide, we‘ll take an in-depth look at what-if analysis in Excel – the different techniques and tools available, when and how to use them, tips and best practices, and examples and use cases.

Why What-If Analysis Matters

Before diving into the specifics of what-if analysis in Excel, let‘s take a step back and consider why it‘s so important and valuable:

Better decision making: By modeling different scenarios, what-if analysis provides insights that lead to more informed, confident decisions. You can see the potential impact of your choices.

Identifying risks & opportunities: What-if analysis uncovers both risks and opportunities that might not be immediately apparent. It prompts you to ask questions like "What‘s the worst case scenario?" and "How much upside is possible?"

Communicating & collaborating: What-if analysis outputs can be easily shared with stakeholders to align on assumptions and decisions. The visual models facilitate productive discussions.

Saving time: While there is some up-front work to set up a what-if analysis, it saves significant time in the long run vs. manually modifying data. You can update scenarios with just a few clicks.

Data Tables

The first what-if analysis tool we‘ll cover is data tables. Data tables allow you to see how changing one or two variables in a formula impacts the result.

There are two types of data tables in Excel:

1. One-variable data table: This shows how changing one variable affects the formula output
2. Two-variable data table: This shows how changing two variables in combination affects the formula output

Here‘s how to create a one-variable data table in Excel:

1. Set up your data and formula. Enter your input values in a vertical column and formula beside in the cell to the right in the same row as the first input value.
2. Select the range of cells that contains the formula cell and the cells below it that you want to fill with calculated values.
3. On the Data tab, in the Forecast group, click What-If Analysis, and then click Data Table.
4. The Data Table dialog box will appear. Leave the Row input cell field blank. In the Column input cell field, type the cell reference for the input value (the cell must contain a formula or value).
5. Click OK. Excel will calculate the formula for each input value and fill in the data table.

The process is similar for a two-variable data table, except you enter two input value ranges (one in a row and one in a column) and specify both Row input cell and Column input cell in the dialog box.

Scenario Manager

Scenario Manager is another what-if analysis feature in Excel. Unlike data tables which are designed for one or two variables, Scenario Manager is useful when you have a number of input values that you want to change simultaneously to create different scenarios.

To use Scenario Manager:

1. On the Data tab, in the Forecast group, click What-If Analysis, and then click Scenario Manager.
2. In the Scenario Manager dialog box, click Add to add a new scenario.
3. In the Add Scenario dialog box, give the scenario a name and select the changing cells (the input values you want to modify for each scenario). Then click OK.
4. In the Scenario Values dialog box, enter the values for the changing cells for the first scenario. Click OK and repeat the process to create additional scenarios.
5. After creating your scenarios, from the main Scenario Manager dialog box, you can view a summary report or a PivotTable report of the scenarios.

Scenario Manager is particularly useful for comparing a base case with best case and worst case scenarios. You can easily toggle between the scenarios to see the different outcomes.

Goal Seek

While data tables and Scenario Manager focus on seeing how input changes affect outputs, Goal Seek works in the opposite direction. With Goal Seek, you specify a desired output value, and Excel determines the input value needed to achieve that result.

To use Goal Seek:

1. On the Data tab, in the Forecast group, click What-If Analysis, and then click Goal Seek.
2. In the Goal Seek dialog box, in the Set cell field, enter the reference for the cell that contains the formula you want to resolve.
3. In the To value field, type the result you want to achieve.
4. In the By changing cell field, enter the reference for the cell that contains the value you want to adjust until the formula reaches the specified goal.
5. Click OK. Excel will display the goal seek status and the input value required to reach the goal.

Goal Seek is handy when you have a specific target in mind and want to work backwards to determine the input required. For example, if you‘re building a loan repayment model, you could use Goal Seek to determine the required monthly payment to pay off the loan within a certain timeframe.

Solver

For more complex what-if analyses involving multiple variables and constraints, Excel offers the Solver add-in. Solver is an optimization tool that finds the optimum value for a formula in one cell – called the objective cell – subject to constraints, or limits, on the values of other cells on a worksheet.

Setting up and running Solver is more involved than the other what-if analysis tools covered. Here‘s a high-level overview:

1. Define your objective, which is the output you want to maximize, minimize or set to a specific value.
2. Identify your decision variables, which are the inputs you can adjust to optimize the objective.
3. Identify your constraints, which are the limitations on the input values.
4. Set up your worksheet with cells for the objective, variables and constraints. The objective cell must contain a formula.
5. On the Data tab, in the Analyze group, click Solver to open the Solver Parameters dialog box. Specify the objective cell, variable cells and constraints. Then click Solve.

Solver is particularly useful for optimization problems like resource allocation, production planning, and investment portfolio optimization.

Best Practices for What-If Analysis

To get the most out of what-if analysis in Excel, follow these tips and best practices:

1. Clearly define your question or problem before starting your analysis. What exactly are you trying to determine or optimize?

2. Identify all key input variables and outputs. Think through dependencies and choose your variables carefully.

3. Use named ranges for your input cells. This makes formulas more readable and models easier to update.

4. Design for adaptability. Structure your model in a way that it‘s easy to add or remove scenarios and make changes to input data.

5. Test your model. Do a sanity check by entering extreme input values to see if outputs make sense. Validate with real-world data.

6. Document and comment. Include sources, assumptions, and guidance on how to use model. Turn on worksheet protection to avoid accidentally overwriting formulas.

7. Start simple and iterate. Begin with a basic model and progressively add more complexity as needed. Regular checkpoints to confirm your model is on track.

What-If Analysis vs Sensitivity Analysis

What-if analysis and sensitivity analysis are closely related concepts that are often used interchangeably. However there are some differences:

• What-if analysis assesses how a model‘s outputs respond to changes in inputs. It involves testing different input values to see the range of possible outcomes.

• Sensitivity analysis takes what-if analysis a step further by measuring how much a model‘s outputs change in response to a change in an input. It identifies which inputs have the greatest effect on the outcome.

In practice, sensitivity analysis can be considered a specific application of what-if analysis that quantifies the sensitivity of outputs to changing inputs.

What-If Analysis Use Cases

What-if analysis in Excel has a wide range of applications across business and finance. Here are some examples:

• A finance manager creating a company budget uses what-if analysis to model different revenue and expense scenarios.
• A product manager determines the pricing required to meet profit margin targets using Goal Seek.
• A real estate investor analyzes the potential ROI of rental properties under different scenarios for rental income, occupancy rates, operating expenses, etc.
• A production planner optimizes resources and constraints using Solver to create a production schedule that maximizes output.

Conclusion & Continuous Improvement

In conclusion, what-if analysis in Excel is a powerful way to model scenarios, uncover insights and make data-driven decisions. By mastering tools like data tables, Scenario Manager, Goal Seek and Solver, you‘ll be able to analyze your data more efficiently and effectively.

Remember, as with any analysis, the quality of your outputs depends on the quality of your inputs and model design. Make sure you understand your data, validate your assumptions, and continuously update and improve your models over time.

Lastly, while Excel is a fantastic tool for what-if analysis, know that there are other tools available for certain use cases. When you have very large datasets, need to incorporate machine learning, or have real-time streaming data, you may need a more heavy-duty solution than Excel.

By staying updated on the latest tools and best practices, you can apply what-if analysis to drive better decisions and optimize outcomes in your work. For more Excel tips and guides, check out the resources at Excel Easy and the official Microsoft what-if analysis documentation.