In a previous blog, we discussed just why Excel is now a liability to your budget, planning, and management reporting process, including providing an example to illustrate this fact.
As a continuation from that blog, we discuss the seven signs of this to help you identify the problem areas.
Planning in Excel can be troublesome as your organisation grows. Each planning process is made up of input forms. The scaling challenge in Excel can manifest itself in many ways.
- Incorporating new business units.
- Referencing central assumptions.
- Additional new contributors.
- Additional new business driver calculations.
Depending on the design of your Excel workbook, adding new staff roles, assets, products, business drivers, or updating calculation macros can be an ever-increasing challenge involving updating each input form.
Doubly, as the number of contributors grow, the forms for manual updates and the corresponding workbook checks grow. In effect, the time required by the finance team to prepare for the planning process can compound exponentially. With each new business driver and each new data contributor, the resources of the finance team are stretched. Scalability of the Excel planning process becomes problematic.
Each planning cycle requires rolling over data and prepopulating fields. A particular example is the quarterly forecast and the update of actuals. In Excel the exercise is fraught. The potential for data errors, misalignment of costs, and unreconcilable mismatches is ever present. The data structure for each source of prepopulated data may have changed. New GL accounts, customers, and products are just a few examples. The more contributors you have, the more versions you have to manage; the greater the challenge; the more time required for spreadsheet maintenance; the higher the chance of errors; and the higher the chance for difficult internal conversations.
The planning process often involves incorporating data from disparate sources. The data may be from an ERP, CRM, HR system, or other business system. Manual integration processes may require double or triple handling or a complex bespoke macro. Manual integration of data is also error prone. Even with checks and balances, data quality is at risk. The greater the number of integration sources the greater the risk of errors.
4. Data integrity – consolidation and consistency
A fundamental pillar of the management reporting process is the presentation of decision-making data. However, Excel by its malleable nature opens the planning process to data integrity issues. Calculation errors may occur at various levels of consolidation. Contributors may choose to work outside of the tool and hard key numbers, which can then impact the consistency of calculations. There may be assumptions or formulae errors from one worksheet to another without the knowledge of the user or the consolidator. It could be as simple as different FX rates used in some Excel workbooks. There may be no flags for errors and no warning that the check process is not working. In a complex model with many contributors, small errors at the user level may lead to material differences when consolidated. The manual consolidation process itself may further compound errors, creating hours of reconciliation or debugging work. With Excel the risk of compromised data integrity is high.
Managing the budgeting and forecasting process with multiple contributors can be a challenging time management exercise. When contributors do not report to finance, or have the planning system as a key accountability, managing the timely completion of the planning cycle becomes increasingly challenging. Excel does not provide transparency on the progress of contributors. Precious time can be wasted chasing submissions and herding the contributors to deliver the planning outcomes on time and in line with expectations. Version control in Excel haunts the finance team at every turn. Not only is it likely the contributor will alter the budgeting template, each contributor will have a number of “final” versions. The finance team is now lumbered with tracking an inconsistent number of versions from each contributor. They become responsible for ensuring the “correct” final version is used in the consolidation process.
6. Automated reporting and business intelligence
Reporting in Excel can be disjointed. In a scenario where the final budget numbers are loaded into another system, the calculations, assumptions, and operational KPIs reside in Excel and may not be referenceable. Where actuals are loaded into Excel, GL mapping checks are required. Where the mapping fails, hours of precious time can be wasted searching for that needle in the haystack. Reporting drill downs are limited to the design of the Excel workbook. Slice and dice analysis of data is not automated and therefore requires additional purpose built add on spreadsheets. Data rich, drill down business intelligence may not be an option in Excel, with the graphical representation of data manual and ad-hoc. As a result, decision making confidence is not optimised, and data integrity is a constant shadow that stalks the Excel reporting process. There are no inherent advantages to presenting and communicating results in Excel. All the advantages of contemporary business intelligence and predictive analytics is unavailable in the Excel reporting environment.
Managing a complex planning process in Excel is akin to lighting an open camp fire in a dry forest; many embers are open to the blowing wind. Each input point in a complex planning model is an “ember” and is a potential source of consternation. The greater the number of factors involved in the driver based, accountable planning process in Excel, the greater the challenges that face the team managing the process. The time consuming challenges of budget planning in Excel are multifaceted and not limited to:
- Inconsistent and manual calculation of key budget drivers.
- Lack of transparency in commentary, and low interdepartmental collaboration.
- Troublesome version control and contributor workflow.
- Changing assumptions, automating rollovers and consolidations which can lead to unidentified errors.
All of these factors contribute to late nights, tense planning conversations, and low business confidence in the budget process.
What’s missing in Excel is the built-in gyroscope constantly calibrating the direction of contributors.
Any one of the factors above will be a red flag to your Excel planning process, and a liability to your organisation. To avoid being red faced in your planning meeting, consider the advantages of Integrated Business Planning (IBP) software.
An IBP asset allows for you to define the workflow; provide a consistent basis for a driver-based planning model; single click rollover process; integrate and align your KPI’s; create confidence in data integrity; and enhance decision making, all with data rich graphical business intelligence and predictive analytics.
Build your business partnering reputation within your organisation. Talk to Professional Advantage today about how you can make this a reality for your organisation.