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 simple model for calculating the returns on the basis of 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 on the couple of inputs present in the model and then alter the values for seeing the way it holds impact on the IRR.
The Sensitivity Training
At the very first step, first setup the assumption table. For following inputs, we will turn up with assumptions:
• Operating Expenses
• Net Income Exit Multiple
• 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 value of the investments when one is ready to exit. The initial investments assumption simply represents how much amount of cash one put 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:
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 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 following line items:
• Gross Profit
• Marginal Cost
• Net Income
• Operating Expenses
• Exit Value
• Initial Investment
• Investor Cash Flow
In the 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 assumption of year one revenue. Subsequently the revenues will show growth in comparison to 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 example. Then, our gross profit calculation is simply the revenues minus the marginal cost.
Operating expenses for the 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 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 year five column, we need to calculate the exit value by simply multiplying the exit value to multiple assumption by the net income of years (=H21*C7 as example).
Now we can easily calculate the investor cash flow. The Cash flow is simply the net income added to 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 any investment nor any exit in the years. In the year five, the cash flow will be 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 have the basic model going and then understand the specific inputs that drive it, one can easily construct the sensitivity table. Two inputs that one want to flex are, the growth rate and the exit multiples. One wants to look 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.
In top left cell of area where one will place the sensitivity table, will reference the particular result of the IRR calculation. This particular cell represents output value on which one wants to measure impact of their assumption changes. In cells directly to 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, actual value in these cells are only numbers):
Now, one can create the sensitivity table by choosing the rectangle of the cells that it include both the rows of the growth assumptions and then the column of multiples. Go to 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. 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 it 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.
There are couple things to keep in mind about the sensitivity tables. These inputs of model need to be on same page at sensitivity table. Sometimes inputs can be easily moved around the model is constructed to accommodate the analysis, but that one limitation that is to be kept in mind.
Some can be tempted for linking the flex values in sensitivity table directly to input values. This won’t easily work because as these 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 rest of model will remain connected to the new cell.