The challenge of managing your Company’s legacy spreadsheets

The time spent updating and managing legacy financial spreadsheets

You Company might be faced with the continuous amendments and revisions of its financial models. Often there is a need to completely re-engineer the model from scratch, which is not only time-consuming but is susceptible to material error.

Legacy financial model challenges

Even if your Company can continue using the financial model, the model layout is maybe inconsistent, previous model users have hard-coded numbers which renders the model not audit-friendly, or the model appears to source external file data sporadically across the workbook. A financial model with such ad-hoc external cell referencing and hard-coding would look similar to the following.

The model may lack simple navigational attributes such as the incorporation of a table of contents, customised hyperlinks i.e. the ability to move back and forth from the “Revenue Assumptions” and the “Revenue Output” worksheet, or an “Error Check” worksheet to document model errors across the financial model.

Even the lack of simple aesthetics such as the manner in which the balance sheet is presented, or the employment of differing sized heading titles across the financial model, can help to either hinder or assist a model user from managing a financial model. Hard-coding of numbers should be restricted to assumptions worksheets, as it makes it hard for future model users to understand the mechanics or source of any hard-coded numbers. The following screen shot represents an assumptions worksheet, where model developers can hard-code model specific details such as business unit name, facilities etc.

The advantages of a best-practice financial model

Your Company’s financial model users will achieve immense time savings and alleviate much financial model error, through the use of consistent, best-practice and robust financial modelling. It will allow your Company’s financial model developers to pass the operation of the model over to applicable users, as it will be more auditable, concise and user-friendly to amend and update.

A best-practice financial model will be able to accommodate future changes to the company. There will be adequate provision for future financial model users to add newly acquired or created products, business units or assets.

Additionally the model will present a concise user guide for model users, including screen shots and hyperlinks to the appropriate worksheets, which will better inform model users of the mechanics of the financial model.

The value of a financial model to your Company

The Model will be the centralised repository of your Company’s actual, budget and forecasted financial numbers, which executives and other stakeholders can access and view in a concise manner. The likelihood of material error and misstatement will be greatly reduced, and your Company’s financial model will also be more auditable, because external file links are confined to one area of the model. The seamless nature of the financial model to update, will free-up time and instead allow your Company to undertake value-adding analysis such as sensitivity/scenario analysis, driver analysis and executive summaries.