If you’re like us and have had limited investment in the budgeting process, you’re probably using excel to produce your budgets. We now have over 300 cost centres and each cost centre manager owns and produces their budgets, making consolidation a nightmare!
Up until recently, we used macros and vba to manage the collation of budgets, with a macro saving down completed budgets to a shared drive on the network. This worked fine for a number of years, but combining was starting to become a time consuming issue. Combined with the new security issues with requiring excel documents needing certificates for macros, something had to change.
During covid the company invested in moving over to Office365, but we’ve been running a dual model with shared drives and some SharePoint usage for a number of years. The company hasn’t yet invested in any resource for capitalising on SharePoint or the Office365 stack, so we started researching how we could leverage this powerful tool into improving the budgeting process for both our users and ourselves.
SharePoint & Document Libraries
We created a dedicated SharePoint site for budgeting. This gave us the scope for communicating to our large user base (over 300 managers!) and a simpler combination process, using our favourite new tool, Power Query.
Within this SharePoint site, we created a document library, and folders that had unique permissions – improving security in the process. Now our managers could access the document library and only see the documents they need. Saving the countless emails to a fro.
To start with we had an awful lot of push back, as there was a fear of adopting SharePoint as a concept around the business. We ran a large campaign with training sessions around the business, to put our users at ease with the updated process. After all, the budget templates remained largely unchanged, it was only how they accessed the documents that had slightly updated.
Consolidation & Reporting
By moving to SharePoint for budgeting in specific folders, we now had access to all budget templates throughout the budgeting process, which was a stepped change from our old process, which meant we could now do interim reporting. But how?
Power Query has a neat tool for combining excel documents.
In the Get Data menu, select from file and then from SharePoint folder.
You can then enter your budget SharePoint site, and select/ filter for the files required.
This gave us the ability to have a budget snapshot at any time. Under the previous process, we would need managers to submit their files using the vba process, adding time to check all documents had been submitted. Now we could access these files at any given time throughout the process.
We created a suite of summary reporting, allowing us to check on budget process for all of our 300+ cost centres. The added benefit was also we could share these reports with our area managers, when they refreshed the reports they would get a consolidated view of the budget files they had access too. Saving a huge amount of time for reporting as these managers now have access to these reports on a real-time basis throughout the budget process.
Leave a Reply