Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Reporting Challenge 2: Build a P&L from 15,000 GL accounts

by
17th Jul 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

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.

Related articles

  • Reporting Challenge 1: Producing management accounts from a trial balance with 15,000 accounts
  • David Carter's Five Minute Pivot Table Tips
  • Getting better reports out of your accounts package
  • Reporting Challenge: Resource planner outgrows Excel

    Subscribe to the ExcelZone NewswireSeveral 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.

  • Tags:

    Replies (5)

    Please login or register to join the discussion.

    avatar
    By gercarol
    08th Aug 2005 10:01

    Intergrate to a more flexible GL reporting tool
    Excel, Crystal and Access are all possible solutions but by far the easiest solution I think would be the following.

    I have used a package a lot recently called Greentree (GT) its a full featured system but for this case you only need the nominal ledger component, specific consolidation packages would also do the trick but most of those are quite expensive.

    Steps:

    Setup the 15,000 nominal ledger codes in GT these can be imported from Excel, you would import the nominal code, description and the groupings you want for the account. You can have as many groupings as you want for each account and they are independant of the COA segments so it dosent matter if the accounts are out of order etc.

    Ok thats the base setup of the host system done now once a month you export a Trial balance to GT once again just done via excel.

    The final step is the reporting and this is where GT allows you to report in Excel based upon the groupings you have setup rather than the nominal accounts themselves. As you can have unlimted groupings for each nominal code you could have different levels of summary that you could report off or maybe accounts grouped together by division etc even when the segments dont have any logic to them.

    End result is that you can create and write reports based on your 15,000 accounts that will group those accounts together in whatever way you wish with a single export once a month.

    Ive seen this working with 32,000 accounts at a ODPM organisation, why anyone needs so many accounts is beyond me but it worked.

    Thanks (0)
    avatar
    By David Carter
    08th Aug 2005 12:33

    next step?
    Thanks everyone. John Stokdyk seems terribly confident that I'm going to be able to work out the answer on this one. I wish I felt the same way!

    Anyhow, Ger knows a company with the same problem who sorted it by importing the data into Greentree. The problem is that we are effectively talking about a new accounts package here - several £'000's? [I think Greentree are an Australian company new to the UK, mentioned once or twice on the site]

    Bradley, I've known of Monarch for years, but I thought it was a niche product for mainframe users who had to scrape report data out of print files. You're implying that now it's a complete reporting solution. Perhaps we should be giving it a bit more exposure on the site. What can you do with Monarch that you can't do with Excel?

    Green/Richard. Sorry for delay in reply. I've been on holiday for a week. I think you're suggesting that I'm wrong and we CAN automate his reports every month via pivot tables.

    If so, I will need your help on this. However, I think we're at the stage when it's time to stop talking hypotheticals and start working with some real data. I'll get back.

    Thanks (0)
    avatar
    By Richard Willis
    02nd Aug 2005 11:51

    Why use Text to Columns? What's wrong with Left, Mid, Right?
    David

    If the user's account ID format is consistent, e.g. 3+5+4 or whatever, then surely the elements can be extracted using =Left, +Mid, and =Right in combination with =IF! These can be left as standing formulae in the sheet and from that point on automation should be possible.

    Thanks (0)
    avatar
    By AnonymousUser
    02nd Aug 2005 12:16

    One button press
    Can you use ODBC to establish a link with the nominal, then reference your data sheet (ie have no hard coded elements in it) to the range "ODBC"? Write a small VBA routine to refresh the ODBC and the pivot tables, and you can deliver a single button press.

    Do it that way then each month your review would be to check for any additional codes or anomalies. Written as suggested by Richard with LEFT RIGHT and MID, and lookup tables for your code descriptions, anomalies and any additional category requirements, you should get very close to what you want.

    The other way is to chop up the data as you suggest and use a series of IFs (for the months) and SUMIFs (for the codes) to aggregate into a fixed report design.

    No need to stray into Access or Crystal, imho, except that round about the 55,000 lines (your mileage may vary) of data mark you might need to put another Gig of RAM into your PC! (And turn the Norton Office plug-in off - it just chews memory to no good effect)

    Thanks (0)
    avatar
    By User deleted
    02nd Aug 2005 13:59

    Automate with Monarch
    I have just added a recommendation for Monarch on the earlier thread so I won't repeat it here. I will add that it can be automated to do the equivalent of text to columns within the model that it set up initially.

    Thanks (0)