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 of proper formatting, there is also the aspect of external presentation. While excellent analytical precision is important, a sloppy presentation can detract from good analysis. An analyst’s work reflects their professionalism and their employer’s standards. Sloppy work can give the wrong impression of the work’s quality. As such, it is quite important to properly format financial models to make them presentable.
One such method of formatting that helps with 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 as 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 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, a 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 decimals.
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 are 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.
CFI is the official provider of the Financial Modeling & Valuation Analyst designation and on a mission to help you advance your career. To learn more, see the following free CFI resources: