The assumptions sheet of the cap table

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.

Overview

Cap tables focus on data recording and subsequent calculations. Whilst I have done all the calculations for you, unfortunately, you will have to input all the assumptions in the model.

This doesn’t have to be a huge chore as maintaining your own cap table is very simple. You just input the terms of the deal once it is completed and you see all the outcomes.

Since I’ve made something that allows you to test scenarios and the effects of certain terms, then of course the model requires more learning to use.

Today’s episode features a walkthrough of the ‘Assumptions’ sheet in the Pro Cap Table model.

Filing out the Assumptions sheet

The assumptions sheet is your playground for deal inputs. These are then calculated in other sheets.

I spent time considering how to aggregate different assumptions and to place them together so things would feel more logical. This sheet deals with the high-level deal terms such as valuation, investment, pro-rata, ESOP and the like.

Starting assumptions

These are the absolute basics you have to fill in and they are simple.

Current date

At the top of the cap table Assumptions sheet, you have the current date.

This is today’s date and is calculated by a formula. You can change this date if you want to though.

If you use today’s date then you see the current status of your cap table.

If you want to look into the future (or past) you can change the date to one of your preference. Why would you do this?

Well, I built the cap table to be flexible so you can scenario plan. Say you know you want to raise 5 more rounds of capital, well then after you input the amount you raise, you need to tell the model to pretend the date is in the future. So you change the date to the future date (post the 5 rounds) so you can see the effects in the summary sheets.

Just remember to change the date back when you are done.

Starting shares

Next, you need to sort out your starting shares. These are simply what you the founders and other key staff started with, and then the number of shares you have put into the ESOP. This is mainly a check to ensure you start out filling in the cap table correctly.

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.

General assumptions

Next, you see each round from Common, and A to I. All the assumptions and calculations are in line.

You input your pre-money 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 at 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.

ESOP

The next block of assumptions is 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 Series-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.

Pro-Rata

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 bit 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.

Liquidation preferences

The last part of the assumptions sheet is where you stick in your liquidation preferences.

You normally have a 1x. If you have 2x (Or more, gosh forbid!) for some reason you can pop in 2x.

Conclusion

If this seems a little confusing, it is (not going to lie). There is a lot to learn if you don’t know what all the terms mean already.

The best thing to do is to have a play and see what happens. Then read about anything you don’t learn at a basic level so you understand the concept, and then more over time as you need to know more. That’s the best way to learn in my opinion.

If you don’t have the model yet, get it here.

There we go. Let’s get moving to the next episode.

Read next step

14 parts in this guide

You can jump to a section if you prefer:

1. What is a cap table and other important questions
2. Cap table dilution step-by-step example
3. Cap table dilution math
4. Starting the cap table (The drop-down menus we need)
5. Shareholders sheet
6. Deal calculations
7. The cap table sheet
8. The assumptions sheet
9. Individual shareholder returns sheet
10. Returns waterfall calculation
11. The ESOP sheet
12. The Common sheet
13. The convertible notes and warrants sheet
14. The preference shares sheets (From Series A to I)