What if Analysis (Goal Seek, Scenario manager)

What-If Analysis in Excel is a powerful feature that allows users to explore different scenarios by changing specific variables in a spreadsheet. Two key tools for What-If Analysis are Goal Seek and Scenario Manager.

Goal Seek and Scenario Manager are valuable tools in Excel for conducting What-If Analysis. Goal Seek helps find the required input to achieve a specific result, while Scenario Manager facilitates the creation and comparison of different scenarios to analyze the impact of variable changes. These features enhance decision-making and planning by providing insights into the potential outcomes of different scenarios.

1. Goal Seek:

Goal Seek is a feature in Excel that enables users to find the input value needed to achieve a specific goal or result. It is particularly useful when you have a target value in mind and want to determine the necessary input to reach that goal.

How to Use Goal Seek:

• Set Up Your Data:

Ensure you have a cell containing the target value you want to achieve and another cell with the formula that calculates the result.

• Go to the “Data” Tab:

Navigate to the “Data” tab in the Ribbon.

• Click on “What-If Analysis”:

Choose “Goal Seek” from the “What-If Analysis” options.

• Set Goal Seek Dialog Box:

  • In the Goal Seek dialog box:
    • Set “Set cell” to the cell with the formula result.
    • Set “To value” to the target value you want.
    • Set “By changing cell” to the input cell that Goal Seek should adjust.

  • Click “OK”:

Goal Seek will calculate and adjust the input cell to achieve the specified target value.

Example Scenario:

Suppose you have a loan repayment calculation where you want to find the monthly payment needed to pay off a loan in a certain number of months.

• Set cell: Cell containing the loan repayment formula result.
• To value: The target monthly payment.
• By changing cell: The cell containing the interest rate.

Goal Seek will adjust the interest rate until the monthly payment reaches the target value.

2. Scenario Manager:

Scenario Manager allows users to create and manage different scenarios in a worksheet. This is beneficial when analyzing how changes in multiple variables impact the overall outcome. Users can create and switch between various scenarios without altering the original data.

How to Use Scenario Manager:

• Set Up Your Data:

Arrange your data in a worksheet, including the variables you want to change and the resulting values you want to compare.

• Go to the “Data” Tab:

Navigate to the “Data” tab in the Ribbon.

• Click on “What-If Analysis”:

Choose “Scenario Manager” from the “What-If Analysis” options.

• Add a Scenario:
  • In the Scenario Manager dialog box:
    • Click “Add” to create a new scenario.
    • Provide a name for the scenario.
    • Specify the changing cells and values.
  • View and Compare Scenarios:

Use the Scenario Manager to switch between different scenarios and compare the impact on the worksheet.

• Edit or Delete Scenarios:

Modify existing scenarios or delete scenarios as needed.

Example Scenario:

Consider a financial model where you want to analyze the impact of changes in both interest rates and loan terms on monthly payments.

• Create Scenario 1 for a 15-year loan term with a specific interest rate.
• Create Scenario 2 for a 20-year loan term with a different interest rate.

Switching between scenarios allows you to observe how changes in loan terms and interest rates affect monthly payments.