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.
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:
This model will surely be very easy and simple as that one can illustrate easily how to work on a sensitivity analysis.
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.
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.
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):
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.
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.