This is the 6th part of the Pro Cap Table training course on the deal calculations. 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:
All the fundraising deal calculations are done in one sheet called ‘Calculations.’
There is a tonne of calculations here, so I’m just going to explain the high level.
There are no assumptions inthe deal calculations sheet. This pulls in links from each sheet and does the calculations. These then feed into all the other sheets.
The first part illustrates everything up to the price per share at every series of fundraise from A to I. In one look you can see the effect of each and every round.
The next section is three checks. If everything says ‘OK’, it’s OK. You can see here that there is a ‘-1’ in some cells. This is fine. When you do deals, there are fractionalisation of shares. You need to deal with them somehow. This sheet lets you know that you need to do something about them. Another note is that if you run experiments and forecast future raises, there will be errors since you didn’t input all the details you need to… but that’s cool since you are testing.
If investors do pro rata, or can, this is something you want to know. This shows you that the series-B investor does 31.3% pro rata. So if the S-B investor is to do a $2m investment, then the round can be up to $2.9m to ensure that the S-B investor maintains their share. There are iterative calculations here if you increase the round size, it increases the dilutions, which changes what the pro rata for the investor needs to be.
To calculate the price per share, you need to know the number of shares outstanding immediately prior to the deal.
This model takes a ‘time based’ approach to modelling. Every change in the model is accounted for up until the day before the transaction. Something that happens on the day or the day after a deal doesn’t effect the deal. This sheet shows you the situation the day before a deal happens in order to calculate your deals (the top part of this sheet).
Finally, the last part of the deal calculations shows you the ownership of each class of share. There are a pretty stacked chart and the % ownership.
This sheet is totally complicated! You don’t need to do anything here, but it’s ideal to understand the formulas. Go through each of them.
Click here to continue
The cap table sheetShow off to your friends. Hit a social button to share the cap table love!