This is the 8th part of the Pro Cap Table training course on the cap table assumptions. In this series we go through the basics you need to know, then work sheet by sheet so you know how to make a seriously kick ass cap table.
There are 14 parts:
- What is a cap table and other important questions
- Cap table dilution step by step example
- Cap table dilution math
- Starting the cap table (The drop down menus we need)
- Shareholders sheet
- Deal calculations
- The cap table sheet
- The assumptions sheet
- Individual shareholder returns sheet
- Returns waterfall calculation
- The ESOP sheet
- The Common sheet
- The convertible notes and warrants sheet
- The preference shares sheets (From Series A to I)
You can join the course and get these sent straight to your mail box here:
The cap table assumptions sheet is where you put in the deal assumptions. These are then calculated in other sheets.
At the top of cap table assumptions sheet, you have the current date. This is today’s date and is calculated by a formula. If you want to look into the future (or past) you can change the date a few years into the future. if you use today’s date then you see the current status of your cap table.
Next, you need to sort out your starting shares. Input the shares you have as the model starts, then add in any ESOP allocation you have. The ESOP will increase at each round if you want. You can then see the total number of shares and the % ESOP you start with.
Next, you see each round from A to I. You input your premoney valuation and then you need to decide how to add the investment. You can either input the investment manually or you can opt to use a calculated pro rata..
If you want to input the investment manually, you just pop in the investment. Next to it, you can see what the pro rata investment calculation is.
The drop down menu next is how you decide if you are to use the manual input or the calculated pro rata. The next column of actual investment reflects the choice you picked.
The price per share is calculated for you. If the price per share is going up, that’s a good sign 😉
You input the closing date as the date the transaction closes. The model will add up any changes as the day before the closing date.
The last column is a switch for convertible notes. You pick the method of conversion if you have convertible notes. if you have no notes, then it doesn’t matter what you pick as nothing will convert at the round.
The next block of assumptions are for ESOP. There is only one assumption you need to make (or not). What is the post money ESOP %? All the formulas are there as FYIs. So let’s look at the S-A ESOP. You input 20% because that is what the investors want. You can see in the Current ESOP available is 3%… so the ESOP increase needs to be 3%. This means that the New ESOP shares you need to issue is 86,348. The New ESOP Stake is going to be 20%. The issued and outstanding shares under plan is the amount of shares you have granted to staff- This only increases if you grant more shares in the ESOP sheet. Since you have increased the ESOP by 3%, the available for issuance has increased too.
We mentioned the pro rata above. Here is a calculation to calculate the pro rata when you want to do it automatically.
You insert the investment amount planned, which is say $2m for the S-B. On the basis of the S-A, the S-A investors have a 31.3% pro rata. For them to maintain their stake at S-B, the max round size with pro rata is $2.9m. If your investors are opting to take their pro rata then you would flip the “2. pro rata” switch and this will feed in automatically. You may need to do a but of fiddling to get to the actual number. You could use this section to figure out the round size and then manually input the actual number in the manual section.
The last part of the assumptions sheet is where you stick in your liquidation preferences. You normally have a 1x. If you have 2x for some reason you can pop in 2x.
If this seems a little confusing, the best thing to do is to have a play and see what happens. That’s the easiest way to learn.
Click here to continue
Individual shareholder returns sheetShow off to your friends. Hit a social button to share the cap table love!