What-If Analysis in Excel

In the data-driven environment, making informed decisions is very important in all aspects of business as well as personal finances, and just about everything in between, including forecasts. Microsoft Excel, a central component of the analytical and productivity toolkit, includes a powerful tool called What-If Analysis, which is a set of tools and features that allow users to model various scenarios, weigh different outcomes, and ultimately make better strategic decisions. In this article, you will learn about What-If Analysis in Excel, its definition, and when to use it, with examples. 

Table of Contents:

• What Is What-If Analysis in Excel?
• Why Do We Need to Use What-If Analysis?
• Types of What-If Analysis in Excel
  • Scenario Manager
  • Goal Seek
  • Data Table
• When Should You Use Each Tool?
• Practical Use Cases Across Fields
• Conclusion

What Is What-If Analysis in Excel?

What-If Analysis is a useful method that can be used in Excel, which allows users to change input values to see how the change affects the results of one or more formulas. What-if analysis allows users to test different categories or questions and see how this changes their desired outcomes. 

For example, “What will my profit look like if I increase the sales by 20%?”, “How much should I sell to get a target revenue?”, or “What happens if my costs increase, but my revenue stays the same?”. Rather than creating different versions of the worksheet for each case, users can use What-If Analysis to confirm and compare scenarios within a single spreadsheet. This makes it easy for Excel to be used for simple forecasting, planning, and making decisions.

Master Excel: Elevate Your Data Analysis Game

Unlock powerful insights with expert Excel techniques and become a spreadsheet pro with real-world examples and smart tips.

Explore Program

Why Do We Need to Use What-If Analysis?

1. Strategic Planning 

What-if analysis helps you develop different business scenarios by modelling various future outcomes. You can model the best-case scenario, the worst-case scenario, and the expected-case scenario to see how each would change your objectives. In this way, you can prepare for all unexpected scenarios.

2. Financial forecasting

Using a what-if analysis, you can model future financial performance by testing various adjustments with variables such as sales, expenses, rates of return, etc. It allows you to model cash flow, profit amounts, and losses to measure and monitor. This will help you to keep your financial records accurate and help you in your profit analysis.

3. Risk Management

It allows you to test changes in any identified key business methods, such as costs, charges, or customers, so you can see in advance any likelihood of vulnerabilities and create plans to limit that vulnerability. 

4. Data-Driven Decisions 

The use of What-If Analysis allows you to model realistic outcomes based on actual data instead of making assumptions. This leads to better, more confident decisions that are supported by proper records or reports using data rationale, rather than making decisions without an aim.

Types of What-If Analysis in Excel

Excel provides three built-in tools to perform What-If Analysis, each suited for different types of scenario modeling.

1. Scenario Manager

Scenario Manager works when you want to analyze the impact of different combinations of input values on your results. It is well suited for comparing multiple, simultaneous “what-if” scenarios, especially if more than one variable is changing. Instead of keeping individual copies of your worksheet, you can keep multiple scenarios organized in one place and change them from one to another, if needed.

Example: 

Now, let’s consider a scenario where you’re a business owner getting ready to announce a new product, and you want to estimate profits. Since it is a new launch, there is a lot of uncertainty about how many units you will sell, as well as the costs you may need to spend for production, which can also vary based on supplier pricing or scaling of the production.

In this scenario, Excel can be a great help. It allows you to create and compare “what if” scenarios based on a set of changing values for sales and costs without having to do the calculations each time, or create separate spreadsheets for each scenario.

You can build three possible scenarios to accommodate different business situations:

1. Best Case Scenario: You plan for high demand, so that you are able to sell 5,000 units. At the same time, if you are able to get your production costs down, such as to ₹10 per unit, perhaps you will earn higher profits because you are able to sell more and at a lower cost.

2. Moderate Case Scenario: You’re selling around 3,000 units, using ₹15 per unit for production costs. It is neither a best-case nor a worst-case scenario—an average forecast.

3. Worst Case Scenario: You predict poor sales performance, selling only 1,000 units, and much higher production costs at maybe ₹20 per unit. When you combine that, you’re either reducing your profit or even losing money.

Using the Scenario Manager option in Excel, you can just flip from scenario to scenario and see the expected profit in each case. You can make a better decision if you understand what is likely to happen if things go better or worse than expected. It also allows you to help prepare your backup plan in case actual results take an unexpected turn from your original assumptions.

How to Use:

Step 1: Navigate to Data > What-If Analysis > Scenario Manager.

Step 2: Click on Add to define a scenario.

Step 3: Select the changing cells (for example, sales and costs).

Step 4: Type in your scenario values.

Create several scenarios and then click the Summary button to see a summary comparison table.

