… wave a magic wand and make budget spreadsheet work disappear?
They say a magician
never reveals the secrets of their tricks.
But I'm an accountant, not a member of the Magic Circle.
Far from keeping
secrets about how I wave my magic wand, it's my job to reveal them!
The great disappearing budget spreadsheet trick
Like me, you've probably spent a lot of time working on budget spreadsheets.
If you've been organised, you've crafted input spreadsheets for your whole organisation, setting out the columns for the dates in the new periods and then rows to represent the types of income or cost (with customers, products, departments and account codes). You've then written lots of cell formulae to take subtotals and add it into a master consolidation spreadsheet.
Every year you start out with a great plan to get all these spreadsheet templates completed before you ask your budget holders for submission.
And every year, other priorities intervene. You scrape through, re-hashing the previous year's spreadsheets and tweaking your master consolidation, hoping you've not made too many formula errors.
When you've finished, you have the warm glow of a job completed, based upon maybe three months of hard graft/midnight oil. You resolve to improve "next year", but in your heart you know that the budget will be out of date really quickly (and other accountants agree with you) [link]
You also know you're probably stuck with using spreadsheets for forecasting - you don't have the time or money to implement anything else, even though your organisation needs to update the budget more than once a year.
Yet the thought of building a responsive, flexible forecasting model that you can update more frequently fills you with dread - budgeting once per year is more than enough work and stress!
Do not despair!
What if I told you that I have discovered that most of this budget work is unnecessary, if you plan and structure your spreadsheets differently and use a magic trick that's built into Microsoft Excel?
Not only do have the power to remove a high proportion of your spreadsheet prep work, but you can also make the process more flexible to the changing needs of your organisation. Right now, I'm pretty sure that every organisation needs to be flexible in the wake of the pandemic.
What's the magic word?
How do you use this? How can this save you days of budget work?
Here's a quick video where I show you this magic trick...
Our budget spreadsheets typically have columns for dates/months, and rows for other data attributes. This locks us in to creating huge spreadsheets, where we copy formulae across columns under the Date columns.
UNPIVOT allows you to 'magically' convert these multiple columns into a single column of data, tagged with the date.
Once we have that, we can create simpler formulae, and because we're working in the Excel Query world, we can avoid the need to copy calculations across rows and columns.
If we follow simple structure rules for our budget input spreadsheets we can re-use them time and time again, even if the forecast period changes. Then the process of rolling forward the spreadsheets for the next time you forecast/budget is a matter of minutes, not days.
I understand you might still be unsure as to how you make this work. That's okay. It took me some time to get my head around this and use it and create flexible, working forecast models.
Now I used these models for real organisations, I wouldn’t ever go back.
I encourage you to play with the UNPIVOT function to learn how it can help you.
Can I help you further?
Frequently when I speak with finance professionals they want to know more about how they use Query Tools / UNPIVOT themselves.
InfoSuperstar Accelerator Programme
I'm therefore currently working with a small number of clients on a virtual coaching basis to help them learn the skills they need to leverage these time gains. This Accelerator programme
includes "Bootcamp" workshops to learn tools like Unpivot rapidly, together with monthly on-demand coaching and support to make sure that the learning is applied and beneficial Excel solutions are created.
Whilst last time I checked there were still only 168 hours in every week, but I do still have some time availability, so if you'd like a no-obligation chat about my Accelerator programme ( More details here
), you can book a meeting into my diary here.
"Build a better, flexible Excel forecast model" course
I'm also planning to launch a course , with a couple of format options (self-guided or coached). This will teach application principles and include a blank spreadsheet template for participants to adapt, saving DAYS of work.
Again, with no commitment, could you let me know if this would be of interest to you? I'll create the materials and run the course when I get enough interest.
Until next time,
Adios Amigos!
Andy
