Excel 2024: Do 60 What-If Analyses with a Sensitivity Analysis

October 31, 2024 - by Bill Jelen

Goal Seek lets you find the set of inputs that lead to a particular result. Sometimes, you want to see many different results from various combinations of inputs. Provided that you have only two input cells to change, the Data Table feature will do a sensitivity analysis.

Using the loan payment example, say that you want to calculate the price for a variety of principal balances and for a variety of terms.

You have a three-variable model for car payment. You want to do a sensitivity analysis with Principal from $21K to $30K and Term from 36 to 72 months.

Make sure that the formula you want to model is in the top-left corner of a range. Put various values for one variable down the left column and various values for another variable across the top.

The formula calculating payment has to be the top-left corner of the sensitivity analysis. Below that, enter 21000 to 30000 in 1000 unit increments. To the right of the top left cell, enter 36, 48, 54, 60, 66, 72.

From the Data tab, select What-If Analysis, Data Table.

On the Data tab, under What-If Analysis, choose Data Table.

You have values along the top row of the input table. You want Excel to plug those values into a certain input cell. Specify that input cell for Row Input Cell.

You have values along the left column. You want those plugged into another input cell. Specify that cell for the Column Input Cell.

The data table dialog asks for a Row Input Cell and a Column Input cell. Since you have monthly terms across the top row, those should get plugged in to B2. The loan principal along the left column should be plugged in to B1.

When you click OK, Excel repeats the formula in the top-left column for all combinations of the top row and left column. In the image below, you see 60 different loan payments, based on various inputs.

The results of the sensitivity analysis range from a low of $341 a month to $902 a month. In this screen shot, a color scale marks the highest numbers in red.

Note

I formatted the table results to have no decimals and used Home, Conditional Formatting, Color Scale to add the red/yellow/green shading.

Here is the great part: This table is live. If you change the input cells along the left column or top row, the values in the table recalculate. Below, the values along the left are focused on the $23K to $24K range.

Change the numbers in the top row and left column and the data recalculates. In this image, with months from 48 to 63 and prices from 23K to 24K, the monthly car payments range from $418 to $553.

Tip

You can build far more complex models and still use a data table. In my podcast 2141 "Will Asteroid Bennu Strike the Earth" on YouTube, I had a model with 100K NORM.INV and 100K VLOOKUP. Those 200,000 formulas were sent to a SUM function that summarized them. I used a Data Table to run those 200,001 formulas 100 times. The whole thing recalcs in about 11 seconds.

Thanks to Owen W. Green for suggesting this tables technique.

Bonus Tip: Create a Data Table from a Blank Cell

Note

If you took a class on financial modeling in college, you likely used a textbook written by Professor Simon Benninga. He showed me this cool Excel trick.

Simon Benninga tells a story of a game called Penny Pitching. You and another student would each flip a penny. If you get one head and one tail, you win the penny. If the coins match (heads/heads or tails/tails), the other student gets the penny.

It is simple to model this game in Excel. If RAND()>.5, you win a penny. Otherwise, you lose a penny. Do that for 25 rows and chart the result. Press F9 to play 25 more rounds.

This is known as a Random Walk Down Wall Street. Simon would point out a result like the one below, where a hot young stock analyst is on fire with a series of wins, but then a series of losses wipe out the gain. This is why they say that past results are not a guarantee of future returns.

25 rounds of penny pitching. A formula =IF(RAND()>0.5,1,-1)+B3 keeps track of the cumulative winnings or losses.

Instead of 25 trials, extend your table in columns A and B to run 250 trials. This would be like playing one round of penny pitching every work day for a year. Build a row of statistics about that year, as shown below.

In G14:L14, calculate statistics from the current 250 rounds of Penny Pitching. What was the Max, Min, Average, Win Streak, Lose Streak, and Final result.

Create an odd data table where the blank cell in column F is the corner cell. Leave Row Input Cell blank. Specify any blank cell for Column Input Cell.

The blank cell in F14 becomes the top left corner cell of the analysis. Select F14:K34. In the Data Table dialog, leave the Row Input Cell blank and choose any blank cell as the Column Input Cell. This only works because of the RAND() in the model.

When you create the table, Excel runs the 250 coin flips, once per row. This 30-row table models the entire career of a stock analyst. Every time you press F9, Excel runs the 250-row model for each of 30 years. You can watch an entire 30-year career be modeled with the simple press of F9.

You've now modeled 250 flips times 30 iterations. You can see the stats over all and get a picture for the range of possibilities.

Thanks to Professor Simon Benninga for showing me this technique.

This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by Lizzi Sassman on Unsplash