What-If Analysis in Excel allows you to experiment with data to see how changes in one or more variables affect the outcome of formulas in your worksheet. This is particularly useful for financial modeling, budgeting, and forecasting. In this section, we will cover two powerful What-If Analysis tools: Scenario Manager and Goal Seek.

• Scenario Manager: Allows you to create and save different sets of input values (scenarios) and switch between them to see how they affect the results.
• Scenarios: Different sets of values that you can apply to a worksheet to see how they affect the results.

1. Open Scenario Manager:

   • Go to the Data tab on the Ribbon.
   • Click on What-If Analysis in the Forecast group.
   • Select Scenario Manager.

2. Add a Scenario:

   • In the Scenario Manager dialog box, click Add.
   • Enter a name for the scenario (e.g., "Best Case").
   • Select the cells that will change (e.g., B2, B3, B4).
   • Click OK.

3. Enter Scenario Values:

   • Enter the values for the scenario.
   • Click OK to save the scenario.

4. View Scenarios:

   • In the Scenario Manager dialog box, select a scenario and click Show to apply it to the worksheet.

Suppose you have a simple budget model with the following cells:

• B2: Revenue
• B3: Costs
• B4: Profit (calculated as B2 - B3)

You can create different scenarios to see how changes in revenue and costs affect profit.

1. Add Scenarios:

   • Best Case: Revenue = 12000, Costs = 6000
   • Worst Case: Revenue = 8000, Costs = 8000

2. View Scenarios:

   • Switch between "Best Case" and "Worst Case" to see how the profit changes.

• Goal Seek: Allows you to find the input value needed to achieve a specific goal in a formula.

1. Open Goal Seek:

   • Go to the Data tab on the Ribbon.
   • Click on What-If Analysis in the Forecast group.
   • Select Goal Seek.

2. Set Goal Seek Parameters:

   • In the Goal Seek dialog box, set the following:
     • Set cell: The cell containing the formula you want to achieve a specific value for (e.g., B4).
     • To value: The goal value you want to achieve (e.g., 5000).
     • By changing cell: The cell that Excel will change to achieve the goal (e.g., B2).

3. Run Goal Seek:

   • Click OK to run Goal Seek.
   • Excel will adjust the input value to achieve the goal.

Using the same budget model, suppose you want to find out what revenue is needed to achieve a profit of 5000.

1. Set Goal Seek Parameters:

   • Set cell: B4 (Profit)
   • To value: 5000
   • By changing cell: B2 (Revenue)

2. Run Goal Seek:

   • Excel will adjust the revenue to 12000 to achieve a profit of 5000.

1. Create a new worksheet with the following data:

   |   | A       | B       |
   |---|---------|---------|
   | 1 |         | Budget  |
   | 2 | Revenue | 15000   |
   | 3 | Costs   | 9000    |
   | 4 | Profit  | =B2-B3  |
   

2. Add the following scenarios:
   • Optimistic: Revenue = 18000, Costs = 8000
   • Pessimistic: Revenue = 12000, Costs = 10000
3. Switch between the scenarios and observe the changes in profit.

1. Using the same worksheet, find out what revenue is needed to achieve a profit of 7000.
2. Document the steps and the result.

Solution 1: Using Scenario Manager

1. Add Scenarios:
   • Optimistic: Revenue = 18000, Costs = 8000
   • Pessimistic: Revenue = 12000, Costs = 10000
2. View Scenarios:
   • Optimistic: Profit = 10000
   • Pessimistic: Profit = 2000

Solution 2: Using Goal Seek

1. Set Goal Seek Parameters:
   • Set cell: B4 (Profit)
   • To value: 7000
   • By changing cell: B2 (Revenue)
2. Run Goal Seek:
   • Excel will adjust the revenue to 16000 to achieve a profit of 7000.

In this section, you learned how to use Scenario Manager and Goal Seek for What-If Analysis in Excel. These tools allow you to experiment with different input values and find the necessary inputs to achieve specific goals, making them invaluable for financial modeling and decision-making. In the next section, we will explore Data Validation techniques to ensure data integrity in your worksheets.