Reporting Challenge 2: Build a P&L from 15,000 GL accounts
An AccountingWEB member approached David Carter for advice about ">how to derive monthly management reports from a chart of accounts with 15,000 separate accounts - an unwelcome side effect of multi-segment nominal ledger desgin. David's first impulse is to see if Excel pivot tables are up to the task.
We've had plenty of suggestions on how to handle this monster of a Trial Balance. Most reckon the answer lies in Access or Crystal Report writer.
However, as always, my own initial instincts are to see what an Excel pivot table can do.
So I agree with reader Aongus Curran's suggestion about using Text to Columns to chop up the codes, then Vlookup to bring in some code descriptions, and finally using pivot tables.
P&L pivot table tutorial
Up to now all the pivot table tutorials on AccountingWeb have dealt with building up pivot tables from transactions. So I've taken this opportunity to write up a new tutorial on how to build up a pivot table from a set of balances, in this instance from a Trial Balance with a multi-segment code.
Using Text to Columns and Vlookup you can get good results very quickly, as I hope the tutorial shows. However, we still face a couple of problems.
Account codes wrongly set up
The customer tells us that in some cases he has created account numbers that don't follow the rules.
Without knowing the exact details, I think this could be handled fairly easily by creating another lookup table. It would contain two columns. Column A ACNO_1 would contain all the account codes. Then make a copy of these in column B, heading ACNO_2.
Now find the faulty accounts in the ACNO_2 column and change them to an account number that IS correct.
Before you run the monthly accounts, use Vlookup to pull the ACNO_2 account codes into the Trial Balance. Use these corrected ACNO_2 account codes, rather than the original ACNO_1 account codes, to create the reports.
2. Create the reports automatically each month
Each month after the postings are complete, our reader's client simply wants to press a button and the reports are all printed out automatically as his monthly management pack.
I don't see this is possible with my solution, which would require an hour or two's work each month chopping up the account codes and cobbling the pivot tables together.
So in the end I guess pivot tables won't give him everything he wants. Perhaps it has to be Access or Crystal after all.
Several AccountingWEB members have already offered David Carter their advice on this challenge and he will continue to explore new avenues in the coming weeks. If you would like to keep up with developments on how the Reporting Challenge is progressing, click the button on the right to receive regular updates in our fortnightly ExcelZone newswire. The subscription routine will take you to the AccountingWEB home page when it's finished.