Financial modeling best practices

The landscape for in-house finance teams and practice accountants is evolving, placing an increasing emphasis on frequent modeling.

A financial model forecasts a business’s financial performance into the future and is typically based on the company’s historical performance and assumptions. There are many types of financial models but in this blog we focus on models typically used to make decisions about budgeting and forecasting and month-end consolidation.

A recent McKinsey survey of global business leaders and middle managers found 86% need to forecast faster for greater insight, and 65% said they are hampered by incomplete data.  On average, 61% said most of their decision-making time is used ineffectively leading to lower productivity, lack of certainty, and frustration.

These survey results highlight the key challenge for people building and maintaining their models which is incomplete data.

As business advisors, we see many models and spend a lot of time trying to understand them for implementation. These financial models include monthly consolidation, 3-way forecasting and ad hoc forecasting like salary forecasting or headcount planning.

Many of the recommendations and considerations in this blog come from our observations from creating, using, formatting or reviewing many different models.  Some models are harder to understand than others because customers have reached a tipping point of size, or complexity and are looking for bespoke software to help them better reflect their business planning.

Data sources and updates

Why are we interested in data? We want to use live  financial data to help create our models and to inform our decisions.  Often historical data is used to model actual results which in turn are used to predict or forecast the future.

The more detail  in your data and financial model, the more you will benefit from upfront planning, standardization and structure around your model.

If we are using Excel worksheets a lot of the time spent is in the data collation phase. This can be complex and time consuming especially when combining data from different sources with varying levels of detail.

One of our suggestions with models, regardless of where it’s created is to enhance the supporting documentation.  Often accountants are time poor so don’t have the time to write everything down which limits the documentation for models. However the benefit of including high-level details can improve the structure of the model. The notes serve as a helpful guide for others to understand and step into the project. For those who have created the model, the notes can also be a useful refresher, particularly when dealing with a complex model that hasn't been revisited for a while.

You also need to consider where your data is coming from such as your ERP, data warehouse or other sources?  Best practice is to identify your data input section and if these are tabs in a spreadsheet, color coding data input tabs consistently across all your models helps speed up navigation.

It's best to consider how frequently the data sources and the model templates are refreshed . If the updates are a manual process, what's the cadence and would it be worthwhile to have a schedule for the data updates? For example, in a consolidation model you most likely want the model to be dynamically updating while you are going through the month-end close process and then the historical months to remain static once the books are closed.

If the data is coming from multiple sources,  how is it being kept in sync?  What controls are in place to reconcile the data back to the source? If using an Excel model, it is wise to set-up a few key reconciliations for each model and error checks.

If your data automatically updates via a data platform or data warehouse, someone in the finance team should regularly check the feed is accurate because multiple reports and models will be relying on this information. It is also a good idea to have a main contact for any data  issues experienced, this can be an internal team member or someone external.

Data security is crucial for various reasons, including the need for protection due to regulatory requirements, the sensitivity of commercial information, and the inclusion of payroll data. Perhaps your financial model or sections of your model need a password and a process for managing these passwords. Consider how to protect the data you’re creating within the model and the best way to manage version control and back-ups. You should test recover from time to time (when you don’t need it).

An area that is often overlooked is the data limits of the system being using and when is the appropriate time to break models into chunks or sections.  For example, Excel has a limit of just over a million rows, 1,048,576 and Google sheets has a limit of 40,000 rows. Other systems may have limits or performance issues to consider when models are large. When finance teams split budget and forecasting models up, this can also lead to challenges around rolling those models up and keeping them in sync so data should be an ongoing consideration - the life line to an effective model.

Collaboration

The purpose of the financial model is to get business unit experts to contribute to the planning as well as share the results.

Before you start building your model, you need to consider what is the purpose and who is the financial model helping?

It is important to collaborate with the users of the information throughout the model process. We recommend checking in with business unit experts  from the outset so you can outline the process used to create the model and what the ultimate output will be.  This will inform your scope and capture some additional questions from these subject matter experts that  hadn’t been considered.

Early consultation helps provides a holistic structure for the model rather than one with many iterations and additional tabs.  The working model also needs to be shared with the finance team as well as with your business unit experts. Often the act of explaining the model to another person (training someone) can lead to good insights as to what can be improved to make the model more clear or obvious to others.

The understanding of the model across finance at the beginning of the process helps build trust and you have back-up experts for support when required. Fresh eyes help validate your model. How often have you found yourself reviewing a model with someone, only for them to identify a glaring issue that went unnoticed because you were too immersed in the details?

Consider how to present the financial model as not all end users will want to view the data as tables or sheets of data. It is worthwhile to include charts and graphs so it’s easier to spot trends and identify outliers in the data.

