A sensitivity Analysis- Financial Modeling
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.