Excel has often been the technology of choice for budgeting and planning models for a range of organisations in various industries. In the mining industry, this is no different.
Further, many organisations have fallen into the trap of using Excel spreadsheets that were intended for short-term use as their ongoing budgeting models. Over time, these spreadsheets take a life of their own, with many alterations to calculations and formulas as the organisation changes and drivers of income and costs are better understood.
There is no doubt Excel serves an important purpose in the planning process, but when modelling Life of Mine (LOM) plans there are unique challenges that are often encountered.
Below are 10 reasons why LOM plans are particularly challenging in Excel and some of their consequences.
1. Prone to error
Studies have found that 88% of all spreadsheets have errors (Panko, 2008) and a study by Powell, Lawson and Baker in 2007 found a single error had an impact of over $100 million! These errors are not just embarrassing for the finance department, but also cause management to make decisions based on inaccurate information.
The rate and significance of errors in Excel models is even more apparent in LOM plans due to their life span and level of detail. Often, LOM plans can go over 20 or more financial years with hundreds of drivers which are calculated using Excel formulas which magnifies the risk of error.
2. Difficult to maintain and update formulas
As mentioned above, LOM plans can have many drivers and calculations which might be manageable if they are set in stone. However, it’s very uncommon that some of these won’t change over time as the operation evolves and moves through the mine life cycle.
Excel doesn’t store formulas in a central location, making it extremely difficult to ensure all dependent formulas and calculations are updated as a result of business changes. Some of the simplest changes, such as updating an account mapping, can become arduous.
3. Poor performance and reliability
One of the most common issues with using Excel with budgeting and planning is the performance and reliability. As these models are shared amongst teams they will often be saved on a ‘network’ or ‘shared’ drive, meaning multiple people are accessing the same file at once. This results in Excel crashing or performing very slowly. This issue is even more present when dealing with large and sophisticated models such as LOM plans.
4. Time consuming to update and consolidate data
Although it is relatively quick to build Excel models, updating drivers and rates and consolidating data each month can be very time consuming. Combined with updating actuals for re-forecasting, much of the cycle time is spent updating or integrating data from source systems, rather than analysing and making sense of the outputs.
It is usually the task of the finance team to make the updates and consolidate site-based plans which restricts these highly skilled personnel from more value adding activities. Moreover, this manual consolidation further increases the risk of error and mistakes.
5. Disconnected from Balance Sheet and Cash Flow budgets
Due to the performance and functionality limitations of Excel, organisations are forced to disconnect Balance Sheet and Cash Flow budget models from the underlying LOM plans that feed much of the future balance sheet movements and expected cash inflows and outflows.
In an ideal world, any changes to underlying models should automatically be reflected throughout all dependant models (ie, Cash Flow) to ensure broad-based data integrity and accuracy.
6. Lack of integration with reporting systems
Effective budgeting systems are ones where Cost Centre and Account structures are replicated from reporting systems. This allows for easy integration between systems, while making analysis meaningful by comparing apples with apples.
Due to the limitations in Excel, the structures and hierarchies used may not correlate to those used in management and statutory reporting, further making the reporting cycle time consuming and inefficient.
7. Inability to collaborate concurrently
Excel doesn’t neatly allow multiple users to update a workbook at the same time, which can lead to users creating their own versions or wasting time trying to co-ordinate access when deadlines are looming. Changes become hard to track and finance teams or management don’t have real-time visibility of updates throughout the cycle.
8. Limited What-If capability
While Excel allows you to do basic What-if modelling, one major limitation is the ease of comparing multiple scenarios. If you are like other organisations, when you are ready to model another scenario, you will hit the ‘save-as’ button in Excel and create another workbook file.
While this preserves the previous scenario, it is very difficult to easily compare what has changed between scenarios, especially when you have many iterations. Consequently, this limits the ability to do meaningful What-if analysis by contrasting different scenarios.
9. Lack of control
Despite Excel having protect sheet/workbook features, these are inferior in comparison to the functionality of a dedicated budgeting and planning system. The lack of control will often lead to management or corporate overriding assumptions or drivers made by the site.
The result is that the mine site becomes disengaged and disconnected from the forecast numbers, reducing accountability and ownership as the question arises over who changed the assumptions rather than why the performance is not in line with expectations.
10. Lack of traceability and auditability
Finally, one of the pitfalls of using Excel for LOM plans is the lack of traceability and auditability.
From a traceability perspective, although Excel provides formula auditing, this feature is less effective when formulas reference other formulas or even references an external file. Many hours are wasted tracing through formulas to find the underlying driver, or worse, people override the formula in the output or report to suit the situation, which causes downstream issues.
Regarding auditability, Excel doesn’t have a built-in audit log, making it almost impossible to have complete oversight of changes made, when they were made and by who. This further discourages accountability as the administrators are unable to pinpoint the reason for mistakes or inaccurate data entry.
Benefits and features of a fit-for-purpose Budgeting and Planning solution
Excel serves an important role in every organisation as a tool for modelling and analysis. But when the model becomes a critical system for the planning process, it’s time to consider a dedicated fit-for-purpose Budgeting and Planning system that address the challenges and limitations of Excel.
High performing organisations understand the limits of using Excel, and when it is time to leverage Budgeting and Planning solutions to make planning, reporting and analysis a lot easier and more accurate.
Some of the benefits and features of a fit-for-purpose Budgeting and Planning solution are:
- Integrate seamlessly with source systems (ERP, dedicated mining solutions, payroll and HR systems) to reduce data entry and improve accuracy
- Leverage the same system and models for LOM, Budget and Operational plans, while integrating with 3-way Profit and Loss, Balance Sheet and Cash Flow models.
- Inherent Consolidation of Entities, Cost Centres and Accounts
- Ability to store and calculate millions of cells in real-time
- Manage Master Data (Dimensions/Structures, Entities, Cost Centres, Accounts) and formulas in a central place for easy maintenance, data and calculation integrity and auditing
- Many users can collaborate concurrently with Workflow capability for control and visibility
- Manage Versions and Scenarios using dedicated functionality with full audit trial and history
- Built-in What-if capability with the ability to analyse, compare and calculate variances between versions
With the right solution, you can free up and empower your finance team to focus more on the value-add activities to partner with the business, and at the same time allow the wider business to be more accountable against their plans.
Learn how a leading mining company extracted value and reduced risk with the right Budgeting and Planning technology. Read IGO’s case study here.
Interested in exploring fit-for-purpose Budgeting and Planning tools for your organisation?
At QMetrix our consultants are finance professionals, with a majority holding professional accounting qualifications (CA/CPA). We have successfully implemented solutions for mining companies such as Independence Group NL (IGO) and AngloGold Ashanti, and can make a recommendation for a Budgeting and Planning tool that suits your unique business. Let’s chat about your needs and challenges, and how we might be able to help.