One of the challenges with sharing the entire model widely can be managing people’s access so it is best to include in the documentation how the output is shared and who can edit.  Make sure the owner of the model is noted and document the people who have access.

Resourcing

Given that all organizations have limited resources, it is essential to identify the specific people and tools required to develop and sustain your financial models.Resourcing includes the time it will take to prepare and update a model and how it fits in with the overall workflow and timetable in your organization.

Some models like the annual budgeting process take a lot of time to plan, prepare and review and require a lot of internal resources from many teams in the organisation. There are also several cycles of review and approval that need to be considered. Often the review process is what gets squeezed the most in timetables.

The same situation may arise during month-end consolidation. Identifying the time required for preparing the month-end close and pinpointing bottlenecks can aid in devising strategies to speed up the process.

Does your team have the capacity for ad-hoc analysis and capturing what some of this “one off analysis” is as it might be more routine that initially realized and be worth investing in a more sustainable model or reporting system.

Time and people are closely linked but people have different skills. Who in the finance team has modelling skills already and who needs further development with new budget and forecast software?

Keeping the lid on complexity

The challenge is finding the right balance between too much complexity and not enough in your financial models. Just because someone can build it, or it is fun to build doesn’t mean the model needs a macro.

It is important to always consider your audience and the purpose of the model and whether it is a one-off or a recurring model request.

Some simple things can help reduce complexity (at least for others) such as using clear names where possible rather than acronyms. It is also useful to consider labelling values because it can be easier to review when referring to inputs rather than a cell reference.

Many FP&A tools like Phocas use names rather than cell co-ordinates in the complex formulas. Use of the grouping feature in Excel can help structure the spreadsheet into sections – this acts a bit like a table of contents when collapsed. The cell comments also help others or yourself when it comes to reviewing or updating the model at a subsequent date.

Your business may adopt a consistent approach, such as using color coding, to easily differentiate between data input, assumptions, and outputs. Separating the assumptions tabs that drive the model also allows for easier scenario modelling. Where possible — think simplicity over complexity —and consider how you could train someone else on the model and can someone else fix it if it breaks.

Sometimes complexity is necessary but having documentation will help with updates and succession planning.

Automating financial models

Let’s review the most common financial model: month-end consolidation. This usually involves bringing separate entities together to consolidate in a single view usually for a month end or board pack.

Month-end can be time-consuming, especially as reports become outdated or the audience's needs evolve. Additionally, there's nothing more frustrating than completing your consolidation, only to discover that a few numbers are incorrect, requiring a rerun of the entire process.

Some of the key advantages of having this information in a dedicated business planning and analytics platform means integration of the information is automatic and dynamic.

Check Actuals against Budget over time in the Profit and Loss (Income Statement)  are simply clicks of the button. You can also present the data back to the users in many ways. Alternatively, you can choose to view specific sections of the information, allowing you to focus on one or several entities, such as companies, branches, departments, or cost centers.

Another key advantage of having this information in the same system is your analysis and financials are all in one place.

Forecasting models in a business planning and analytics platform

More finance teams want to carry out 3-way forecasting. It is easily achievable when building a budget model in Phocas by connecting with Financial Statements.
Mini drivers are an easy way to represent the links between your statements when you use 3-way forecasting. You use the mini drivers to forecast or budget your Balance Sheet items, so your Cash Flow Statement better reflects reality.

Mini drivers can be used to make many changes to your balance sheet and three out-of-the-box templates cover common scenarios for debtors, creditors and stock in Phocas. These templates have pre-built lines with basic inputs for a calculation. They allow you to quickly model common interactions between your Profit and Loss (Income Statements) and Balance Sheet items without having to manually enter formulas.

Salary forecasting or headcount planning

You can also forecast salaries by integrating your HRIS data and by managing the drivers of payroll tax and superannuation which is a huge difference when completing this task in Excel which you need to add separately. The platform manages all the intricate data matching, has privacy settings and can be set to forecast to a granular level. So you can plan for every staff member currently working and starting during the financial year.

The power of visualization for action and understanding

When we refer to data driven models we are not just talking about integrating and automating this information together, that’s the easy part in a business planning and analytics platform. Finance teams need to present the financial models to all the users of this financial information so they can get the insights and answers they need.

Dashboards are an excellent way to share and explain results with a team. They make it easier to share analysis with colleagues for greater collaboration and impact.

Sparklines are a clear and powerful way to review a model in progress and can show the difference between actual and forecast numbers and making sure there isn’t a big gap.

The effectiveness of visualization to aid collaboration and comprehension is highlighted in the context of data-driven models, helping more people be connected to the strategy and be aware of ongoing results.