## Venture Capital Calculations

November 9th, 2006With all my recent readings on Excel I figured I should make something that didn’t have anything to do with my hockey pool. So I decided to make an easy to use spreadsheet for valuing a company, determining how large of investment results in what percentage of ownership, shares to issue, share price, etc.

If you want to skip right to downloading my spreadsheet, *go ahead*.

**Update September 2013: **People still download and use the spreadsheets I made over a half dozen years ago, though I never did get a job in venture capital or even finance. I seem to be back to fixing other people’s websites, proving what a waste of time, effort, and money my Sauder MBA was. But if you want to know more about valuing a startup, you should check out this post on Funders & Founders.

If you would like some explanation on valuing a company and selling a portion to investors (especially VCs) I recommend a portion of the Strategis.gc.ca website, particularly this section. My spreadsheet implements the classic VC method as defined in Jeffry Timmons’ book. It **now also implements** two discounted cash flow valuations one with linear growth and one with exponential growth.

I also implemented a version of the First Chicago Method. My implementation relies on accessing the likelihood of three scenarios and assigning a percentage of the original market size estimate that is to be captured in each scenario.

The final portion of the spreadsheet contains some simple VBA macros that make use of Goal Seek to determine the requirements to achieve a specific IRR, investment size, and maintain a certain percentage of ownership under the classic VC method.

The spreadsheet makes use of a large number of worksheets and defined names of cells to make it hopefully easy to understand. The spreadsheet makes use of a few tips and tricks from Analyzing Business Data with Excel. One convention I use is cells with green backgrounds can be changed at will to allow for **“what if” analysis**. Orange cells are changed by VBA macros. Green for go, orange for caution.

The Excel spreadsheet contains the following worksheets:

- Input: for inputting all the values needed for the classic VC valuation method
- Valuations: shows the PV, shares to issue, and share price using the four different methods on one worksheet
- Classic VC: Estimates PV of the company, shares to issue and their price
- First Chicago: Re-estimates the PV of the company, and recalculates shares to issue and their price.
- DCF: uses a discounted cash flow calculation and a linear growth in market share
- BreakEven: this is a simple line graph that shows yearly income and yearly

fixed costs including the break even point for the linear discounted cash flows - DCF2: uses a discounted cash flow calculation and an exponential growth in market share
- BreakEven2: this is another simple line graph that shows the yearly income and the yearly fixed costs including the break even point for the exponential discounted cash flows
- Solve for IRR: contains a macro to solve for a particular IRR
- Solve for Investment: contains a macro to solve for a particular investment size
- Founder Control: contains a macro to solve for maintaining a certain percentage of the company owned by the founders
- Workarea: interim calculations

Hopefully this is of use to someone. I also hope the people who make use of it show a little gratitude, something that was not always done by my former classmates who were all too happy to benefit from my effort though *they didn’t always express it*. I think this is being continued by the next generation of MBA students at Sauder based on some recent web searches that were conducted on Muschamp.ca today. Though one or two students in succeeding classes have written me about my various notes and reading summaries *I made available online to try and make a point*, at which **I failed.**

It should be noted there is absolutely no warranty or guarantee with these calculations. I did test them many times and check them by hand with a calculator. I made the spreadsheet using Office for Mac 2004 but it should work on most other versions. There are four macros, most of the spreadsheet will work without them if you are feeling paranoid. The discounted cash flow valuations are done almost entirely with VBA so that portion of the spreadsheet won’t work without macros enabled.

Although I provided some rough breakeven analysis to do it properly you need a more detailed cost structure and likely a monthly model. I may get around to making one but I think I’ll start a new spreadsheet as the current one is now *over 200 Kb* and **has twelve worksheets**.

This entry was originaly posted on , it was last edited on and is filed under: Finance and tagged: Excel, MBA, Spreadsheet, Valuation, Venture Capital.

Pingback: Muskblog » Blog Archive » The Origin of Brands: A Book Review()

Pingback: Muskblog » Blog Archive » Ready to Rocket VEF?()

Pingback: Muskblog » Blog Archive » Long overdue keyword referral post()

Pingback: 30th post about Venture Capital – Muskblog()

Pingback: SYWTOABP 4: How much does it cost to open a brew pub? « B.C. Beer Blog()

Pingback: 10 Year Blogiversary – Muskblog()