Management Accounts from a DMS

Management Accounts from a DMS

Didn't find your answer?

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.

By bro0010
02nd Aug 2014 13:13

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

www.onionrs.co.uk

 

P.S. SQL can be used with CSV files, so don't think it's very complicated.

Thanks (0)