Excel for Budgeting

Overview

During an Excel University webinar, Jeff gave a demonstration of Power Query that related to a company’s annual budget process. Initially, I was not familiar with Power Query but decided to learn it. Ultimately, I was able to incorporate Power Query into our annual budget process, eliminating the need for the software we were using.

Our controller and CFO were thrilled when I gave them a demonstration of what I had built and how it worked. We discontinued the other software, reducing our costs, and were able to distribute a sleek and simple budget template with instructions to our department users that year. In this post, I’ll walk through the journey of building this from start to finish.

Power Query Budgeting Journey

The beginning

Initially, I had no clue what Power Query was or how it could help me. I attended an Excel University webinar where I learned a brief overview of Power Query from Jeff. He gave a demonstration related to a company’s annual budget process, which completely pertained to me in my position.

He described building a process to build a report and so the process, once built, would not have to be repeated each time in order to update the report. This could make the data entry portion in our annual budget process much more efficient.

• Note: archive recording of webinar available for registration here.

Getting budget data from managers

In the example, Jeff taught how to use Power Query to access a folder of budget templates (theoretically sent out to department users for input) and run a series of steps to extract the necessary data only, format it, and then use it a final, clean, deliverable report. I was amazed at how relevant this was for me and decided to reproduce this example for our annual budget process.

Previously I was emailing templates back and forth to each department and manually updating a budget master file. This became quite complicated as there were several different versions of budgets floating around, and I didn’t always know who had seen which version, which made for difficult budget meetings. Whenever I sent out an updated version, I’d receive another update from someone else right afterwards. Clearly the data entry coordination was troubling.

Non-Excel Solution

To eliminate this issue my manager suggested a CPM software that would coordinate our budget process and allow users to access and pull their own reports. Both of us went through a lot of effort to integrate this software with our ERP system and then use in our annual budget process.

At first, it seemed like a great solution. However, we found that this software ended up complicated things even more.

After learning it ourselves, we had to teach our department users how to access and use it to complete workflows assigned to them for budget submission. Unfortunately, there was quite a learning curve, and I realized that this software was not worth the time and effort when users were only accessing it once a year to submit their budgets.

Not to mention – the data entry portion in the software was so slow (it took 30 seconds to enter one number), and I got complaints from each of the users.

I had been thinking to myself, “Man, I miss Excel… There’s got to be a better way to do this in Excel.”

During the webinar, Jeff instantly got me thinking how many ways I could do this in Excel! Jeff built a report using a query to extract data from template files Then, as those template files updated or changed, all I would need to do is click “Refresh All” and the query would run, extracting the updated data from the templates and updating my final report. Boom! Just like that. It was slick. I instantly loved it! So, I set out to do just this.

Excel Solution

First, I created the budget templates to have our departments fill out. I showed prior years’ budget and actual numbers for reference, but locked those cells so they could not be changed (intentionally or unintentionally).

• Note: this post talks about locking cells with worksheet protection

On the far right was a highlighted column for next year’s budget which they were instructed to input.

• Note: this post talks about a couple ways to format locked or unlocked cells

Then, I created another tab (for my use only), which linked only the account names and “new” budget column from the template, since this was the data I would want to extract. I locked cells on this tab as well so it could not be tampered with.

I wanted to password protect the workbooks so anyone couldn’t just hop in and change the numbers, but warning: this does not work with Power Query. Instead, I stored the template files in a specific location on our server and only the relevant department users knew where to access them, which served as a sufficient control for this.

Then I rolled forward our budget masterfile, the final, clean, formatted version that would be PDF’d and ultimately distributed to our Board and other stakeholders.

I added a tab at the end for Power Query, which extracted the relevant data from the folder of budget templates, and displayed it in a table.

• Note: this post shows how to use Power Query to pull data from multiple workbooks and this one shows how to pull values from multiple CSV files.

Then, I used VLOOKUP’s in the masterfile to match the account name in that table and find the corresponding budget number. BOOM! This worked wonderfully.

• Note: to learn about VLOOKUP, check out these VLOOKUP posts

So, as department users entered and updated their budget numbers, they simply needed to save the file. Then, periodically, I would go into my masterfile, refresh all data, which would pull any updates into my PQ tab, and then update the budget masterfile numbers accordingly.

I used various tie-outs along the way and tested the process repeatedly.

• Note: to learn how to set up an Error Check worksheet, check out this article.

One complication was with adding or deleting a budget line item. If a department wished to do so, they were unable to (due to the locked cells). Thus, I instructed them to email me their request, in which I could unlock the cells and add or delete the row accordingly. I then needed to trace this through the process to make sure it flowed through the masterfile appropriately. This served as an extra control to make sure these “structural” budget changes were completed throughout all documents.

Excel Results (lower cost, higher efficiency)

Ultimately, I was able to incorporate Power Query into our annual budget process, eliminating the need for the software we were using. Our controller and CFO were thrilled when I gave them a demonstration of what I had built and how it worked. We discontinued the other software, reducing our costs, and were able to distribute a sleek and simple budget template with instructions to our department users that year.

We received great feedback, and I was so proud of the new process I had built, implemented, and trained on throughout our organization. I can’t thank Jeff and Excel University enough, as Power Query made a huge difference for us.

Additional Resources

Way to go Moss, that is an inspiring story … and congratulations on your success!

Here are some links to learn more about the topics Moss discusses in this post:

• Webinar
• Protection
• Power Query posts
• VLOOKUP posts
• Budgeting with Excel
• Error Check article

If you use Excel for budgeting I’d love to know! Please share which Excel features help with your budget process by posting a comment below … thanks!