Importance of Commenting and Documentation in Excel Financial Modelling

Have you had the experience, where you look at an excel model that you have created in the past, and think.. how did I calculate this number, why did I do like this to derive this value, what is this number hardcoded into this cell, what is the source of this assumption. I have come across this many times. This can be minimized by using proper comments and documentation.

In software development one of the main concepts that is taught at the very beginning in any computer language is “commenting”. Due to the complexity and collaborative nature of software development, comments and documentation play a pivotal role in creating good code. Building complex financial models is very similar to coding and the importance of comments applies to financial modelling too. Already there are signs of excel slowly evolving into a programming language. Some examples are… the introduction of LET() function where you can define variables for reuse within a formula, use of DAX (Data analysis expressions) in power pivot and use of M language in power query which actually is programming languages.

Below I am discussing the concept of commenting and documentation, in regards to financial modelling and use of excel. And I use the word “commenting” to refer to both commenting and documentation. And it is not the standard excel commenting functionality, though that also is part of the general concept of commenting.

Types of commenting

Versioning:

If you’re building a complex model in excel, it is highly likely that you will build multiple iterations of the same model. And sometimes you will have to go back on certain changes that you did before. In such cases, identifying the model that you need to go back to will be a nightmare, if you don’t remember the changes that you did for each model.

One practice that I do is that If I feel the number of versions for a model is going to be excessive, I maintain a separate sheet called “versions”. This sheet includes the changes that I did in each version. There have been instances where I developed more than 40 major versions of the same model. This practice was really helpful in instances like those.

Version Control in an excel model

And for the creation of the version (or backups), I use a macro that I have created. This automatically prompts the user asking for the comment to be added to the end of the file name and create a backup of the file in the same folder with the filename format

< File Name yymmdd_hhmmss (comment)>. Eg. myfile 201119_112428 (with 10% COC)

This helps me identify the latest version quickly as if I order the files by the name, the latest file will be at the bottom.

Background:

This is to provide a background about the model/working. This will be especially useful if the model involves different business processes and domain knowledge. I generally include meeting notes, screenshots of the relevant emails, location of certain documents that I referred to, Process flow charts and block diagrams etc. in this section.

The macro that I’ve developed to create the documentation template

I have developed a separate macro to create these two sheets, along with some sheet separators, with a single click. (As shown in the above picture.) The code below.

Sub addsheet()
    ' add sheets to the workbook
    Sheet_Exists ("|| ")
    Sheet_Exists (" ||")
    Sheet_Exists ("||")
    Sheet_Exists ("Background")
    Sheet_Exists ("Version")
    
End Sub


Function Sheet_Exists(WorkSheet_Name As String) As Boolean
    ' checks whether a sheet exists in the current workbook by the same name
    Dim Work_sheet  As Worksheet
    
    Sheet_Exists = False
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = WorkSheet_Name Then
            Sheet_Exists = True
        End If
    Next
    
    If Sheet_Exists = False Then
        Sheets.Add(Before:=Sheets(1)).Name = WorkSheet_Name
    End If
    
End Function

Cell Comments

This is the types of comments that we all are aware of.

New commenters introduced in Office 365 is more suitable for action style comments

With the latest version of Microsoft 365 excel renamed the previous comments as Notes and introduced a new type of comments with the name comments which can be used to collaborate in a team. These are very useful if you are entering comments to action something (Either for you or for others), which can be removed once it is done (Marking as resolved). And it has a thread style where you can start a conversation with time stamps.

Traditional excel comments. More suitable for information style.

The other type is good old comments that you insert using the shortcut Alt + F2 or going to Review > Notes > New Note. Personally, I prefer this type (Now called notes) over the other and use them extensively. Types of comments that you would do using this style would mainly be information. Eg. Source of the data in the cell, Logic that you followed to arrive at a value

Inside Cell (Using excel functions)

This is a different breed of comments (similar to inline comments in coding). In this, you utilize an excel formula called “N()”. This formula will convert any text that you type inside the parenthesis to zero. So by using this you are just adding a zero to the end of a formula. Below is an example where I’ve used it to explain the calculation of monthly cost per employee.

If I didn’t include this in the cell there is no way for someone to understand why those two numbers 8 and 22 are used. I’ve worked on models which I inherited from previous users, where there were very specific hardcoded numbers in cells, which nobody knew what those were.

Why Comment

For others

The excel model that you’re building will most probably be referred by many other people. It can be your supervisor for validation and approval. It could be another person, who will have to build a part of the same model. It could be your successor who will work with your model after you leave the particular role. For anyone who will be referring to your model, comments will be extremely valuable to understand the model and will save a lot of time.

Unlike, comments that are done for your own use, these comments needs to be very clear and detailed at the same time. Because… The other person who will be referring to wouldn’t have sat in numerous meetings you had before building the model. He/she wouldn’t know the thinking process that you went through in building it. He/she may not have the same level of knowledge about the subject in concern.

For you

During the tenure within an organisation, how many excel models would an analyst create. And there is highly likely that you will forget certain logics or sources after some time. In these scenarios, if you had done proper commenting in the first place, you will be thanking yourself.

Conclusion

Similar to software development, proper commenting plays a crucial role in excel model development too. Not only will this save time, but also will improve the accuracy as the models get evolved.