Playing with Sensitivity Analysis

Not this week, but last week, I went to an Excel webinar where the presenter showed a very simple way of doing sensitivity analysis without data tables. The technique utilized the newest functions and array capabilities that Excel now brings.

I’ve used one of the functions but only in a straightforward way and did not push it in creative directions as the presenter did, but what he did opens up possibilities.

I’m going to keep it simple using revenue and expense as familiar terms that everybody uses but it will be kind of similar to what he did. The example doesn’t get any simpler than this, but I do want to put in my terms so that I will remember this.

SEQUENCE Function

The two features he showed was the SEQUENCE function and the # symbol. I’ve used the SEQUENCE function but only as from 1 to some end number. But, duh, you can actually use it to create a range of numbers that you want to deploy in an equation, like a range of interest rates, or a range of quantities.

Since we are talking about sensitivity analysis, we want to analyze a range of situations (in numerical terms) to see how sensitive the situation is to a change in a variable.

The SEQUENCE function looks like:

As per the image above, there are four information you need to supply: how many rows, how many columns, the start of the sequence, and the step increase or decrease.

Again, to keep it simple, I’m going to have a range of possible revenue numbers across the columns and a range of possible expenses down the rows.

To create a row of possible revenue streams from $100,000 to $380,000, increasing by $20,000, here is how the SEQUENCE function would be set up:

Note I asked for 1 row but 15 columns.

To create a column of expense numbers running from 50,000 to 190,000 using a step amount of $10,000:

Note in this instance, I asked for 15 rows and 1 column of expense numbers.

Also, note, and this is key for the next section, when you type in SEQUENCE, the formula automatically creates the proper number of rows or columns of numbers. You don’t have to copy that formula across or down. These series of numbers are called arrays. I believe SEQUENCE is often referred to as an array formula.

# symbol

And that array dynamic is what brings in the # symbol.

Now we are going to calculate the profit that one gets from these different revenue and expense combinations.

Profit = Revenue – Expense

Or, in formula terms:

D5 refers to the first revenue number $100,000. C6 is the first expense number $50,000. The # symbol indicates that D5 and C6 are part of an array of numbers. Once you key in that formula in cell D6, Excel then runs out that formula across all of the rows and columns. You don’t have to copy and paste.

In the image below, I boldfaced the border around cell D6 to indicate where the formula D5#-C6# lies. Once I entered that formula, the table then automatically then filled out.

At this point, you can apply conditional formatting to help you spot where numbers turn south. If you increase the range, say instead of 15 columns but 20 columns you will have to adjust the conditional formatting. Decreasing the range from 15 to 10 will automatically decrease the range of the conditional formatting.

(I tried playing around with the range in the conditional formatting box but I wasn’t successful in making it automatically extending the range beyond the initial range.)

More Complicated Sensitivity Analysis Situations

Now, I just did a rather simple example that generally does not happen in real life. Life is definitely more complicated than that.

You might have to play around with maybe quantities of inputs for revenue and expenses to generate different scenarios. I think you can get really creative with that. You might have an input section where you can designate the range, the start point, the increments.

This SEQUENCE and # combination can replace Data Tables. Data Tables are known to slow down the performance of the spreadsheet. This SEQUENCE/# combination might improve the performance; I just don’t know yet. I did a rapid search and haven’t found anything to indicate any performance comparison.

But they are definitely worth trying out.