How to Use the What If Analysis Tools in Excel

What If Analysis in Excel: A Comprehensive Guide

What-if analysis in Excel is a powerful tool that allows you to explore different scenarios and outcomes by changing input values in your formulas. This guide will walk you through the three primary what-if analysis tools in Excel: Scenarios, Goal Seek, and Data Tables, along with additional tools like the Solver add-in.

Scenarios

Creating and Managing Scenarios

Scenarios are sets of values that you can save and substitute automatically in cells on a worksheet. This tool is particularly useful when you need to compare different sets of assumptions or outcomes.

1. Define Your Scenarios:

   • Go to the Data tab and select What-If Analysis > Scenario Manager.
   • Click Add to create a new scenario and give it a name.
   • Specify the cells that will change and the values for each scenario.

2. Switch Between Scenarios:

   • Use the Scenario Manager to switch between different scenarios. This will automatically update the values in the specified cells, allowing you to see the different outcomes.

3. Creating Scenario Summaries:

• If you have multiple scenarios, you can create a summary report to compare the effects of each scenario. This helps in making informed decisions by visualizing the impact of different sets of values.

Goal Seek

Finding Input Values to Achieve a Goal

Goal Seek is a tool that helps you find the input values needed to achieve a specific result.

1. Using Goal Seek:

   • Navigate to the Data tab and select What-If Analysis > Goal Seek.
   • In the Goal Seek dialog box, specify the cell that contains the formula you want to adjust, the value you want to achieve, and the cell that contains the variable you want to change.

2. Example:

   • Suppose you want to find the sales price that would result in a specific profit margin. You would set the cell containing the profit margin formula as the target cell, the desired profit margin as the value, and the cell containing the sales price as the changing cell.

Data Tables

Analyzing Multiple Variables with Data Tables

Data Tables allow you to analyze how changes in one or two variables affect the outcome of a formula.

One-Variable Data Table

1. Setting Up a One-Variable Data Table:

   • List the possible values of the variable in a column or row.
   • Enter the formula that you want to analyze in an adjacent cell.
   • Select the entire data table range, including the list of variable values, the formula, and blank cells.
   • Go to the Data tab, select What-If Analysis > Data Table, and specify the input cell for the variable.

2. Example:

   • If you want to see how different loan amounts affect the monthly payment, list the loan amounts in a column, enter the formula for the monthly payment in an adjacent cell, and then create the data table.

Two-Variable Data Table

1. Setting Up a Two-Variable Data Table:

   • List one set of values horizontally and the other set vertically.
   • Enter the formula above the vertical list of values.
   • Select the entire data table range and go to the Data tab, select What-If Analysis > Data Table. Specify the row and column input cells corresponding to the horizontal and vertical variables.

2. Example:

   • To analyze how both loan amount and interest rate affect the monthly payment, list loan amounts horizontally and interest rates vertically. Enter the formula above the vertical list and create the data table.

Additional Tools: Solver Add-in

Advanced What-If Analysis with Solver

The Solver add-in is more powerful than Goal Seek and can handle multiple variables and constraints.

1. Installing Solver:

   • Go to the File tab, select Options, then Add-ins. Click Manage and select Solver Add-in to install it.

2. Using Solver:

   • Define the target cell that contains the formula you want to optimize.
   • Specify the changing cells that Solver will adjust to achieve the desired result.
   • Apply constraints to restrict the values that Solver can use.
   • Run Solver to find the optimal solution.

3. Example:

• Suppose you want to find the optimal product mix to maximize profit. You would set the profit formula as the target cell, specify the production levels as the changing cells, and apply constraints such as production capacity and resource availability.

Practical Applications

Forecasting and Advanced Models

In addition to the basic what-if analysis tools, Excel offers features for forecasting and creating advanced models.

1. Forecasting:

   • Use the fill handle or the Series command to generate future values based on existing data.
   • For more complex data, use worksheet functions or the regression analysis tool in the Analysis ToolPak add-in.

2. Advanced Models:

   • The Analysis ToolPak add-in provides tools for regression analysis, which can help in extending complex and nonlinear data.
   • Use the Solver add-in to find optimal values for formulas involving multiple variables and constraints.

By mastering these what-if analysis tools in Excel, you can make informed decisions by exploring various scenarios and outcomes, making your financial forecasting and planning more robust and accurate.