Formatting Numbers in a Financial Model:
In our previous article, we discussed the color coding aspect of formatting Financial Models. There are more divergent standards for formatting beyond the
color coding but we will still deal with it, because it is important to maintain internal consistency in the models.
There are different categories of numbers, currency, percentages, dates etc., we will see few standards of formatting these various categories.
Dollar / Other Currency Signs:
In general, when dealing with currency, the symbol ( $, £ etc.,) should be used only at the very top and the very bottom of each schedule in financial
For instance, for the income statement, you would only use the currency symbol for total revenues at the top and then again, for net income at the bottom
of the statement, as shown in the image below.
Same goes for balance sheet or cash flow statement. In certain cases, the currency symbol may be displayed at the bottom of the major sections.
It should be noted that while using the currency, the suggested format for the number is Accounting Number Format. Technically, currency format can be used
but accounting format offers proper alignment for both positive and negative numbers.
Normally, it is not desired to show any decimal places, in models, for numbers representing financial figures. The exceptions to this rule are, share
prices (2 decimal places) or per share quantities, such as dividend per share or EPS (also 2 decimals).
If the company is smaller and has revenue in the millions, tens of millions, or hundreds of millions rather than the billions, then you may show up to one
single decimal place (e.g. ‘$98.5’ million) throughout the model.
You can show the revenue upto a single decimal, if the company is smaller and has revenues in millions, tens of millions or hundreds of millions rather
than billions (eg: $48.5 million). But this should be followed throughout the model, consistently.
Note: In the model shown above, the revenue is in billions, so decimal place was not used.
To increase the decimal places use Alt + H + 0, and Alt + H + 9 to decrease the decimal places. No equivalent shortcuts on Mac.
All percentages are displayed to one decimal place (eg: 9.6% or 89.4% etc.). Calculated Percentages should be italicized so that they are visually
distinguishable from other numbers in the model. If you have assumptions which take the form of a percentage, do not italicize them. Italicize only those
percentages that are calculated.
Usually, dates are displayed formatted with either full month, day and year or simply by the year itself.
The format of the numbers should be designated as “Date Formatting” (press Ctrl + 1, to get to the dialog box of cell format. And under number select
“Date” for formatting type or “custom” with components of dates in the “Type” field.
If the dates are inadvertently formatted as “text formatting” they may not show up correctly.
To calculate the date of the next year and previous year in financial models, you can use the following formulas:
• Previous Year: =
• Next Year: =
We are taking the same month and day, but adding or subtracting 1 to the year each time.
For valuation multiples in financial models – such as EV/EBITDA, EV/Revenue, or even P/E – the best practice is to use a “0.0 x” format or a close
variation of that.
You may apply “Conditional Formatting” or even “Custom Number Formatting” if the multiple is above 0.0 x or 100.0 x, but even that is not necessarily
In situations where the multiple is below 0.0x (i.e. negative valuation multiple) or above 100.0x, it might be better to use the IF(ISERROR()) or IFERROR()
built-in function in Excel so that you display “NM” or “N/A” or equivalent text rather than applying formatting at all.