Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

Simplify Excel Accounting Rollups

 

It happens once a year in every good-sized company across the world. Two words that cause ripples of havoc throughout the company - Budget Time.

If your company is like most, the process goes something like this:

  • Someone in accounting or finance builds a cool template in Excel for a single department. Maybe the template has last year's data, maybe not. Maybe the template includes some VBA macros or other power tricks, or maybe not.
  • The accounting or finance department creates a worksheet for every department in the company. A master spreadsheet collects data from all of the department worksheets.
  • We e-mail the departments sheets to each department head to fill in their budget. Six dozen spreadsheets go out, and a week later, most come back. After some phone calls and hand-holding and threats, finally the last of the spreadsheets come back.
  • We discover that Joe in marketing took it upon himself to insert 12 new rows into our worksheet template! Freda in Investor Relations added graphs, word art and 10 new columns in the middle of the data. After late nights at the office, the accounting folks get the worksheets rolled up to a global number, only to discover...
  • You know the story - this first pass, we will discover revenues will be up 1% and expenses up 200% and the CFO doesn't think that will work, so we go back to step 1.
I first stepped into a finance department in 1988. Back then, it was Lotus instead of Excel, but this basic dance has been happening year after year after year in company after company after company.

I am happy to report that I have found the holy grail. Over the past week, I have watched a demo of software that is absolutely amazing. It does what it says it will do, does it incredibly well, and does it affordably. I am here today to shout to corporate accounting and finance departments everywhere to consider this software.

You can look at the company's website, but first, let me show you what I learned in the demo. After seeing the demo last week, I went back last night and took screen shots along the way. I really haven't seen such a cool product in a long time.

If you've read Guerilla Data Analysis Using Microsoft Excel, you know that I came up through accounting, finance, and operations departments. I was the analyst responsible for forecasting for years. I am sure that the software will handle similar problems for other departments, but the quick demo here is a forecasting example.

Here is the forecasting spreadsheet for an imaginary region. It is a nice spreadsheet, good use of color, some data validation that points to a hidden sheet, maybe a little VBA here or there. The demo workbooks has four worksheets for four regions.

As the finance or accounting person, we can already see some trouble spots. We have formulas in there and we know some people will screw those up. The data validation is pointing to a hidden sheet, and we know that someone will manage to break that.

In addition to the regional worksheets, we have a rollup sheet for the Central Division. The rollup sheet points to the individual workbooks.

Again, you can imagine the possible problems. The formulas in D7 rely on Glen not changing the structure of the Illinois worksheet. We are going to have to cut and paste to copy the data back into the workbook. In general, it will be your typical budgeting season.

Along comes the software from Balenz. This is amazing stuff. Let's talk about requirements. Your company has to have a server with SQL server, but that is pretty much standard for most mid-size companies. You will need one or two people in accounting with permission to run the software, and those two people are required to have Excel 2000 or greater. There are no other requirements for the dozens of people who will get the individual worksheets. Those people run straight Excel, 97 or greater.

The Balenz software is easy to use. One of the accountants with "author" privileges will start the Balenz software and create a new rollup by specifying our forecasting worksheet. Note that this is a stock spreadsheet, it has nothing special for Balenz when we start the process.


After you specify the spreadsheet, you wait while Balenz analyzes the spreadsheet. This takes a good minute, but you can tell that they are really figuring out what is going on. When you get to the next screen, you realize that these guys have things under control. A window pops up to allow you to provide specs about the workbook. You identify which worksheet is the "rollup" worksheet. In our case, it is the Central worksheet. This is the worksheet that does not get distributed, but sure has to be right after the worksheets go out and come back.

You also specify if any worksheets need to go out to each region in order to have the worksheet work. In our case, there was a hidden Ref worksheet in the workbook in order to make the data validation work. You might have a hidden sheet with department lookups or rates or prices or whatever. Specify one or more visible or hidden worksheets here and they will go out to the regional managers.

Once you've done this initial setup, the rollups will become a breeze. The image below is the Balenz control panel. At any time, you can distribute worksheets with a few mouseclicks. You can see who has returned the sheets. You can review the submissions, choose to accept or reject or send them back out. If you are out of the office, you can do all of these actions from the web.


I apologize for being such an accounting geek, but here is the feature that absolutely amazes me. Whenever I would be involved in a rollup process, you can always count on someone doing something to louse up the spreadsheet. Someone adds a row, adds a column, blows up the formulas, etc. Balenz handles this with ease. Check out this image from the Illinois sheet. I purposely did what any typical sales manager would do: I added rows, added some scratchpad formulas, etc. etc. Now, there is extra data, extra rows, hidden rows. The formula that used to be in row 19 is now in Row 22.

When the data comes in and gets rolled up, Balenz handles the extra rows without any hassle at all. The formulas in the rollup sheet now point to the totals in Row 22 instead of Row 19 for Illinois. It is a beautiful thing.


Bottom Line If you are facing an annual budget/forecast/anything rollup, you owe it to yourself to check out the Balenz software. If you are in Northeast Ohio, I would be glad to show it to you. If you are elsewhere, contact the Balenz team for a demo.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.