When we design something that exists in a physical form in the world, we have the benefit of having something to pick-up, turn over, peer into, kick, or thump when something is not working.
Additionally, if we are designing something like a car and find that the dashboard lights are not working, it is a safe bet that the problem lies with the electrical wiring or switches in the vicinity.
Not so with spreadsheet modeling. Despite the fact that we can see a model, it’s not actually ‘‘there,’’ and when problems arise, we have only our mental map of it to use in figuring out what is wrong. And, unlike a physical counterpart, a problem in one area of the model can be caused by something else not seemingly related to the problem at hand. So the design principles we apply as we build our model are critical. The more we can do things correctly the first time around, the less trouble and confusion will result. Some principles to consider:
- KISS—Keep it simple, stupid.
- Have a clear idea of what the model needs to do.
- Be clear about what the users want and expect.
- Maintain a logical arrangement of the parts.
- Make all calculations in the model visible.
- Be consistent in everything you do.
- Use one input for one data point.
- Think modular.
- Make full use of Excel’s power.
- Provide ways to prevent or back out of errors.
- Save in-progress versions under different names, and save them often.
- Test, test, and test.
KISS
The overriding principle in model building is the ‘‘Keep it simple, stupid’’ principle. The KISS principle does not mean that a model should be simplistic and do nothing but the most rudimentary of calculations. Rather, it means that whatever you need your model to do, keep it simple. A variation of this is the principle of Occam’s razor: the best solution is the simplest one.
Keep the formulas simple, even if it means using one or more lines to break up the calculations. If you write a formula and then look at it again 10 minutes later and have a hard time understanding it, that is a sign that you may want to break up the formula into two or more cells.
Keep the structure of the model simple, with a flow of calculations that, as much as possible, go in one consistent direction in the model, from the ‘‘beginning’’ to the ‘‘end.’’ Generally, you can consider the ‘‘top’’ sheet in Excel— whose screen tab is at the leftmost at the bottom of the screen—to be the beginning. The ‘‘bottom’’ sheet is at the end. This will give the user a sense of the start and the end of the model. A ‘‘simple’’ structure will mean different things to different people. On the one hand, it may mean that there should be only one sheet, with the beginning of the model at the top and the results at the bottom. On the other, it may mean that there should be several sheets, with each sheet containing particular blocks of inputs or calculations.
Keep your formatting simple, with just enough to make visual distinctions on the screen to help your users, without going into a psychedelic mix of florid colors and heavy lines. Bold types is helpful for highlighting items on the screen, but use it sparingly. If the screen holds a profusion of bold type, then the highlighting effect is gone, and the screen now looks visually ‘‘heavy.’’ KISS is a very beneficial principle to follow. Determine what ‘‘simple’’ means to you and those who will be using your model. If there is a difference, go with the ‘‘simpler’’ of the two. The more you can follow that standard, the more your work will be used.
A good model should be powerful and fulfill its analytical goals, allow its settings to be changed quickly and with reliable results, and be fun to use. A truly great model ‘‘disappears’’: the users use the model to get the results they want without the model’s functions or interface design intruding into their consciousness.
Have a Clear Idea of What the Model Needs to Do
Having a clear idea at the outset of what your model needs to do is an absolute requirement. If you do not have a clear idea, the best thing to do is to step away from the computer and continue to think out what the model should be. A good way is to build a small pilot model that can give you a proof of concept, or simply to take pen and paper and start sketching out the flows.
The clearer the modeling goal, the less messy the model
Being clear goes a long way in helping you follow the KISS principle. Sometimes, you have a clear idea but the idea is that the model should have more than one primary function. This is to be expected if you are building a standard model that will get used in many different situations. For example, your model may be used for credit analysis and for an equity valuation. The credit model may need a ‘‘cash sweep’’ module, whereby excess cash produced as a result of your assumptions can be used to automatically repay outstanding debt
The valuation model would have to pay attention to the development of ‘‘free cash flows.’’ In this case, a good approach to take is to develop one solid ‘‘calculation engine’’ at the core of the model, the output of which can be used in different ways.
An important distinction here if you are building a model for others is that their sense of what the model needs to be may be different than yours. Always build your models to match, or exceed, your users’ expectations.
Be Clear about What the Users Want and Expect
If you are creating a model for others to use, be absolutely clear about what your audience wants and expects. Do not assume that you know what they want—often they themselves only have a vague idea of what they are looking for, making it likely that what you produce for them will meet with thumbs down reception. If they have a model they like that they are already using, it is a good idea to make your (new and improved) model follow some of the layout and analytical steps used in the old model. Users generally like to stick to the steps that they are comfortable with.
You will also have to gauge the skill levels of the users and develop your interface appropriately. Another important tip: check the version of Excel that they have and make sure that there are no compatibility problems with the version in which you are developing your model.
Maintain a Logical Arrangement of the Parts
With the goal clear in your mind, the natural way to set out a good layout is to follow the flow of calculations. The bigger the model, the more important it is that this principle is followed. What do you need to calculate first in order to get to the next round of calculations? After that, what else in order to get to the final result? In this way, it will be easy to follow and check the model’s workings.
Many models are unnecessarily difficult to follow because the calculations are done by formulas that are spread out willy-nilly across the model. Granted, there will be times when the calculation blocks cannot follow each other in one smooth flow, but the more they can be ordered in a logical and visually accessible way, the easier it will be for you and your users to work with the model.
In terms of the final output, this can be a separate sheet that organizes and presents the various parts of the model in one summary form.
Make All Calculations in the Model Visible
A corollary of the logical arrangement is that all calculations must be visible. A ‘‘black box’’ model is the most intimidating kind of model. This is the kind of model where the calculations are not visible and the model produces its results with no indication of how it does so. By the same token, nothing is more reassuring to users than to see how the model is working and to be able to check for themselves the calculations—all the better if the formulas are arranged in a logical fashion.
And not only formulas, but also the ‘‘toggles’’ or settings that allow you to set how the formulas work.
Be Consistent in Everything You Do
As much as possible, make the parts of the model be consistent with one another. Use the same label for the same item if it is shown at different places in the model. Calling the same row of information ‘‘cash flow from operations’’ in one place but
‘‘operating cash flow’’ in another is a prescription for confusion and error. The same columns in the sheets should contain the same year. When you know that every sheet’s column H contains the data for year 2003, formula references across sheets become less prone to error.
The same font and font size should designate the same type of item. If you are using colors in your fonts and cells, be sure to follow this consistency rule, too.
Use One Input for One Data Point
There should be only one place in the model to enter one data point. For example, if you need to work with the current stock price of a company, enter it in one place only and have the model always read that input, either directly or indirectly, when it needs to calculate anything that would use the current stock price.
Having multiple inputs for the same data item will exasperate your users, and only leaves room for conflicting inputs for the same data point.
Think Modular
Build the model so that it has blocks or modules of formulas that perform discrete operations within them. As a block completes its tasks, it passes the results to the next block. This approach makes the work of building the model, and later of checking and auditing it, that much easier. It also makes changes easier to implement, as you can work with the modules and not have to roam over the whole model to change formulas.
In military parlance, the expression ‘‘fire and forget’’ refers to missiles that unerringly hit their target, no matter what the battle conditions are after the launch. The parallel for developing models the right way is ‘‘develop and forget’’: develop and construct your model robustly, and let it be capable of future changes easily. The ‘‘think modular’’ approach is by far the most effective way to get to this level of model-building expertise.
Make Full Use of Excel’s Power
A valid way to describe Excel, or any spreadsheet program, is that it is a big calculator. Just as you would not take a pencil and do a sum on paper before entering that number into a calculator, you also should not have any intermediate tools between you and the spreadsheet. Do everything in it.
Excel has a whole repertory of over 250 functions (preformatted formulas) that make it a hugely powerful calculator.
These functions are divided into the following types:
- Financial
- Date and time
- Statistical
- Lookup & reference
- Database
- Text
- Logical
- Information
You won’t need to know all the functions. In fact, for the financial modeling that is used in investment banking and finance, you will only need to know as a start about 35 or so functions
Because Excel’s functions work with one another, putting combinations of functions together will often give you exponential leaps in your modeling expertise.
Excel also lists its functions when you click on the Function icon on the menu bar. Help screens can be called up to help you understand what each function does.
Excel has its own programming language, called Visual Basic for Applications (VBA). This is a powerful language for writing macros to create various user forms to help with the user interface or to automate tasks.
Provide Ways to Prevent or Back Out of Errors
There are two types of errors to worry about:
Formula errors. Formulas can work well when all the data are entered but will show an error if a data point is missing. An example is the #DIV/0! (‘‘divided by zero’’) error in formulas where a number is being divided by another. In this case, the simple remedy is to write the formula with a way to prevent the error, by checking whether the denominator is zero or not. If it is, the statement just returns a zero.
User errors. A good developer can usually guess what the ‘‘typical’’ user will do, given a particular point in working with a model, but there is no way to guess what the ‘‘untypical’’ will do! There are countless unexpected ways that users interact with a model. Where the number is expected, they may put text and vice versa; formulas that they have been told not to touch get altered, and altered radically; messages displayed in the middle of the screen describing the next step go unread, and the wrong button is clicked; and so on. To prevent user errors, we can employ a variety of approaches such as designing the screen to guide the user to do the right thing as much as possible; using Excel’s data validation features that prevent the wrong type of inputs (e.g., a number when a text string is expected) from being entered; writing very clear and explicit messages on the screen about what to do. However, there is likelihood that users will still make mistakes.
Save In-Progress Versions under Different Names, and Save Them Often
This is not so much a design principle as an operating principle to use when you are designing. Anytime you work with any electronic documents, you should remember to save frequently. And don’t just save under the same name. This is because you want to have a record of your work over time, in case the latest version gets corrupted. For example, if you had saved a workin- progress 30 minutes ago as Newmodel08.xls and the current
Newmodel09.xls has up and died, then you can go back to version 08 and pick up the work again. You will have lost only 30 minutes of work. The shorter the interval between saves under different names, the less you lose if you have a system crash. It would be disastrous if you used the same name again and again over days of development work! It is also a good idea to save and rename whenever you have completed a particular feature and you want to start adding something new to the model.