2. Goal Seek

Goal Seek works backwards; it starts with the outcome you want and helps you calculate the value that has been saved for months to get you the output you want. Goal Seek is perfect when you have only one variable to change and you have a specific target or objective in mind. 

Example:

You want to save ₹10,000 over the next 12 months, and you don’t have a number in mind for how much you can save each month. If you have an existing formula in Excel that multiplies your monthly savings by 12 (months), you can use Goal Seek to find out how much you need to save each month to reach your savings goal of ₹10,000. 

Instead of trying different numbers manually, Goal Seek will do this automatically and will calculate that you will need to save about ₹833.33 a month.

Example Calculations:

Step 1: In cell A1, enter “Monthly Savings.”

Step 2: In cell B1, enter a placeholder value (for example, 0).

Step 3: In cell A2, enter “Total Savings.”

Step 4: In cell B2, enter the formula:
  =B1 * 12

This multiplies whatever you put in B1 by 12 months to give you your annual savings.

Use Goal Seek

Step 1: Go to the Data tab → What-If Analysis → Goal Seek

Step 2: In the dialog box, set: ‘Set cell‘ to B2 (your Total Savings cell).

Step 3: ‘To value’ to 10000 (your savings goal in ₹).

Step 4: ‘By changing cell’ to B1 (your Monthly Savings cell).

Step 5: Click OK. 

Result: Excel will adjust B1 to the precise value needed.

Calculations:

After Goal Seek runs, you’ll see B1 = 833.3333…
Monthly Savings * 12 = Total Savings 

X * 12 = 10,000 

X = 10,000 / 12 ≈ 833.33 

So you need to save ₹833.33 each month to reach ₹10,000 in a year.

How to Use:

1. Select Data > What-If Analysis > Goal Seek. 
2. In the dialog box, set the ‘Set cell’ to the formula cell (e.g., total savings).
3. Set the ‘To value’ to your goal (e.g., 10,000).
4. Set the ‘By changing cell’ to the input cell (e.g., monthly savings).
5. Click OK, and Excel will determine the value you need.

3. Data Table

A Data Table is a tool used to observe the effects of changing one or two variables in a formula. A Data Table is particularly useful when conducting a sensitivity analysis. A sensitivity analysis illustrates how changes in input values affect the outcome in a tabular format. 

For Example: Let’s say you are taking out a loan from a financial institution, and you want to see the total monthly payments by changing the interest rates and terms of the loan. You already have the monthly payment formula, but now you want to quickly run through scenarios. 

With a two-variable Data Table, you can keep the interest rate (5%, 6%, 7%) along one side, and each loan term (10, 15, and 20 years) along the other. Excel creates a table of monthly payment results for each variable combination.

How to Use:

1. Set up a formula to calculate the result (e.g., monthly loan payment using PMT).
2. List the values for one variable in a row and the other in a column.
3. Select the whole table area (including the formula).
4. Go to Data > What-If Analysis > Data Table.
5. Enter the row and column input cells that correspond to your variables.
6. Excel will generate a table showing all possible outcomes.

When Should You Use Each Tool?

Practical Use Cases Across Fields

• Business Planning: Model the various revenue, pricing, and cost scenarios that allow you to predict profit and plan action.
• Marketing: Model potential campaign ROI by testing possible levels of budget and expected results.
• Personal Finance: Model savings plan contributions, loan repayment schedules, and monthly budgeting targets.
• Project Management: Model the effect of new timing, resource, or cost changes on your overall project outcome.
• Sales Forecasting: Model revenue based on different sales targets, discounts, or production possibilities.
• Inventory Management: Model stock levels or reorder levels based on various patterns of demand.
• Investment Analysis: Model returns under different market conditions and interest rates.
• Operations: Model how changes in production rates, labor, materials, etc., impact total efficiency and costs.

Kickstart Your Excel Journey – For Free!

Master essential Excel skills and unlock the power of spreadsheets with our beginner-friendly free Excel course.

Explore Program

Conclusion

What-If Analysis in Excel is a powerful tool that supports better decision-making by letting users play with many different situations or scenarios and evaluate the potential outcome without changing the original data. Whether you are tracking business numbers, planning personal objectives, or evaluating operational changes, tools like Scenario Manager, Goal Seek, and Data Table give you more clarity and foresight. By looking at a range of possibilities, you will be able to plan for the best case and worst case, reduce risks, and make decisions based on facts or trends. In the end, What-If analysis changes Excel from a flat document to a decision-support system.

To learn more about Excel and master it, check out this Excel Certification and prepare for your job interview with Excel Interview Questions designed by industry experts.

What-If Analysis in Excel – FAQs