Chapter 9: What-If Analysis in Excel – Scenario Manager, Goal Seek, Data Tables

🔮 Chapter 9: What-If Analysis in Excel

📖 Introduction

What-If Analysis tools in Excel allow you to experiment with your data by changing variables and observing how outcomes adjust. This is especially useful for financial forecasting, budgeting, and goal-based planning. In this chapter, you’ll learn about Scenario Manager, Goal Seek, and Data Tables—three powerful tools for projecting future outcomes based on different input assumptions.

📊 What is What-If Analysis?

What-If Analysis involves changing values in cells to see how those changes affect the results of formulas. It helps users make informed decisions by modeling different scenarios.

🧩 Types of What-If Analysis Tools

1. Scenario Manager
2. Goal Seek
3. Data Tables

📌 1. Scenario Manager

Use Scenario Manager to create and save different groups of input values. This allows you to switch between various scenarios without changing the actual data.

📍 Steps to Use Scenario Manager:

1. Go to Data > What-If Analysis > Scenario Manager
2. Click Add and enter a scenario name (e.g., Best Case, Worst Case)
3. Select changing cells (e.g., sales price, cost)
4. Enter values for each scenario
5. Click Show to apply a scenario to the worksheet

🎯 2. Goal Seek

Goal Seek determines the input needed to reach a specific goal. For example, you can find out what interest rate you need to reach a savings target.

📍 Steps to Use Goal Seek:

1. Go to Data > What-If Analysis > Goal Seek
2. Set Set cell: (the formula cell)
3. Set To value: (your goal)
4. Set By changing cell: (input variable)
5. Click OK to calculate the required input

🔢 3. Data Tables

Data Tables allow you to test multiple values of one or two variables and see how they affect your results. It’s useful for sensitivity analysis.

📍 Steps to Create a One-Variable Data Table:

1. Set up a formula using one input
2. List possible input values in a column
3. Select the range including the formula and inputs
4. Go to Data > What-If Analysis > Data Table
5. Enter the input cell in the “Column input cell” box
6. Click OK

📍 Steps to Create a Two-Variable Data Table:

1. Set up a formula with two changing variables
2. List one set of values down a column and the other across a row
3. Select the entire grid (including formula at top-left)
4. Go to Data Table
5. Enter both row and column input cells
6. Click OK

🧠 Use Cases of What-If Analysis

• Financial forecasting
• Business decision making
• Pricing and break-even analysis
• Inventory planning and logistics

📝 Practice Task

1. Create a monthly profit formula: Revenue – Expenses
2. Use Scenario Manager to compare three cases: High Sales, Average, and Low Sales
3. Use Goal Seek to find the minimum sales needed to break even
4. Create a one-variable Data Table to analyze profit based on varying sales values

✅ Conclusion

What-If Analysis enables users to simulate multiple outcomes and plan ahead with confidence. Whether you’re budgeting or forecasting sales, these tools help you explore all possibilities. In the next chapter, we will cover Macros in Excel and how to automate your tasks efficiently.