Tag: Sensitivity Analysis

Financial models which allow to run sensitivity analysis in order to understand the effect on e.g. a company's value depending on any change in its key assumptions

This is a collection of financial model templates for Real Estate businesses and its related sectors. The models included in this bundle are the following (all Excel models): Joint Venture / WaterFall Real Estate Financial…

Price: $200.00
Add to wish list
$200.00 – Purchase Excluding 7.7% tax

This Financial Model Template in Excel assists you to determine the financial feasibility for your next Tourism Project. The model comes up with monthly and yearly financial forecasts based on the estimated number of visitors…

Starting at: $0.00
Add to wish list
Purchase Excluding 7.7% tax

The purpose is to calculate, compare, and apply different theories of corporate valuation in order to assess their equivalence by using as a platform the retail store company Jumbo S.A. (a retail company in the…

Starting at: $0.00
Add to wish list
Purchase Excluding 7.7% tax

The FMCG Financial Model provides a framework to accurately forecast the financial statements of a FMCG company over the next 8 years. The model uses a detailed breakdown to estimate the company’s operating assumptions on…

Starting at: $0.00
Add to wish list
Purchase Excluding 7.7% tax

A sophisticated way to score the decision to pay off debt or invest as it relates to the ability to grow the investment amount.

Starting at: $0.00
Add to wish list
Purchase Excluding 7.7% tax


What is Sensitivity Analysis


Sensitivity analysis is a very useful tool to analyze the impact of any changes in key assumptions on the key output of the model, such as the NPV and IRR. It is also known as the “what-if” analysis since you have to go through a series of what-ifs to run different simulations and determine the impact of each assumption. By running a variable through different scenarios or circumstances, you’ll be able to determine how sensitive the output is and also find out how to keep the inputs constant.

For example, we’ll use the Sensitivity Analysis Model of a Gas Station to check how a sensitivity analysis work:

Sensitivity Table
Sensitivity Table

Here, we are checking what will happen to the Unlevered IRR respective to the assumptions of gas price (increase the price or decrease the price).

Normally, the IRR should go up if you increase the prices but in this case, it’s not because this model is built on a different logic of a constant profit margin per gallon which means, that the percentage profit margin per gallon in relation to the gasoline sales price gets lower if the gasoline prices increases.

Now, it depends if reality is indeed is like this if not, you will have to change the logic of your model. So, this sensitivity analysis can be a very useful tool to check if your model is working the way it should.

Sensitivity Analysis Tables
Sensitivity Analysis Tables

We then extended the sensitivity analysis instead of checking only two assumptions to ensure a wider coverage. Meaning, we are checking the changes in 10 main assumptions for the effects on the key outputs which is the unlevered IRR. As usual, each of these tables has 5 scenarios for two different key assumptions each (either increasing or decreasing).

Key chart of the sensitivity analysis
Key chart of the Sensitivity Analysis

Using the tables’ data, we then built a chart to have an easier and understandable view to check what happens to the unlevered IRR if we change each assumption by 20% up or by 20% down. Basically, which assumption has the most effect, ergo, which is the most important assumption driving the value of your model, in the above case, surprisingly, the most important assumption is the related to the non-gas revenues since a gasoline station today makes its money by selling non-gas items and not by selling only gasoline anymore.

 

Steps Used to Conduct a Sensitivity Analysis


To analyze how sensitive the output is, you will need to run it through a sensitivity analysis which can be complex depending on what you’re trying to make a study out of. When planning to conduct a sensitivity analysis, one needs to note down these parameters:

  • Basic structure – includes a combination of assumptions that are to be varied, ensuring which and how many assumptions are needed at a period, assigning values before the simulation, analyzing the correlations of the values, etc.

  • What to vary – set of different assumptions that can be chosen to vary in the model, such as the number of activities, objective in relation to the risk assumed and the projected profits, technical ratios, number of constraints and limits, etc.

  • What to observe – include the value of the variable as per the regular business plan, the value of the decision variables, and value of the variable after going through between two different strategies applied

To better understand the above-mentioned parameters applied in a model, here are the steps used to conduct a sensitivity analysis:

  1. Input the base case output to be put under sensitivity analysis. (e.g. V1)
  2. Calculate the new value of the input by measuring through the sensitivity test while all other inputs in the model are kept constant. (e.g. V2)
  3. Calculate the percentage change in the output and input respectively.
  4. The sensitivity is then calculated by dividing the percentage change in output by the percentage change in input.

The process is repeated to the rest of the inputs until the sensitivity figure for each of the inputs is fulfilled. Therefore, if you arrive at a higher sensitivity figure, the more sensitive your output is to any change that might occur in your business cycle. The same also applies vice versa.

 

Uses of a Sensitivity Analysis


One of the best uses for a sensitivity analysis is for building a model for economic decision making. All the data provided will be a great reference to come up with the best decision and also can be fully utilized repeatedly to conduct a sensitivity analysis. The model will help the user to understand the risks and benefits, uncertainties, limitations, and scope of the model. Since most of all the decisions aren’t definite, to conduct a sensitivity analysis will be a great tool to test various assumptions in different simulations, arriving at the most optimal solution or answer about the uncertainties in decision making.

There are many more uses that a sensitivity analysis is built for, such as:

  • Indicating the sensitivity of simulation to assumptions of the input values in a model
  • Predicting the effects of different scenarios and decisions
  • Testing the robustness of the values
  • Assessing the risks of a strategy or the business plan
  • Determining if the optimal value criteria are reached or still have space to grow
  • Identifying the dependency of the outputs to certain inputs, whether it is helpful or risky
  • Acting as an error checker of the model
  • Building informed and appropriate decisions or strategy
  • Removing redundancy in the model structure
  • Representing as a tool to communicate between entities
  • Etc.

 

Sensitivity Analysis Example Models


Building a sensitivity analysis model isn’t as simple as it sounds. It takes skill, experience, and the equivalent know-how to be able to fully utilized the usefulness of the model. If you are looking for sensitivity analysis example models, you can check out our list of financial model templates which included sensitivity analysis example models. You will be able to see how they function and you will also learn how to build one for yourself. By using the template as your base model, you will be able to build a working financial model with a sensitivity analysis section included. The financial model templates are ready-made by expert financial modelers that have substantial financial modeling experience and industry know-how, so you don’t have to hire a professional to build a fully customized model for you.