One of the great things that a good financial model can do is test different business scenarios. A good model should also test how sensitive the results can be to changes in the assumptions. A great way to tackle both of these goals is to build a sensitivity table.
To demonstrate how a sensitivity table works, let's build a very simple model that will calculate the return on a hypothetical investment. We will assume a certain investment amount, forecast annual cash flows and calculate an exit value. From these calculations we can calculate an internal rate of return (IRR). Our sensitivity analysis will look at a couple inputs in the model and alter their values to see how it impacts the IRR.
Sensitivity Training
First, let's set up an assumptions table. We will come up with assumptions for the following inputs: - Growth
- Operating Expenses
- Margin
- Net Income Exit Multiple
- Initial Investment
- Year 1 Revenue
The growth assumption will represent how quickly revenues for the investment will grow. Operating expenses will represent our annual overhead costs. Our margin assumption will help us calculate our cost of goods sold. The "Net Income Exit Multiple" will help us determine the value of our investment when we're ready to exit. Our initial investment assumption represents how much cash we put up to make the investment. And year-one revenue is our starting point for revenue growth.
Let's use the following values as the corresponding assumptions for these inputs:- 15%
- $1,000,000.00
- 35%
- 5 x
- $2,500,000.00
- $3,500,000.00
This model will obviously be very simple so that we can easily illustrate how to perform a sensitivity analysis.
Forecasting Cash Flow
Let's set up a simple layout to calculate our cash flows. Across the top of the model, our headings will be "Year 0," "Year 1," etc. through "Year 5." Down the left-hand column of the model, we'll have the following line items:- Revenue
- Marginal Cost
- Gross Profit
- Operating Expenses
- Net Income
- Initial Investment
- Exit Value
- Investor Cash Flow
- IRR
In year zero, we will leave blank values for most of these line items. For the initial investment value in year zero, we will reference our initial investment assumption and make it negative (=-C8 for example).
For our revenue line item, we will set year one revenues equal to our year one revenue assumption. Subsequent revenues will grow the previous year's revenue by our growth rate assumption (=D13*(1+$C$4) for example).
Marginal cost is simply equal to revenue multiplied by one minus our margin assumption (=D13*(1-$C$6) for example). Our gross profit calculation, then, is simply revenues minus marginal cost.
Operating expenses for years one through five will be equal to our operating expenses assumption. If we wanted to make our model more sophisticated, we could add an inflation rate to gross this figure up over time, but we'll keep it simple for now.
Page 1 of 3 :: First | Last ::  Prev | 1 2 3 | Next 
|
