Break-even Analysis using VBA
November 17th, 2006
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.
This entry was originaly posted on , it was last edited on and is filed under: Technology and tagged: Finance, Microsoft Excel, VBA.
After receiving an email asking “what about the residual value?” I added a residual value calculation to my discounted cash flow valuation. The residual value is the estimated present value of all future cash flows into the distant future. It assumes that the last estimated net income can be maintained indefinitely.
I don’t know how realistic that is, but it is commonly done none the less. In addition to fixing all my existing spreadsheets I recreated an example found elsewhere that hopefully illustrates how to do these calculations without Visual Basic.
While working on another spreadsheet dealing with new entrepreneurial ventures and modeling their cash flow and break even point, I discovered the most insidious mistake yet in my VBA. The total market size is in sales per year, my discount cash flow calculations did not account for this, though the VC valuation did. I specifically state this on the Input worksheet and have altered the VBA code. I also introduced another variable, the discount rate is used in the PV calculations, before I was using the desired IRR.
You can simply set Desired IRR and Discount Rate to be equal to the same number or alternatively you can use a different number for the VC calculation and the discounted cash flow calculation which is why I introduced it.
I’ve made a few more additions and modifications to my spreadsheet. If nothing else it generates some interesting graphs. There are now 11 market share growth scenarios and six fixed costs growth patterns which when combined with the thirteen data cells results in a lot of variation. Obviously I haven’t tried every possibility but I have spent considerable time writing and debugging the VBA and I’ve tried to keep things simple and hopefully realistic.
People are finding this blog posting in search engines now and presumably making some use of my Excel file. People have no qualms making use of my labour but they don’t always thank me for my time and effort. Perhaps I would have gotten more ‘hits’ if I had written breakeven as one word. However I spellcheck and the dictionary seems to think it is two words or should have a hyphen though websurfers think otherwise.
I’ve added even more market share growth options and fixed an error or at least a potential error in my exponential growth function. It is actually expontential on a yearly basis then linear on a monthly basis. I spent a lot of time and trial and error building several really interesting and hopefully accurate market share growth curves:
With all the work I put into the market share growth my boring linear year over year fixed costs needs improvement. I probably won’t do as many growth patterns but some variability would be more realistic. I’ll add a selector for the fixed costs growth pattern and I will base the growth pattern on an estimated linear growth.
There is now a graph of the monthly market share along with two more market share growth options. I may come up with a few more as well as some more exotic cost functions or refactor some of the VBA to be more object oriented.
I added a chart of the monthly income and its present value and two growth functions that add an element of randomness to the market share but still advance and reach the target market share at the end of the time period.
I plan to chart the market share percentage instead of just dollar amounts, possibly as something other than a line graph, probably a 100% bar chart. I’ve also stared at the monthly income chart often enough to dream up two more growth functions I’ll be working on.