The analysis of an investment is an examination of the tradeoff between expected cash flows and the risk to those cash flows, resulting in a discounted present value.  Our spreadsheets are split into a Summary, Valuation, Debt Details, and Debt Financing template.  If a firm does not have significant debt, the templates related to it are removed. 

The Summary page is split into Risk and Growth Assumptions, Results i.e. equity pricing, Enterprise, i.e. firm pricing and debt and tax effects, and a chart of layout of the cost structure of the firm.  Assumptions made are macroeconomic, related to growth and cost of capital, and market information related to the firm.

Note:  Discount Rate, Cost of Capital, and Expected return are used interchangeably in this document.

Conclusion
This modeling exercise can yield many counterintuitive results for the novice trader, and sometimes, even for seasoned ones.  We strongly believe that a lot of intangible factors are built into the firm’s growth numbers.  An experience user of our system can tell in minutes if a stock is cheap or expensive, or at least, not expensive or not cheap.  The difference between cheap and not expensive is that a cheap stock should be bought, whereas a stock that is not expensive should at least not be used in a short strategy, even if certain risks make one uncomfortable in a long position.

Risk Assumptions

1 Year Treasury Yield

10 Year Treasury Yield

Beta (correlation of a stock’s returns with a proxy of the entire market e.g. S&P 500)

The interest rate ‘spread’ of the firm’s bonds i.e. their yield minus the yield of Treasury bonds of similar duration, (estimated in the absence of market information.)

We place a maximum limit our beta input at 2.25.  The reason is that the market seems to ignore a substantial amount of day to day volatility in high growth stocks.  Additionally, we use a minimum beta of .75.  The market would rarely set a risk premium of 0 if a stock were illiquid or not move for months. 

These factors are used to calculate the Risk Premium, which is the return investors expect above a return from Treasury bonds for investing in the securities of the firm.  The expected return is calculated for the firm as well as its stock.  These two are distinct, because private market and internal budgeting decisions can carry substantially different discount rates from public market pricing of the firm’s stock.  The discount rate applied to the firm reflects the return an investor would expect to purchase a stake in the firm in a private market transaction.  The discount rate applied to the stock reflects the expected return expected by a broader set of investors reached through a public offering.

Growth Assumptions

Growth Retention         This is the change from year to year of the growth rate.  For the mathematically inclined, this is the second derivative of the revenue.  Using a formula allows us to project growth into the future mechanically, i.e. without making any judgment calls.  The next year may be influenced by Street analysts or management statements, but future years are almost always a mathematical function of past growth.  This project was begun skepticism, if not suspicion of the numbers touted by the sell side, and we firmly believe a general mathematical solution is far superior to the prejudices inherent in human forecasts.  This solution is not ‘naive’, in that it takes into account cyclical, seasonal, and firm specific factors that might impact future growth.

Terminal Growth 
This is resulting growth in year 10 of the projection, in a band around nominal GDP growth ranging from inflation to 10%.  We allow higher terminal growth for the very rapidly growing firms, but typically not into double digits.  The mean is slightly below nominal GDP, as the majority of the growth of an economy is captured by small firms.

Enterprise  
For firms with debt, the leverage must be included in the total market value to calculate appropriate multiples, and its impact must be taken into account to calculate the taxes and capital structure risk to the equity.  The reverse is also true, in that the market price of the equity carries a tremendous amount of information on the perceived risk of the debt and hence, the operating and financial health of the firm.

The Cost Structure chart graphically illustrates the revenue and cost projections on the next template. 

Valuation

The latest two years and the latest and comparable year ago quarter are used to make projections of revenue and costs.  The revenue projection can also take the prior quarter revenue as an input in order to calculate certain boundaries on the growth rate.

We then focus on the cost structure of the firm, trying to see what the most likely scenario of costs is going forward, partly based upon existing trends, partly upon economic imperatives.  All costs and capital expenditures are calculated as a % of revenues.  If liability information is available, it allows for a calculation of interest costs independent of revenues.

The resulting difference between revenues and costs gives us a sense of the cash the firm will generate going forward.  When it is discounted using our discount rate for the firm, it gives us the present value of the firm.  The terminal value, which often represents a large percentage of the value of a firm is calculated using the formula for a growing annuity. 

The free cash flow to equity is calculated after subtracting interest and taxes.  The resulting projection is then discounted using the cost of capital to the equity. The difference between the value of the firm and the value of the equity is explained by the value of the debt and taxes as well as the different volatility of public vs. private market value.

The calculation of equity value incorporates the effect of expected dilution.  Such dilution can occur as a result of stock issuance, or, convertible bond or option issuance.

The last few lines of the sheet are accounting reconciliations, which give us a data error check, as well as a number comparable to announced earnings by companies.

Debt Schedule

This sheet is meant to detail the maturity schedule of the liabilities of the firm, and, if available, details on its duration and specific tranches.

Debt Financing

This sheet projects the financing needs of the firm for the next few years, as well as activities that generate or consume cash.  If a firm relies heavily upon short term financing, we extend the duration of its liabilities to measure the impact of the unavailability of short term debt under adverse market conditions.