Financial Modeling: Murder by Numbers
By: John Siegler
John Siegler is a co-founder and CFO of Practice Technologies, Inc., creator of RealDealDocs. RealDealDocs gives you access to legal documents drafted by top US Lawyers. Search over 10 million documents and clauses for Free at www.RealDealDocs.com.
To borrow a line from the Police, it might seem as easy as your a-b-c 's , but there 's a lot that goes into effective financial modeling.
For my entire career, financial modelling has always been central to the analysis I've relied upon to evaluate a business' health or justify an investment in its growth.
There are several important steps to follow in developing a financial model which will serve your objectives as an entrepreneur, whether you're trying to manage what you have or raise capital for what you could. This is particularly true for newer enterprises, as the discipline associated with identifying and thinking through the key business drivers is invaluable to the early planning process.
1. Figure out what you're trying to accomplish.
As an entrepreneur, you have a number of competing objectives. Depending on how established you are, you may have a business to run on a day-to-day basis, and it 's hard to find the time to plan, build and manage against a set of financial models.
You may be tempted to build a simple income statement-type spreadsheet that lays out revenue assumptions and backs out costs.
But effective financial models can and should be used for so much more. Using them, you can look six to sixty months down the road to plan for organic growth, evaluate opportunities to enter new markets or take on new sources of capital, or anticipate liquidity problems.
I highly recommend taking the time to build a model which will generate a consolidated set of financial statements that will provide a more comprehensive picture of your business. And the sooner you identify the range of scenarios, the easier it is to plan and build your model to accommodate them.
2. Plan, and then plan some more
A rule of thumb in traditional software design and development is that for more complex projects your engineering team may spend half of the overall project timeline in planning and design. In my view, that 's overdoing it for financial modelling, but not by much. Key planning considerations include:
- Breaking down the key business drivers and assumptions, and how they are all related (more on this below)
- Determining the level of detail / drill-down capabilities
- Building a simple map of how your supporting sheets will roll up to your consolidated financial statements
- Determining what type of sensitivity analysis you want to model and present
3. Identify the key business drivers and assumptions
Particularly if you're looking to raise capital, breaking down and modelling your key assumptions and drivers is the most important aspect of building your projections, and one of the most important elements in presenting your business.
It will reflect your understanding of your market(s), growth opportunities and drivers, operating requirements, and what it takes to pull it all together. It is also an opportunity to demonstrate that your aspirations are firmly grounded in the reality of reasonable expectations about time to market, delays, cost overruns, etc.
So if you're modelling a new product roll-out, it 's not sufficient to say you'll sell X Widgets each month for $Y per and multiply the two numbers. Instead, you need to model out what drives unit sales, what are the elements of pricing (including discounting, upsells, bundling, etc.), how each of these elements might change over time, and then pull it all together.
As you gain more information and market experience, or if you simply want to run some scenario analysis, you'll be able to tweak each of these variables and watch it flow through the analysis.
This holds true for almost every revenue and cost driver - wherever possible, use formulas to do the work on clearly identified sets of assumptions that can be easily updated without needing to reformat the sheets manually.
4. Do the Sanity Check
Far too often, reasonable assumptions accumulate to generate unreasonable outcomes, particularly when the financial model is extremely sensitive to changes in key variables or if compounding effects occur in the revenue streams.
For example, in modelling an e-commerce business line recently, seemingly minor changes in the conversion rate of site visitors to paid subscription accounts (from, say, 0.75% to 1%) had a dramatic effect on the cumulative revenue stream over the 36 month forecast period.
So it 's essential that the model pass the smell test. If the compounded growth rates are not credible, it is frequently a reflection on your judgment as an entrepreneur, and it can negatively affect your access to capital.
Putting dampers on your model, such as by decreasing growth rates once you achieve a certain market penetration, or simply adjusting your assumptions downward at various stages can help present more reasonable outcomes.
5. Put together a range of scenarios
You'll want to generate downside and upside scenarios to complement your base case view of the business. Again, this requires judgment to put the pieces together and determine which scenarios make sense and which ones are a perfect recipe for disaster by showing a complete business collapse or a path to unlimited growth.
6. Take a step back and figure out what it all means
Frequently, someone will present a set of numbers who hasn't taken the time to figure out what they really say or how they stack up to comparable companies.
Understand and communicate, in plain language, what your margins are, where your forecast business is most sensitive to breakout opportunities or potential setbacks, and what your overall level of comfort is with the forecast.
Of course, the sad fact of model building is that no matter how careful you've been to lay everything out, you're going to be, well, dead wrong. It 's simply not possible, particularly in a newer (or even pre-revenue) business, to predict what 's going to happen with any level of precision.
But the process of building out the model will not only test, and then shore up, your understanding of your business, it will give you a sound foundation to measure your results, analyze them relative to your expectations, refine them, and continually improve your ability to plan for your business' growth.