When building a financial model, for any discipline, you may want to think about if you want to have lots of worksheets with not much in each one or few but rich in content.
Which would you choose if you were given a choice of having a model with:
- Many (say 20) shallow worksheets (say 50 lines deep); or
- Fewer (say eight) but deeper worksheets (say up 500 lines deep)
Well there is no ‘right’ answer but my preference is to have fewer tabs with the contents of each one clearly bundled up using data grouping, but lets’ explore each approach.
Shallow but many worksheets
You have shallow, easy to navigate and print worksheets. The biggest drawback of this approach however is the high number of tabs for which you probably have to give them creative names that can become so cryptic that they don’t resemble the actual contents of the tab. Another significant drawback is having to scroll across to look for your tab is also – some models have so many tabs they need a menu or navigation system which invariably doesn’t work after the model has been living its life for a few months / years.
The clear advantage is that you do not need to use Data Grouping to bundle information up which can be a little frustrating if the cell you want to reference in a formula is grouped up.
Deep but few worksheets
The pros of having fewer tabs you can see all of the tabs on the screen. This means that you (and any user) always have a good sense of the overall structure of the model. The tabs are divided into key sections governing the project, and also allow you to structure your Inputs tab in a logical fashion.
That is, all the assumptions under the heading “prices”, you know will be flowing into the revenue tab. Fewer tabs also mean fewer inter tab references, which makes life easier for the reviewer / auditor and for some larger models is a computational efficiency consideration.
My take on Excel Worksheet structure
In summary, I prefer to minimise the number of worksheets whilst maintaining an ease of presentation. As a rule do not have more worksheets than you can see on the screen. This may sound too restrictive but works well.
I strongly support the approach of fewer but deeper worksheets in a model.
I usually have one central calculation sheet that does virtually everything in the model. With all calculations on one sheet it is much easier to trace precendents and dependents when there is some problem to investigate.
I think it is also very significant for other users to easily understand the overall model structure (i.e. inputs – calculations – outputs). With loads of different sheets it is more difficult for others to get a quick overview of the model.
Hi Gavin,
As always I think its all about striking the fine balance between userbility and auditability / editability (thats a lot of “bilities”!).
In particular When working on portfolio models (such as 5 power stations in one transaction, or 20 wind turbines each being commissioned at differing times) I find that reducing the sheets down to one per asset is essential.
On Greenfield assets then a sensible number is around 10 but I would stress this includes, Disclaimer, Cover page, Log tab and any explanatory notes which aside from Summary, Inputs and Scernario Manager leaves ~5 calculation tabs split up according to main areas of interest. I like to keep the engineers away from the $ and the tax accountants away from the GWh !
Horses for courses anyway.
Another great article! As a former “many and shallow” modeller, I was forced to convert recently.
Another advantage of the “few but deep” approach is that the number of references between sheets can be kept to a minimum (think of the headers alone…), which does a bit to conserve model size, speed, and usability. On exceptionally complex/large models, the performance improvement can be obvious.
Of course, each client has their own preferences – so sometimes the point is moot.
I find that “virtual workspaces” that are larger than one screen are hard to visualize and keep oriented in my head. Outputs from spreadsheets that exceed a single piece of paper, I want to have on graded and cascaded pages, sections, chapters, and so on of discreet levels of granularity. I don’t need to have all the details on one page at the same time, but I need to have their cumulative effects and significant meaning structured in clear and relevant visual presentation.
When working on a live recording project with a large number of inputs, I find it essential to have all the discreet channel strips on the physical control surface within the reach of my eyes and hands, at the same time. A system with “64 channels” which requires me to page through eight pages of “virtual” screens, and only allows me to control eight channels at a time, starts to give me headaches thinking about it.
I remember one of the first spreadsheet models I studied called “Sales Sheets”; a sales and marketing control and accounting system. It’s design used the principle of granularity very well. Each page was a single printable sheet. Pages represented sales people, departments, territories, product lines, sales/accounting/control periods, activities, results, activities vs results, goals, accounts, assignments, and so on, as required.
A key organizing principle was the output page, which defined the scale of presentation and made the model highly usable for management purposes. The output of a single page could be printed in detail or summarized as an input to a consolidated sheet representing a higher level view: for example, daily activity would consolidate to weekly, up to monthly, then quarterly, annual, and so on.
Each page was carefully designed for simplicity and clarity and had excellent error detection, logic, and input control, with high auditability.
This program was originally designed for Supercalc running on an 8-bit cpu in CP/M with 64 kilobytes (large for the time) of RAM, using dual 8″ floppy disk drives (no hard drive), and was a quantum leap in efficiency when compared to the patchy manual systems (or lack of system) it was intended to replace.
Growing into the cloud, with SASS and multiple servers housing multiple virtual machines running terrabytes of RAM, 16 core processors, and solid state HDD’s as fast as RAM-Drives, gives us the option of consolidating highly complex processes and interdependencies into a single workspace and organizing that workspace with logical niceties of virtual organization into pages, tabs, cells, rows, columns, sheets, workbooks, and so on, most of which is irrelevant if we lose sight of the fact that the purpose is to organize and transform raw data into useful information which has value and meaning for the decision makers who must use that information in making risky choices with real world consequences, in a time sensitive and competitive environment.
No useful model will lack depth of content, analysis, and accuracy. No useful presentation of output will lack granularity, clarity, and a useful virtual representation of the facts it represents.
Good design will start with a thorough understanding of the requirements which the design will satisfy, what the model will actually represent in the real world, and how the information will be used to satisfy the requirements. From this the most important design parameters will be the definition of outputs, and their priorities, dependencies, and levels of detail, including their visual and physical characteristics, and how they will be communicated to the user.
Next most important is to understand the necessary inputs which are required in order to derive the output information.
In the real world this is a place where things can get messy, as speculations, anticipations, decisions, predictions and weather reports are the stuff of inputs, and our computing machines only deal in discrete quantifiable data, assumed to be facts.
How you get from inputs to outputs is irrelevant or at least an area of abundant choices and alternatives (until you want the d*m thing to actually work on the quirky lap top you got from the IT geeks who said it would do every thing you might want including serve lunch and candlelight dinners). The important thing is to have command of your tools, whatever they are.
Remember the old General Electric Decision Tree? On one sheet of paper you could tell whether doing a thing would turn a profit or not. It was one of the most valuable contributions to business logic in the history of enterprise and made GE one of the largest compaies in the world, and a household name in every home in America . . . until they decided to expand by vertical integration, dipping their toes into large scale land development with the construction of a “New Town” (where every house would be “All Electric” and sport GE appliances in every room). This ambitious and laudable project scored very high on the Decision Tree Profit Projection Meter, but never got built and lost over a hundred million dollars of capital investment. What went wrong? They began their design with an assumption statement, endorsed by their design consultant and board of directors which listed 110 assumptions to go into the location decision and project investment criteria. Unfortunately, 102 of their assumptions turned out to be wrong.
What’s my point?
Well, deep or wide, do the design first, and the requirements analysis even before that. Speed of execution, program/model maintainability, portability, reusability, language/system compatibility, version compatability, end user sophistication, and operator skill sets, are all potentially valid criteria and/or requirements. As are power consumption, ‘green values’, avoiding Tsunamis, eliminating poverty, and world peace.
Nobody ever said life was supposed to be easy!