In my experience making a good model requires you to spend time not modelling, but talking about it instead. With my immediate team, I can talk in front of the monitor with the model on it.
But other project members do not talk in columns and rows; they prefer to talk with pictures.
Pictures mean charts. And making good charts is hard.
A well-designed chart is understood by its audience within 5 seconds without any further explanation. It conveys the message you intended and minimises off-topic questions.
In my experience making a good model requires you to spend time not modelling, but talking about it instead. With my immediate team, I can talk in front of the monitor with the model on it.
But other project members do not talk in columns and rows; they prefer to talk with pictures.
Pictures mean charts. And making good charts is hard.
Data visualisation and the FAST Standard
I am a frequent user of the FAST Standard when it comes to financial modelling. I also spend many hours in the office hitting tried-and-tested keyboard shortcuts. For me, using the mouse feels like cheating. People unfamiliar with the FAST Standard may find my comments geeky and boring – but there are many like me that know there is no better, faster or more satisfying way to build financial models.
However, there is an omission from the FAST Standard that I’d like to address: data visualisation. Graphs, charts, tables, etc. The FAST Standard is generally silent on this topic (only one paragraph – 2.05-06 – touches on the subject briefly).
I find it odd that most of the hard financial modelling labour is put into making a sophisticated, optimised, multi-timeline, all-inclusive, top-notch model and yet so little attention is given to the output it generates.
Only a few people (me and my geeky co-workers) will see the engine of the model, while others (“the management”) will only see the pictures it generates. Therefore the overall value of the model is determined by the quality of the charts and tables it creates.
The best models can generate the most brilliant results, but if the charts are unclear, the message will not be understood, not believed, or worse, understood wrongly. Therefore the value of such a model will be marginal.
What’s on the web?
If you are interested in making your models more valuable by improving how you present your outputs, then I recommend the following websites:
The blog Perceptual Edge from Stephen Few should be first on the reading list. He includes dashboard design and is the inventor of the bullet graph. Most of his tips and tricks have been published in his Visual Business Intelligence Newsletter. Go to the ‘Library’ on his website and be amazed.
Edward Tufte is the founding father of data visualisation. His standard piece “The Visual Display of Quantitative Information” gives a complete overview of the history of (handmade) charts and introduces one of the most important definitions in chart design: data ink and chart junk. The first edition of his book was published before the era of Excel (1982) and might look a bit classical. Nonetheless, the content is still applicable to most visual design challenges you could face today.
Chandoo.org is a very broad website on Excel. It gives a wide range of options for making dashboards (start here), VBA tricks, and other clever stuff. Some of the dashboard models can be downloaded so you can pick out the elements of use to you.
Storytelling with data focuses on the story behind the data. The challenges are recommended – readers of the blog post make their best attempts at a nice visualisation. It’s useful if you are looking for new, more effective ways to display your data.
These are my favourites, but I am curious to know if you would like to share your sources for data visualisation. Please leave a comment at the bottom if so.
Handy tips when working in Excel
Here are some handy tips when you are building your financial model and starting to make some charts:
Since there is no easy way of telling that data in rows are used in a chart, I always mark them red as some kind of export. The trace dependents (Alt M, D) shortcut won’t forward you to the appropriate chart but, hey, it’s a start.
Before making the chart, I collect the data for the chart in a single block. I include a row at the top for the horizontal timeline and add borders both top and bottom.
Once your chart is up and visible, allow yourself to test various forms and layout of the same data. Creating a good chart will take time and will save laborious explanations.
Never use borders on charts and bars: it is classic non-data-ink.
Use boxplots if you think your audience can handle it. Unfortunately most of the time, they cannot. Refer to this memo for a quick introduction.
If you plan to use the chart in Powerpoint, a horizontal bar chart often fits better on the slide than a vertical bar chart. The bars of a bar chart should always start at zero so that your eyes compare the length of the bar to estimate the value.
Line charts should ideally start at zero as well, but this could be ignored if the slope of the line is more important than the absolute value it represents.
Make sure you’re thinking carefully if you are proposing to use a pie chart. Often there are better alternatives, take a look here or here.
Avoid dual y-axis since it introduces unneeded complexity.
Use only one font and be consistent in font settings (e.g. bold for titles, light grey for axes, medium grey for axis numbers).
Be subtle with colours and use bright colours to attract attention to specific data.
The visualisation of data has my long time attention and therefore I am always curious if you as readers of this blog have might have other improvements. For example, I’m still struggling with naming the label versus naming the chart series. In the label of the line item I prefer precise and complete words to describe that line. However, I want the legend in the chart to be short, condensed and frequently abbreviated. My best attempt so far is to make additional line items to include labels in the last data point of the chart series with a last-column-flag.
As an example I have used data from the F1F9 31-day course. One chart in the standard Excel chart format followed by an improved format. Both charts contain the same data, but the second one gives an instant overview and uses less space.
