About half of the readers of this blog originate from Google searches. The most popular searches involve Excel, financial models for startups, sources and uses, CAPEX and headcount plans. In an effort to fill the apparent void on the Internet related to complete financial models built with Excel, I have posted a model which includes examples for all of the frequent search terms that bring readers here (cash flow, CAPEX, sources and uses, headcount, etc.) The model is complete and a good example of what to send to a venture capital or private equity firm or a hedge fund to raise any type of financing.
A few words about this model:
- It is a real model that I built for a client (prospective clients you can see my work)
- It is not a template for you to use (I suppose if you changed all the assumptions it's usable, but...), but the component parts are recommended for capital raising
- The model is for a "triple play" telcom business (cable, voice and data) so the cost of sales part of the model is over 100 assumptions and the CAPEX is fairly involved including physical constraints; this "complexity" is designed to show industry knowledge to financing sources; for more on the purposes of financial models see this post
- The model includes the following tabs:
- Sources and Uses
- Assumptions (which also includes the Financial Summary--P&L and balance sheet information)
- Financial Statements (which includes the P&L, balance sheet, revenue detail, COS detail, CAPEX row 178, and debt schedule row 251)
- Cash Flow Statement
- OPEX (operating expense detail and the headcount plan)
- The model makes extensive use of named fields (after you open the model press F5); named fields make it easier to understand formulas because you use a name instead of a cell reference in the formula
- In addition to being a startup, the strategy called for a series of acquisitions (a rollup) which are modeled
- The model uses a customer acquisition cost driver for the organic growth of subscribers
A few things that would have changed the model design include:
- If I had a complex debt structure (combination of a line of credit, term loan and mezzanine debt) I would have used a separate tab for the "debt schedule" to make it easier to find
- If I had an operating company (instead of a startup) I would have included historical information for at least three years in the "financial summary" and probably have given it a separate tab; if you have a good operating history of revenue growth or a turnaround I would include five years of historical data
- If I had historical information on each acquisition, I would have forecasted/modeled each acquisition on a separate named tab (with a separate P&L and a balance sheet) and then consolidated them; such a presentation would have made it easier to understand the economics of each business unit and to evaluate the reasonableness of the purchase price
- If the headcount additions were based on time instead of capacity requirements (as shown in the model) I would have used date triggers to drive headcount additions
Click the following link to download a copy of the model. Download sf_blog_model.xls
The auditing tool bar in Excel may aid in understanding. Click View>Toolbars>Formula Auditing
Please remember that the complexity of the model serves a purpose--to demonstrate knowledge of a complex industry. Most models can be successfully built with fewer assumptions about revenue and cost of sales. Some additional thoughts on detail in models are here.