|
The Brode Report | January 2011
|
|||||
|
Struggling with spreadsheet data overload? Crazy-hard analysis? Multiple entity forecasts? Complex cash flow structures? I'm always happy to discuss situations--call me at (303) 444-3300. |
||
|
On Art and Craftsmanship in Financial Modeling There is this tendency to think that financial forecasting and analysis is all hard-edged, that all about it is known, and it just takes a well-trained practitioner to kick out the results that are needed. My experience is very different from that. I find ample room for creativity that draws on both the art and the craft of creating forecasts, and I want to share one example from each of these areas with you. Starting with the art of forecasting, one thing that I see consistently in early stage companies is that the operational team is convinced that they have a dozen different revenue streams. And here’s where art comes in. After sitting through dozens of investor meetings where we’re trying to explain the business, my experience is that investors just can’t handle more than about four different line items. And the funny thing is that the number of revenue lines a company wants to present is often inversely proportional to the size of the company. I’ve seen companies with under a million in revenue with ten lines. By contrast, consider the revenues for two companies:
The art, of course, is how to summarize. I’m constantly searching for the right way to break the business down into recognizable chunks. For example, if you sell five of the line items to the same customer, it may make sense to talk about average revenue per customer and customers instead of displaying volume numbers for five different products. Another way is to show penetration of different regions where their populations start to use the service over time. No matter how the business is sliced, the most important part of the storytelling side is that the story needs to be simple enough that the audience can get it. VLOOKUP Another reality of financial forecasting is that we all use Excel, which gives you more than enough rope with which to hang yourself. So now I want to delve into one quick bit of technical craftsmanship. I saw a model recently where data was being pulled via a VLOOKUP formula. What caught my eye on this was that the lookup column was 20 cols over from the index column, and it wasn’t a whole bunch of data in a solid table between the two columns. It was a decent sized worksheet where the result column just happened to be that far away. The 20 in the formula was, of course, hardcoded. I was struck by the fragility of this setup. If someone inserted or removed a column in that range, or if they moved either of the index or result columns, then the formula would be delivering incorrect results. My takeaway: long VLOOKUPs are prone to error. Better to use OFFSET from the result column. If you don’t know how far down to go on the result column, then use MATCH on the index column to get the right parameter for OFFSET. In general, I find OFFSET to be one of the most powerful functions around and would strongly suggest that you add it to your toolbox. An example of this is provided here in this file. Until we speak again, wishing you all the best for 2011. David Brode 
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
Connect with me | | | Share your thoughts | | | Subscribe | | | See past issues | | | www.brodegroup.com |
| The Brode Group | Strategic Financial Consulting - Real-World Results | (303) 444-3300 |