Scenario Analysis (What-if, Goal Seek)

Scenario Analysis in Excel is a critical tool for forecasting and decision-making. It allows users to test different business scenarios, explore possible outcomes, and make informed decisions by adjusting key variables. The two primary tools used for scenario analysis are What-If Analysis and Goal Seek.

What is What-If Analysis?

What-If Analysis is a set of tools that lets you change input values in your formulas to see how those changes affect the outcome. It helps you simulate various business situations, such as changes in pricing, cost, or sales volume.

Types of What-If Analysis:

• Scenario Manager – Lets you create and save multiple sets of input values (scenarios) and switch between them to see the impact.
• Data Tables – Useful for analyzing how changing one or two variables affects results.
• Goal Seek – A reverse calculation tool used to find the input value needed to reach a specific result.

Using Scenario Manager

With Scenario Manager, you can model situations such as:

• Best-case, average-case, and worst-case sales projections.
• Budget planning based on different cost assumptions.
• Strategic financial planning with alternate market conditions.

Each scenario is saved and can be compared side by side in summary reports.

Understanding Goal Seek

Goal Seek is used when you know the desired outcome of a formula but not the input value that achieves it.

Example:
If you want to know what sales volume is needed to reach a profit of ₹50,000, Goal Seek will help you find that sales figure by adjusting the variable in your profit formula.

Steps:

1. Set the cell with the outcome (formula).
2. Define the target value you want.
3. Choose the cell you want Excel to change to meet that target.

Benefits of Scenario Analysis

• Improved Decision-Making: Provides clear insights into how changes in variables affect results.

• Financial Forecasting: Predicts revenues, costs, and profits under different conditions.

• Risk Management: Evaluates risks by simulating worst-case and best-case scenarios.

• Strategic Planning: Enables long-term planning based on potential business outcomes.