In this story you will learn about how to quickly so sensitivity analysis using an existing financial model. Here’s the story:

Christiane has just joined the project appraisal team for a hydro project. She didn’t yet have the time to look at the project documents and evaluate the existing financial model. She is having her morning coffee when she receives an email from her colleague Lea.

 

 

So in short, Lea is asking her to extract some information from the model and also provide her with the sensitivity table.

Christiane is a bit worried now. she thinks to herself:” what if the model is not in a good shape?”. She analyses the situation in her mind and come up with two possibilities:

  • Possibility 1 (best case scenario):The model already contains the main components and I will maybe do quick tweaks to get the information that Lea wants.
  • Possibility 2 (worst case scenario): if the model is not in a good shape then I will use one of my own models, plug in the inputs from the original model into my template model and extract the information I need.

After she has analyzed her options, she opens her laptop and looks for the financial model and goes through different worksheets included in the model. It’s seems to be a good model.

  • There’s a worksheet labeled “Inputs” containing model inputs.
  • A “Summary” sheet containing key model assumptions and results presented in form of tables and charts.
  • It contains the three financial statements in both semi-annual and annual basis
  • a number of calculation sheets all flowing same column structure and format
  • different integrity checks are built in and with a master check summing up all different checks included in the model.

Her first impression is that it’s a standard model. Of course she still needs to do a thorough cell by cell check to evaluate the model and importantly understand the base case inputs but she will need more time. She has to take what she has as given.

So she start drafting the email she wants to send to Lea:

Dear Lea,

Thanks. I hope you are doing great too.

I had a quick look at the model and my first impression is that it’s a standard model. However i will need more time to do an overall check and define our own base case. However, just for the purpose of this first teaser, please see attached and below an extract of the information you request…

She leaves the email and switches to the model to extract the sources and uses and insert it in the presentation and also in the email.

Next step is to prepare a sensitivity table. usually, she doesn’t like to do a generic 5% to 10% sensitivity on key inputs. She prefers to rely on studies and contracts. But she understand at this stage, they do not have the studies yet so she has to reply on the generic tests.

So she goes back to the model and to her surprise, the model does not contain a sensitivity analysis. She takes a deep breath and tell herself. “It’s no big deal. I have time to built-in sensitivity into the model myself”

She already has a template for sensitivity. She can simply insert it with the model and make the necessary links. So being conscious of time, she starts right away:

Step 1: She imports the “sens” sheet and the macro from her file into the financial model

To download the sensitivity template check the below link:

Sensitivity and Scenario Analysis Excel Template with VBA

Step 2: she defines 4 key sensitivity inputs, and the output that she wants to tracks while changing the sensitivity parameters.

step 3: defines the magnitude of change she wants to apply to different sensitivity inputs she defined in the previous step.

Step 4: she links the sensitivity inputs to the calculations in the model.

Step 5: she runs the macro to populate the sensitivity results

She’s done! She simply copy and pastes the sensitivity table into the presentation and the email to Lea.

Moral of the story: not having sensitivity built into a model is not a good sign. Because one of the main functions of a financial model is to apply sensitivities and tests different point of views and risks. However you can easily built in sensitivity into a model.