How To Build a What-If Analysis in Excel (With a Free Template)

How confident are you in this year's forecast? Not whether the numbers are right, but whether you've actually stress-tested what happens when they're not. 

Most forecasts and financial models get built around a single set of assumptions: revenue grows at X%, costs stay flat, headcount stays where it is. That plan gets submitted, approved, and then reality does something different. By the time the variance shows up in a monthly report, the decisions that caused it are months behind you. 

What-if analysis flips that sequence. Instead of waiting to see what happened, you model what could happen before you commit. You pick a target, change a variable, and see the output. You run three versions of next quarter and decide which one you're actually planning to. What if analysis help you make decisions with a clearer picture of the future.

To make that kind of scenario modeling practical in Excel, we've shared a free what-if analysis template from Vena below. 

How to Run a What-If Analysis That Actually Informs Decisions 

Here’s how to run a what-if analysis that helps you make better decisions.

• Start with a clear question. What-if analysis is only as useful as the question driving it. "What if revenue grows" is not a question. "What happens to our net margin if subscription revenue grows 10% but professional services revenue drops 15%" is. Define the objective in one sentence before you touch a spreadsheet. It keeps the analysis focused and makes the output easier to communicate. 

• Identify the variables that actually move the outcome. Every financial model has a handful of inputs that drive most of the output. For a revenue scenario, it might be unit volume, average contract value, and churn rate. For an expense scenario, it might be headcount count and average fully-loaded cost. List those variables before you start adjusting anything. Modeling the wrong inputs produces precise answers to the wrong question. 

   

• Set a target value and use drivers to get there. Once you know what you're testing, set a target -- a revenue number, a margin percentage, a cost ceiling -- and work backward. Driver-based modeling lets you answer not just "what if this happens" but "what would have to be true for us to hit this number." That's where scenario analysis becomes genuinely strategic rather than just arithmetic. 

   

• Compare scenarios against the base forecast, not against each other. The forecast is your reference point. Each scenario should show the dollar and percentage variance from forecast so leadership can evaluate the magnitude of each outcome. Scenarios that only compare to each other miss the most important question: how far off the current plan are we talking? 

   

• Document your assumptions. Scenarios that can't be explained are scenarios that don't get used. Before presenting any what-if analysis, write down what changed and why the assumption is reasonable. This matters especially when the scenario involves optimistic or pessimistic projections -- leadership needs to understand the logic, not just the number. 

How to Use Vena's Free What-If Analysis Template for Excel

The template has two working tabs: Instructions and What-If Modeling. Follow these steps to move through it.

Step 1: Review the Instructions Tab

The Instructions tab outlines the three-phase workflow: define forecasting data, run scenario modeling and review the scenario analysis. It also includes a field legend showing which cells are modifiable inputs, dropdown menus or fixed calculations.

Review this tab before entering any data.

Step 2: Import Your Forecast Data

The What-If Modeling tab is where you will build and compare your scenarios.

Start by importing the entities, departments and accounts you want to model. For each line item, paste the monthly forecast values into the appropriate period columns, from Period 1 through Period 12.

The template supports entity-, department- and account-level data across a full fiscal year.

Step 3: Set Your Target Value and Spread Method

At the top of the What-If Modeling tab, enter the target value you want the scenario to model, such as a revenue target or cost ceiling.

Then select a Spread Method from the dropdown:

• Proportional Spread distributes the change based on each line item's existing weight.

• Percent Increase applies the same percentage change across all included lines.

• Even Spread distributes the difference equally across the included lines.

Each method produces a different set of values in the scenario columns.

Step 4: Select Which Lines to Include or Override

Each row includes an In Use toggle and an Override toggle.

Set In Use to TRUE for any line you want included in the scenario calculation. Set it to FALSE for lines that should remain unchanged, such as accounts with contractually fixed values.

Use the Override toggle when you want to enter a specific value manually instead of applying the selected spread method.

Step 5: Review the Scenario Variances

The scenario analysis section on the right side of the What-If Modeling tab compares the original forecast with the scenario output.

You can review monthly scenario values alongside the original forecast, with dollar and percentage variances calculated automatically. Expand or collapse sections to examine individual periods.

The summary row at the bottom shows the total forecast, total scenario and full-year variance in both dollars and percentage.

Stop Planning Around One Version of the Future 

A single forecast isn't a plan. It's one bet. The organizations that navigate uncertainty well aren't the ones that predicted it correctly. They're the ones that already knew what they'd do when the numbers moved. 

What-if analysis doesn't make forecasting easier. It makes the decisions that follow from it better. That's the point. 

Download the free Vena What-If Analysis Template and start modeling scenarios before your next planning cycle.