Imagine a successful and growing organisation who have used an Excel model to construct their annual budget plan for many years. However, in the past 18 months the business operations have changed significantly. They have opened new branches, seen a change in client mix, and experienced significant growth in revenue. To add to the complex environment for the organisation, the finance team have also had changes in key staff.
The budget plan was submitted for approval by the MD, and a meeting to review the draft submission was convened. The budget plan played a significant role in setting operational and financial goals for the coming year. In fact, the budget plan established the parameters for the whole of the business’ bonus payments.
Attending the meeting was the MD, Planning Consultant, Finance Head, GM, and Operations Manager.
In the first instance the finance head provided an overview of the consolidated key targets and metrics. It took only a few minutes for the MD to question the basis for the EBITDA (earnings before interest, tax, depreciation, and amortisation) target. The operations manager and planning consultant soon highlight the numbers they reviewed were different!
As the team dug a little further, it appeared that each individual in the meeting had a different final EBITDA target and different gross margin percentage.
It quickly materialised that the meeting had been undermined by the Excel planning gremlin called Version Control.
You could see the blood slowly drain from the finance head’s face. The ensuing line of questioning created an uncomfortable situation for the finance team and undermined their standing in the business. Various versions of gross margin meant the EBITDA was created from inconsistent calculations.
The MD and GM were quickly losing confidence in the newly appointed finance head as they struggled to provide answers for the differences in numbers between versions.
The meeting had derailed: the planned constructive meeting to confirm targets, plans, and actions had become an inquisition into the accuracy of the numbers presented.
As it transpired, the Excel spreadsheeted planning process had become over complicated and unwieldly. The Excel planning process was now a liability to the finance team.
Confidence was lost.
The new finance head had inherited the spreadsheeting and was not aware of how and where changes in assumptions or input parameters impacted other sheets and ultimately the final consolidation. The executives relied on the finance team to ensure consistency in process and consolidation. However, over time each business unit had developed their own version of the planning model, creating problems for comparability and consistency.
Error in basis of calculations.
Business units were creating separate spreadsheets to calculate margins and costs outside of the planning model. The business unit manager would then transpose their calculations into the planning model. However, the basis for the calculations made by operations were drastically different, using averages of averages and excluding key overhead cost metrics which the business unit managers were unaware of. The junior finance staff did not challenge the manual calculations made by the business unit managers.
Inconsistency of assumptions.
Calendarisation of costs and business assumptions were inconsistent from business unit to business unit. Costs by month were driven by the working weeks in each month. Some business units had not updated the change in assumptions for the new year, other business units had deleted the calculation all together.
The Excel liability.
In summary, the finance head had a crisis of confidence in the planned numbers. Underpinning this was a web of semi bespoke spreadsheets with calculations that had a varying basis of accuracy. Although the fundamental business drivers were the same, the complexity of multiple contributors, multiple business units, locations, and the changing customer mix had made the use of Excel spreadsheets for planning both problematic and a liability.
The finance head just experienced how utilising Excel in the planning process can suffocate business confidence unnecessarily. Having consistent and confident data for decision-making is central to executing the strategic plan. Business confidence in the finance team can evaporate quickly when Excel is the central planning tool. The challenges faced by the finance head can be eliminated with an Integrated Business Planning and Business Intelligence software.
We continue this topic in our next blog titled 7 signs Excel is a budget planning liability.
