What If Analysis

What-If Analysis is a powerful Excel feature that allows users to test different scenarios and assess the effects of various inputs on a particular outcome or formula. It is particularly useful in financial modeling, budgeting, and forecasting to simulate real-world situations and make informed decisions.

There are three primary tools under What-If Analysis:

• Data Tables: Used to see how changing one or two variables impacts the results of a formula. A one-variable data table changes one input value and observes the effect, while a two-variable data table changes two inputs at once. For example, a financial analyst can test how different interest rates and time durations affect loan payments.

• Goal Seek: This tool is used to find the input value needed to achieve a specific goal or result. For instance, if a business wants to find out how many units it must sell to reach a target profit, Goal Seek will determine the required sales volume.

• Scenario Manager: Allows users to create and save multiple sets of values (scenarios) and switch between them to see how changes in inputs affect the outcome. For instance, companies can model best-case, worst-case, and most-likely scenarios in budgeting or investment plans.

The value of What-If Analysis lies in its ability to help businesses plan for uncertainty and adapt to changing conditions. It enhances decision-making by allowing modelers to test multiple hypotheses quickly. It also improves stakeholder communication by clearly illustrating the impact of various decisions.

Professionals using Excel for advanced modeling rely heavily on What-If Analysis because it eliminates the need to rewrite or duplicate models for each possible outcome. It streamlines the planning process, improves transparency, and supports strategic business forecasting.

Importance and Benefits of What-If Analysis:

What-If Analysis provides several practical advantages in business modeling:

1. Improved Decision-Making

It helps managers evaluate the outcomes of various strategic choices. For example, a marketing team can test how increasing the advertising budget affects product sales.

2. Supports Forecasting

Forecasts can be tested under multiple assumptions. Companies can simulate demand, revenue, and cost patterns under different market conditions.

3. Risk Management

It allows businesses to assess vulnerabilities by simulating adverse scenarios (e.g., inflation, raw material price hikes) and preparing contingency plans.

4. Efficiency

What-If Analysis automates scenario creation without manually altering formulas or duplicating worksheets, thus saving time and reducing the chance of formula errors.

5. Clarity in Presentation

It visually presents the results of variable changes, making it easier to communicate with stakeholders, investors, or teams.

Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20.

If you sell 60% for the highest price, cell D10 calculates a total profit of 60 * $50 + 40 * $20 = $3800.

Create Different Scenarios

But what if you sell 70% for the highest price? And what if you sell 80% for the highest price? Or 90%, or even 100%? Each different percentage is a different scenario. You can use the Scenario Manager to create these scenarios.

Note: You can simply type in a different percentage into cell C4 to see the corresponding result of a scenario in cell D10. However, what-if analysis enables you to easily compare the results of different scenarios. Read on.

1. On the Data tab, in the Forecast group, click What-If Analysis.

2. Click Scenario Manager.

The Scenario Manager dialog box appears.

3. Add a scenario by clicking on Add.

4. Type a name (60% highest), select cell C4 (% sold for the highest price) for the Changing cells and click on OK.

5. Enter the corresponding value 0.6 and click on OK again.

6. Next, add 4 other scenarios (70%, 80%, 90% and 100%)

Finally, your Scenario Manager should be consistent with the picture below:

Note: to see the result of a scenario, select the scenario and click on the Show button. Excel will change the value of cell C4 accordingly for you to see the corresponding result on the sheet.

Scenario Summary

To easily compare the results of these scenarios, execute the following steps.

• Click the Summary button in the Scenario Manager.
• Next, select cell D10 (total profit) for the result cell and click on OK.

Result:

Conclusion: if you sell 70% for the highest price, you obtain a total profit of $4100, if you sell 80% for the highest price, you obtain a total profit of $4400, etc. That’s how easy what-if analysis in Excel can be.