Share with your network!

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.

What you should do to make your Financial Model beautiful

  • Uniform colour coding as in blue for historical data and assumptions. Black for calculated data
    Color coding for Financial Models

  • 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
      Excel shortcut for uniform coloring

  • 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

  • Do not use merge cells anywhere on the sheet. CTRL+ space does not work properly when merge cells are there
  • 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
    Excel shortcuts for Freezing panes

  • 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