A sensitivity Analysis- Financial Modeling
                     A sensitivity Analysis- Financial Modeling
About Us
MindCypress is an excellent platform for cognitive e-learning with a great 
progressive  course structure. We have been creating an impact on the 
online education industry,  since 2015. Currently, we are catering to most 
parts of the United States (USA), United  Kingdom (UK), Middle East, Africa 
and South East Asia for services like Classroom and  Live Virtual Training 
Courses. In today’s time, we are making our presence globally in  the field of 
e-learning. Professionals and scholars would get a career growth with  
MindCypress’s innovative self-learning & certification program. E-learning 
courses from  MindCypress gives you the convenience and flexibility to take 
sessions from anywhere  and indulge in the modules at your own pace. Our 
courses are best suited for people  who want to continue working while, 
studying and earn a certificate that can turn out to  be beneficial for their 
career growth.
One of the best things that you can do from a good financial model is to 
easily test many  different scenarios of business. A good model will even test 
the sensitivity level of the  results to the changes made in the assumptions. 
A better way of tackling both the
above-mentioned goals is to create a sensitivity table.
For demonstrating the working of the sensitivity table, try building a simple 
model for  calculating the returns on the basis of the hypothetical 
investment. Here we will try to  assume some investment amount, forecast 
the annual cash flow, and then calculate the  exit value. From the above 
calculations, we can easily calculate the internal rate of  return that is IRR. 
Our established analysis will have a look at a couple of inputs present  in the 
model and then alter the values for seeing the way it holds an impact on the 
IRR.
The Sensitivity Training
At the very first step, first set up the assumption table. For the following 
inputs, we will  turn up with assumptions:
• Operating Expenses
• Growth
• Net Income Exit Multiple
• Margin
• Initial Investment
• First Year Revenue
Growth assumption will simply represent how fast revenues for investment 
will grow. The  Operating expenses will then represent the annual overhead 
costs. The margin  assumption will somewhere help us in calculating the cost 
of goods sold. “Net Income  Exit Multiple” will assure us in determine the 
value of the investments when one is ready  to exit. The initial investments 
assumption simply represents how much amount of cash  one puts up for 
making the investments. Our beginning point is year-one revenue for the  
revenue growth.
Use the following values for these inputs as the corresponding 
assumptions:  15%
$1,000,000.
00  35%
5 x
$2,500,000.
00
$3,500,000.
00
This model will surely be very easy and simple as that one can illustrate 
easily how to  work on a sensitivity analysis.
Forecasting the Cash Flow
Let’s easily set up one simple layout for calculating the cash flows. At the 
top of the  model, the headings will be as “Year 0,” “Year 1,” and many more 
through “Year 5.”  Then, down the left-hand side column of the particular 
model, we will have the following  line items:
• Revenue
• Gross Profit
• Marginal Cost
• Net Income
• Operating Expenses
• Exit Value
• Initial Investment
• IRR
• Investor Cash Flow
In year zero, we will leave space blank values for most of the line items. 
For initial  investment values in the year zero, we will then reference 
our assumption of initial  investment and then make it a negative (=-C8 
for example)
For the revenue line item, set the year one revenues equal to the 
assumption of year  one revenue. Subsequently, the revenues will show 
growth in comparison to the  previous year’s revenue by the personalized 
growth rate assumptions (=D13*(1+$C$4)
for example).
Marginal cost is equal to the revenue that is multiplied by one minus of our 
margin  assumption (=D13*(1-$C$6) as an example. Then, our gross profit 
calculation is simply  the revenues minus the marginal cost.
Operating expenses for years one through the five shall be equal to the 
assumptions of  operating expenses. If one wanted to make their model 
more complex and  sophisticated, then they could simply add the inflation 
rate for gross this figure over time,  but they will keep it simple for the 
present time.
Net income is nothing but gross profit minus the operating expenses. And 
using that,  they now hold a simple and easy income statement.
Preparing an Exit
We have already calculated the initial investment line, so one can move on 
easily for  calculating the exit value. We prepared an assumption that the 
investment will be priced  at five and half times the net income. Then we will 
make our exit passage in year five, so  under the year five column, we need 
to calculate the exit value by simply multiplying the  exit value to multiple 
assumptions by the net income of years (=H21*C7 as an example).
Now we can easily calculate the investor cash flow. The Cash flow is simply 
the net  income added to the initial investment plus the exit value. For the 
year zero, cash flow  will be equal to our initial investments. For the year one 
through the four, the cash flow  will be equal to the net income as there is 
neither an investment nor any exit in the  years. In year five, the cash flow 
will be the sum of the exit value and the net income.  Finally, one can 
calculate the internal rate of the return. This can be easily done enough  by 
the use of the IRR function and then choosing all values in the cash flow line.
Tabling the Issues
Now that one has the basic model going and then understands the specific 
inputs that  drive it, one can easily construct the sensitivity table. Two inputs 
that one wants to flex  are the growth rate and the exit multiples. One wants 
to look at what impact these  certain assumptions will have on the IRR. If the 
impact is much significant, they will know  to be more careful when working 
on these assumptions or even relying on the result.
The top-left cell of the area where one will place the sensitivity table will 
reference the  particular result of the IRR calculation. This particular cell 
represents the output value on  which one wants to measure the impact of 
their assumption changes. In cells directly to  the right of the cell, one will 
place these values of the growth rates that one want to test:  0% 5% 10% 15% 
20%
In cells directly below the initial cell, one will place values of the net income 
exits the  multiples that one wants to tests (note: the “x” here is simply 
formatting, the actual value  in these cells are only numbers):
5 x
0 x
5 x
0 x
5 x
Now, one can create the sensitivity table by choosing the rectangle of the 
cells that
include both the rows of the growth assumptions and then the column of 
multiples. Go to  the data section within Excel and then select “table.” You 
will easily be prompted for the  row input and then a column input.
The row input must reference the growth assumption cells at top of the 
model. The  column input cell must reference the net income multiple 
assumptions cell. Tap okay and  the sensitivity table is proper and complete 
(although you want to format these output  values to be the percentages.)
The values in the represent what output of the model should be given to 
each  corresponding pair of the assumptions. Rather than manually changing 
the values for  testing each and every scenario, one can look at impact at 
once and then spot trends or  the optimal assumptions.
Pitfalls
There are a couple of things to keep in mind about the sensitivity tables. 
These inputs of  the model need to be on the same page at the sensitivity 
table. Sometimes inputs can  be easily moved around the model is 
constructed to accommodate the analysis, but that  is one limitation that is 
to be kept in mind.
Some can be tempted for linking the flex values in the sensitivity table 
directly to input  values. This won’t easily work because as this table flexes 
the values in its calculations  and then the flex values will change even. 
There is some way around this.
In this assumptions table, you can easily CUT and then paste the input 
values that want  to flex in the cell next to where actually they are. By 
removing these values, all  references in the rest of the model will remain 
connected to the new cell.
MindCypress will help you with the training. Contact us today!
Resource: https://blog.mindcypress.com/p/a-sensitivity-analysis-  
financial-modeling