Best practices for excel modelling- A complete guide

Most of us nurture a desire to learn something with perfection in life. Let’s say you were crazy about learning to drive and gave it your all. Today, after long you want to go out for a drive with friends but alas, your car refuses to co-operate! The problems had existed since long but you never took good care of the car, never maintained it or followed what’s best for your car and today when you need it badly, it chooses not to obey you. What does this signify? Just learning a tool is not enough. We need to continuously follow the best practises to maintain the tool so that in times of requirement it functions properly and produces desired results in a hassle-free manner.

Financial modelling as an analysis tool can produce wonders, but only when the modeller follows best practises to make it work. By best practises we refer to the key principles that need to be followed to build and maintain the model for maximum functionality.

Let’s now the best practises to be followed for excel modelling:

1. Standard Structure:

Each company follows a set of guidelines for the structure and the financial models must adhere to it. The number of sheets, necessary page breaks at intervals, logical flow of the sheets from left to right and top-to bottom, sequencing of the sheets, and proper explanation whenever required all come together to define a standardised structure of the financial model. Also, it is highly important to format cells appropriately following standard practices. Eg: symbols such as for currency, percentages values, etc. must be consistent. The absence of a standardised model would lead to confusion and lack of readability. Hence it is imperative to ensure that a consistent and standardised structure is adopted.


2. Protection:

Data for the financial model must be protected from any kind of tampering. Hence the excel sheet should be password encrypted against any unauthorised access. Ideally, there should be two levels of password protection. One for accessing the file and one for modifying it. The first password would allow a Read-only access to the file where changes can be made in the cell, but it cannot be saved in the original file. In a user wants to make and save changes in the original sheet, he would have to access it through the other password.

3. Appropriate documentation of assumptions:

Assumptions are an integral part of a financial model and they need to be documented very systematically for easier understanding. A separate sheet needs to be assigned for assumptions, wherein inclusion of source data and calculations will provide a good reference. Also, all explanations regarding the assumptions taken should be preferably mentioned in details so that any user at a later stage also doesn’t face issues in understanding the reason behind the assumptions.

4. Use linking and not hard-coding

Financial models are full of numbers and hence there is a lot of scope for errors or omissions. Where there are repetitive use of the same numbers across sheets or the same sheet or other dependent variables, it is best to link the cells. When an input in one cell changes due to certain factors, the output would change automatically. So when there are sudden changes to be made and the analyst is already working in pressure situations, he need not worry about these things. Other than reducing chances of errors, linking rather than retyping also relieves the hassles of final changes which can be done by other users working on it.

5. Consistency in formulas

One of the best methods to reduce errors in a financial model to maintain consistency in formulas. How does one achieve this? Firstly, ensure that in a calculation table, the same formula exists throughout the table. To check this, click on Formulas tab in excel—Show formulas. Secondly, check for blank rows and columns in data blocks, else it poses a hindrance to DATA Filter and DATA SORT.

6. Formatting of charts

Formatting of charts in the financial models must be easy on eyes with uniform font size, color coding, legend, axes label, scaling etc. One of the most common mistakes of financial modelling is not labelling the charts appropriately. You must ensure that appropriate units and measures such as MWh, litres, headcount, ‘000, kms are mentioned on the axes as well as data tables. Also, it is best to take notice that column and row headings should have unit or currency headings and should only contain a single type of unit or currency.

7. Readability

One of the most important aspects of financial modelling is to simplicity. If a model is not simple, it will not be readable. Instead of going for complex templates, you must try to keep the model well-organised by using a simple template focussing on core functionality. The sheets should only be the ones really required, the cells can also be grouped in cases where applicable, and the navigation should be as seamless as possible.

8. Flexibility/ futuristic

Financial modelling is a dynamic tool. It has to be futuristic as well as flexible. When you build a model, it is must to create one that is adaptable and responsive to changes within and outside the company. Changes within the company can be based on sales, profit, organizational restructuring, any product launch, technological innovation etc. Whereas, external changes refer to any macro economic development, change in interest rate regime etc.. All these have the capacity to impact the sales, operating cost as well as profits. The more these factors are taken into consideration in the assumptions, the lesser is the requirement for manual changes in the future.

Eg: Instead of just mentioning that revenue of pen will increase from Rs 3500 to Rs 5500, we need to precisely mention that the Revenue = Unit sales * Price per unit and provide input whether the increase in revenue is due to increase in unit sales, price being constant or vice versa.

9. Reader focussed

A Financial model caters to readers of various segments. A Business Analyst, Financial Manager, Chief Accountant or a Senior Management official can use a model created by you. Having a diverse group of users’ means it is a huge responsibility to ensure that the model is suitable in catering to the requirements of all the stakeholders involved.

10. Integrity checks

It is natural for a model to have errors, but what is more important is how easy it is for the user to identify the errors and rectify it at the right time. An even better approach is to work towards reducing the errors. One way to help reduce errors is to include error and integrity checks in your models. It is advisable to collate all the individual checks across the model into one separate sheet. This allows the model builder to check that the model, or section of the model, works correctly.

Ensuring the best practices increases usability and authenticity

Why do you build financial models? To forecast the trend accurately over a long period of time. So for a model that you have so meticulously built, would you not like to spend some more time and tighten the loose end to boost its functionality? Following the above mentioned best practises or essentials are the basic steps to ensure that the model is fool proof. A structured, non-complex, accurate and adaptable model is what every analyst wants. Isn’t it?


0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.