Regular master data in Excel – penny wise, pound foolish

Are you regularly getting ‘master data’ in Excel – and then capturing it in your accounting/ERP system – or even worse:  keeping it in Excel?  Please stop!

I’ve done some work for a debt collection company the past few months.  They receive their ‘master data’ every month in Excel.  New/old accounts, new balances, new client info, etc.  I then had to report on changes in balances (graphs) and business ‘growth’/’decline’.

The ‘quick fix’ was to handle everything in Excel:  copy-and-paste new data into a ‘summary’ workbook and then run multiple vlookups.  This method was ok at the time – since the sustainability of the business was unsure.

But this has been going on for almost a year now..  Plus, any ad hoc queries would take time (money).

If I had to do it all over – I would’ve immediately designed an integration app that would (auto) load new data into a database and run a series of scripts to (auto) generate reports.  It would’ve saved myself some time and the customer some money.

If you’re getting Excel sheets (or CSV or ‘flat’ text files) from your customers, suppliers, transporters, etc and would like to integrate it with your account/ERP system – or run an independant system:  please contact me – I’ve learnt a few lessons..