Muschamp Rd

Breakeven analysis in a growing market

December 7th, 2006
Microsoft Excel logo

I’ve created a third Excel spreadsheet that can be used to value a company. All three are slightly different, though they all can be used to value a company as a venture capitalist might. All three use the classic VC method which uses the total market size, the company’s market share, and the operating margin at the time of exit to figure out first the terminal net income, then the terminal value and finally the present value.

The first spreadsheet is the simplest and uses the least VBA. It contains the least sophisticated model for discounted cash flow but is the only one with a version of the First Chicago Method of valuing a venture. It also allows for a weighted average of the differing valuation methods and contains a few simple scripts that use goal seek.

The second spreadsheet I created has a much more elaborate model for computing break even analysis which is done almost entirely in VBA. It allows for a lot of variation in growth models, but it still is built around the classic VC method. The market size is fixed and the final market share is inputted. One idea I had and probably caused me a lot of headaches was the concept of months until the venture has income. This allows for a company to begin operation and paying fixed costs such as rent and salaries while developing their product or service, not earning income until a given number of months have passed. The companies cash position is carefully tracked and everything is calculated on a monthly basis.

So after all that, why a third spreadsheet? Well the other two were both built around the classic VC method of valuing a company. Every single other feature was tacked on in a sense to provide some additional information and to give other valuation(s) to compare the classic VC valuation against. This latest spreadsheet is build around the growth model, the VC calculation is performed last and is reliant on the growth model not independent of it. Plus I’m trying to improve my VBA and Excel skills to help me find a job.

Initially there were only linear growth curves but the model allows for the market to shrink as well as grow. I’ve since added some additional market share and fixed costs growth curves, including one with a degree of randomness.

It contains the following worksheets:

  • Input: which contains all the inputs and controls plus the button that makes it all go.
  • WorkArea: is for interim calculations and other stuff that helps make it all go.
  • DCF: this is the discounted cash flow output. I made it horizontal this time which is how it is usually done, but I’m not sure it is as easy to work with algorithmically.
  • Market Share: a bar graph of the firm’s market share
  • Market Size: a line graph of the total market size
  • Monthly Income: a line graph of the net income, present value of the net income, and the company’s fixed costs on a monthly basis
  • Yearly Cash Flow: this is a graph of the company’s cash reserves, net income, and fixed costs per year
  • Valuations: this is the final output, the classic VC valuation, the Net Present Value, along with the first month of positive cash flow and the month the company runs out of cash if ever. The yearly totals for cash, net income and fixed costs are also outputted here.

The variables inputted make the whole model go and can drastically affect the final company valuations so here is a more in depth explanation:

  • Initial Market Size: this is the size of the market, in yearly sales dollars, at the current point of time, year zero.
  • Initial Market Share: this is the company’s market share as a percentage of the total market size.
  • Market Size Growth Rate: this is the percentage the total market grows or shrinks per year.
  • Market Share Growth Rate: this is the percentage the firm’s market share grows per year.
  • Years To Exit: this is the number of years the model runs for.
  • Market Size Model: this selector controls how the market grows.
  • Market Share Model: this selector controls how the company’s market share grows.
  • Operating Margin: the company’s operating margin.
  • Initial Fixed Costs: the yearly fixed costs in dollars.
  • Fixed Costs Growth Rate: the percentage the company’s fixed costs grows per year.
  • Fixed Costs Model: the growth model for fixed costs.
  • Tax Rate: the percentage of the company’s positive net income that the government takes.
  • Initial Cash Reserves: the amount of cash the company starts with.
  • Risk Free Interest Rate: this is perhaps slightly mislabeled, companies often run out of cash, when this happens the month is noted and the model continues to run. However in the real world you need to get this shortfall from somewhere and this model allows you to borrow the necessary funds at this interest rate. You can make it generous or not so generous.
  • Discount Rate: this number is used in the present value calculations. It does not have to be the same as the desired IRR.
  • Desired Investment: the amount of money the company needs or that the VC is willing to invest.
  • Currently Issued Shares: the number of shares the company has already issued prior to the VC investing.
  • Desired IRR: the rate of return the VC feels is necessary to make this investment worthwhile.
  • Comparable PE Ratio: the Price to Earnings ratio of a comparable company is necessary to value the company using the classic VC method.
  • Capitalization Rate: necessary to calculate the residual value portion of the discounted cash flow valuation, as per this example.

There have been mistakes both minor and more major in my previous spreadsheets which I’ve always subsequently fixed. I’ll endeavour to fix any that are discovered in this spreadsheet but no warranty or guarantee is given or implied.

Hopefully these spreadsheets are of use to someone, people keep ending up on my blog looking for something similar to what I’ve produced and I’ve noticed others are selling spreadsheets that perform similar functions by examining the keywords people are using to arrive at my blog. I’d like to think no one has taken credit for my hard work, but I’ve been ripped off and mistreated too much to be that naive. I’ve tried to build them so that others can easily learn from them or modify them as they see fit. These spreadsheets make some use of my MBA though in truth I’m mostly self taught as far as Excel goes and my undergrad degree in computer science is probably more useful in the writing of Visual Basic for Applications code.


  • I added three additional market share growth models and two fixed costs growth models, this allows for some more interesting graphs.

    I think this is all I will be doing on these three spreadsheets, I’m not a big fan of VBA or its’ IDE. On the Mac OS X version of Excel I can’t use the scroll wheel when I’m editing VBA but I can when I’m working on the Worksheet. Before any Windows weenies get their panties in a not, they might do well to remember, that Microsoft Excel was originally a Macintosh program that was ported to Windows. Though nowadays Microsoft does something resembling simultaneous development, though there are features that appear to exist on only the PC or the Mac version…

  • I added three additional market share growth models and two fixed costs growth models, this allows for some more interesting graphs.

    I think this is all I will be doing on these three spreadsheets, I’m not a big fan of VBA or its’ IDE. On the Mac OS X version of Excel I can’t use the scroll wheel when I’m editing VBA but I can when I’m working on the Worksheet. Before any Windows weenies get their panties in a not, they might do well to remember, that Microsoft Excel was originally a Macintosh program that was ported to Windows. Though nowadays Microsoft does something resembling simultaneous development, though there are features that appear to exist on only the PC or the Mac version…

  • Now all three spreadsheets I created that use VBA to calculate the discounted cash flow valuation for an entrepreneurial venture consider the residual value. I also created a spreadsheet that does not use Visual Basic but recreates a more traditional means of calculating NPV.

  • Now all three spreadsheets I created that use VBA to calculate the discounted cash flow valuation for an entrepreneurial venture consider the residual value. I also created a spreadsheet that does not use Visual Basic but recreates a more traditional means of calculating NPV.

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

  • Pingback: Microsoft Office Upgrade – Muskblog()

  • Pingback: Ready to Rocket VEF? – Muskblog()

  • Mary Ann

    Thanks Muskie. Very Much! This will be a great help for my feasibility study.

  • I’m glad someone found it useful. I’ve definitely given away too much stuff for free, the ROI of this blog is not positive.

  • I left a reply to Mary Ann’s comment but I did it from inside WordPress and it has not appeared here yet, my enthusiasm for plugins is diminishing. Spammers have a way around Disqus too, at least Chinese spammers do.

Posts on Muskblog © Andrew "Muskie" McKay comments not necessarily so...
CFA Institute does not endorse, promote or warrant the accuracy or quality of Muskblog. CFA® and Chartered Financial Analyst® are registered trademarks owned by CFA Institute.