You might also be interested in
Replies (5)
Please login or register to join the discussion.
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.
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.
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.
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)
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.