Import worksheets will greatly enhance the credibility of your financial model. One of the greatest challenges users face when using, managing or enhancing an existing model is the need to fully understand the data sources and how they interact with the model. It is vital to keep the number of import worksheets to a minimum, as this will enhance the auditability and process flow of the financial model.
Now who likes spending time doing data input?
Import worksheets can help lessen the need to hard-code/data input financial numbers into your financial model, which is not just time consuming but is prone to human error. It will allow financial model users to undertake more fun stuff like value-adding sensitivity or ad-hoc analysis.
Let’s keep this Financial Model relevant
Import worksheets enable users to better manage, improve and safeguard the financial model, even when source financial data changes, is amended or is updated. This guarantees to a certain and reasonable degree, the financial model’s usefulness going forward.
Please madam/sir; keep those Import Worksheets to a Minimum!
There should be a small number of import worksheets. For a consolidated financial model, try to maintain separate import worksheets for each business unit, division or joint venture project. The import worksheet should be sourced as the corresponding export worksheet from one source external financial model.
Like any facet of a financial model, the quantity of import worksheets should be minimised to ensure the financial model is not too busy and hard to navigate around.
Building Flexibility and Added Value into your Financial Model
Incorporating import worksheets give users more flexibility and enable multiple versions of a financial model to be maintained. This is pertinent for sensitivity or scenario analysis, where users can manipulate key metrics of an external business unit, project or asset model (i.e. asset disposals or acquisitions), and then update that in order to gauge the financial impact on the overall corporate financial model by related corporate events.
Please update your source data links
Just because you amended a source file feeds into a financial model, does not mean it will update instantly in your dependent financial model every time.
When updating the data in these import worksheets, remember to always have these externally linked files open in the same application of Excel. Like many of my financial modelling colleagues, I have had the frustrating experience of crashing or freezing my computer, when I have tried to update externally closed links.
Minimise hard-coding, let Import Worksheets shoulder some of the hard work
Building Import worksheets will add a sophisticated and value-adding dynamic into any financial model. It will enable a seamless updating of source financial statements, undertaking sensitivity analyses and better auditing and understanding the mechanics of the financial model.
Excellent write up Simon. I endorse this same approach albeit called “Interface sheets”.