The rigour and discipline of a best practice financial model represents a variety of tangible and intangible benefits. It is clear the abundant functionality of a spreadsheet delivers immense flexibility to a financial professional in many pursuits; strategic planning, investor pitchbook presentations, project or investment finance and financial reporting.

All of these disciplines, albeit very different in part, are beneficiaries of a spreadsheet’s rich features and functionality. These various disciplines of financial modelling can leverage the following techniques, which can greatly improve the corporate governance credentials of a best practice financial model.

 

Error and Alert Checks

This should be the first and most important tool which comes to mind. If a financial model’s numbers don’t jive to the source financials or reports different numbers across the spreadsheet, then a company’s ability to direct and control its business will be severely compromised.

Taking the time, either during the development or review of a financial model, to add error and alert checks will greatly increase executive confidence with the financial model for decision-making.
 

Bullet-proofing data inputs

The rich functionality and capabilities of Microsoft Excel mean there are many approaches here.

• Drop-down lists and dependent drop-down lists. A fantastic tool to limit and pre-define input parameters. It helps to guide financial model users with the operation of a model, especially when inputs need to be qualitative and not a number i.e. depreciation method.
• Data validation. Quoting its description in the Excel ribbon, “prevent invalid data from being entered into a cell”, which like a drop-down list will fortify a financial model from incorrect or inappropriate input values.
• Conditional Formatting. Personally, this Excel feature is underrated and underused; mainly because most financial modellers overlook its added value. Instead of using default cell formats, applying conditional formatting will improve and educate model users in the use of a financial model.

Formula Auditing

Although there are some great and powerful commercial, proprietary Excel add-ins, which greatly amplify the existing Excel auditing feature. The existing auditing tools in Excel will critically improve the executive decision making based on financial model projections and analysis. The model developer or reviewer can effectively sense-check formulae using the following:

• Trace precendents and dependents
• Show formulas
• Error checking
• Evaluate formula

Workbook Structure

The information process flow of a best practice financial model must be clear and logical; otherwise executives will be unable to make complete decisions on a model that is not transparent. Avoid over-engineering the model, as it makes it very for users to verify and understand financial outputs and metrics.

 

Table of Contents

Similarly aligned to workbook structure, this will greatly assist executives to operate and understand the financial model fully. The addition of a table of contents or index feature with hyperlinks to other sheets or areas, will greatly improve the operation of a best practice financial model.

Dashboards

Sure, it rehashes and summarises is slick graphs, tables and ratios the detail of the financial model, but it also delivers valuable and intangible benefits to the executive. Its high-level, user-friendly and bite-size nature, will allow executives to rapidly understand the overall financial picture of a corporation for decision making – instead of the time-consuming exercise of digging into the detail of the model.