-
Geographical Analysis 6
-
Lecture1.1
-
Lecture1.2
-
Lecture1.3
-
Lecture1.4
-
Lecture1.5
-
Lecture1.6
-
-
Cap Table 3
-
Lecture2.1
-
Lecture2.2
-
Lecture2.3
-
-
Simulation 6
-
Lecture3.1
-
Lecture3.2
-
Lecture3.3
-
Lecture3.4
-
Lecture3.5
-
Lecture3.6
-
-
Search Index 8
-
Lecture4.1
-
Lecture4.2
-
Lecture4.3
-
Lecture4.4
-
Lecture4.5
-
Lecture4.6
-
Lecture4.7
-
Lecture4.8
-
-
Fund Distributions 5
-
Lecture5.1
-
Lecture5.2
-
Lecture5.3
-
Lecture5.4
-
Lecture5.5
-
Cap Table Functions
Creating Functions to Track Cap Tables¶
Instead of doing this over and over again, we can create a function which takes a current cap table as well as the new investor information and returns what the new cap table will look like.
import pandas as pd
#Start with our basic cap table
cap_table = pd.DataFrame([['Michael', 1500],
['Sean', 1500],
['Employees', 1000]], columns = ['Investor', 'Shares'])
cap_table['% Ownership'] = cap_table['Shares'] / cap_table['Shares'].sum()
cap_table['Price'] = np.NaN
cap_table['Value'] = np.NaN
Our inputs would be the total amount raised and the amount owernship that is going to be given after creation of new shares.
#Create our inputs
total_raised = 100000
post_ownership = {"ABC Capital": .1,
"XYZ Capital": .1}
To make it easier to understand, we will break down each part of the function to see how it all flows. One new thing that we are going to control for is the case where an investor who is already invested in the company decides to increase their stake. We need to consolidate their previous stake and new stake in this case.
#Create a copy of the table to update with
new_cap_table = cap_table.copy()
#Find the valuations
new_ownership = sum(post_ownership.values())
post_money_valuation = total_raised / new_ownership
pre_money_valuation = post_money_valuation - total_raised
print(new_ownership)
print(post_money_valuation)
print(pre_money_valuation)
0.2
500000.0
400000.0
#Compute the price
total_shares = cap_table['Shares'].sum()
share_price = pre_money_valuation / total_shares
print(share_price)
100.0
#Create a dataframe to track the newly created shares
post_ownership = pd.Series(post_ownership).reset_index()
post_ownership.columns = ["Investor", "% Ownership"]
post_ownership['Shares'] = post_ownership["% Ownership"] * post_money_valuation / share_price
print(post_ownership)
Investor % Ownership Shares
0 ABC Capital 0.1 500.0
1 XYZ Capital 0.1 500.0
#Combine the old and new data, and group by the investor name to get the total shares (to consolidate)
new_cap_table = pd.concat([new_cap_table, post_ownership])
new_cap_table = new_cap_table.groupby("Investor")['Shares'].sum()
new_cap_table = new_cap_table.reset_index()
print(new_cap_table)
Investor Shares
0 ABC Capital 500.0
1 Employees 1000.0
2 Michael 1500.0
3 Sean 1500.0
4 XYZ Capital 500.0
#Add back in the columns for price, value and percent ownership
new_cap_table['Price'] = share_price
new_cap_table['Value'] = new_cap_table['Price'] * new_cap_table['Shares']
new_cap_table['% Ownership'] = new_cap_table['Shares'] / new_cap_table['Shares'].sum()
print(new_cap_table)
Investor Shares Price Value % Ownership
0 ABC Capital 500.0 100.0 50000.0 0.1
1 Employees 1000.0 100.0 100000.0 0.2
2 Michael 1500.0 100.0 150000.0 0.3
3 Sean 1500.0 100.0 150000.0 0.3
4 XYZ Capital 500.0 100.0 50000.0 0.1
#Put it all into a function
def update_cap_table(cap_table, total_raised, post_ownership):
#Create a copy of the table to update with
new_cap_table = cap_table.copy()
#Find the valuations
new_ownership = sum(post_ownership.values())
post_money_valuation = total_raised / new_ownership
pre_money_valuation = post_money_valuation - total_raised
#Compute the price
total_shares = cap_table['Shares'].sum()
share_price = pre_money_valuation / total_shares
#Create a dataframe to track the newly created shares
post_ownership = pd.Series(post_ownership).reset_index()
post_ownership.columns = ["Investor", "% Ownership"]
post_ownership['Shares'] = post_ownership["% Ownership"] * post_money_valuation / share_price
#Combine the old and new data, and group by the investor name to get the total shares (to consolidate)
new_cap_table = pd.concat([new_cap_table, post_ownership])
new_cap_table = new_cap_table.groupby("Investor")['Shares'].sum()
new_cap_table = new_cap_table.reset_index()
#Add back in the columns for price, value and percent ownership
new_cap_table['Price'] = share_price
new_cap_table['Value'] = new_cap_table['Price'] * new_cap_table['Shares']
new_cap_table['% Ownership'] = new_cap_table['Shares'] / new_cap_table['Shares'].sum()
return new_cap_table
cap_table = pd.DataFrame([['Michael', 1500],
['Sean', 1500],
['Employees', 1000]], columns = ['Investor', 'Shares'])
cap_table['% Ownership'] = cap_table['Shares'] / cap_table['Shares'].sum()
cap_table['Price'] = np.NaN
cap_table['Value'] = np.NaN
total_raised = 100000
post_ownership = {"ABC Capital": .1,
"XYZ Capital": .1}
print(update_cap_table(cap_table, total_raised, post_ownership))
Investor Shares Price Value % Ownership
0 ABC Capital 500.0 100.0 50000.0 0.1
1 Employees 1000.0 100.0 100000.0 0.2
2 Michael 1500.0 100.0 150000.0 0.3
3 Sean 1500.0 100.0 150000.0 0.3
4 XYZ Capital 500.0 100.0 50000.0 0.1
Now that we have created a function to update our cap tables, we can try it with a few rounds of financing. This function will make the process automated and much easier! We will track all the cap tables over time as well to see the evolution.
cap_table = pd.DataFrame([['Michael', 1500],
['Sean', 1500],
['Employees', 1000]], columns = ['Investor', 'Shares'])
cap_table['% Ownership'] = cap_table['Shares'] / cap_table['Shares'].sum()
cap_table['Price'] = np.NaN
cap_table['Value'] = np.NaN
historical_cap_tables = [cap_table]
total_raised = 100000
post_ownership = {"ABC Capital": .1,
"XYZ Capital": .1}
cap_table = update_cap_table(cap_table, total_raised, post_ownership)
historical_cap_tables.append(cap_table)
total_raised = 200000
post_ownership = {"ABC Capital": .1,
"M Ventures": .05}
cap_table = update_cap_table(cap_table, total_raised, post_ownership)
historical_cap_tables.append(cap_table)
total_raised = 500000
post_ownership = {"ABC Capital": .05,
"M Ventures": .05,
"ABC Capital": .05}
cap_table = update_cap_table(cap_table, total_raised, post_ownership)
historical_cap_tables.append(cap_table)
total_raised = 1000000
post_ownership = {"ABC Capital": .025,
"Growth Fund": .025}
cap_table = update_cap_table(cap_table, total_raised, post_ownership)
historical_cap_tables.append(cap_table)
print(historical_cap_tables)
[ Investor Shares % Ownership Price Value
0 Michael 1500 0.375 NaN NaN
1 Sean 1500 0.375 NaN NaN
2 Employees 1000 0.250 NaN NaN, Investor Shares Price Value % Ownership
0 ABC Capital 500.0 100.0 50000.0 0.1
1 Employees 1000.0 100.0 100000.0 0.2
2 Michael 1500.0 100.0 150000.0 0.3
3 Sean 1500.0 100.0 150000.0 0.3
4 XYZ Capital 500.0 100.0 50000.0 0.1, Investor Shares Price Value % Ownership
0 ABC Capital 1088.235294 226.666667 246666.666667 0.185
1 Employees 1000.000000 226.666667 226666.666667 0.170
2 M Ventures 294.117647 226.666667 66666.666667 0.050
3 Michael 1500.000000 226.666667 340000.000000 0.255
4 Sean 1500.000000 226.666667 340000.000000 0.255
5 XYZ Capital 500.000000 226.666667 113333.333333 0.085, Investor Shares Price Value % Ownership
0 ABC Capital 1415.032680 765.0 1082500.0 0.2165
1 Employees 1000.000000 765.0 765000.0 0.1530
2 M Ventures 620.915033 765.0 475000.0 0.0950
3 Michael 1500.000000 765.0 1147500.0 0.2295
4 Sean 1500.000000 765.0 1147500.0 0.2295
5 XYZ Capital 500.000000 765.0 382500.0 0.0765, Investor Shares Price Value % Ownership
0 ABC Capital 1587.031304 2907.0 4613500.0 0.230675
1 Employees 1000.000000 2907.0 2907000.0 0.145350
2 Growth Fund 171.998624 2907.0 500000.0 0.025000
3 M Ventures 620.915033 2907.0 1805000.0 0.090250
4 Michael 1500.000000 2907.0 4360500.0 0.218025
5 Sean 1500.000000 2907.0 4360500.0 0.218025
6 XYZ Capital 500.000000 2907.0 1453500.0 0.072675]
The first thing to track is how the ownership changes over time. If we pull the column for ownernship from each of the historical cap tables, we begin to see the evolution. First let's look at what we are pulling out with list comprehension.
historical_owernship = [x.set_index('Investor')['% Ownership'] for x in historical_cap_tables]
print(historical_owernship)
[Investor
Michael 0.375
Sean 0.375
Employees 0.250
Name: % Ownership, dtype: float64, Investor
ABC Capital 0.1
Employees 0.2
Michael 0.3
Sean 0.3
XYZ Capital 0.1
Name: % Ownership, dtype: float64, Investor
ABC Capital 0.185
Employees 0.170
M Ventures 0.050
Michael 0.255
Sean 0.255
XYZ Capital 0.085
Name: % Ownership, dtype: float64, Investor
ABC Capital 0.2165
Employees 0.1530
M Ventures 0.0950
Michael 0.2295
Sean 0.2295
XYZ Capital 0.0765
Name: % Ownership, dtype: float64, Investor
ABC Capital 0.230675
Employees 0.145350
Growth Fund 0.025000
M Ventures 0.090250
Michael 0.218025
Sean 0.218025
XYZ Capital 0.072675
Name: % Ownership, dtype: float64]
Now, we concat these together and transpose them (so that the columns correspond to the different inevstors).
historical_owernship = [x.set_index('Investor')['% Ownership'] for x in historical_cap_tables]
historical_owernship = pd.concat(historical_owernship, axis=1).transpose()
print(historical_owernship)
Michael Sean Employees ABC Capital XYZ Capital \
% Ownership 0.375000 0.375000 0.25000 NaN NaN
% Ownership 0.300000 0.300000 0.20000 0.100000 0.100000
% Ownership 0.255000 0.255000 0.17000 0.185000 0.085000
% Ownership 0.229500 0.229500 0.15300 0.216500 0.076500
% Ownership 0.218025 0.218025 0.14535 0.230675 0.072675
M Ventures Growth Fund
% Ownership NaN NaN
% Ownership NaN NaN
% Ownership 0.05000 NaN
% Ownership 0.09500 NaN
% Ownership 0.09025 0.025
Finally, let's reset the index (dropping it) because it is all labeled the same, and fillna with 0 to denote that if there is no value, it should be 0% ownernship.
historical_owernship = [x.set_index("Investor")['% Ownership'] for x in historical_cap_tables]
historical_owernship = pd.concat(historical_owernship, axis=1).transpose()
historical_owernship = historical_owernship.reset_index(drop=True).fillna(0)
print(historical_owernship)
Michael Sean Employees ABC Capital XYZ Capital M Ventures \
0 0.375000 0.375000 0.25000 0.000000 0.000000 0.00000
1 0.300000 0.300000 0.20000 0.100000 0.100000 0.00000
2 0.255000 0.255000 0.17000 0.185000 0.085000 0.05000
3 0.229500 0.229500 0.15300 0.216500 0.076500 0.09500
4 0.218025 0.218025 0.14535 0.230675 0.072675 0.09025
Growth Fund
0 0.000
1 0.000
2 0.000
3 0.000
4 0.025
Plotting the ownership that different parties have over time really drives home how these rounds of financing have led to dilution for many parties that were not increasing their stakes in the rounds.
import matplotlib.pyplot as plt
from matplotlib.ticker import PercentFormatter
ax = historical_owernship.plot(kind='line')
ax.set_xlabel("Round")
ax.set_ylabel("Percent Ownership")
ax.set_title("Company Ownership over Time")
ax.yaxis.set_major_formatter(PercentFormatter(1))
plt.show()
historical_value = [x.set_index("Investor")['Value'] for x in historical_cap_tables]
historical_value = pd.concat(historical_value, axis=1).transpose()
historical_value = historical_value.reset_index(drop=True)
print(historical_value)
Michael Sean Employees ABC Capital XYZ Capital \
0 NaN NaN NaN NaN NaN
1 150000.0 150000.0 1.000000e+05 5.000000e+04 5.000000e+04
2 340000.0 340000.0 2.266667e+05 2.466667e+05 1.133333e+05
3 1147500.0 1147500.0 7.650000e+05 1.082500e+06 3.825000e+05
4 4360500.0 4360500.0 2.907000e+06 4.613500e+06 1.453500e+06
M Ventures Growth Fund
0 NaN NaN
1 NaN NaN
2 6.666667e+04 NaN
3 4.750000e+05 NaN
4 1.805000e+06 500000.0
historical_value.plot(kind='line')
plt.show()