Financial Modeling: Sensitivity Analysis

To demonstrate how a sensitivity table works, let’s build a very simple model that will calculate the return on a hypothetical investment. We will assume a certain investment amount, forecast annual cash flows and calculate an exit value. From these calculations we can calculate an internal rate of return (IRR).

Our sensitivity analysis will look at a couple inputs in the model and alter their values to see how it impacts the IRR.

Sensitivity Training

First, let’s set up an assumptions table. We will come up with assumptions for the following inputs:

Growth
Operating Expenses
Margin
Net Income Exit Multiple
Initial Investment
Year 1 Revenue

The growth assumption will represent how quickly revenues for the investment will grow. Operating expenses will represent our annual overhead costs. Our margin assumption will help us calculate our cost of goods sold. The “Net Income Exit Multiple” will help us determine the value of our investment when we’re ready to exit. Our initial investment assumption represents how much cash we put up to make the investment. And year-one revenue is our starting point for revenue growth.

Let’s use the following values as the corresponding assumptions for these inputs:

15%
$1,000,000.00
35%
5.5 x
$2,500,000.00
$3,500,000.00

This model will obviously be very simple so that we can easily illustrate how to perform a sensitivity analysis.

Forecasting Cash Flow

Let’s set up a simple layout to calculate our cash flows. Across the top of the model, our headings will be “Year 0,” “Year 1,” etc. through “Year 5.” Down the left-hand column of the model, we’ll have the following line items:

Revenue
Marginal Cost
Gross Profit
Operating Expenses
Net Income

Initial Investment
Exit Value
Investor Cash Flow

IRR

In year zero, we will leave blank values for most of these line items. For the initial investment value in year zero, we will reference our initial investment assumption and make it negative (=-C8 for example).

For our revenue line item, we will set year one revenues equal to our year one revenue assumption. Subsequent revenues will grow the previous year’s revenue by our growth rate assumption (=D13*(1+$C$4) for example).

Marginal cost is simply equal to revenue multiplied by one minus our margin assumption (=D13*(1-$C$6) for example). Our gross profit calculation, then, is simply revenues minus marginal cost.

Operating expenses for years one through five will be equal to our operating expenses assumption. If we wanted to make our model more sophisticated, we could add an inflation rate to gross this figure up over time, but we’ll keep it simple for now.

Net income is simply gross profit minus operating expenses. And with that, we now have a simple income statement.

Making an Exit

We already calculated our initial investment line, so we can move on to calculating our exit value. We made an assumption that our investment will be valued at five and a half times its net income. We will make our exit in year five, so under the year five column, we will calculate our exit value by multiplying our exit value multiple assumption by that year’s net income (=H21*C7 for example).

Now we can calculate investor cash flow. Cash flow is simply net income plus initial investment plus exit value. For year zero, cash flow will simply equal our initial investment (a cash outflow represented by a negative amount). For years one through four, cash flow will be equal to net income since there is no investment nor any exit in these years. In year five, cash flow will be the sum of our exit value and net income.

Finally, we can calculate our internal rate of return. This can be done easily enough by using the IRR function and selecting all the values in our cash flow line (=IRR(C27:H27) for example).

Tabling the Issue

Now that we have a basic model going and we understand the inputs that drive it, we can construct a sensitivity table. The two inputs that we want to flex are the growth rate and our exit multiple. We want to see what impact these assumptions will have on IRR. If the impact is significant, we will know to be extra careful when making these assumptions or relying on their result.

In the top left cell of the area where we will place our sensitivity table, we will reference the result of our IRR calculation (=C29 for example). This cell represents the output value on which we want to measure the impact of our assumption changes. In the cells directly to the right of this cell, we will place the values of growth rates that we want to test:

0% 5% 10% 15% 20%

In the cells directly below our initial cell, we will place the values of net income exit multiples that we want to test (note that the “x” here is just formatting, the actual value in the cells are simply numbers):

4.5 x
5.0 x
5.5 x
6.0 x
6.5 x

Now we can create our sensitivity table by selecting the rectangle of cells that include both the row of growth assumptions and the column of multiples. We go to the data section (within Excel) and select “table.” You will be prompted for a row input and a column input.

The row input should reference our growth assumption cell at the top of the model. The column input cell should reference our net income multiple assumption cell. Click okay and our sensitivity table is complete — although you may want to format the output values to be percentages.

The values in this table represent what the output of our model would be given each corresponding pair of assumptions. Rather than manually changing these values to test each and every scenario, we can look at the impact all at once and spot trends or optimal assumptions.

Pitfalls

There are a couple things to note about sensitivity tables. The inputs of the model need to be on the same page as the sensitivity table. Sometimes these inputs can be moved around after the model is built to accommodate this analysis, but that is one limitation that needs to be kept in mind.

Some may be tempted to link the flex values in the sensitivity table directly to the input values. This won’t work because as the table flexes these values in its calculations and the flex values will change as well. There is a way around this, however.

In your assumptions table, you can CUT and paste the input values you want to flex into the cell directly next to where they are. Buy cutting these values, all the references in the rest of the model will remain linked to the new cell.

In the old cell where the values originally were, retype the input value. In the cell to the right (the one that is linked to the rest of the model) you now type over the original value and reference the value you just typed. You are now left with two “input” cell values: One is an input value that is only referenced by the other input cell next to it, the other links to the first cell and flows to the rest of the model.

You can now link the flex input values to the left-hand input value (the one that is only referenced by the cell next to it) and when you input the row and column values for the sensitivity table, you reference the right-hand input value which flows to the rest of the model. This way, when the table flexes the values of the second input, it will change the rest of the model without affecting our flex variables.

Sensitivity tables can be a very powerful tool in financial modeling and should be in any serious modeler’s toolbox. They can save lots of time in the planning process — rather than running several scenarios, you can present the management team with a sensitivity table that has already done it for them.