Top-Down Bottom-Up Budgeting with PowerExcel

by | Sep 25, 2023 | Budgeting, Finance and Banking, Ms Excel, OLAP, Planning, PowerExcel

When I was a young accountant and learning about budget planning…

I thought, “Wow, there are companies out there adopting bottom-up budgeting and others doing top-down…and I wonder what makes a team choose one of those.”

Fast forward 30 years, and I can tell you that both methods exist in many businesses simultaneously. Mostly, people start at the bottom. And they’ve typically got a little expertise whereby they use formulas—for example, to guesstimate cost or revenue figures for the next year (“assume utilities will rise by 12%”). If they’re more advanced, they might even use artificial intelligence. In any case, they build their budget from the bottom up.

Then, a group of accountants like you and me get together and consolidate all that detailed forecasting and budgeting. We come to a consolidated result and take it up the chain of command, to the board, shareholders, and senior management decision-makers. They look at the consolidated numbers and may not like some of them. They say, “No, change this number, change that number, change those numbers,” and push their directives from the top down. So now, our job is to take top-level targeted numbers and get it to the bottom.

If you’ve built your models in Excel, this becomes very difficult. You need to go down to the bottom, change some assumptions, roll it all up, and check if you have the number that management wants. If you don’t, you must go down to the bottom again and start over until you roll it all up again. In some organizations, this sort of circular process can take months; it’s expensive, time-consuming, and demoralizing.

The good news is that if you use a different technology, this can be a much easier process. For example, our own software, PowerExcel, will enable you to do both bottom-up and top-down budgeting. Indeed, you can do them in sequence, as described above, but in relatively supersonic speed as compared to using Excel alone.

Let’s consider a simple example: a spreadsheet that is filtered to show our a Sales account, with Months in columns and Departments in Rows. The team has gone out and done a good job at the finest detail level, creating a sales forecast that totals 12,000.

However, having shown it to management and the board, they come back and say, “No, I can’t live with anything less than 15,000.”

So, now we need to be able to work “top down” to change the forecast to that number. How will we be able to do that?

The old system would be to wrestle with all these numbers individually, in another round of “bottom up” number-crunching. Instead, now, happily, with our technology, I can set a target—essentially by typing a number like 15,000 in a cell—and use a formula that we’ve all agreed upon and believe in. It’s a formula that will lead to equitable results, “pushing down” and incrementally increasing the numbers throughout so that their new total will get very close to the target. (We should note that we can go “top down” not only the full-year total—in this example, 15,000 rather than 12,000—but we also do so along any aggregation, like monthly totals for all departments.)

Next, we can fine-tune further from the bottom-up. We can ask ourselves, during which months and in which departments will we realistically be able to increase our sales numbers? Indeed we might be more optimistic about some, less so about others. The point is that now the choice is ours about which numbers we want to change, once again from the bottom up.

Since our technology supports both bottom-up and top-down budgeting, you will almost certainly begin with a bottom-up method, entering figures into a shared, centralized planning model. Then, with input from senior management, you can push numbers down via the top-down approach. Past that you can fine tune in a more detailed bottom-up exercise that matches the top-down expectation you’ve been asked to meet. All this work will be automated, across every department, across every region, whichever way your business is segmented.

Because both the “bottom up” entered numbers and the top-down formulas that govern the calculations are in the shared model, none of the data lives Excel, even as it is visually displayed there, in spreadsheets. If you delete any numbers, they are still there, upon recalculation. If you try to change a number determined by the model’s internal formula engine, it won’t allow you. The modelling engine—what’s underneath PowerExcel—is where the technology resides that will save you and your colleagues so much time and bother, so very different from the way most firms work today.

We encourage you to try our free PowerExcel version. If your business needs more dimensions or members than the freemium offers, reach out to me on LinkedIn or look on our website. We’ve got paid versions of our product, and we’ve got great consultants with years of experience that can help you and bring productivity gains within your company.

Subscribe to the PARIS Tech blog