Financial Model Formatting – Cell Styles
Making your financial model look professional
Back in school, teachers always gave a few extra marks to the students who set out their workings in a neat way. Why? Not only is the work easier to understand, but because those workings are an insight into the mind of the person who created them, we trust them more if things are laid out in a structured manner. On a subconscious level, we are more likely to believe that they know what they are doing.
Each cell in our model sends a message; long formulae, inconsistent formatting and other such problems will make people scratch their heads and question whether they should have trusted us with their project in the first place.
To prove our point, let us look at the following two examples. At a glance, Example 1 looks like a work in progress while Example 2 looks like a finished model, despite both examples showing the same numbers.
We need stakeholders at all levels to trust our models. Our colleagues need to be able to understand and operate them. Management needs to be able to review and rely on them for critical business decisions. Banks and other external parties need to trust them in order to provide needed liquidity.
As modellers, we have an important responsibility to format our models in a neat, consistent and professional way. And for that we apply Cell Styles to our financial models.
Cell Styles in Microsoft Excel
According to Microsoft, a Cell Style is defined as a set of formatting characteristics, such as fonts and font sizes, number formats, cell borders, and cell shading.
Many Excel users find themselves manually formatting cells in their spreadsheets by using the Font, Alignment and Number format options in the Home tab.
Some even go overboard and create styles by using VBA programming and then fiddle around with the code every time a change in Cell Style is requested.
However, it doesn’t have to be that complicated. Excel has a useful functionality that allows us to format multiple cells in one step. There are several built in Cell Styles that we can apply or modify that will allow us to uniformly format our financial model or any Excel spreadsheet with ease. They can be found in the Styles group in the Home tab.
Creating new Cell Styles in Microsoft Excel
- As well as using built-in styles, we can also create custom Cell Styles by going to the Home tab in the Styles group and clicking on New Cell Style.
2. A Style Name box will appear where we can name our style. At PPS, we tend to abbreviate cell style names to keep the length of the name manageable. For example, we would name a style as Inp_0dp and it would stand for Input cell style with zero decimal places. You can create your own naming convention, but try to keep it both simple and consistent.
3. By clicking Format, we can now customise the formatting and then click OK.
4. The newly created Custom Style will now appear in the Style Name Box.
Although initial set up of Custom Styles can be daunting, once they are there, any changes are processed quickly and applied automatically. We can also copy them to other workbooks and have a consistent style throughout all our spreadsheets.
5. If the existing style requires a change, we can modify it by right-clicking on that Cell Style. We will be taken to the same Format Cells Dialogue Box where we will be able to apply the change. Once the change is made, it will be applied automatically to all cells formatted in that style within our workbook.
6. If we have two similar styles that we need to create, we can create one, duplicate it and apply changes to either. This will help us speed up the process of creating new Custom Styles.
What is formatting used for
At PPS, we group our different cell styles into Headers, Inputs, Calculations, Labels and Error Checks.
When creating custom cell styles for your model, we tend to consult your marketing department so that the colours in the models we build are in line with your company’s branding. And if the branding changes, using Styles allows us to change all cells formatted with a given style, at the click of a button.
The changes are now easier to update as they can be processed centrally and this lets us keep the size of the model intact because every cell we tweak manually creates a new entry in our files metadata which can affect calculation speed and ultimately the size of our model.
Consistency of formatting is a significant part of any model build process as it gives users more confidence in our model.