Chapter 21: Goal Seek and Scenario Manager

Introduction to Goal Seek and Scenario Manager
Goal Seek and Scenario Manager are powerful tools in Excel for performing what-if analysis. They help you understand how changes in your inputs can affect the outcome of your formulas. This chapter will cover how to use both tools effectively to make data-driven decisions.

1. Goal Seek

Purpose:
Goal Seek is used to find the input value needed to achieve a specific goal or result. It’s useful when you know the desired outcome but need to determine the necessary input value to reach that outcome.

How to Use Goal Seek:

1. Prepare Your Data:
   Set up your worksheet with a formula that calculates the result based on one or more input values.
2. Open Goal Seek:
   • Go to the “Data” tab on the Ribbon.
   • Click on “What-If Analysis” in the “Forecast” group.
   • Select “Goal Seek” from the dropdown menu.
3. Set Up Goal Seek:
   • In the “Goal Seek” dialog box, enter the following:
     • Set Cell: The cell containing the formula you want to achieve a specific value for.
     • To Value: The desired result you want to achieve.
     • By Changing Cell: The cell that Excel will change to achieve the desired result.
4. Run Goal Seek:
   • Click “OK” to run Goal Seek. Excel will find the value that needs to be entered in the “By Changing Cell” to achieve the desired result in the “Set Cell.”

Example:
You have a loan calculator with a formula for monthly payments. If you want to determine the interest rate needed to achieve a specific monthly payment, use Goal Seek to find that interest rate.

How to Use Goal Seek:

1. Set up a loan formula with cells for loan amount, interest rate, and monthly payment.
2. Use Goal Seek to determine the interest rate needed to reach a desired monthly payment.

2. Scenario Manager

Purpose:
Scenario Manager allows you to create and compare multiple scenarios or sets of input values to see how they affect the outcome of your formulas. This is useful for analyzing different possibilities and making informed decisions.

How to Use Scenario Manager:

1. Prepare Your Data:
   Create a worksheet with formulas that depend on various input values.
2. Open Scenario Manager:
   • Go to the “Data” tab on the Ribbon.
   • Click on “What-If Analysis” in the “Forecast” group.
   • Select “Scenario Manager” from the dropdown menu.
3. Add Scenarios:
   • In the “Scenario Manager” dialog box, click “Add” to create a new scenario.
   • Enter a name for the scenario and select the cells that will contain the input values.
   • Enter the values for these cells under “Scenario Values.”
4. Show Scenarios:
   • After adding scenarios, you can click “Show” in the Scenario Manager dialog box to view the results of each scenario.
   • Compare the outcomes to analyze different possibilities.
5. Create Summary Report:
   • Click “Summary” in the Scenario Manager dialog box to generate a summary report.
   • Choose the type of report you want to create (e.g., Scenario Summary or Scenario PivotTable Report).

Example:
You are planning a budget with different scenarios for revenue and expenses. Use Scenario Manager to create scenarios like “Best Case,” “Worst Case,” and “Most Likely Case” to see how different scenarios impact your overall budget.

How to Use Scenario Manager:

1. Set up a budget sheet with cells for revenue and expenses.
2. Create scenarios for different revenue and expense values.
3. Use the Scenario Manager to compare the impact of each scenario on your budget.

Practice Assignment:

1. Goal Seek: Create a simple financial model with a formula for calculating interest or payments. Use Goal Seek to find the necessary input value to achieve a desired result.
2. Scenario Manager: Develop a worksheet with different input values for a business scenario (e.g., sales forecasts). Use Scenario Manager to create and compare multiple scenarios.
3. Scenario Summary: Generate a Scenario Summary report to visualize the impact of different scenarios on your data.

Pro Tip: When using Goal Seek, ensure that the formula in the “Set Cell” is correctly set up and can produce a result based on changes in the “By Changing Cell.” For Scenario Manager, organize your scenarios clearly and use descriptive names to easily identify and compare them.

This chapter provides a comprehensive overview of Goal Seek and Scenario Manager, enabling you to perform advanced what-if analyses in Excel. Mastering these tools will help you make informed decisions based on various potential outcomes. For more in-depth Excel training and practical exercises, Anjni Computer Education offers additional resources to enhance your skills.