Why is financial model formatting important?
Financial model formatting, which refers to the visual presentation of a financial model, helps keep the displayed elements of the model clean and easy to read for the user. A good financial model should be consistent, efficient and clear. Proper formatting helps instill these qualities in a financial model.
Aside from the utility in proper formatting, there is also the aspect of external presentation. While excellent analytical precision is important, sloppy presentation can detract from good analysis. An analyst work reflects their professionalism and their employer’s standards. Sloppy work can give the wrong impression about the work’s quality. As such, it is quite important to properly format financial models to keep them presentable.
One such method of formatting that helps the proliferation of information is color coding. Another such method is the formatting of numbered cells.
Recommended formatting: Numbers
When inputting information into Excel, it is recommended that all decimals be entered. For example, if Net Income for a past year is $4,503.26, it is helpful not to round this input to $4,500 or even to $4,503. Keeping the raw data whole as $4,503.26 helps maintain data integrity, reduces rounding errors and increases data granularity.
Instead of rounding the data input, Excel can format and round the data for the user. Formatting can be set such that an input of $4,503.26 will appear as $4,503. In fact, this is recommended.
As shown in the picture above, input data can contain as many decimals as needed (for calculations) and still be displayed as a whole number (for visuals). Rounding numbers can be easy as pressing the increase/decrease decimals button within Excel, which is shown in the top right corner of the picture above.
Recommended decimal formatting
While the following are just practical guidelines, they are quite recommendable as industry-wide standards. Not all financial models will follow these formats for decimals.
- No decimals: Years, dollar values in thousands or less.
- One decimal: Percentages, multiples, dollar values “in millions or more”.
- Two decimals: EPS, share price, prices if less than $100, some multiples, some percentages
- Three decimals: Shares outstanding when expressed “in millions”
Naturally, there is room for interpretation and judgment in the selection of decimal formatting. The most important thing is to remember to remain consistent across data of the same type.
Typical financial model formatting for public companies shows all dollar values in millions. The recommended decimal formatting for dollar values, then, is to one decimal. As such, net income of $4,531,000 should appear as $4.5 (million) in the appropriate cell.
A financial analyst has a list of potential exit multiples for a certain valuation model. These multiples are 8.51, 10.00 and 11.19. The analyst can elect to display these multiples as either one decimal or two decimal.
A financial model shows all shares outstanding “in millions.” The recommended decimal formatting for shares outstanding in such a case is to three decimals. If shares outstanding for the company is 258,555,555, shares outstanding in the model should appear as 258.556 (million shares) in the appropriate cell.
We demonstrate this concept in our financial modeling course.