What is a What If Analysis

Imagine you’re in charge of a big project at work, and you’re faced with lots of “what if” questions. What if the costs go up? What if you need to adjust your team’s size? Excel’s What-If Analysis tool is like your strategic partner in figuring all this out. It lets you play with different scenarios in your spreadsheets to see how changes might affect your project or budget.

For instance, you’re planning the budget for the next quarter. You can use What-If Analysis to see how changing your sales targets or costs could impact your profit margins. Or, if you’re aiming to reduce expenses, it can show you what needs to change to hit your goal.

Excel offers a few ways to do this. In this article, we’re focusing specifically on what-if analysis using the Excel Data Table tool. This tool lets you compare values for one or two variables in your formula. It shows multiple possible outcomes. It’s great for seeing how changing your assumptions affects financial results. This way, you can plan and decide with more confidence.

What-If Analysis: Example

Let’s look at an example. We want to see how much we can invest in a monthly retirement account. We want to achieve a particular return over a set number of years.

The following are the arguments we’ll use in our analysis:

  • -$200 – this is how much money we’ll invest in the retirement account each month.
  • 20 – this is the number of years till we reach retirement age
  • 4% – this is the annual interest rate

If you’re planning for the future, like saving for retirement or calculating the growth of an investment over 20 years, Excel’s Future Value (FV) function is a powerful tool to have in your toolkit. It helps you understand how much your money could grow over time, considering a consistent interest rate.

The FV function is great because it shows you the potential future value of your savings or investment, factoring in regular contributions or a single initial amount. Whether you’re setting aside money every month or have a lump sum to invest, this function can help you project how much you’ll have in the future.

The syntax for the FV function is as follows:

FV( rate, nper, pmt, [pv], [type] )
  • rate – (required) The interest rate per period.
  • nper – (required) The total number of payment periods in an annuity.
  • pmt – (required) The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv
  • pv – (optional) The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt
  • type – (optional) The number 0 (end of the period) or 1 (beginning of the period) indicates when payments are due. If type is omitted, it is assumed to be 0.

For our example, the formula would appear as follows:

=FV(B5/12, B4*12, B3)
FV Future value function in Excel

When using Excel’s Future Value (FV) function, there’s a little tweak we need to remember about the interest rate and the number of payments, especially if you’re dealing with monthly payments instead of yearly.

Here’s the simple breakdown:

  • If your interest rate is given yearly (like 4% a year in cell B5), but you’re making monthly payments or contributions, you need to adjust the rate for each month. So, you divide that yearly rate by 12 to get a monthly rate (because there are 12 months in a year).
  • Similarly, if you’re planning over several years, you’ll need to convert those years into months for the calculation. Multiply the number of years (cell B4) by 12 to get the total number of monthly payments or periods.

And about entering your monthly payments: you’ll put them in as a negative number. This is just Excel’s way of understanding that you’re taking money out of your pocket or account—it’s an outflow of cash.

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

How to Use a Data Table for What If Analysis

Let’s say you’re curious about how much money you could end up with in 20 years if you experiment with different interest rates. Instead of manually changing the interest rate every time and trying to keep track of the results, Excel has a cool tool that can do all that heavy lifting for you: the What-If Analysis Data Table.

Here’s how you can easily compare different outcomes:

  • First, make a list of the different interest rates you’re curious about.
  • Then, find the spot right above where your list starts and to the right of your interest rates. Here, you’re going to reference the original formula you made for calculating future value (that’s the FV formula in cell B7).

Since we have already created the FV formula, we will simply reference it in this cell.

=B7

What we’re doing next is using this spot as a starting point for Excel to apply each interest rate you listed to the FV formula. This way, Excel automatically shows you the future value for each interest rate you’re considering, without you having to change and remember each result manually.

You’re setting up a kind of experiment to see “what if” I used this rate or that rate, and Excel shows you all the possible outcomes in one go. It’s like lining up all your options side by side so you can easily compare them.

If you’ve ever tried to make sense of those super long formulas in Excel, mixing and matching cell references that just make your head spin, here’s some good news: there’s an easier way to compare how different interest rates affect your money over time.

Just follow these simple steps:

  • Highlight all the cells involved in your calculation. This includes your original formula, the list of interest rates you’re curious about, and where you want the results to show up. Imagine you’re working with cells from F2 to G19.
  • Go to the top menu and click on “Data.” Then find “What-If Analysis” and click on “Data Table.”
What If Analysis Data Table

A little box pops up asking you about the “Row Input Cell” or “Column Input Cell.” Since your interest rates are in a column, you’ll focus on the “Column Input Cell.”

What If Analysis Data Table
  • In that box, you tell Excel which cell has the interest rate you originally used in your formula. Let’s say that’s cell B5.
What If Analysis Data Table

Hit “OK,” and like magic, Excel fills in your table with all the possible outcomes for each interest rate you listed.

What If Analysis Data Table

What this does is take the interest rate from each row next to your formula and uses it to show you what would happen if that were the rate you got.

It’s like Excel is doing all the heavy lifting, trying out each interest rate for you and showing the results all at once.

Quick Tip: Making Your Formula Look Better

Sometimes, having your main formula just sitting at the top of your table can be confusing or just not look that great.

Here’s a quick fix to make it blend in or stand out, depending on what you want:

  1. Click on the cell with your main formula (for example, cell G2).
  2. Press Ctrl + 1 on your keyboard. This opens the Number Formatting window where you can change how the cell looks.
  3. In this window, find the “Custom” option on the left side. There, you’ll see a box where you can type something. Delete whatever’s in there.
  4. Now for the fun part: Press Windows key + Period (.) to open up emojis. Yes, you can use emojis in Excel! Pick something fun that relates to your table, like a money bag emoji.
  5. Click OK to finish. If you want, you can also change the color of the text in that cell to make it pop or match your theme.
Custom number formatting

Featured Course

Fundamentals of Financial Analysis

Whether you’re a newbie or have an MBA in Finance, you’ll FINALLY “get” the big picture. This comprehensive course will equip you with these critical skills – even if you’ve never taken a finance or accounting class.
Learn More
Financial Analysis Course Cover

Data Table with Multiple Arguments

Want to see how different years and interest rates play together? Let’s step it up.

Imagine you want to compare a bunch of interest rates and different time periods all at once.

Here’s how:

  1. Move the prototype formula to the cell directly above the first listed interest rate (in this example, cell F2).
  2. Create a list of years that will reside across the top of the results matrix (in this example, cells G2 through M2).
  3. Select all cells that contain the prototype formula, interest rates, years, and result cells (F2 through M19).
  1. Select Data (tab) -> Forecast (group) -> What-If Analysis -> Data Table.

In the Data Table dialog box, the Column Input Cell is the same as before (cell B5), and the Row Input Cell is set to cell B4 (this is the cell reference in the prototype formula that will be changed with each iteration through the top row of listed years.)

Improve Excel Performance

When you use the What-If Analysis with really big data tables—think hundreds of columns or thousands of rows—you might notice Excel starts moving slower. This happens because Excel is working hard to update the table every single time you change something.

But don’t worry, there’s a way to speed things up. You can tell Excel to wait and update the table only after you’ve made all your changes. Here’s how:

  1. Go to the “Formulas” tab at the top.
  2. Click on “Calculation Options” in the “Calculation” section.
  3. Choose “Automatic Except for Data Tables.” This tells Excel to chill out and not update your What-If Analysis tables until you’re ready.
  4. After you’re done with your changes and ready to see the results, just hit the F9 key, and Excel will update everything at once.

What If Analysis: Goal Seek

What-If Analysis in Excel isn’t just about data tables; there’s more under the hood. One of these tools is Goal Seek.

When you use Excel, you usually put numbers into formulas to get an answer. But what if you know the answer you want and need to figure out the numbers that make it happen? That’s where Goal Seek comes in. It’s like working backwards. You tell Excel the result you want. Then, it figures out the numbers for you.

Want to dive deeper into Goal Seek, see it in action, and explore detailed examples? Check out our comprehensive guide: Read our detailed article on Goal Seek.

Download the Workbook

Ready to put what you’ve learned into practice? Click HERE to download our practice workbook. Dive in and start experimenting with What-If Analysis in Excel today!

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.