Using Color Codes to Differentiate Your Financial Models

Deciphering Large Financial Models

Large Financial models are often complex. Small changes in parameters may lead to very different results. As analysts, when we build our models it’s a great feeling to see the valuation numbers. However, the client (who is indeed the target audience) may hate to see it! The client (He could very well be your boss) wants to know where the flow (Where the numbers are coming from) along with the logic, and not just the end result. He does not have time to spend to understand the model.

When I started my career creating models, I created this huge business model of a retail company. I spent like a full week creating this guerilla and when I finished and sent the model Friday night, I was on cloud nine.

On Monday, I received a cold looking email from the client basically trashing my model.

He couldn’t understand the nuts and bolts and said something about the drivers that should be changed to make the pricing more attractive. He wanted me to redo the thing and have it ready in the evening!

That’s when I learnt the 101 of Financial Modeling. Clearly Indicate the numbers that they can change!

Color Codes can Save Tons of Time and Money for Financial Modelers

Modeling is Not Rocket Science (and by corollary Readers of Your Models are not Rocket Scientists). They cannot and do not have the time to understand your beauty. So treat them like 3 year olds.

So the clients happiness quotient could have been 10x, if my model looked something like:

I figured it the hard way out, but I have an interesting shortcut for you to figure this in like 10 secs.

Shortcut to Figure out Assumptions (Constants) in Your Financial Model

Excel has a ton of shortcuts and this one is a life savior. All you need to do to find constants in your model. Follow the below steps:

Step 1-Press F5

Step 2-Select Special

Step 3-Choose Constants

step 4-Uncheck everything other than numbers

Step 5-Press Enter (Selects all the hardcoded number), go to Home and change the text color to Blue

Commonly said “Picture speaks a 1000 words”, so let me show you in pictures

1. Press F5 and select Special

2. Select Constants and uncheck the others

3. Go to Home and Change the color to blue

4. This is how clients love your Financial Models

Focus on Customer Satisfaction

When building a financial model, it is important to realize that the model is not meant for you. It is meant for your clients – your bosses and their clients to read. It is essential that the model is presentable and easy to decipher and change.

Large models often contain thousands and thousands of numbers and values. As an analyst you should always differentiate between numbers you have entered on your own and the numbers that have been derived using formulas.

Bonus Tip

I also figured that while differentiating colors is a good practice, do not use too many colors. Remember this is a formal model you are building, not a coloring book

Other best practices

Some other best practices to follow when doing financial modeling in Excel are:

• Input cell naming: Ensure that whatever cells are used to input data into the model are named so that they can be referenced easily

• Proper linking: Cells should be linked from left to right and top to bottom throughout the workbook in formulae

• Particular year should be specified in the same column throughout the workbook

• Grouping: Use the Group feature instead of deleting or hiding rows that you don’t want to display

Way Forward:

This series gives you a flavor of possible errors in financial models and best ways to avoid such errors. To learn more about financial modeling, EduPristine, a name trusted by Fortune 500 Companies, undertakes Financial Modeling training across various nations world-wide.

This training aims at imparting its participants the ability to apply modeling techniques in a wide range of practical scenarios. Candidates will learn the latest tools and techniques of building financial models using excel to depict financial statements and investment analysis.

For further info visit us at: Financial Modeling Training

Templates to Download:

I have created a template for you, where the subheadings are given and you have to link the model to get the cash numbers! You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).

For any queries regarding financial modeling, feel free to put the comments in the below box.