As we mentioned in the previous blog post, this week we are bringing you a step by step guide to conduct your own sensitivity analysis! In this post, we will be walking through sensitivity analysis step by step with two examples: one deals with personal finance, while the other deals with a small business. We hope you like it and learn something new. Let’s get started!

STEP 1 - KNOW YOUR OBJECTIVE

The first step is to know your objective, or the question do you want to answer. this needs to be fundamentally defined so it can work in any situation before asking what if. Keep in mind that this objective needs to have various inputs for sensitivity analysis to be effective. For the examples will bring you today, those questions are:

“How many units do I need to sell to break even?”

“What is my monthly mortgage payment?”

STEP 2 - DEFINE THE VARIABLES

Once you know your objective, you must then define the variables that go into it, and more importantly sidelines the variables that don't quantitatively affect that objective for now.

In the example where we look at a small business calculating breakeven sales for the month, the inputs are:

Selling price of the product,
The variable costs incurred to make and sell that product to a customer, and
The business’ monthly fixed costs.

In the example where we look at a monthly mortgage payment, the key variables are:

The price of the property,
The down payment made,
The interest rate, and
The loan duration

While noting the variables above, it is just as important to identify and separate the components that may matter at some point, but are not variables in the model. These could be:

The type of advertising done during the month. The type of advertising done with the amounts spent will likely have an impact on where the customers come from, but will not change the number of sales needed to break even.

The color of the product. Assuming the color doesn’t change the cost of the product, the number required to be sold to break even will be the same. It could be considered that additional color options may boost the amount of sales, but that still will not change the breakeven amount.

What competitors are doing. While competition may decrease the prospects for future sales, it doesn’t change the amount needed to breakeven based on costs. This aspect should be considered after the breakeven amounts are calculated as a part of the strategy moving forward.

How much money the buyer makes. This does matter when considering affordability, but the question we're looking to solve first is: “What is the monthly payment?” regardless of income.

The color of the house. This can be changed later and it's not going to affect how much you pay for the house although you might need to incur some extra cost to repaint it down the road.

The school districts. This can be very important for the future in terms of both opportunities for your children as well as the general value of the home. However, the monthly mortgage payment is generally calculated and locked in at the start of the loan.

Remember that these sets of factors can certainly be important when making decisions down the line, but they do not directly affect the model that you set out to create in step one. Knowing the difference between what information will affect your model through its inputs versus the information that will help contextualize your analysis is critical.

STEP 3 - SET UP THE MODEL

For many people this is the fun part. Setting up your model gives you an answer based off the data and assumptions that you've made with that data. during this up it's very important that you clearly label your data and check your calculations, so you understand what your inputs are and how they affect the output. Once you've done all that you're ready to move on. Here’s an example of what these models might look like:

STEP 4 - CHANGE THE INPUTS AND OBSERVE THE RESULTS

As you'll see below, our favorite way of performing this analysis is to copy the relevant input and output areas of my spreadsheet with relative references so I can view the changing results in real-time side by side with the original inputs.

For the cases we presented you today, we went in and changed a few variables to see the effect on the initial objective (the monthly mortgage payment and the break-even units in sales). Look at the results below:

STEP 5 - ANALYZE AND LEARN FROM THE RESULTS

Finally, it's important to observe and learn from the results. The value from this step comes in two parts: the first part is to understand how each input has a direct impact on the output. In the case of the mortgage example, you will learn that increasing the down payment or changing the loan term has a disproportionate impact on your monthly payment.

The second part involves linking those qualitative factors that we mentioned earlier to your analysis and evolving the model is necessary. Here's one that might look like for each of our examples:

Home Payment Example

I currently make $70,000 a year before taxes, for me to have a house that doesn't exceed $1,500 monthly payment, I need to put more money down towards the house I want or find a home in a different area. this other area might not have as nice school districts, but I'm comfortable with that if it means I can buy the home sooner.

Small Business Example

After running the numbers, it looks like the most effective way to cut advertising costs. but if I do that I might not sell as many and therefore might not break even as easily. maybe I can take another approach where I negotiate with my seller to change our deal about how they get paid. That way I can keep selling my product to my customers while being mindful of the risks I'm taking.

This fifth and final step that we've laid out here containing these two parts is the most important thing that you will read in this entire post. Once you develop the skills to put some numbers into a spreadsheet, it is very easy to create models and mess with the inputs to see what happens. The value of this analysis comes from taking those quantitative factors and tying them back to your reality. remember the numbers in the spreadsheet don't mean anything if you can't interpret and act on them. For our examples there's no point in saying that you can get your down payment down to $500 a month if you put up $300,000 if you don't have the money or aren't willing to do that. Similarly, it would be a lot easier for this business owner to break even if you didn't have to pay his rent.

Once you have all the quantitative inputs and outputs for your model, we recommend that you take those results and put them right next to the qualitative factors and weigh them against one another. By doing this consistently, you will allow yourself to ask those important "what if" questions with sensitivity analysis and interpret them in the real world.

We hope this helps you understand the concept of sensitivity analysis a little bit better, if you're interested in more content like this please be sure to email us and let us know we'll be happy to help you!

How to: Sensitivity Analysis