Sensitivity Testing Model Assumptions

We have all made them, those wonderful Excel models with their hockey sticks and their double-digit rates of return. You may have strong accounting and Excel skills, but a financial model is only as good as its assumptions. In this article, I’ll give some pointers on how to corral your assumptions and, most importantly, how to validate them.

Whether it’s a project or a new business unit, the typical financial model looks like an income statement. It will have rows for revenue and expense items, and columns showing these items as they change month over month. If this sounds too basic, you may skip ahead to the section on sensitivity testing.

You need to realize that the more you have played around with a system’s parameters so that it gives you better results, the less likely it is that it will work in the future – Marcel Link

The first thing you want to do is extract your assumptions from the formulas on the model sheet. For example, if you think you can add 100 users each month, type 100 in its own cell and label it “monthly new users.” Every formula that uses the 100 figure should reference this cell. Later, if you decide the correct figure is 120, you only need to change it in one place.

If you expect to add 100 users per month, this is a linear increase. A chart of your total users will be a straight line. If you think that transactions per user will also increase linearly over time, then your total transactions will show a quadratic increase – or order n-squared, as we old coders like to say. This chart will be a parabola. To validate your model, it is important to understand the order of its time series variables.

People sometimes mistake a parabolic increase for an exponential one. You will only have an exponential series if there is compounding, like an account that accrues interest, and then interest on the interest. For example, you may think that your user base will grow by 10% per year, but compounding it is optimistic. When in doubt, make a chart of the series and use the Excel “trendline” feature.

Ultimately, all your assumptions will flow to net income, like monthly user fees, the user adoption rate, and the associated expenses. You may also have constant values (or values considered constant for the scope of the model) like the number of U.S. car dealerships, and annual new vehicle sales. Extract all of these from the formulas.

The goal is to have only formulas on the model sheet, and all parameters on a separate sheet. By parameters, I mean the constants as well as the model assumptions. For example, you may have a variable date that marks the launch of a new phase, or a true-false flag that toggles a partner relationship.

Once you have the parameters on a separate sheet from the model, add formulas here to summarize the results. You might want to see a summary of the income variables, an IRR of the net cash flows, and maybe a chart. Now, for analysis, you don’t need to look at the model sheet. You can experiment with the parameters, and see the results on the same page. The model sheet sits in the background and does all the work, like a subroutine in a computer program.

I always include a time series chart with one or two key income items. This helps me to see the impact of changing parameters, plus it makes a great presentation. This is the ideal setup for analyzing the model – as well as goal-seeking a desired result, which brings us to the headline topic.

It’s human nature – the first thing you do with a financial model is set the parameters to justify your business case, and then you fixate on those settings. Psychologist Danny Kahneman calls this “anchoring.” All future discussions will now start with the anchor settings.

The way to prevent the model from misleading you, and your client, is to sensitivity test the parameters. First, though, let’s look at some common sense tactics that don’t require math.

• Find historical data. Always, always, always work with actuals wherever you can find them. If your client has previously rolled out one system, that tells you how fast they will roll out the next system. Also, link your model values to reality, like – how many trainers are available?

• Find objective estimates. Ask people for numerical answers to specific questions – without anchoring. How many dealers can you sign up in a month? At what price?

• Use the buddy system. Do not be the lone modeler. The last time I did one of these, I reviewed all the assumptions with my client’s CFO. I also sat with a financial analyst and walked her through each and every formula. Consultants talk about getting “buy in,” but this is really about getting accuracy.

• Write good documentation. For each row in the spreadsheet, write down what you think it means and how you got the number. This is a lot of work but, trust me, you will be surprised how valuable it is. Does your SAAR figure include used vehicles? Does your revenue include sales tax?

You also need to accept, and communicate, that the new project might not pencil. No one ever likes to hear that, but it’s better than making a bad investment. Sensitivity testing will show you what happens when the parameters move away from the ideal. For example, maybe it still shows a profit, but below your hurdle rate or outside your timeframe. By the way, it’s good form to establish these thresholds up front.

For each parameter in the model, you want to determine how “sensitive” the results are to a change in the parameter. Some of these, you will know from the order discussion, above. For example, if net income is linear with price, then a 10% increase in price will mean a 10% increase in income. On the other hand, if you modeled the decrease in sales due to increasing the price, then you have a downward sloping parabola (for which you should perform a separate supply and demand analysis – but, I digress).

Set up a separate sheet, like the one shown above, and list what you think are the most realistic values for each parameter. Next to these, list what you think would be its best and worst case values.

Run the model for each case, changing one parameter at a time, with the others set at their baseline values. Record the results for each run. In this case, my result variable is cumulative net income over some time horizon. You might be using IRR, discounted cash flow, or a breakeven period. For a breakeven period, “strong” means a shorter period.

The sensitivity for each parameter is simply the proportional change in results, divided by the proportional change in the parameter. Geometrically, you would like to know the slope of the line defined by the change in results – except that the parameters all have different units.So, what you are computing is the slope of this line on a logarithmic chart – rise divided by run, with your independent variable on the horizontal axis. The steeper the slope, the greater the sensitivity.

This analysis tells you which parameters you need to focus on, in terms of both model accuracy and critical success factors for the project. A sensitivity value of 1.0 is high. It means your results depend directly on this parameter. You will want to spend time validating this number and then, when the project launches, you will want to manage to it.