Muschamp Rd

Break-even Analysis using VBA

November 17th, 2006
Microsoft Excel logo

In between power outages I’ve been working on improving my ability to use VBA in Excel. I’ve been reading books but sometimes you learn better by doing, so since I’ve also been thinking a lot about venture capital I decided to make some Excel spreadsheets to perform calculations to determine company valuations and perform break-even analysis.

The first spreadsheet I made
just kept getting larger and wasn’t designed to do a very accurate accounting of cash reserves and fixed costs just various valuation models. So I planned a spreadsheet, which you can download, that has a much more detailed cost structure and carefully tracks cash position in addition to valuing the company using the Classic VC and discounted future cashflow method.

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.

Although the spreadsheet was intended to value companies and estimate their cash needs, it is general enough that it could be used to compute the NPV of other investments and projects if you can message your cash flows to be a percentage of a total market.

Once again there is absolutely no warranty, but the code should be reasonably robust and I did test it and attempt to verify the calculations with a calculator. If it makes you feel better I have a BSc. in Computer Science, an MBA, and passed all three CFA® exams.

Hopefully the spreadsheet is reasonably easy to figure out if you are familiar with company valuations and NPVs. Even if you are not just put some numbers in the green boxes, select some growth types and press the button, Excel will do the rest.

Currently there are the following worksheets:

  • Input: all the necessary input goes here, I don’t allow negative numbers, and the one button that makes it go is also on this sheet.
  • Valuations: this is where the final output ends up, the valuation of the company, and a yearly snapshot of its cash position, income, fixed costs, and even the month the company will be cash flow positive or run out of cash
  • Cash Flow Chart: this is a line chart of the yearly cash flows. This chart shows the growth of net income, cash, and fixed costs on a yearly basis.
  • Monthly Income: this is a chart of the monthly net incomes and the present values of that future income. I also added the monthly fixed costs so you can see how that grows graphically.
  • Market Share: this is a bar chart of the monthly market share.
  • DCF: this is the discounted cash flows of the net income done on a monthly basis.
  • WorkArea: this contains interim calculations and information that was used to build the valuations and discounted cash flows.

On the Input worksheet are 14 green cells, two selection boxes, and a button. The first batch of green cells are those needed to calculate the classic VC valuation of a proposed venture. The second batch of green cells are needed in addition to determine the monthly and yearly cash positions of the venture as well as the discounted cashflow valuation. The selection boxes allow you to determine the growth model of the company’s market share and the growth model of the company’s fixed costs.

The data that needs to be inputted is detailed as follows:

  • Total Market Size: the size of the market your venture will be operating in or selling into, the number is the total sales per year.
  • % of Market Captured: this is how much market share your venture will capture by the end of the business plan or valuation calculation.
  • Operating Margin: this is the percentage of every dollar of income that is lost as a variable cost.
  • Years to Exit: this is what VCs care about when the venture will be sold, this is also the end of the cash flow calculations.
  • Comparable P/E: this is the P/E ratio of a comparable company and is needed to value this venture when preparing it for sale.
  • Desired IRR: this is the rate of return the VC needs or at least desires to make this investment worthwhile. It is also the rate used to discount the cash flows. This is a yearly rate and I actually convert it to a monthly rate for the discounted cash flow calculations.
  • Desired Investment: this is how much money the VC plans to invest. It is needed to calculate return on investment.
  • Currently Issued Shares: these are shares that have already been issued by the company prior to this VC investing, this is needed to determine the share price and how many shares of the company the VC gets.
  • Months Until Income: this is the number of months before the company has any income at all. Income actually starts the next month after this one in my calculations.
  • Estimated Yearly Fixed Costs: this is the dollar amount of fixed costs the company must expend per year. It is necessary to determine the break even point.
  • Estimate Yearly Fixed Costs Growth: this is the percentage fixed costs are expected to increase on a yearly basis. I allow for a variety of fixed costs growth models, so this number is sometimes used only as a guide.
  • Initial Cash Reserves: this is the amount of cash the company has now. It is completely independent of the Desired Initial Investment cell. It can be as big or as small as you want, even zero. This is the starting point for all cash calculations and if the company ever dips below zero, the spreadsheet will note that and continue calculating the discounted cash flows.
  • Tax Rate: in my tax regime you don’t pay taxes if you don’t have a positive net income and the tax rate never changes over the duration of the calculations. A tax rate of zero is allowable but not negative numbers currently.
  • Discount Rate: this is the discount rate used in the present value calculations for the discount cash flow.
  • Capitalization Rate: this is used to calculate the residual value of the discount cash flow valuation as per this example.

That describes the current state of the spreadsheet. Hopefully it is of use to someone. No warranty is implied and enough interim information is hopefully displayed to give some level of confidence. I’ll of course attempt to fix any mistakes that are found and may make additional additions and modifications. Comments and questions can be left below.


Leave a Reply

I hereby reserve the right to delete, edit, and/or mark your comments as spam.

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

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.