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