How to Perform a Sensitivity Analsis

While clearing out my MBA locker the other day, my friend asked me "how do you do those sensitivity analyses -- they're so cool!" Colin, this is for you.

The purpose of a sensitivity analysis is to answer this question: "how bad could it get if the assumptions you're putting into your analysis are erroneous?" The S.A. isolates two key variables -- usually discount factor and EV/EBITDA multiple -- to answer this question.

I often perform a SA when doing a discounted cash flow (DCF) analysis. (By the way, a DCF is simply a way to determine what the projected future cash flows of a company are worth today.) We'll assume this is the case for this example.

Before we get to the SA, we first have to establish a base-case DCF.

Establishing a base-case DCF:

1. Project out the next 5 years of cash flows. Doing any more than 5 years would be presumptious -- subject to too many unforeseeable variables. To project cash flows, take NOPAT (EBIT*(1-T)) and then adjust for working capital changes and depreciation and amortization. [The reason for staying at "operating level" metrics is because often this is done in an LBO setting, and PE firms will recapitalize the company, so they do not want their numbers to be complicated by irrelevant debt PMTs.]
2. Do this for the next 5 years.
3. Use the Constant Growth Model to find the present value (determined at Year 5) for all future cash flows. CGM = D1 / (WACC - g). Here, D1 is your projected cash flow in year 6, and g is your long-term growth rate (usually 3.5%, since that is historical nominal GDP growth).
   
4. Discount all those CFs by your weighted average cost of capital.
5. Sum those #s up -- this is your base-case discounted cash flow valuation of the firm.
   

Here's the important part: did you notice how the key to your DCF was the assumption of your growth rate (here, our base is 3.5%) and the discount rate? If you changed those inputs, your final output would be much, much different.

Bankers want to cover their butts (both for pride and liability reasons!), and so instead of giving a *precise* number to their clients (ie, "We think we can sell your company for $X million dollars") they give a *range*. Often, this range is the result of the SA that they apply to the base-case DCF analysis. Here is how it works.

1. Link a cell to the output you'd like to sensitize. For example, below I sensitized D41, which is the present value of a) the cash flows from 2009 through 2013, and b) the Gordon ("Constant") Growth Model.



2. The row (to the right) and the column (below) your sensitized cell will be your alternative inputs for the sensitivity table. I use 5 cells in each direction. The middle of those 5 cells is the "base-case" variable; in other words, 3 cells to the right of the sensitized cell is the 3.5% growth rate and 3 cells below the sensitized cell is my base-case discount rate (let's say it's 11.7% for our company.

It should look something like this:

...to be continued

MN Venture Capital Funding at 14-year low

This chart shows quarterly equity investments into US-backed venture capital since 2001. It comes courtesy of PriceWaterhouseCooper's MoneyTree report.

Only $3 billion was invested in Q1, an all-time low. That number is essentially half of the $5.7 billion from Q4 '08.

No sector was spared. Measured by both dollars spent and deals done, the numbers were down.

The sectors that grossed the highest net financing were (no surprise) software, BioTech, and Medical Devices. As a whole, they made up 53% of the financing. The sectors with the least financing were was electronics and retailing, with $27 million and $28 million, respectively.

Spotlight on Minnesota

The most active investing group -- which has an office here in Minnesota -- was Oak Investment Partners. Oak made 13 different investments in the quarter. It would be interesting to ask Jerry Gallagher why Oak was relatively bullish on the quarter -- ie, whether they have a fundamentally different view of the economy, or whether it was an isolated incident based on good deals in front of them. Per the Star Tribune, 4 Minnesota companies received a total of $56.1 million. The med-device startup Atritech received $30 million -- more than half of MN's total funding.