Scenario Manager in Excel

Scenario Manager is a powerful tool in Excel that allows you to create and analyze multiple scenarios in a worksheet. This tool is particularly useful for performing "what-if" analysis by exploring different combinations of input values. In this tutorial, we will go through the steps of using Scenario Manager with real-time examples.

Step-by-Step Tutorial

Step 1: Set Up Your Data

First, you need to set up your data for analysis. Ensure you have the variables you want to change and a formula that calculates the output based on those variables.

For example, suppose you want to analyze the effect of different sales prices, quantities, and costs on total revenue. The setup could be as follows:

  Sales Price = $10 (in cell A1)
  Quantity = 1000 (in cell A2)
  Cost = $5 (in cell A3)
  Total Revenue = Sales Price * Quantity - Cost * Quantity (in cell A4)

Step 2: Open the Scenario Manager

  1. Go to the "Data" tab on the ribbon.
  2. In the "Forecast" group, click on "What-If Analysis" and select "Scenario Manager..." from the dropdown menu.

Step 3: Create a New Scenario

  1. In the Scenario Manager dialog box, click on "Add..." to create a new scenario.
  2. In the "Scenario Name" field, enter a name for your scenario (e.g., "Best Case").
  3. In the "Changing Cells" field, select the cells that will change between scenarios (e.g., cells A1, A2, and A3).
  4. Click "OK" to move to the next step.

Step 4: Define the Scenario Values

  1. In the "Scenario Values" dialog box, enter the values for the changing cells that correspond to this scenario. For example:
    • Sales Price = $12
    • Quantity = 1200
    • Cost = $4
  2. Click "OK" to save the scenario.

Step 5: Add More Scenarios

  1. Repeat the process to create additional scenarios. For example, you might create a "Worst Case" scenario with the following values:
    • Sales Price = $8
    • Quantity = 800
    • Cost = $6
  2. Click "OK" after adding each scenario.

Step 6: View and Compare Scenarios

  1. Once you’ve created all your scenarios, select the scenario you want to view from the "Scenario Manager" dialog box.
  2. Click "Show" to display the values for that scenario in the worksheet.
  3. The selected scenario’s values will replace the existing values in the changing cells, and the total revenue formula will update accordingly.
  4. You can switch between scenarios to compare the results of different inputs.

Step 7: Generate a Scenario Summary

  1. If you want to summarize all the scenarios in a table, click on "Summary..." in the Scenario Manager dialog box.
  2. Choose the result cell that you want to analyze (e.g., Total Revenue).
  3. Click "OK," and Excel will generate a new worksheet with a table comparing the results of each scenario.

Real-Time Examples

Example 1: Sales Price Analysis

Scenario: You want to analyze how different sales prices will affect total revenue. You can create different scenarios based on various price points and quantities sold.

  1. Enter the following data:
    • Sales Price in A1: $10
    • Quantity in A2: 1000
    • Cost in A3: $5
  2. Create three scenarios with different sales prices: $8 (Worst Case), $10 (Base Case), and $12 (Best Case).
  3. In the "Total Revenue" cell (A4), enter the formula: 
    =A1*A2 - A3*A2
  4. Use Scenario Manager to create and switch between these scenarios to see how changes in sales price impact total revenue.

Example 2: Budget Forecasting

Scenario: You want to forecast your budget under different conditions, such as optimistic, pessimistic, and realistic scenarios for income and expenses.

  1. Enter the following data:
    • Income in B1: $50,000
    • Expenses in B2: $30,000
    • Net Profit in B3: =B1 - B2
  2. Create three scenarios with different values for income and expenses:
    • Optimistic Scenario: Income = $60,000, Expenses = $28,000
    • Realistic Scenario: Income = $50,000, Expenses = $30,000
    • Pessimistic Scenario: Income = $45,000, Expenses = $35,000
  3. Use Scenario Manager to compare the scenarios and observe how they impact net profit.

Example 3: Investment Return Analysis

Scenario: You want to analyze the returns of an investment under different conditions (e.g., different interest rates or investment periods).

  1. Enter the following data:
    • Principal in C1: $10,000
    • Interest Rate in C2: 5%
    • Years in C3: 5
    • Future Value in C4: =C1*(1+C2)^C3
  2. Create scenarios for different interest rates (e.g., 3%, 5%, and 7%) and investment periods (e.g., 5 years, 10 years, and 15 years).
  3. Use Scenario Manager to evaluate how changes in interest rate and investment period affect the future value of the investment.

Tips for Using Scenario Manager

  • You can create up to 32 scenarios in a single worksheet using Scenario Manager.
  • Each scenario can change multiple input values, making it useful for complex analyses.
  • Scenario Manager is a great way to compare "what-if" scenarios side by side to make informed decisions.
  • Remember to update your scenario values if you make any changes to the base data or formulas.

Conclusion

Scenario Manager is an excellent tool for performing "what-if" analysis in Excel. It allows you to create multiple scenarios with different input values and compare the outcomes to make better business decisions. By following the steps and examples provided, you can use Scenario Manager to conduct advanced data analysis and explore various possibilities with ease.




Advertisement