6.3. Using What-If Analysis: Goal Seek, Scenario Manager, Data Tables

What-If Analysis in Excel is a powerful feature that allows users to explore different outcomes based on changing inputs. It’s particularly useful for decision-making, forecasting, budgeting, and financial modeling, where you need to understand the potential effects of different variables or scenarios on a given result. The three primary tools within Excel’s What-If Analysis are Goal Seek, Scenario Manager, and Data Tables. Each of these tools helps users analyze different scenarios and determine the best course of action based on the input data.

1. What-If Analysis Overview

What-If Analysis is used to predict the effect of changing certain input values on the final outcome of a formula. It allows you to test various possible outcomes without actually changing the data, which makes it a valuable tool for businesses and individuals looking to analyze scenarios and make informed decisions.

• Goal Seek: Finds the input value needed to achieve a desired result from a formula.
• Scenario Manager: Compares different sets of input values to see how they affect the outcome of a formula.
• Data Tables: Analyzes the effect of one or two variables on the results of a formula.

Let’s look at each tool in more detail:

2. Goal Seek

Goal Seek is used when you know the desired outcome (result) of a formula, and you want to determine what input value is required to achieve that result.

• How It Works: Goal Seek allows you to set a target value for a specific formula’s result and then automatically adjusts one of the input values to achieve that target. It works for a single-variable scenario and is useful when you want to find out how much of an input is needed to reach a particular goal.
  • Example: Suppose you have a loan with a fixed interest rate, and you want to know what monthly payment you need to make in order to pay off the loan in 12 months. You can use Goal Seek to calculate the required payment by specifying the total loan amount and the target number of months to pay it off.
• Steps to Use Goal Seek:
  1. Select the cell that contains the formula you want to solve.
  2. Go to the Data tab and select What-If Analysis > Goal Seek.
  3. In the Goal Seek dialog box, enter the Set Cell (the formula cell), the To Value (the desired result), and the By Changing Cell (the input cell to be adjusted).
  4. Click OK, and Excel will calculate the value needed to achieve your goal.

3. Scenario Manager

Scenario Manager allows you to create, manage, and compare multiple scenarios in a worksheet. Scenarios are essentially different sets of values that can be applied to a formula to see how the results change. This tool is useful when you have several input variables that you want to test, but you don’t want to manually enter each combination of inputs.

• How It Works: Scenario Manager lets you define different sets of values for one or more input cells, then compares the results of a given formula across all the scenarios. It’s especially useful for financial modeling, sales forecasting, or any situation where you need to evaluate multiple possibilities at once.
  • Example: A company wants to forecast its revenue under different conditions, such as varying sales volumes, price changes, or production costs. You can set up different scenarios (e.g., "Best Case," "Worst Case," and "Most Likely Case") and compare the outcomes.
• Steps to Use Scenario Manager:
  1. Go to the Data tab and select What-If Analysis > Scenario Manager.
  2. Click Add to create a new scenario. Enter the name of the scenario and specify the changing cells (those that will vary in different scenarios).
  3. Input the values for each scenario and click OK.
  4. After creating all scenarios, you can view the results by selecting Show to compare different scenarios and their outcomes.
  5. You can also generate a summary report to see all scenarios side by side for comparison.

4. Data Tables

Data Tables are used to analyze how one or two variables affect the result of a formula. They allow you to quickly perform sensitivity analysis and see how changes in input values affect the output. A Data Table is an array of values where one or more input variables are systematically varied, and the corresponding results are displayed in a table format.

• How It Works:
  • One-Variable Data Table: You can vary a single input value (such as interest rates or tax rates) and see how changes in that value affect the result of a formula.
  • Two-Variable Data Table: You can vary two input values and examine the impact of changing both on the result.
• Example:
  • One-Variable Data Table: You have a formula that calculates the future value of an investment, and you want to see how the future value changes when the interest rate is varied. You can create a Data Table that tests different interest rates and shows how the future value changes for each rate.
  • Two-Variable Data Table: You have a formula that calculates profits based on both sales volume and price per unit, and you want to see how changes in both variables affect the profit.
• Steps to Use Data Tables:
  • One-Variable Data Table:
    1. Set up the formula in a cell.
    2. Create a column or row of possible input values for the variable you want to change.
    3. Select the range of values, including the formula cell.
    4. Go to the Data tab and select What-If Analysis > Data Table.
    5. In the Data Table dialog box, specify the input cell (the one that will change) and click OK.
  • Two-Variable Data Table:
    1. Set up the formula in a cell.
    2. Create a two-dimensional array of possible values for the two variables.
    3. Select the range of values, including the formula cell.
    4. Go to the Data tab and select What-If Analysis > Data Table.
    5. In the Data Table dialog box, specify the two input cells (one for each variable) and click OK.

5. Practical Use Cases for What-If Analysis

• Budgeting and Financial Forecasting: What-If Analysis tools allow you to evaluate different financial scenarios, such as changes in expenses, revenue, or investment returns, to make better financial decisions.
• Pricing Strategies: Scenario Manager and Data Tables can be used to analyze how different pricing strategies affect profit margins and overall sales.
• Investment Analysis: Data Tables are commonly used to see how changing variables like interest rates, inflation, or stock market returns affect investment outcomes.
• Project Management: Goal Seek can help project managers determine the resources needed (e.g., time, budget) to achieve project goals, while Scenario Manager can test various project timelines and resource allocation scenarios.

6. Conclusion

What-If Analysis is a powerful suite of tools in Excel that enhances decision-making by enabling users to explore different scenarios and forecast potential outcomes based on varying inputs. Whether you're using Goal Seek to find the input required to meet a specific goal, Scenario Manager to compare multiple scenarios, or Data Tables to analyze the effect of one or two variables, these tools are essential for anyone involved in financial modeling, budgeting, forecasting, or data analysis. By mastering these tools, you can make more informed decisions and better understand the potential impact of changes in your data.