N° 2 - Four key questions to ask about your monthly reporting model
Recent research highlighted that a staggering 88% of all spreadsheets used by companies almost every day contain mistakes. (Source: Oracle, 2017). The existence of these errors often has significant financial, reputational, operational, and legal repercussions which can go unnoticed for weeks or even months.
The spreadsheet model’s building process is the first instance where such mistakes can be avoided, and where you can limit the likelihood of errors in the future.
At Lazuli Solutions, we implement excel spreadsheet modelling best practice for all clients, and we ask several key questions of our models. Four of these are:
1. Have you defined the objectives of the model?
Without clear objectives, financial spreadsheets used for monthly reporting are a challenge to build, and even harder to navigate once completed. The main reason for this is the organisation and structure of modelling components.
Therefore, before building a spreadsheet model, set out clear objectives. Doing so will provide guidance and direction, facilitate the planning stages and help evaluate the success of the spreadsheet model once completed. In turn, this will help establish the outputs you require to meet the objectives that you set.
Remember: Defining specific outputs leads to successful monthly reporting financial spreadsheet models. Clearly state these outputs before starting the building process to ensure a more streamlined build.
For example, a monthly reporting spreadsheet model could have multiple purposes to (1) create a reporting spreadsheet model with a three to four-year timeline and (2) use the management accounting actuals to seed the forecast. Therefore, your main objective is to create a reporting spreadsheet that’s defined as an actuals plus forecast model.
Excel spreadsheet modelling best practice summary: Determine the main aims of the spreadsheet and keep those at the forefront of specification, design, build, test and deliver stages.
2. Have you formalised the model interfaces?
Before building your model’s interface(s), ensure that you establish a formalised design to ensure that they remain consistent.
What is an interface? An interface joins one system or set of calculations to another through a user-friendly system—and a formalised design will keep those systems consistent.
A lack of formalised interface will lead to a higher number of human errors due to lack consistency.
For example, you may have multiple interfaces—between the accounting system and the model, the financial statements of the model and the reporting key outputs in the model, and between the actuals and the forecast in the model. Errors will creep in when accounts are ignored and forgotten if the interfaces are not formalised through a consistent system.
Excel spreadsheet modelling best practice summary: All interfaces should have a formalised process, method, look and usability, and all team members should agree them. To further reduce the likelihood of errors occurring, the interface should be as simple, effective and transparent as possible.
3. Is the model use process holistic?
How the model is used, called the ‘use process’, is defined by five factors. Keeping in mind how users will be using the model through these factors, you’ll be able to build a model that is easy to use and understand. The factors are:
1. Events—A series of events define the use process. These include:
• Updating actuals
• Making adjustments
• Making prior-period adjustments
• Checking outputs
• Re-forecasting
• Publishing the draft reporting pack
• Publishing the final reporting pack
2. People—It is essential to determine both the users of the model, the users of the results of the model and very importantly, the owner of the model.
3. Timing—Identify how often the model will be used and build a model that is appropriate for the frequency of use.
4. Method—What happens when the model is used. This includes determining the physical location of the files, the naming convention associated with the files, and the audit trail across the various outputs, with the explained reasons behind differences in the various outputs.
5. Outcome—Determine the answers the various users will be looking for and make the use process for finding those answers explicitly clear.
Excel spreadsheet modelling best practice summary: Holistic factors are key to establishing the successful use of the model.
4. Have you honestly evaluated the efficacy of the resulting model?
Once the model has been built, it’s time to evaluate its efficacy in relation to the objectives set out at the start.
We stress that this is a very important question, even if frequently overlooked. Identifying and rectifying these situations results in less risk in the future use of the model.
When assessing your model, ask the following questions:
How long did the project take start to finish? Who was involved?
What is the probability of errors in the final solution (particularly focus on the interfaces of the solution)?
Have you achieved the objectives defined at the start of the project?
Have you communicated, tabulated and archived explanations for changes in outcomes? Are these within the actual model?
Is the use process user friendly and transparent? Could anyone look at the final model and understand it independently from those that built it?
Excel spreadsheet modelling best practice tip: Get an independent third party to test new models. This is best done by someone who wasn’t close to the project and hasn’t been exposed to the model before. The result is a more honest review on the efficacy of the model.
An alternative solution…
If you would like an objective, external assessment conducted on the efficacy of the models you rely on, our Excel Check service is a cost-effective solution.
When we receive your model, we put it through a thorough MOT and error checking service. We give you a list of errors and potential errors and advise on best practice modelling functionality. We also deliver to you a duplicate model that is simple, transparent and reliable.
To book your Excel model check today, call 020 7450 3318 or email dominic.robertson@lazulisolutions.com.
Please note, we sign NDAs and all models will be treated confidentiality.
