I have refrained from ranting on Excel models since July when I posted the first rant. This new rant I attribute to preparation for my upcoming workshop on October 26 at FIU--Preparing Financial Models for Business Plans. The example below is representative of an attempt to develop revenue detail, albeit with quite a few opportunities for improvement. It may be easier to follow along if you download the file Download september_rants.xls
The most obvious shortcoming is that the time series (January-December) is presented vertically. I have never seen a case where time series presented vertically made more sense than horizontally. First of all, every financial statement prepared by a CPA shows time series horizontally so we have been trained to understand time periods horizontally. Secondly, the logic of the assumptions (Submitted Applications, Approved Applications, etc.) presented horizontally is much harder to comprehend (at least for me). Lastly, linking the data on this page to another worksheet would be very time consuming (involving a manual link to each cell) because the time series data is not presented horizontally. (Hint: if you have to manually link to every cell in a series you should probably transpose the data.)
Second, this spreadsheet is designed to show the logic for revenue but no where does it indicate the revenue per approved application (one of the key assumptions). Revenue per approved application is a hard coded assumption in each row of the fourth column. Consequently, this key assumption is hidden and the model needs to be re-keyed every time you want to do sensitivity analysis on revenue per approved application.
Third, Submitted Applications drives the whole model but the values are all hard coded, basically giving you no insight into how to determine what affects the number of Submitted Applications per month. When I queried the preparer on what affected the number of applications she told me it was based on the number of offices and the number of application processors per office. This is very valuable information to better understand the business logic, but it was omitted in this model. It turns out that the number of offices is the key revenue driver that makes clear the key strategic issues in the business--how many offices can be opened and how many offices open per year. Never would have known this from the model.
The last major issue is that this model mixes revenue assumptions with balance sheet detail, making it much harder to understand the logic of the revenue detail. Separate balance sheet detail from revenue detail/logic and your models will be much more self-explanatory.
I would present the revenue detail as shown below. The file is here Download rants_1.xls
With the clarity of this presentation the revenue drivers for the business are self-explanatory. If this detail were part of a complete model I would have put each of the key assumptions in blue on a separate page with all the key assumptions for the model and linked to that page. If these assumptions had changed over time I would have presented them on the assumptions page by period and then used the VLOOKUP command to insert the assumed value in the proper period in the revenue detail.
(Note: the Total Locations Available already exist in this particular business, are therefore a matter of fact and require no additional supporting assumptions.)
