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

Kashflow logo
Share this content

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 Ac...

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.

About AccountingWEB

Replies

Please login or register to join the discussion.

avatar
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
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
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
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 Anonymous
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)

Related content