Scenario Manager in Excel | How to become an Expert of MS Excel - Class 6 PDF Download

Introduction

Excel is a powerful tool for data analysis, and one of its useful features is the Scenario Manager. Scenario Manager allows you to create and manage different scenarios based on changing inputs, helping you analyze how changes in variables affect your data. This article aims to provide a beginner-friendly introduction to the Scenario Manager in Excel, including step-by-step instructions, examples, and sample problems with solutions.

What is Scenario Manager?

The Scenario Manager is a built-in tool in Excel that allows you to analyze the impact of different variables on your data. It is especially useful when you have multiple sets of input values and want to compare the results.
Scenarios can be created for various scenarios, such as best-case, worst-case, or different assumptions. By defining different scenarios, you can quickly switch between them and see how they affect your calculations.

Setting Up Scenarios

To begin using the Scenario Manager in Excel, follow these steps:
Step 1: Open Excel and create a new spreadsheet.
Step 2: Enter your data and formulas in the appropriate cells.
Step 3: Identify the input cells that you want to change in your scenarios. These are the cells that will affect your calculations.
Step 4: Go to the "Data" tab in the Excel ribbon.
Step 5: Click on "What-If Analysis" and select "Scenario Manager."

Running and Viewing Scenarios

Once you've set up your scenarios, you can run and view them by following these steps:
Step 1: In the Scenario Manager dialog box, click on "Add."
Step 2: Enter a name for your scenario and specify the changing cells by selecting them in the spreadsheet. These are the input cells that you identified earlier.
Step 3: Enter the values you want to assign to the changing cells for the selected scenario.
Step 4: Click "OK" to add the scenario.
Step 5: Repeat the above steps to create additional scenarios.
Step 6: To view a scenario, select it from the list in the Scenario Manager dialog box and click on "Show."

Example:
Let's say we have a spreadsheet with sales data for different products, and we want to analyze the impact of different price scenarios on our revenue.
Here's how we can set up and run scenarios using the Scenario Manager:
Step 1: Enter your data and formulas in the spreadsheet, including the sales quantity, price per unit, and total revenue.
Step 2: Identify the price per unit as the changing input cell for our scenarios.
Step 3: Go to the "Data" tab, click on "What-If Analysis," and select "Scenario Manager."
Step 4: In the Scenario Manager dialog box, click on "Add."
Step 5: Enter a name for the scenario, e.g., "Best Case."
Step 6: Select the price per unit cell and enter the best-case value, e.g., $10.
Step 7: Click "OK" to add the scenario.
Step 8: Repeat the above steps to create additional scenarios, such as "Worst Case" with a price per unit of $5.
Step 9: To view a scenario, select it from the list in the Scenario Manager dialog box and click on "Show." You will see the spreadsheet update with the new prices and revenue calculations.

Creating a Summary Report

The Scenario Manager also allows you to create a summary report that consolidates the results of your scenarios.
Here's how you can do it:
Step 1: In the Scenario Manager dialog box, click on "Summary."
Step 2: Choose a location in your spreadsheet where you want to place the summary report.
Step 3: Click "OK."

Example:
Continuing from the previous example, let's create a summary report for our scenarios:
Step 1: In the Scenario Manager dialog box, click on "Summary."
Step 2: Select a location in the spreadsheet where you want to place the report, e.g., cell A10.
Step 3: Click "OK." Excel will generate a summary report that shows the changing cells, scenario names, and resulting values.

Sample Problems and Solutions

Problem 1: A company wants to analyze the impact of different discount rates on its net present value (NPV). The initial investment is $10,000, and the discount rates to be considered are 5%, 10%, and 15%. Calculate and compare the NPV for each scenario.

Set up the spreadsheet with the necessary formulas for calculating NPV based on the discount rate and cash flows. Use the Scenario Manager to create scenarios for each discount rate and view the resulting NPVs.

Problem 2: A sales team wants to evaluate the effect of varying advertising budgets on monthly sales revenue. The possible budget levels are $5,000, $10,000, and $15,000. Calculate and compare the sales revenue for each scenario.

Enter the necessary formulas to calculate sales revenue based on the advertising budget. Use the Scenario Manager to set up scenarios for each budget level and view the resulting sales revenue.

Conclusion

The Scenario Manager in Excel is a valuable tool for data analysis, allowing you to create and manage different scenarios to assess the impact of changing variables on your calculations. By following the steps outlined in this article, you can easily set up and run scenarios, view the results, and generate summary reports. Excel's Scenario Manager simplifies the process of analyzing data and enables better decision-making by providing insights into various scenarios.