A key differentiator of the many different Best Practice Methodologies that have been developed, both by in-house and professional financial modelling organisations, is the way in which off-sheet references are treated.
There are several approaches that are out there and they are listed below. My views are not of a purist or of an academic but of an experienced transaction modeller where transparency, speed, accuracy and presentation of information are the defining constraints.
1. Extensive use of Range Names in Excel
Range name all cell ranges that will be used and use the range names in the formula. There is no benefit in this approach and it results in an unmanageable number of names. When Microsoft introduced this feature it was very useful but it should be treated as a ‘power tool’ and only used when really needed – and don’t get me wrong, those instances do indeed exist.
And so, the exception to my view of not using range names as off sheet references are when they are used as named constants such as Days_Year or Kgs_Lbs where it is indeed very useful. However, on these occasions the ‘importing’ would be done directly within the formula itself.
2. Dedicated area for imported lines
On every sheet that pulls information from another, import that information at the top of the destination worksheet in a dedicated area. All formulae on that worksheet that require the information are then linked to where it has been brought in at the top of the worksheet.
This is better than range naming but has a downside that formula created down at say line 300 will have to ‘reach’ all the way up to the top of the worksheet which makes them cumbersome to visually check using F2 which is really handy when working in a transaction (as opposed to academic) environment.
3. Tailored approach
Each time the source information is required within a calculation on a destination worksheet that information is imported close to but not manipulated into the area where the actual calculation is being performed. This is my preferred method because it makes checking and presenting the calculations much more efficient.
Purists will argue that performing multiple links to the same off-sheet data source has a higher risk of picking up the target incorrectly and should be preferred because it only has to be done once.
I fully appreciate this argument but I propose that linking it in as needed close to where the calculation takes place allows for quicker sense checks, ensuring that it is sourced correctly has to be done only once, and besides, bringing in the data in the calculations introduces the risk that it was altered on the way through.
Avoid multi-level linking – ‘daisy chains’
Do not link source data to a cell which itself has been linked to the original source. Always go to the source or as in method 2, the dedicated area for that information. The result of this malpractice has been aptly termed ‘Daisy Chaining’ by F1F9 / Financial Mechanics. It is an ugly, inefficient and error prone practice which is to be avoided.
I was just reading the said article, and in the section “avoid multi-level Linking – daisy chains”.
There is a typo in using the company name F1F9.
It is ‘F1F9′ and not ‘F1F19′.
Can you please update the article for the same.
Thanks Sourav, it has all been updated to F1F9 now. Thanks for spotting this.
Rickard,
Well described as always – as you’d expect nothing in it that I would disagree with, and I think you’ve captured the primary alternatives. On the ‘imports at the top’ Alternative 2, I would also note that I see folks put a summary of exports at the bottom of the source worksheet from time to time, though we feel this has then the weakness of daisy chaining.
Not sure ‘tailored approach’ captures the concept of your recommended alternative No. 3. To the extent you don’t want to broadcast this as the F1F9 or FAST Modeling Standard, understandable, probably more descriptive to refer to it as ‘only have off-sheet references as links placed proximately to formulas, never directly in formulas’ — clearly more than a mouthful : -).
Opportunity to hear from you as well what you think about row-anchoring the off-sheet links (for subsequent copying) and/or what you think about color conventions in this regard?
Hi John,
I agree with your first comment that it is often used in combination with Export Areas but don’t think it adds much value (but an awful lot of unnecessary Excel code!)
One of the strongest features of the philosophy behind FAST modeling standard is the simplicity (check out http://www.fast-modeling.net/) which is to a large extent achieved by a disciplined avoidance (or simplification) of off-sheet links. If you are looking for a financial modelling training course in London then Financial mechanics (http://www.fi-mech.com/) should definitely be on your shortlist. The methodology is pretty similar the financial modelling methodology preached by Navigator Project Finance (http://www.navigatorPF.com) with the main difference that Fi-Mech are more focused on productivity and Navigator are more focused on transaction presentation.
In regards to row-anchoring for subsequent copying, yes, I like it. It is just a shame it looks so bad…. Colour-coding, hm, same argument…. Both methods are great for the developer but will probably not improve the chances of getting an investment-committee across the line…. From a model audit perspective though it opens up a lot of interesting questions, like ‘this line should be blue but is read – is that an ‘error?’
Maybe we should generate some Fi-mech screenshots and put up on Fimodo to provide readers with a better understanding of what I have tried to explain?