Everything from color, numbers, formulas, and text have specific formatting logic when it comes to creating a financial model. Great financial model formatting is key to a great financial analyst.
For both text and numbers, the following schemes are essential –
Excel will always round for you according to the format. For example, if cash is entered as 25.382, excel will present it as $25.4. This will ensure that all calculations will be as accurate as possible while still looking great and clean.
Excel will flow and be dynamic in this sense.
For example – =F5*(1+10%) should not have the percentage symbol within the calculation. Instead, break out inputs into a separate line item. I.e. the 10% should be input into another cell (G7 = 10%) and should be referenced as – =F5*(1+G7).
When formatting numbers in a financial model, there are a few basic rules that help keep things simple and clean. To begin with, a good rule of thumb is to utilize excel’s standard types – number, currency, accounting, etc. By doing this, excel will register and display things such as negative numbers in parentheses as the program aligns the decimals between positive and negative numbers.
One should use custom formatting for percentages, firm specific formats and more creative presentations to guarantee numbers are computed appropriately in their correct format. A custom number format is created in “Type” after using the access method.
For example, the number 3.4 typed as 0.0 would be displayed as 3.4 where the number 3.4 typed as #,###.0 would be shown as 3.4.
It is common to see letters or words after a number whether its dealing with multiples, basis points, or years. Excel will read these as a “number” and not as a “text”. For example, the number 3.4 typed as #,##0.0x would be displayed as 3.4x and the number 150 typed as 0 “bps” would be shown as 150 bps.
Formatting matters because excellent analytical work and excellent presentation are both important for a multitude of reasons. For starters, your work represents you and your firm. Also, sloppy, inconsistent work may give the wrong impression about the work quality. Lastly, the numbers can be 100% accurate, but a poor presentation could lose a deal.
It is very important to take the few extra minutes to format and make the work easy to understand.
Learn about the different elements within a financial model, or how to properly set one up: