• Forecasting Shareholders’ Equity

    The shareholder’s equity is affected by the net income (retained earnings) and any share transactions such as capital raises, option executions and share repurchases.

    Share Repurchases are driven off management guidance and the market conditions. Share repurchases can be represented on the balance sheet as a separate account in treasury stock or netted into the common shares account. When shares are repurchased, cash goes down and so too does equity in an equal amount for it to balance.

    To forecast share repurchases, you need the following inputs:

    • Numbers of shares repurchased: Look to management footnotes for guidance. If there aren’t any, then forecast based on historical rates.
    • Forecast Share Price: To forecast the share repurchase drivers, use analyst EPS estimates multiplied by the expected earnings multiple. When in a bank, you will have access to institutional research, but this research is not always available for retail investors.If you have access to an online brokerage account, sometimes they will make available their research otherwise you can access some earnings consensus numbers from Yahoo.

    When doing share repurchase forecasting, do not forecast more in buybacks than the managements has forecast as outstanding.

    Options and Warrants Proceeds – look in the management footnotes for an up to date listing of the options and their strike prices. Determine which options can be exercised (they are fully vested) and assume that investors will be rational and only exercise options that are in the money (i.e. the strike price is at or below the share price).

    Example: Cisco SH Equity

    Step 1: Set up the spreadsheet and link the historical figures.

    Step 2: The ending common stock takes into account all stocks that are issued or repurchased throughout the year. Copy the information from page 42 of the 2010 annual report into the spreadsheet. Be sure to set up the formulas so that you do not need to enter figures in twice.

    Step 3: Common shares in Cisco are issued through option grants, employee purchase plans and restricted stock awards. The aggregate total of the stocks issued can be found on page 42 of the annual report.

    Page 67 outlines the stocks that were granted under the employee purchase program.

    Page 68 of the AR outlines the options that were exercised and their average exercise price. Use the average weighted option strike price and multiply it by the number of options exercised to get the proceeds from options.

    Page 69 outlines the restricted stock units that were vested.

    Step 4: The sum of these amounts will not match up with the aggregates found due to rounding errors so we are going to add an “Other” line to reconcile the differences.

    Step 5:  The issuance of common stock comes from options, warrants, capital raises and employee purchases. We have already accounted for the options and there were no capital raises, so the remainder must be from stocks granted under the ESOP.

    Step 7:  The number of shares repurchased can either be read from the table on page 66 (be sure to add Other Repurchases of Common Stock) or the summary table on page 42. We are not going to reconcile the differences in average share price and shares of stock repurchased, so link the $ value of shares repurchased from the cash flow statement.

    Step 7:   Stock repurchases can be used to reduce the value of common shares or expensed against retained earnings. Cisco has elected to allocate a portion to each so calculate the percentages. The amounts allocated to common stock and retained earnings are found on page 42.

    Step 8: The share based compensation expenses are listed on page 70 of the AR2010. Copy them to the sheet.

    Step 9: Link the drivers from the income statement and calculate share-based compensation as a % of the drivers.

    Step 10:  Add the calculations for retained earnings. The 433 starting figure is taken from page 42 of AR2010.

    Step 11: Forecast the common stock that will be issued. In absence of other information, straight-line the figures. 2010 appears to be an outlier for the restricted stock awards so we will use the previous years’ historical numbers.

    Step 12: To calculate the proceeds from the stock options, we will estimate an average strike price of $20 and a weighted average price of $18 under the ESOP. Calculate the total proceeds and link it to the common stock calculation table.

    Step 13: The amount spent each year for repurchases is the share price x number of shares. We’ll use Yahoo Finance’s information to forecast this (unless of course, you have access to CapitalIQ or Bloomberg). Earnings per share are provided for the next two fiscal years. To calculate 2013, increase the 2012 estimate by the five year growth estimate.

    Step 14: Use the price earnings ratio specified by Yahoo to come up with a projected share price.

    Step 15: Projecting the speed of share purchases requires you to make some assumptions. We will assume that Cisco’s repurchase pattern will be similar to previous years. This allows us to put 331 shares in 2011.

    From the AR, we can see that the remaining amount authorized under the share repurchase program is $7.0 billion (pg 66). That leaves us with approximately $921 mm in 2012, or 45 mm shares.

    Step 16: The share repurchases are allocated to common stock and retained earnings. Estimate the allocations and link it to the common share table.

    Step 17: Straight-line the share-based compensation % and calculate the total share-based compensation expenses.

    Step 18: Link the total share based-compensation expenses to the common stock calculations table.

    Step 19: Link the common stock and retained earnings to the summary table at the top of the page. Straight-line the other accounts.

    Step 20:  Link the accounts to the balance sheet.

    Step 21: Link the accounts to the statement of cash flows.


    Leave a Reply

    Your email address will not be published. Required fields are marked *