This post is part of the series on Business Development Essentials. These posts provide an overview of the fundamental areas of business management. Feel free to contact me directly should you have any questions or need help in any of these areas!
After getting to grips with the structural concept of a model last time we will now focus on something which is equally important: the looks of your model, i.e. the formatting of the sheets.
Formatting is Clarity – Or the Opposite
If you think that formatting is not as crucial as e.g. the way you write your formulas or the level of flexibility your financial model offers, think again. What good are very efficient formulas and high flexibility in a model if nobody can use it properly because they spend ages trying to find their way around it? And even if the model is not for a client or colleague but just for yourself, you should still format it properly – you will thank yourself the moment you have to make some quick calculations before an important meeting or late at night when your brain cannot deal with that extra level of complexity caused by poor formatting. In most cases though you will build something which others will use as well, and in order to make life easy for them you will need to understand their needs using empathy.
Format like your customer is a psychopath who knows your home address!
You may find this header a bit extreme, but this comes from someone who suffered other people’s bad formatting practices for many years. I spent countless hours trying to figure out someone else’s models, hours I could have spent doing something more useful or fun. While there are many good formatting tips, I thought I would focus on the ‘deadly sins’ of formatting and how to avoid them. Let’s begin.
Text Colour Coding
There are a few standard rules about how text colour coding is done in the financial industry, and here are the two most important ones which really are industry standard:
- Inputs (i.e. everything which is not a formula but manually entered) are blue
- Everything else is black.
Why exactly those two colours? No idea, but everyone should know about it since 90% of people use them this way. You may use more colours, for example to label certain things to make them stand out, but labeling inputs and formulas the way I described above makes it very easy for everyone to identify input areas in your model without having to search very long. Also, it keeps you from making mistakes, such as not being able to find all your inputs when you update your numbers. And finally, just in case one of the infamous 10% happens to look at your model, include a little table on the first sheet where you explain your colour code – that way everyone will know what you mean.
Some people like to keep their spreadsheets mostly black and white (I am one of them), while others like to use more colours to highlight different cells. While I acknowledge that there is no black and white answer to this (pun intended), one thing you should bear in mind is: be consistent. As long as your colouring is systematic and you explain what you do, other users will be able to understand it. Having said that, if you end up using 20 colours it probably will get confusing even if you are consistent, so you may want to limit the use of background colours to maybe 5 or so to keep it simple. If you feel that more clarification is required you can always use more extensive labeling or comments to make sure it is clear.
Borders are another example of things which are helpful when used in moderation. Some folks like to use borders around pretty much everything – I find this to be too hard on the eye and it makes your spreadsheet look a bit cluttered. Personally I use borders primarily to separate label rows (e.g. dates on the horizontal top line, or line item labels in the label column) from the corresponding data, or to mark key line items (e.g. the sum line in a cash flow). Also, I always use them for the key structure of tables – again, without giving every cell or even every column a border, but to separate the data area from its labels.
HINT: I always turn off the grid lines so that the spreadsheet is simply white – it looks a lot cleaner and helps me a lot when I work on it for many hours since my eyes feel more relax. Try it out!
Hiding lines or columns
Ever tried finding a cell which was referenced in a formula and it just seemed to be invisible? Welcome to the sin of hiding lines and columns! I honestly do not know why people do it – if you do not want a line or column to appear, you can group it! And everyone will be able to see the little icon on the side next to the line number or column letter which indicates “something is grouped here”. You can then ungroup it with a click on the icon, and re-group it again by doing it again – easy. Hiding lines or columns, on the other hand, turns it into a hide and seek game, driving other users (and sometimes even the spreadsheet’s creator) insane! My advice is simple: just don’t do it. Get familiar with grouping lines and columns, and your life will be a lot easier.
Merging cells is another deadly sin when it comes to financial modeling: merged cells just cause trouble! They cause problems in formulas and also in VBA (in case you want to use code in your model). People mostly use them to center a label across different cells below. There is a clever alternative how you can achieve the same without having to merge cells: format the horizontal text orientation as “center across selection”. For that to work you need to have the cells highlighted over which you want your text to be centered – and it will look the way you wanted it without the hassle of merged cells!
Today we discussed formatting and its importance for making your model clear and understandable. Please keep it in mind whenever you do something in Excel – having good formatting standards will distinguish you from the large majority of people using spreadsheets. In the next post we will learn how to create outputs which present information in a useful way, especially to people less familiar with the details (often known as bosses).