I thought it might be useful to post up a model cap table (Cap Table Model with Waterfall). This cap table can be used by a pre-funded startup and then a financing can be layered in. In other words, it shows both pre-money and post-money very clearly. It is a simple model and NOT perfect, but it will work very well in most situations for you. Here are things to note:
1. Cells that are shaded yellow are input cells. For example, cell E2 is the spot to put in the negotiated pre-money valuation.
2. The model includes a simple waterfall analysis using both participating and non-participating preferred (see line 44 and then columns M and O). The larger the preferred stock liquidation preference the larger the impact of participating preferred. Play with the investment numbers (increase them in column L).
3. The option strike price in cell J1 is arbitrary. I just set it at about 1/4th of the calculated Series A price. But you should ask your accountants and lawyers about strike prices and Section 409A of the tax code.
4. The green box at row 45 just provides a nice double check on post-money valuation calculations.
5. Columns I and J are important as they show the % of preferred stock ownership compared only to preferred stock outstanding. This is important as there are often charter or contractual provisions that require a preferred stock only vote.
6. You really should try to focus on cell formulas. They are simple, but critical to understanding how the cap table works and how valuable the cap table can be to your understanding of your stock holdings.
So….enjoy the model. BTW, when I ask a company for its cap table (which I typically do as soon as I am actually interested in digging in further), I expect to get it in Excel (not PDF) and I expect to get it quickly. If it takes a week to show up in my inbox I assume that the management team was not well prepared to pitch in the first place.
Ask any questions in the comments. Thanks.