• Mueller Terry posted an update 1 year, 11 months ago

    The Simple Cap Table is an Excel template that allows you to easily calculate the value of any cap table without having to have any prior knowledge of how to compute cap tables in Excel. Most importantly it lets you know exactly what the numbers you are working with are and gives you a good idea of what they will look like after the transaction closes. In this article we will discuss how to use the Simple Cap Table. We will also discuss what to do next once the transaction closes. After reading this article you should have the ability to understand how to calculate the correct values for all shareholders and debt holders in any given company and what to do after a transaction closes.

    A waterfall analysis is often the technical name used to define the method of computing exact values each debt holder and shareholder will receive upon any liquidation events of the business (e.g. an acquisition, an IPO, or another similar event). Essentially it is a series of mathematical calculations where you apply all the deal terms simultaneously to the entire cap table in order to generate the value of the entity as a whole. It essentially attempts to simulate the exact transaction that occurred or will occur between you and a potential acquirer or borrower of your business. It can however be applied to any business and their ownership structure because it can be used to approximate any ownership structure that can be applied to a company regardless of its equity structure.

    This may sound complicated and overwhelming but really it’s not. If you want to understand how to use a simple cap table template in Excel to make sense of the data you will need to start by learning about the three primary tables which will make up the cap table: the enterprise cap, the owner equity and the preferred stock. These are the most basic components and they are what most people think of when they hear about a cap table or equity index in Excel. In reality the concept goes much further than that. The cap table template uses these three components in order to make sense of the financial statements and the analysis conducted in order to provide a true picture of the owner equity, the enterprise cap and the preferred stock.

    To illustrate this point we can take a simple example to illustrate the way these three components interact with one another to give us a clearer picture of how they work together. Let’s say that Company X has two shares of common stock which has been issued and is now being traded as a secondary offering. Company X has acquired Company Z which is a competitor which is in the market for market value of the issued common stock. At closing the stockholders of Company X have voted to effect the acquisition by selling 50% of their shares in company X and acquire a share of market value from company Z. This transaction effectively changes the shareholders position from long term holders of common stock to short term stockholders of stock options.

    Now let’s assume that all of the investors who voted to exercise the option to purchase shares of stock from company X proceed to sell their shares of common stock and gain a profit. What is the manner in which these new shareholders will keep track of the transaction? What happens is that they will enter the transaction in their books as a purchase transaction and then note it as an asset for tax purposes by computing the capital gain inclusion. The transaction thus turns into an installment sale and all investors are reported as gainers.

    This is a classic example using spreadsheets to represent the purchase and sale transactions but we can extend this to a startup business also. How would a startup go about keeping track of its finances if it did not yet have any tangible assets to use as collateral and had to rely on seed money or other venture capital sources to meet startup expenses? Of course, there may be some difficulty in accurately determining the value of startup assets if the business is still in its earliest stages but using spreadsheets for the purpose of tracking income, profits, losses, equity, and so on makes the process relatively simple.

    In startups , a cap table allows a stockholder to determine the value of company A against company B through a mathematical formula based on the net asset value of A and net equity of B. Company A has issued stock options to stockholders based on the value of A. Company B has issued stock options based on the value of B. The two options are equal because the value of A is less than B and vice versa. The stockholder then exercises his or her right to buy or sell stock based on the fair market value of A minus B.

    The basic concept of the cap table is this: a stockholder wants to know how much he or she stands to gain or lose through ownership of A but does not necessarily want to be locked into a long-term commitment to B. It is for this reason that a dilution provision is added to a standard or limited liability company’s operating charter. Dilution allows a stockholder to enjoy a percentage point of dilution (or decreasing the total ownership stake) by buying more shares of A and fewer shares of B. A company with a dilution provision may issue additional common stock to service holders of A and B; however, if A and B become equal in value, then the company must issue more shares of B to service the remaining portion of A’s debt. Dilution is especially helpful to early investors who obtained their shares as a result of lottery winnings; during the initial period when the market value of A and B was unknown, they had little to lose by selling their A shares.