We have a client that is involved in the motor industry, so has a complex (Multi-Franchise) DMS system. Up until now they have used the DMS system to generate their Management Accounts, but are increasingly frustrated by its lack of flexibility etc. The problem, as I see it is that the present Chart of Accounts runs to 13,500 lines and as such is likely to be very large and potentially unstable in its size. I have written MA's in the past, but does anyone have any recent experience of the best method to transfer such large amounts of data into a reasonable structure. I have used SUMIF/LOOKUP in the past, but it is the size of the COA that is causing concern.
Any pointers in the first instance would be gratefully received and if anyone has any product recommendations then I would be thankful. I am not looking at re-inventing the wheel if I do not have to etc. and am prepared to purchase the product if it stands up to scrutiny and is easy to adapt for the future. I cannot be alone on this one, as many Motor Dealers would be in the same position..
Kind regards,
Stephen
Replies (1)
Please login or register to join the discussion.
Horses for courses
Stephen,
I'm looking at this in a generic sense as I'm not familiar with DMS, but I'm very familiar with different ways of aggregating large volumes of data to produce accounts in efficient ways whilst providing a full audit trail back to the source data (whether it be TB or transaction based).
For very large volumes of data, SQL feeding a PivotTable to do the summarising is often the best approach. The whole accounts pack can then be fed off the PivotTable - think of it as pre-calculating the aggregations that can take some time if done by formulae. I've done accounts dealing with everything from a few thousand records to 23 million records (I kid you not) using a range of techniques. All approaches can incorporate off-system adjustments, if required, quite easily. If needs be, VBA can take some pain out of more complex scenarios, but I generally try to avoid this if possible.
It seems to me that 13,500 records is a reasonably small data set. Either formula based approaches (I'd add Sumproduct and, as a replacement for Lookup formulae, Index/Match into the likely to be useful formulas list) or PivotTable based approaches would probably work very well at those sorts of volumes.
I'd be happy to discuss your options, particularly the use of SQL if you haven't looked at the possibilities before, If you want to PM me.
I hope this gives some food for thought. I suspect you'll not be alone.
Regards,
Ian
Onion Reporting Software Ltd
Sage month end reporting in Excel
P.S. SQL can be used with CSV files, so don't think it's very complicated.