What-if analysis is a financial modeling technique used to predict the possible outcomes of multiple variables by changing key input values. It allows finance professionals to evaluate how different variables, like changes in revenue or expenses, could affect a company’s financial performance. This type of analysis helps businesses to anticipate challenges, identify opportunities, and make data-driven decisions.
Why Perform What-If Analysis?
What-if analysis is particularly useful for both scenario analysis and sensitivity analysis. In scenario analysis, multiple variables are adjusted to simulate best-case, worst-case, or base-case situations. In sensitivity analysis, the focus is on testing the impact of changing a single variable while keeping all others constant. Analyzing potential outcomes across multiple scenarios makes it an asset in financial planning, investment planning, and risk management.
Key Highlights
What-if analysis enables better decision-making by allowing finance professionals to predict outcomes by adjusting key variables such as sales and expenses.
Excel’s built-in what-if analysis tools — Scenario Manager, Goal Seek, and Data Tables — provide finance professionals with easy-to-use methods for testing how different inputs impact financial outcomes.
A clean, flexible model, validated assumptions, and clear documentation of results enhances the accuracy of what-if analysis.
How What-If Analysis Works in Excel
Microsoft Excel offers built-in What-if Analysis tools for exploring potential outcomes quickly and efficiently. These tools include:
Scenario Manager: Allows you to create and save multiple sets of input values and compare their results. For example, you can create different sales or expense scenarios and see how they affect overall profitability.
Goal Seek: Enables you to find the input value required to achieve a desired result. For instance, if you need to know the sales volume needed to reach a certain profit target, Goal Seek can help you identify the required number.
Data Tables: Lets you examine how changing one or two input values will impact a financial model. For example, you can create a data table to show how changes in interest rates and loan amounts affect monthly payments.
Scenario Manager Tutorial
For this tutorial, we’ll use Excel’s Scenario Manager to evaluate how different growth rates for Revenue impact the company’s Gross Profit over the projection period.
Step 1: Set up the financial model.
Make sure your model has clear calculations for Revenue, COGS, and Gross Profit for each year. The Income Statement forecast model shown below assumes a revenue growth rate of 10% per year.
Ensure all formulas are correct and that the model is structured well for scenario testing.
Note: As part of our financial modeling guidelines, we use blue font for hardcoded input values and black font for calculated values.
Step 2: Open Scenario Manager.
Navigate to the Data tab on the ribbon. Select What-If Analysis, then Scenario Manager.
Alternatively, the Excel shortcut keys are Alt A W S.
Step 3: Define the Base Case scenario.
Click the Add button to create the first scenario.
Name it something descriptive, like Base Case.
In the Changing cells field, select the cells where Revenue figures are located (e.g., cells containing 2024F-2027F).
Click OK to proceed.
Our Base Case assumes a Revenue growth rate of 10% per year as the model currently shows, so Scenario Manager will automatically add the values to the Base Case.
Depending on your version of Excel, when you click OK you might see the below warning. Basically, Excel is warning us that when we decide to activate a scenario our formulas will be replaced with hardcoded numbers (in this case revenues).
After clicking OK on the above warning, Excel will then show you what the exact numbers in the appropriate scenario will be. Below, we see the Base Case numbers.
The Base Case scenario is completed.
Step 4: Create a Best Case scenario.
Repeat the process to add a Best Case. This scenario will adjust the forecasted Revenue values for 2024-2027, so we only need to select those cells in the Changing cells field.
For the Best Case scenario, we’ll use a Revenue growth rate of 15% per year.
Below, we see the Best Case revenue numbers.
Click OK. You should see Best Case listed as a scenario along with Base Case.
Click on Show to check the results of the Best Case scenario. Keep in mind that when you do this, the formulas for the projected revenues will be overwritten with hardcoded inputs.
Step 5: Create a Worst Case scenario.
Repeat the process to add a Worst Case.
For the Worst Case scenario, we’ll use a Revenue growth rate of 5% per year.
Below, we see the Worst Case revenue numbers.
Step 6: Show and compare scenarios.
After adding all three scenarios, you can use the Show button to apply each scenario to the model.
Observe how each scenario changes Gross Profit in your income statement model.
This will allow you to see how varying Revenue assumptions affects Gross Profit for each year.
Step 7: Summary output.
Scenario Manager has a “Show” option and a “Summary” on both Mac and PC. Clicking “Show” changes the Revenue values to the selected case (and removes formulas).
As shown below, “Summary” creates a report with multiple scenarios side-by-side. In this case, we see the current revenues, as well as the revenues for each case we created. We can also select a “Result Cells.” In the example below, we chose the 2024F Net Income.
Goal Seek Tutorial
In this tutorial, we’ll use Goal Seek to find out what revenue (the input) is required to achieve a target gross profit.
Step 1: Set a Target.
Assume you want to achieve a Gross Profit of $110,000 in 2024. You need to determine the required Revenue to reach that goal, assuming COGS remains unchanged at $70,373.
Current Data (2024):
Revenue: $171,878 (we applied the Base Case from the Scenario Manager tutorial, replacing the revenue formulas with hardcoded values)
COGS: $70,373
Gross Profit: $101,505
Goal: Gross Profit of $110,000 in 2024.
Step 2: Open Goal Seek.
Navigate to the Data tab on the ribbon.
Click What-If Analysis and select Goal Seek from the dropdown menu.
Alternatively, the Excel shortcut keys are Alt A W G.
Step 3: Set the Goal Seek Parameters.
In the Goal Seek dialog box, fill in the following:
Set cell: Select the cell that contains the 2024 Gross Profit value ($101,505). In our example, this is cell I9.
To value: Enter the target value 110,000.
By changing cell: Select the cell containing the 2024 Revenue value ($171,878 in cell I7). Excel will adjust this value to meet the target Gross Profit.
Step 4: Run Goal Seek.
Click OK to run Goal Seek. Excel will adjust the Revenue value until the Gross Profit reaches your target of $110,000.
Once the process is complete, Excel will display the new Revenue figure that meets the target Gross Profit.
Step 5: Review the Result.
The adjusted Revenue figure should now be $180,373 rather than the initial $171,878. This result means to achieve its Gross Profit target of $110,000 in 2024, assuming no changes to COGS, the business would need to bring in Revenue of $180,373.
Data Tables Tutorial
In this tutorial, we’ll use the Data Table tool to analyze how changes in one or two variables affect a result across multiple formulas.
Create a One-Variable Data Table
Use a one-variable data table to see how changes in Revenue growth impact Gross Profit.
Step 1: Set up your financial model.
Show the Worst Case of 5% revenue growth that we set up in our earlier Scenario Manager tutorial. Ensure the forecasted 2024 data shows the following figures:
Revenue: $164,066
COGS: $70,373
Gross Profit: $93,693
We’ll now use Data Tables to analyze how changes in Revenue affect Gross Profit.
Step 2: Create a one-variable Data Table.
Set up growth rate assumptions: List growth rates (e.g., 5%, 8%, 12%, 15%) in a column on your worksheet, starting in an empty cell below or beside your income statement.
Link the result cell (Gross Profit): In the cell directly above and to the right of your first growth rate, link to the Gross Profit formula for 2024.
Step 3: Verify the formula for Revenue in 2024.
Ensure the cell showing 2024 Revenue is dynamic with a formula that changes with the growth rate of 5%.
This formula takes the 2023 Revenue value and adjusts it by the growth rate shown below the Income Statement. This will update automatically when you run the Data Table.
Step 4: Set up the One-Variable Data Table.
In the area below the income statement, highlight the growth rates and results cells you set up in steps 1 and 2.
Step 5: Go to the Data tab, click What-If Analysis, and choose Data Table.
Alternatively, the Excel shortcut keys are Alt A W T (or Alt D T).
Set the input cell: In the Data Table dialog box, skip the Row input cell and enter the cell reference for 2024F Revenue Growth Rate in the Column input cell field (since your growth rates are listed vertically down a column).
This ensures that Excel adjusts the growth rates, which will, in turn, update the Revenue and Gross Profit.
Step 6: Review the results.
After running the Data Table, the results will show the Gross Profit values based on the different revenue growth rates listed.
You can now see how changing the Revenue Growth Rate impacts the Gross Profit for 2024.
Create a Two-Variable Data Table
A two-variable Data Table involves changing two different inputs (e.g., growth rate for Revenue and COGS as a percent of revenue in 2024) and analyzing their combined impact on Gross Profit.
Step 1: Set up your financial model.
Make sure the Revenue in the model is calculated using a formula and not hardcoded. We will also change COGS from a hardcoded number to a calculation based on COGS as a percentage of revenue.
Step 2: Adjust the formulas for Revenue and COGS in the Income Statement.
Update the 2024 Revenue and COGS formulas in the income statement to dynamically adjust based on the provided assumptions (5% revenue growth and 45% COGS percentage of revenue).
Ensure the forecasted 2024 data shows the following figures:
Revenue: $164,066
COGS: $73,830
Gross Profit: $90,236
Step 3: Set up growth rate and COGS assumptions in the Data Table area below your Income Statement.
Revenue Growth Rates: Enter the potential 2024 growth rates for Revenue (e.g. 5%, 8%, 12%, 15%) in a row. This row is the horizontal axis for your data table.
COGS % of Revenue: Enter the potential 2024 COGS margins (e.g., 45%, 50%, 55%, 60%) in a column. This column is the vertical axis for your data table.
Step 4: Link the result cell (2024 Gross Profit) in the Data Table.
In the empty cell directly above the COGS % of Revenue and left of Revenue Growth Rate, link the 2024 Gross Profit calculation from your income statement.
Step 5: Set up the two-variable Data Table.
In your Data Table area below the income statement, select the range for the 2024Revenue growth rate (5%) and COGS percent of revenue (45%), as shown below.
Go to the Data tab, click What-If Analysis, and select Data Table.
Set the input cells. The Row input cell should be referenced to the 2024F Revenue Growth Rate assumption (cell I17 in our example). The Column input cell should be referenced to the 2024F COGS % of Revenue (cell I18 in our example).
Step 6: Review the results.
Click OK, and Excel will now fill the table with the Gross Profit values.
You can now see how different combinations of Revenue growth and COGS margins impact Gross Profit for 2024.
Note: Depending on your Excel settings, the Data Tables may return one number. This is due to Calculation options in Excel. You can access various Excel settings by going to File then Options. As shown below, if Workbook Calculation is set to Automatic, then the Data Tables should return the proper results. If the calculation is set to Automatic except for data tables, then you will need to calculate the workbook when you want the correct results (F9 is the PC shortcut key to calculate everything in a workbook; alternatively, go to Formulas on the Excel ribbon and then click Calculate Now).
Best Practices for Effective What-If Analysis
To ensure that your What-if analysis produces accurate and actionable insights, it’s important to follow best practices when performing what-if analysis and setting up and analyzing financial models in Excel. Here are some key tips to keep in mind:
1. Start with Clear and Realistic Assumptions
Clearly define and document the assumptions behind your financial model, so you can easily adjust them if needed, and always base assumptions on realistic data.
2. Use Simple and Flexible Models
Avoid over-complicating the model with too many variables or complex formulas. A clean, easy-to-follow model makes it easier to adjust assumptions and reduces errors.
3. Validate Your Results
Always check your results to ensure they make sense in the context of your business. If a desired outcome seems unrealistic or yields unexpected results, revisit your assumptions and double-check your formulas.
4. Run Multiple Scenarios
It’s important to analyze multiple scenarios, not just the best- or worst-case scenarios, for a more realistic view of all the potential outcomes.
5. Document and Communicate Your Findings
Note the scenarios and assumptions you used and prepare clear visuals to communicate the results. Excel’s built-in charting and graphing tools can help you visually present your findings to non-finance and finance stakeholders.
By following these best practices, finance professionals can conduct more accurate and effective what-if analyses, enabling better decision-making and strategic planning.
Using What-If Analysis for Smarter Business Decisions
What-if analysis is a powerful tool that helps finance professionals evaluate potential scenarios and make data-driven decisions. From budgeting and forecasting to risk management and investment planning, this method equips businesses to anticipate and adapt to changes. With tools like Excel’s Scenario Manager, Goal Seek, and Data Tables, performing what-if analysis becomes both accessible and efficient.
By applying best practices and critically evaluating results, finance professionals can use what-if analysis to make more accurate, informed decisions and enhance their overall financial strategies.
Additional Resources
Thank you for reading CFI’s guide to What-If Analysis in Excel. To learn more and continue advancing your career, these additional CFI resources will be helpful:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.