Share Calculations.  Since you have mastered depreciation and other sub-calculations, share calculations are straight forward.  Please download the free Excel spreadsheet share and option price download:  Shares.  This template builds on the revolver example spreadsheet for ease of construction.  Again, the sheet is truncated to focus on the issue at hand.  Determining the dilutive effect of options and warrants requires an ongoing valuation of the company for share price purposes.  Without a very long projection period, it can be impractical to use a rolling DCF valuation.  A rolling DCF projection would also have to take into account dividend policy and restrictions by debt holders.  This is modeled in the “Shares B” tab.  First, a review of the simpler “Shares A” tab using an EBITDA multiple evaluation.

  1. EBITDA Valuation.  In rows 53 through 63, a simple EBITDA multiple method is used to determine the total equity value of the enterprise.  The fixed multiple calculates the then year value of the equity.  Multiple contraction and expansion is not explored here.  Often, this effect is counter-cyclical (low multiple peak of cycle, high multiple trough) because valuations and future expectations are determined more by DCF methods, but is incorrectly applied in a co-cyclical method.   Be very careful in (a) using a multiple in the first place and (b) assuming a change in multiple over time.  Note that in this example the value fluctuates widely when EBITDA moves in year 2012.  This is always a hazard of this method.  The fluctuation was left in to illustrate a dilution effect in the share calculation.  Even though EBITDA valuation methods have limitations, they can be quite useful in option and restricted stock / share pricing because EBITDA can be readily determined.  Using DCF for options and share values is problematic because the necessary forecasts are difficult to agree on.  The tax shelter on options is explained below. 
  2. Share Value.  Rows 64-108.  This is a rather simple method of calculating fully diluted shares outstanding.  The important inputs are the equity value and the number (here expressed as a percentage) of shares or options issued each year.  It is assumed that strike prices are set using fully diluted shares and that new options are issued with a strike price equal to the then agreed value.  This sheet also assumes that new options are fully vested.  While many options have a vesting period, most allow for full vesting upon a change of control.  The fully diluted calculation in the model represents a transactional share price.  If options are priced with a method different from how they are value here, (based only on shares outstanding, for instance) simply add additional calculations to determine strike prices.
    1. Total share count and value.  All shares counts are added on row 71 to determine the total number on a fully diluted basis.  The warrants are assumed to have no anti-dilution provisions and have a nominal strike price.  The options are divided into two traunches for clarity in value.
    2. Option effect.  Rows 75 and 76 use percentage of outstanding (un-diluted) shares to determine the number of options granted each year.  Use a method that matches your option program.  The wedges on rows 84-93 and rows 96-105 determine the dilutive effect of the options.  Option holders (unless they are making some kind of play for voting rights) will not lose money by exercising under-water options.  For this reason, options are not “anti-dilutive”.  Because of this, if you try to short-cut the wedge with weighted average strike price, you can get a false result when the share value is above the lowest strike price, but below the weighted average as in the year 2012.  Each row of the wedge contains a simple formula that does not allow anti-dilution (the if statement) and calculates the number of shares that need to be purchased to satisfy the economic value of the option.  To explain, look at the management options in cell D85.  These options are worth $8.49 per share ($34.45-$25.96).  Management holds 50,000 options.  If they exercise them, they must either pay the strike price or elect (if so able) a cashless exercise of the options.  Management expects $424,491 in value (see M85).  If they elect a cashless exercise, the company would have to come up with (dilute themselves by) 12,323 shares at the current price in the event of a transaction.  If management has $1,297,916 million in cash to pay the strike price, then the company will have to come up with 50,000 shares.  However, the company can go purchase 37,941 shares on the market from selling shareholders with the check they were handed by management.  The other 12,059 have to come from another source.  In either case the company is diluted by the economic value of the shares, not the 50,000 shares.  Note that there may be some rare cases where the strike price is paid, sits on the books as cash (thereby raising the value of all equity) and the full gross number of options is dilutive.  If this occurs, just wire in the strike price to the EBITDA valuation.  This is similar to the NQSO example below.

The management options are modeled as Incentive Stock Options (“ISO”) employee options are modeled as Non-Qualified Stock Options (“NQSO”).  A non-qualified stock option generally results in a compensation tax deduction for the employer and taxable income to the employee (with the strange result that the IRS really gets nothing net and employees pay).  The tax deduction (if the company is able to utilize it – always consult your tax experts) can be material to a transaction’s value.  If the new owner captures this value, it can often be added to a negotiated price as additional “cash”.  In a going concern, it is a reduction in taxes paid.  In both cases, it is appropriate to capture this value (row 108) and adjust model taxes (which will automatically change balance sheet cash) and/or cash for EBITDA valuation purposes (row 62).

3.    Rolling DCF.  The “Shares B” tab is the same as the “Shares A” tab, except that it uses a rolling DCF calculation rather than an EBITDA multiple.  The cash flow input and continuing value are a straight forward unlevered cash flow calculation.  The Gordon Growth method is used for ease of calculation in terminal value.  Filling across the XNPV formula with a fixed end column for date and cash flow will not work, because the formula will not know the start period – which is the beginning of the year being discounted.  Using the regular NPV formula will mis-represent the timing of the cash flows as previously discussed.  Because of this, a discount factor, in row 74, is used.  The discount factor can be applied in a number of ways to each year’s cash flows to determine the then year valuation for the company.  A laborious, but illustrative, method is to make a wedge of discount factors and multiply them against each year’s cash flow, and then sum up the resulting discounted years.  This is done in rows 89-102.  The vertical column of sums represent the value in each successive year.  You could then either link to these sums across the enterprise value or use thehorizontal / vertical technique to move the cells.  A more elegant way is to use SUMPRODUCT to multiply by the discount factors, with a fixed range of discount factors.  This is shown in row 66.  Notice that the formula for the cash flow “runs off” of the block of cash flows.  This is dangerous as any stray numbers or entries to the right of the cash flow will either pollute the answer or result in an error.  This is quick, but not recommended.  If you do decide to use a technique such as this, color the run-off zone black or some other color to warn users not to type in that area.  An even better, but more complicated, method is to use the OFFSET function.  This function returns a range/array that can be used against the range/array in SUMPRODUCT.  In row 67, this formula is used to drive enterprise value.  Note that it will always refer to cells inside of the populated areas in both discount factor and cash flow.  Like the other SUMPRODUCT formula, it only requires one row of discount factors.  Then, like with the EBITDA valuation, debt is removed from the unlevered value and cash and a tax shelter is added to produce equity value in row 71.  You could, obviously, apply a LBO / private equity valuation to equity by looking at only the cash flow available to equity in the form of dividends.  If you do so, ensure that you (i) take into account dividend restrictions in credit facilities and (ii) actually make dividends out of the model so that you are not earning interest on cash that you already dividended out (levered cash flow is not equal to equity cash flow because of compounded interest on cash).