Everyday Excel users tend to forget that the old calculation software is in fact a highly versatile, self-sufficient financial communications tool able to generate on its own the most engaging and robust insights into a complex business plan, commercial data analysis, budget or valuation exercise. This is of course dependent on the user’s ability to master Excel functionalities and to systematically implement best modelling and presentation practices.
While mastering Excel formulae and functionalities requires comprehensive training, Excel’s best modelling practices are no less important and can be broadly summarised as follows:
- Give your model a proper structure : as soon as you reach a certain level of complexity in the analysis, making a clear distinction between “input”, “calculation” sheets and “results” worksheets is indispensable. Each sheet should then be dedicated to one of these three potential uses, to the exclusion of any other (i.e. no inputs in calculation sheets and vice-versa). To crown it all, you can make navigation between sheets easier by setting up a “summary” sheet with internal hypertext links.
- Among other consequences, this implies to tightly frame modifiable input data, not only by colour-coding it and putting it exclusively on dedicated “input” sheets but also by consistently using validation lists and preventing any other changes (by protecting calculation and results sheets with a password for instance).
- You should eliminate all circular references (a must!) and whenever possible, external links and “copy and paste” macros. Although macros have their uses, especially as part of large, recurring and consistent external data import procedures, one can certainly do without them as long as a comprehensive Excel model containing all its source data is transferable by email in compressed (*.ZIP) format. Never forget that if source data files change places or are not available offline, macros will be broken and it will generate errors and make the whole model not only unusable but also a source of immense frustration.
- You should use internal links extensively. This implies that a calculation formula should call all required inputs from input sheets with no harcoding in it (except 0 or 1). Besides, all calculation should only be performed once and called as many times as needed in other calculation or results sheets.
- Last but not least, you should strive to make your results sheet engaging and comprehensive (without becoming confusing). You should be able to test your scenarios from there (through a list) as well as the main hypotheses of the model, whether binary – “Yes or No” – or numerical. Results should be updated automatically as soon as these tests are performed, be interpretable with no particular effort or no knowledge by your client and be presented in a visually attractive way abiding with your organisation’s visual identity. This latter point will help you gain time by allowing you to import your results tables and graphs directly into your presentation documents (Word reports or Powerpoint slide decks) with minimal effort.