When developing a financial model, standards will often communicate the importance of developing a style guide to enhance the presentation and readability of a financial model for those who are external to its initial development. The use of a distinct style should help to enhance the presentation, simplify ongoing model audit, and add a level of consistency and professionalism to the overall build of the model.
Standardising styles will always be a difficult, as each model developer will have his/her own preferences with regards to the various style components. However, to engage in best practice, the most important thing is to be consistent within your model stakeholders, that is ensuring consistency in approach between those who are commonly affected, or can be affected by your financial modelling.
The SMART Financial Modelling Methodology, is one of the most widely recognised best practice financial modelling guides, and details a set of principles of presentation that include the following key elements:
- Visually identify inputs
- State dimensions and labels concisely
- Develop customised style guides
- Worksheets labelled, grouped and colour-coded
- Include integrity checks
- Use conditional formatting
- Use data grouping and a clear heading hierarchy
- Make good use of the screen with freeze panes
- Use line summaries
- Summarise output using plots
The role of presentation within the financial model building process should not be underestimated, as the most complex and well-designed models can lose their impact if they are difficult to interpret, present and audit. As indicated above, an integral part of the principles of presentation is the role of developing a customised style guide.
If the generic cell style set doesn’t suit your taste, you’re left with creating a custom cell style set, and in this article we look at simplifying the process of creating a customised financial modelling cell style set to improve the adoption of a standardised style by model developers. Currently Excel has one built in pre-defined set of ‘Data and Model’ cell styles shown in Figure 1 below:
It is generally considered good practice, when you are creating a model, to include a style guide whereby someone reviewing your model for the first time can use this sheet to develop an understanding of the flow of the model in terms of its components. To encourage the use of cell styles, the aim is to make the development and use of cell styles an efficient process whereby the application of styles to the financial model is not a burden. With this in mind, the example below, introduces an approach to developing custom financial modelling cell style set, which is easy to set-up and apply as the model is being constructed.
Step 1. – Develop a Style Template
By establishing a style template, this sets a clear guide as to the format of the model for anyone who is required to use it. Cell styles are also workbook specific, i.e. if you open a new workbook the custom cell styles will not be available, so if you create custom cell styles, make sure these are applied to the style template which can be copied across to any workbook to ensure the custom styles you have created will be available.
For the purpose of this exercise, the name you intend to call each cell style should be placed within the cell style you have chosen. Not only will this text function as the name of your cell style, but it also visually displays the text settings you have applied to each style.
In addition, each cell style name MUST be different to the default cell style names or it can’t be created.
Step 2 – Setting up the VBA Macro
Once the template has been set up, the idea is to have a way to efficiently apply the newly created formats to the custom cell styles menu. The VBA macro that has been created automates and simplifies this otherwise manual process.
The VBA macro can be copied to your Personal Macro Workbook so that it is available at all times. If your Personal Macro Workbook does not yet exist, the easiest way to create it is to record a macro to it (Figure 3).
Once the personal macro workbook has been created, we can add the macro to it by doing the following:
- Alt-F11 (Open the Visual Basic Editor)
- Click the + next to the VBAProject (PERSONAL.XLS)
- Click the + next to Modules
- Click on Module1
- Click on the View Code icon at the top of the Project Explorer
Add the code below:
Sub customStyleAdd() 'Add custom styles to your workbook by setting up cell styles. 'Also calls customStyleDelete if the user wants to delete all 'preexisting custom cell styles. Dim cellStyles As Range Dim rngCell As Range Dim strMsg As String Dim answer As Variant strMsg = "The following cell styles have been created:" & vbCrLf 'Delete pre-existing custom cell styles answer = MsgBox _ (Prompt:="Do you wish to delete existing custom cell styles?", _ Buttons:=vbYesNo + vbQuestion, _ Title:="Delete Existing Cell Styles?") If answer = vbYes Then Call customStyleDelete End If On Error Resume Next 'Select range (or use preselected range) as the source of the 'preformatted cell styles Set cellStyles = Application.InputBox _ (Prompt:="Select the custom cell styles you wish to create.", _ Title:="Cell Style Range", _ Default:=Selection.Address, _ Type:=8) If IsNull(cellStyles.FormulaArray) = False Then On Error GoTo 0 MsgBox "Please ensure your selection contains:" _ & vbCrLf & vbCrLf & _ "- More than one cell" _ & vbCrLf & vbCrLf & _ "- Includes preformatted cell styles with labels" Exit Sub End If If cellStyles Is Nothing Then On Error GoTo 0 MsgBox "No range selected." Exit Sub End If For Each rngCell In cellStyles If rngCell.Value = "" Then GoTo nextcell Else ActiveWorkbook.Styles.Add Name:=rngCell.Value, _ BasedOn:=rngCell strMsg = strMsg + vbCrLf + vbCrLf + Chr(149) + _ " " + rngCell.Value End If nextcell: Next MsgBox strMsg End Sub Sub customStyleDelete() 'Deletes all preexisting custom cell styles. Dim xStyle As Style For Each xStyle In ActiveWorkbook.Styles If xStyle.BuiltIn = False Then xStyle.Delete End If Next End Sub
Figure 4: VBA Code that automatically applies the Custom Cell Styles
Step 4 – Running the Macro
Once the template and macro have been initially set up, we can run the macro which will create custom cell styles based on the formatting and names that you have used in your template. This can be done without the macro manually, however it has to be done for each style repeatedly, and the macro also provides the option to delete pre-existing custom cell styles if so desired.
To run the macro:
Click on the ‘Developer’ tab → ‘Macros’ → ‘PERSONAL.XLSB!customStyleAdd’ → ‘Run’.
You will be prompted to select whether you wish to delete the existing custom cell styles, and then more importantly to select the range of cells from which your Custom Cell Style Set will be generated. Cells with no label will be excluded as part of the Custom Set.
Once the macro has been successfully run, we can go to Home → Styles → Cell Styles to view the newly created custom cell style set.
Step 5 – Adding to the Quick Access Toolbar
The final step involves adding the ‘Cell Styles’ button to the quick access toolbar. If you click on ‘Cell Styles button’ to display the various styles and then subsequently right click towards the bottom of the open area you have the option to ‘Add to the Quick Access Toolbar’ (see Figure 8). By selecting this option we enable a shortcut icon to be place on the quick access toolbar which is always available to simplify the process of applying the customised cell styles during the model creation process.
Ultimately, the idea is to create a process whereby users can effectively facilitate the set-up of customised cell styles and improve the efficiency related to their application during the model creation process. The use of cell styles is crucial to presentation and model transparency, both of which are key components to widely accepted financial modelling guidelines worldwide.