Most of us think a financial model is a dry subject of spreadsheet lacking aesthetic element. However, that is not true. To make it more understandable to the users, a financial model has to be visually appealing too. Reliability, Consistency and easy navigation are the three pillars of a financial model and to facilitate these, it must be well-formatted, colour-coded and neat.
Here’s how you make your financial model visually appealing
1. Colour Coding:
The first step in formatting a financial model is to use a consistent colour coding to depict different types of cells and data.
Blue: Inputs or any hard-coded data
Black: The final output on the same sheet
Blue: Calculations and references to other sheets
Colour coding helps users to understand different levels of calculations and the flow of input and output across the model.
2. Conditional colour formatting
In this, the modeler sets conditions for certain cells and if that condition gets triggered, the cell gets coloured in a particular manner. For example, if a company incurs a loss for the period, the user can set conditional formatting for the profit/loss cells in a model. The condition will be set to check whether the numeric value of the cell is less than zero. If it is so, the cell will get a pre-determined colour, say red. This kind of formatting will help users to identify potential risk elements.
3. Include a key to abbreviations and terms.
While many investors are familiar with terms like FTE (full-time employee), ARPU (average revenue per user), RevPAR (revenue per available room), VAS (Value-added services) etc. it is always advisable to define these terms to ensure maximum clarity. Any kind of ambiguity will only mislead the users. All the definitions can be combined in a separate tab for easy reference.
4. Format your numbers properly
You must use a thousand separators for each cell. This also calls for a judgment on how precisely you want to depict each number. If it’s a marketing cost per store, one digit decimal approximation is fine, but if it is revenue worth crores of rupees, approximation till only one decimal point is deemed imprecise.
5. Insert comments wherever necessary
It is very important to do referencing of sources in order to add more clarity to data in a model. For example, a cell containing an assumption on marketing cost growth may have been taken from the conference call or Management Discussion & Analysis of the annual report. This particular thing needs to be mentioned in the comments to the cell. The modeler should not worry about adding too much comment. It is never a problem, in fact, it gives maximum clarity to the user.
6. Use a readable font size
Font size is a crucial element of the financial model. Firstly, there has to be consistency in the font size throughout the model. Anything below 9 will be too small for legibility. Also, the font type, preferably Arial or Calibri has to be maintained throughout the model.
7. The naming convention of the file
The visual appeal of the financial model starts not only after opening it but from the time a user lays his eyes on it. Improperly named file creates a wrong impression and causes confusion. The most preferred way of the convention for naming the file should be Name of the company_Forecast_Date of a model. This will facilitate easy recall and distinguish it from other files in the folder.
8. Print-friendly layout
Most the times the executives prefer to take printouts of the financial model and have them as ready references for investor meets, conferences and other important events. The financial model is a huge spreadsheet to be properly fitted into a print format, however, it can be achieved. Before that, we must ensure each tab to have the name of the company, the objective of the sheet as well as the currency. There must be a page set up for printing and the length of the sheet has to be adjusted accordingly. The header should be reported title and footer need to be page number for easy reference
9. Use grouping instead of hiding cells
Sometimes a there is data and calculations done in the rows of the model and we often hide it for a cleaner presentation. However, when we share this model with someone, there are chances of us missing out on this and something being overwritten on the hidden rows. To avoid this, we must always group the rows so that the modeler or user can understand this by taking a quick look also.
- Highlight key metrics other than just financial statements
Not just the financial statements, but there may be many other metrics in the model which may be of significance to the users. It is, therefore, preferable to have a separate tab for various other outputs also. These may differ from sector to sector or model to model. This may also be revenue mix as per geography, employee division as per product line, etc.
Financial models have usually forecasted quarterly as well as annually. So, the best way to present it is to start at a smallest time period level which should add up to the longer periods. The quarterly tabs should add up to the annual period. The quarterly tabs can be grouped together for easy reference. Another practice is to keep the quarterly and annual data in separate tabs to avoid confusion.
Overall, the financial model must convey the story to the user at the first glance. For this, it is important to go step-by-step. The well-structured financial model must have these components:
- Cover page: This would contain the name of the company or the project
- Description of the model: This would contain the forecast objective, method of forecast and the period. For example Valuation of XYZ company by the DCF method for the projected period FY2018-FY2023.
- Contact information: The coordinates of the modeler and his team can be mentioned here.
- Table of contents: If the model is large enough, means if it contains more than 5 tabs, it is preferable to have a table of contents guiding the user through the model. The flow of the tabs needs to be logically from left to right and named carefully. After the first 4 tabs as mentioned above, there needs to be an Assumptions tab followed by historical and projected financial statements, the DCF analysis and the output sheet containing the valuation as well as a sensitivity analysis.
Conclusion: One should focus on aesthetics without disturbing the essence of the model
Mentioned above are few of the guidelines to make the financial model visually appealing. That said, there is also enough room for creativity to format the model. However, it is important to not get too carried away by the appearance of the model. The idea is to make the flow of information understandable and to enhance the clarity of the model. Using too many colours and headings may make the model more confusing and directionless. The key here is to understand what is absolutely important from the formatting point of view and avoiding any unnecessary beautification.