This blog is part of a blog written by our director Alex Carse for the ICAEW as part of its "Intro to Financial Modelling" series.
Transparency in modelling parlance refers to how much effort is required to understand the trail of both inputs and logic that a given cells value is dependent on.
Input transparency
First let’s touch on how we can make inputs transparent. The most important aspect of this is labelling, this will be covered in more detail by a later blog and so to avoid stepping on their toes all I will say on it here is:
Always label
Label all inputs, calculations and outputs
Label with title and units
Label, label, label
Instead I will focus on a different element of input transparency titled in the code as “Identify and separate forecast or dummy data”. It has the following recommendations that are worth drilling into.
Create separate entry areas for forecast and actual numbers, with formulas that select which data is currently in use
Although this recommendation highlights forecast and actual numbers it can equally be applied to any input where the context of the input changes over time.
Identify dummy data clearly with formatting or labelling
This recommendation could be simplified to “Identify data source clearly”. For most inputs why the existing value in the input cell is that value will not be clear to all users, but all data is either dummy or has source and that source should be recorded.
Don’t overwrite forecast data with actuals on a rolling basis
Forecast and actual data have a key difference in that forecasts will change, and actuals will not. This key difference is why they should be treated identified as separate and entered separately. Additionally, if you overwrite your forecast you then cannot go back and compare performance against that forecast.
A similar but much worse issue often seen (especially in operational project finance models for some reason) is overwriting logic with actuals. This should never be done and would be a red flag to me as to whether to trust the entire model.
Calculation transparency
Calculation transparency is often thought of as reducing the complexity of formulae and avoiding VBA as far as possible. But most of the guidance in the financial modelling code is geared towards transparency from consistent column structure to sign convention. Here I touch on two points which are less often thought of.
Avoid duplication
Avoiding duplication helps the developer by reducing the number of locations changes have to be made if the logic requires adapting but I would argue the bigger help is to the user. The user will usually assume that if the model includes the same calculation twice there must be some difference between them and then their mind jumps to why. This loops back to the point I made earlier “Don’t make me think”.
Don't hide things
It is self-explanatory that hidden rows columns and sheets are not transparent, however giving the option not to display data often improves usability. This can be achieved by using grouping rows and columns that may not be required by the user at that point in time. I like to go further than just leaving the plus on the top of left-hand side as I think it is always helpful to have a reminder of what is grouped away. Examples of this are in figures 1 and 2 below.
Figure 5:
Figure 6:
