Financial Modeling Aesthetics: Explained
My encounter with Financial Modelling Aesthetics
As I started building the financial model of Kings Digital IPO there were few places in which I was not careful. I had not kept the template consistent across all sheets. This created a lot of confusion in bringing data from one sheet to another and I made mistakes due to this inconsistency. Also decimal point considerations were not taken care of. Most of the data in my balance sheet and Profit and Loss sheet were more than 2 decimal places. I went through several balance sheet of companies and I noticed that most of them are rounded to zero decimal places. That makes it look aesthetically more pleasant.
Another point which can help a lot is freezing the panes. Typically the headers become invisible when we scroll down a lot. This is because freeze panes has not been done. This is a very important learning for me because I find it missing in most excel sheets that I come across on a daily basis. The freeze pane option appears under view tab.
As I made the layout consistent across sheets I found that not only it looks aesthetically good but also recognising the data becomes easier. You know for sure that let’s say my column E stands for FY12 in all sheets. Custom formatting become very useful in financial modelling. I used custom formatting to show my negative numbers in red and enclosed in parenthesis. Also making the grid lines invisible increases the aesthetic sense of the model. Another very important learning is that there should be a separate sheet for assumptions wherein all relevant assumptions for revenue and cost build up are done.
With feedback from the mentor on how to keep the layout and aesthetics consistent, I improved my model a lot. Navigation became easier.
With feedback from the mentor on how to keep the layout and aesthetics consistent, I improved my model a lot. Navigation became easier.
Uniform colour coding as in blue for historical data and assumptions. Black for calculated data
To make all the constants in your excel sheet uniformly coloured you select all the relevant cells and then change the font colour. The above task can also be done with an excel shortcut. The steps are as given below
Press F5
A window opens up. Select the “special†tab of the window
You get another window. Check the constants radio button and under it uncheck all other options except numbers
This will select all the constant numbers in your sheet. Then apply the relevant colour scheme
All figures to be rounded to relevant decimal places. In balance sheet and Profit and Loss sheet, all figures are rounded to 0 decimal places
Tool tip on text where explanation is required. Tool tip can be added by adding a new comment under review tab
Template should be consistent across sheets. Let’s say column E represents FY07. It should represent FY07 in all sheets
Main layout can be copied across all sheets using CTRL and drag. This prevents duplication of effort
Use freeze panes so that the headers are visible when you scroll down and right. Use of freeze panes is shown in the image below. The headers that comprise of the years 2012,2013 appear even when you scroll down
All units must be mentioned in the sheets
Grid lines should be made invisible
All figures which are negative can be shown in red and enclosed by brackets. Use custom formatting wherever possible
All relevant assumptions should be made in the assumption sheet and should be used from there in revenue build up and cost build up
For your convenience I have also attached the excel sheet so that you can have a look at how I have taken care of the aesthetics aspects
Financial Modeling Aesthetics: Explained
Comments