Login to Reportally


Bringing together the insights from the Cap Table University we've created a worked example of a Cap Table from the ground up. We will contrast the old method - using Excel, with the new method - using Reportally's free cap table builder.

Background to the Cap Table

Moz's CEO Rand Fiskin is a leading blogger in the startup community. Sharing his company's growth through a very interesting series of blog posts. With his consent, we've used material from a few of those blog posts to build his cap table here:

Worked Example: 

Using Excel there are four steps, and some tricky arithmetic:

  1. Founder round: allocating shares between Rand and his co-founder GIllian (Jan 2006)
  2. Seed round: awarding shares to 2 new investors (November 18, 2007)
  3. Option pool round: awarding shares to 2 new investors (1 December 2009)
  4. VC round: (i) awarding shares to a new investors; (ii) selling some of the founders shares to the new investors and (iii) transferring some options to employees (1 December 2009)

Step One - Founder Round

This step is the easiest - we start with 200,000 shares and allocate 50% of the shares to each Rand and Gillian (his co-founder):

Cap Table worked example - Moz Founder Round in Excel


Step Two - Seed Round

Next, we introduce $1M of funding from Ignition and $100k from Curios Office. This is a little trickier.

We use a pre-money valuation of $4.6M*, which equates $23.04 per share: there are 200,000 shares before we allocate any to Ignition or Curios Office; so divide the pre-money valuation ($4.6M) by the number of shares (200,000) to get the price per share ($4.6M / 200,000 = $23.04)

Next we allocate new shares to Ignition and Curios Office:

  • Ignition: they get 43,411 shares for their $1M investment (take the amount invested, and divide by price per share: $1M / $22.50 = 43,411)
  • Curios Office: they get 4,341 for their $100k investment

We can calculate the Fully Diluted position after this round by taking the total number of shares, and comparing the amounts held by each party. In Excel this looks like:

Cap Table worked example - Moz Seed Round in Excel


Step Three - Option Pool

In December 2009, SEO Moz created a share pool of 20%. Normally a share option pool is created before, or at the same time as a funding round. So it is a little unusual to have a two year gap between the seed round and option pool

This round is pretty straight forward. To create a pool of 20%:

  • Take the total number of shares currently outstanding: 247,752 in this case
  • "Gross up" for the size of pool you want to create: number of shares / (1 - size of pool) = 247,752 / (1 - 0.2) = 309,690
  • Allocate size of the pool to the option holders: 20% of the 309,690 = 61,938

Cap Table worked example - Moz Option Round in Excel

Strike price: you may also need to consider the Strike Price for the option holders. This is the amount an option holder pays for each share, when the option is exercised. It may be nominal (e.g. $0.01) or based on the current valuation of the company. There are tax and legal considerations when setting the Strike Price. We've assumed the strike price based on the price per share of the most recent fundraising: $23.04 per share



Step Four - VC funding, cash out and option transfer

This is the most complicated part. Accounting for (i) new VC money from Foundry Group and an additional investment from Ignition, (ii) the sale of some shares by the Founder (cash out), and (iii) the transfer of shares from the Founders to the employees

Taking each step in turn, starting with the investment from the VCs (which is very similar to the Seed round above):

  • We use a pre-money valuation of $75M, which gives us a price per share of $242.18 (based on 309,690 shares outstanding)
  • Ignition: invested $3M, and get 12,388 shares
  • Foundry Group: invested $15M, and get 61,938 shares

Cap Table worked example - Moz VC Round in Excel


Next, we consider the sale of shares from Rand and Gill** - a "Cash Out" round. We know the valuation for these share sales was less than the pre-money value in the round, driven by US regulation, and was reported at $49M:

  • We use a price per share of $157.50, by taking the valuation*** and dividing it by the number of shares outstanding before the VC investment: $49M / 309,690 = $157.50
  • Rand and Gill sold a total of c.$870,000, which we assumed they sold equally. Meaning they each sold 2,765 shares - a total of 5,530 shares
  • We assume Foundry Group and Ignition bought these shares in the same proportion as their overall investment. Meaning they bought 4,600 and 930 shares respectively

Cap Table worked example - Moz Cash Out Round in Excel


Finally, and in another unusual step - but highly respected step - Rand and Gillian transferred some of their shares to increase the employee shareholdings:

  • We assume both Rand and Gillian transferred 4,750 shares each****
  • We assign a nominal value of $1 per share for the purposes of calculating share proceeds

And we're done:

Cap Table worked example - Moz Share Option Transfer Round in Excel

We bring together a running total of shares from each step to produce the Fully Diluted position:

Cap Table worked example - Moz Fully Diluted Round in Excel

Modelling using Reportally: 

By contrast, this entire exercise, which took a couple of hours in excel, can be done in minutes using the Cap Table builder within Reportally

Step One: Founder Round

Simply type in the number of shares and ownership split, and press save:

Cap Table worked example - Moz Fully Diluted Round in Reportally

Step Two: Seed Round

Create an Equity round, filling in the pre money valuation, amount raised (invested) and investor fields:

Cap Table worked example - Moz Fully Equity Round in Reportally

Step Thee: Option Pool

Choose a Share Options round, filling the size of the pool desired:

Cap Table worked example - Moz Share Options Round in Reportally

Step Four: VC funding, cash out and option transfer

For the VC funding round, us an Equity Round as per step 2. For the share sale and transfer, use a Cash out round. Select the seller and buyer from the existing list of investors:

Cap Table worked example - Moz Fully Cash Out Round in Reportally

Fully Diluted

Press "Save and Update" and you're done! Not a single calculation required. Here's the Fully Diluted position:

Cap Table worked example - Moz Fully Diluted Round in Reportally

Returns

...but we're not finished there. We also calculate the Returns based on the Valuation chosen...:

Cap Table worked example - Moz Returns Round in Reportally

Liquidation Chart (or Waterfall Chart)

and finally Reportally automatically creates a Liquidation Chart (also known as a Waterfall chart):

Cap Table worked example - Moz Waterfall Round in Reportally

The entire exercise can be done in 5-10 minutes, saving you hours of time, and given you peace of mind that the calculations have been run correctly.


But Moz are not alone - we've created thousands cap tables for companies using publicly available data and our algorithms. So why not create an Entrepreneur account today - you may find your cap table already waiting for you!

Get my cap table now



Assumptions:

A number of assumptions were made to complete the analysis, and to solve for the final outcome as presented in Rand's posts. You won't need to make assumptions when building your own cap table, as you will know all the data

  • *$4.6M pre money for the Seed round was based on another reference to $4.5M
  • **We don't know the precise split of the share sales, and have assumed that Ignition and Foundry Group bought shares in proportion to their investment in the round (3:15)
  • ***This price per share is a slightly lower valuation than $49M, but is still correct given the rounding margin for error
  • ****We don't know the exact amount of shares transferred, but we know the employee pool totals 19% (existing and old employees) after the transfer
Level: 
Advanced




Free Investor Reporting Platform • Cap Table Management • Investor Dashboard
Reportally is a free-to-use platform with everything you need to manage your startup funding: Cap Table management, Investor Reporting and more...

Question or Feedback?