Big Project-Capitalization tables, modeling, advanced searching.

rvruggiero

New Member
Joined
Apr 22, 2012
Messages
3
So this is a rather difficult project-one with many answers and one where I'll need a lot of help. Please PM me or email me if that seems more reasonable. I'd appreciate any help you can offer.

Heres the issue:

I currently work with lots of Capitalization tables from different companies. These "Cap" tables basically consist of lists of shareholders of a company. They are usually put into categories like Founders (series a), Common stock (series b,c,d,e,f,etc), Warrants, Options, and other categories. Then we have a column with the % ownership each category has in the company. A sample cap table is below.



My job is to create a "waterfall" of how when a company goes public or gets sold or reorganized for a certain $ amount...how the funds get dispersed throughout for the shareholders and which of these shareholders gets paid first and how much they get paid. Usually a "series a" gets paid before "series b", then a "series c",etc then options and warrants.

Each company has their own format for a cap table. One might look like the one I posted above, one might have the shareholders listed horizontally rather than vertically, etc. They all have different layouts but they all preform basically the same function.

What I'd like to accomplish is:

I'd like to write a program in excel (or record one using VBA) in which a user could simply input an excel file of a cap table like I posted, that table could be converted into a standardized layout (if it had a different layout than the one i posted an example for, then it could be converted into the same template as the one i posted) and then could be converted into a model or with which I could record functions and create a model using these functions.

Below is a link to the model I usually create for each company using their own cap tables. Just so you can see how the model works--The "Start Here" tab is where the end user (my boss) can enter a $amt in "Gross Distribution Proceeds" (which is the Sale price of the company) and a few other figures and then the whole model is run from "Net Proceeds for Distribution". On the "waterfall" tab, this is where that same "Net proceeds for distribution" figure is distributed throughout each category (series a, series b, series c,etc) and how each shareholder gets paid.

http://www.4shared.com/file/mkQCUpeN/Waterfall_Model.html

What i'm trying to do is streamline this process.

1.) All I'd have to do is to browse for a file and select a companies cap table in excel format.

2.) Hit a button and have their cap table converted into the format I want (which is to say I basically want a template)

3.) Running my model off this template will enable me to automate it completely with a macro.

Ultimately I'm looking to save my time (and thus tons of time spent copying and pasting the data over from company cap tables into my model, which ultimately means I have tons of room for error and usually need to change multiple formulas to suit each company's specific setup.

4.) To get around the idea that different companies have different criteria in their model, I'd like to have excel prompt me with questions and then adapt the formulas automatically if that makes sense-rather than me having to go in and edit each formula individually which involves tons of trial and error for me to get things working perfectly.

The main issue for me will be:

1.) Different companies have different cap table layouts, so I'd need to somehow search better. I need to use smart searching algorithms to correctly identify data and to reorganize into my template. A simple vlookup doesnt work well as specifying the columns,etc needed for vlookup is different for each company's cap table, since they are all laid out differently-which could give me the wrong values and tons of errors.

Anyone have any ideas or any direction here? I know it's complicated so please let me know either in this thread or pm/email if you can help me a little. I'm also willing to donate a little to you if we can find a good solution. Ultimately I'm proficient with excel, but lack most of the programming skills. I know that a solution theoretically should exist but I need a little (a lot) of help.
 

rvruggiero

New Member
Joined
Apr 22, 2012
Messages
3
Any help before this thread starts heading down the list of threads? Would appreciate any and all help! Thanks!
 

shawnhet

Well-known Member
Joined
Feb 12, 2011
Messages
547
My suggestion is to think about what the commonalities amongst your different types of cap tables are. If there are no commonalities, you may be more or less stuck with your current way of doing things.

If various formats of cap table are common, an approach I've used successfully is to set up a worksheet with many sheets each representing a different format, copying your source document into that sheet and then set up formulae or a macro that allows you to pull information into a common format. For instance, you could paste your above cap table into a sheet called 3 column vertical and set up a set of formulae or a macro to pull at the info on that sheet into a single row of data that you could use for your "waterfall"(after possibly adding some additional data like some sort of ranking data for each type of stock).

However, this is pretty time consuming to set up and it would only make sense if you were sure that you would reuse a format many times. Given what you have described here, it may be that this is not practical.

Cheers, :)
 

Forum statistics

Threads
1,081,615
Messages
5,360,037
Members
400,565
Latest member
Tommy O

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top