Break-even Analysis in a Growing Market

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 code. It contains the least sophisticated model for discounted cashflow 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 payment of 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 company’s 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 valuations 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. You’re welcome to view my resume.

It should go without saying if you download a Microsoft Excel file from my website, upload it to another website and claim it as your own work, I will find out, and I will take action.

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 function 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 would take credit for my hard work but of course I would be wrong. Numerous people have stolen my work and claimed it as their own complete with asserting copyright in direct violation of the CFA® Standard of Professional Conduct I-C Misrepresentation. That’s right after completing my MBA I went on to pass all three CFA exams, comments and congratulations can be left below.

This entry was originaly posted on December 7th, 2006, it was last edited on April 21st, 2024 and is filed under: Technology and tagged: Finance, Microsoft Excel, Spreadsheet.