Uncovering the Hidden Costs of Forecasting in Excel

Edgar de Wit

---

Every organization does budgeting, and often Excel is the most obvious option. Every finance professional knows Excel, a spreadsheet can be quickly created, and you can freely adjust it as desired. And in most cases Excel is pre-installed and free because it is included in your MS Office subscription.

But is Excel really free? This article explains which activities are involved in budgeting and forecasting and how much time is required.

Manual Data Entry and Errors

Ideally, you want to feed lots of data into your budget model. For example, accounting data, but also your sales numbers, personnel costs, expenses, projects etc. Your budget spreadsheet also needs to represent the structure of your organization, your chart of accounts, your departments, business units etc. All that data must be entered, modelled, and maintained in the spreadsheet, and that takes a lot of time. You must also update and reconcile the data every month, which becomes more cumbersome if your organization consists of multiple business units.

You then have to create and verify all formulas. This part needs to be done carefully, because small errors can cause big differences in outcome. Once your spreadsheet has been filled in and all formulas tested, you have to format it. It is best to do this last to avoid doing double work when you change your structure.

Updating your monthly numbers and maintaining your organizational structure will require a lot of time on an ongoing basis.

Our research shows that finance professionals spend an average of 24 hours per business unit on updating, checking and entering data.

Collaboration and Version Control

Excel offers collaboration, and are tracking all changes, which makes it easy to work with multiple people on the same file. You can see what has changed and by whom at the cell level. But file-based collaboration is only possible if all collaborators can view and edit everything in that file. And that scenario often does not apply to a budget cycle. For example, a director from one department may need to see the marketing costs across the company. But a sales director may not need to see the operating expenses of other departments.

Another point is version control. Tracking changes is not the same as version management. It's great you can see the last change, but you need to know which version has been sent to the board, for example. This means you have to archive a versioned copy of the file. We often see file names such as "Final budget version 2.1.4 Board". Something like this must be tediously recorded; otherwise, you will quickly get confused ("Did we send version 2.1.4 to the board or version 2.2?").

Our research also shows that finance professionals spend an average of 10 hours per business unit on version management and collaboration.

Automation and Scalability

Spreadsheets are good for ad-hoc analyses and calculations. You can put your assumptions and data in your spreadsheet. But without programming, add-ins, and power queries, spreadsheets are poor at automating repetitive tasks. For example, importing trial balance data must be done at least once a month. Accounting software does have export options for this, but that is often per company. You must also continuously update the information about cost centres and departments in your business.

You must also export every month and every company. This action is repetitive, dull, and error-prone. You'll need to check every step. Using Excel for your budgeting and forecasting seems tempting at first, but in the long run it is a disaster in terms of inefficiency.

A budget spreadsheet with all logic and data is still manageable. It will be a disaster if you want to allow all your budget holders to create their own budget. This turns your Finance department into a postal company: they'll need to send out template files and then collect them again, to merge into one master spreadsheet. Over and over again.

We see that finance professionals spend an average of 3 hours per business unit on reviewing, making adjustments, finalizing and documenting their spreadsheets.

Maintenance and Update Costs

A spreadsheet needs continuous maintenance. Adding new data, adjusting the organizational structure, and setting up reports in accordance with the latest requirements are actions that you always have to do as a finance professional. Your Finance department needs to spend hours analyzing the figures. Outdated or incomplete spreadsheets have another risk: you may no longer trust it and have to start over.

Total Cost of Ownership (TCO) between Excel and XLReporting

We have calculated the above in hours and costs. We have divided the budgeting and forecasting process into 10 essential steps. We looked at who is typically involved: the controller or the CFO. The hours are averages based on many conversations with controllers and CFOs and our own experiences.

The 10 typical steps in a budget are:

1. Data Gathering and Input
2. Formula Setup and Validation
3. Formatting and Layout
4. Version Control and Collaboration
5. Review and Adjustment
6. Finalization and Documentation
7. Testing and Debugging
8. Presentation and Preparation
9. Training and Familiarization
10. Documentation and Archiving

We have calculated the time-spent in 2 different scenarios:

Scenario A: Budgeting with 1 business unit

Excel

You spend quite some time per year with budgeting in Excel. Approximately 100 hours and €15.250 salary cost. Most of the time is spent importing the data and tediously documenting and archiving the files.

XLReporting

How does that compare to a tool specifically aimed at budgeting? XLReporting is a solution for this, and we know how much time people spend during their budgeting process.

Because XLReporting connects with your accounting system, time is well-spent. Documentation and version management are done automatically within XLReporting. Of course, you must learn how to use a new tool, which takes some time. But even with 1 business, you'll save 66 hours which you can use to improve your processes.

Scenario B: Budgeting with 10 business units

Excel

The number of hours you spend on spreadsheets increases if you have to work with more business units. In particular, the collection and input of data is increasing enormously. You then quickly spend 388 hours per year on budgeting, or more than €45,000 in wage costs.

XLReporting

The benefit increases as your organization is growing. As an example, with 10 business units, you'll save at least 5 working weeks per year when you start working with XLReporting instead of Excel!

Excel is time-consuming and therefore not cost-effective if you want to use it for recurring processes such as budgeting and forecasting. You really need a better solution for that.

Please contact us if you want to know how much your Excel solution costs. Our experts can calculate this for you and advise you on how to save.

← Back to home