Budgeting with Pivot Tables #3. Reporting Actual vs Budget
Continuing his series of tutorials on using pivot tables with budgets, David Carter now produces the monthly reports comparing actual to budget, Month and Year to Date.
In tutorial #1 we assembled budgets for the London, Birmingham and Edinburgh regions. We transposed them all into the Combined worksheet of the Budget-tut-master.xls workbook, then used pivot tables to create a consolidated company budget.
Continued...
The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.
Registration is FREE and allows you to view all content, ask questions, comment and much more.
Or if you are already registered, login here
Great stuff
Great stuff David. Even after several years of pivot table/ lookups etc usage there were still new things I've learnt.
Unable to download spreadsheets
Hi, There are no files when i follow the links, can you help?
Thanks,
Ross
What about the blank rows that appear...
This has been really useful - I knew there must be a way to do this with pivot tables, but I was struggling to work out how exactly to do it. My problem now though is that our company is arranged by department, and the line items included in the different departments are different. However, when I add a calculated item, it puts every single line item under every department. For a really simple demonstrative example, before I add the calculated item I might have the following:
Finance department
Tax consultancy
Audit fees
General administration
Stationery
However, when I add the calculated item, my data ends up looking like:
Finance department
Tax consultancy
Audit fees
Stationery
General administration
Tax consultancy
Audit fees
Stationery
And the lines which have appeared which were not previously there are still blank for budget and actual, but show a variance of 0.
Is there a way to hide the lines with no data? I have searched extensively and can find people with the same problem, but not a solution.
Worked it out
Forget it, I've FINALLY figured it out. I needed a calculated field instead of a calculated item. Thanks anyway!
This is good stuff but....
Reading your tutorials have enlightened me to a few things I never knew as well, and I have been using Excel for a number of years. I especially liked your Sage and MS Query tutorials, as I like the way it automatically updates the data input into Sage, and can therefore save several hours work at month end (or indeed thorughout the month) importing the same data time after time. All this depsite Sage's limitations. However, how can I use the budgeting part that you have described here, in the same pivot table created by the afforementioned Sage/MS Query tutorial? Then you have a dynamic P&L, that incorpates the budget all in one encompassing spreadsheet. Is this possible?