The right tool

One of our clients recently said that they wanted to be able to analyse sales by a couple of different categories, one they called ‘market sector’ and the other ‘region’. Region was relatively easy, as that was stored against the customer and all invoices were linked to the relevant customer. Market sector, on the other hand, proved harder to pin down. It turned out that the client’s customers could be divided into several types and each type of customer generally bought one kind of product, but not exclusively. Their current method of analysis involved examining all the paper invoices each week, classifying them by region and market sector, adding them up with a calculator and typing the resulting figures into an Excel workbook, and this practice had been going on for many months.

Now, the person who originally created the workbook had left and the new person doing the job had no experience of Excel, let alone any formal training. The result was that they treated Excel as if it was Word, with which they were familiar: they laid out tables with row and column headings, borders and cells and they typed the figures into these cells. To get total figures in the last column and last row, they added up all the figures with a calculator and typed in those figures too. This poor person was spending hours each week going through paper files, adding up numbers by hand and typing the results into a tool that specialises in adding up numbers… Of course, the workbook was littered with mistakes, with numbers being mistyped or added up incorrectly. The whole thing was a shambles.

This situation arose for two reasons. One was the lack of training: none of the employees were given any training in how to use the computers on their desks, but were just expected to pick it up as they went along. The other reason was that the company’s line-of-business application didn’t provide the analysis the business now needed. The region data was held against the customer records but wasn’t considered when reporting invoices, while the market sector data wasn’t even being stored. Since the manual analysis was already such a time-consuming task, people were classifying the whole amount of each invoice according to one sector, even if there might be products from different sectors on that invoice. Whichever sector showed the greatest value on that invoice was given as the sector for the whole amount, so these hand-calculated figures were under-reporting some values and over-reporting others. In other words, they were just plain wrong. As a further result, the term market sector itself had become confused in the minds of the users, some thinking it referred to a customer and others thinking it an attribute of a particular invoice. After careful consultation and consideration, we decided it was really just product type by another name, and so applied to the individual lines on an invoice and not to the invoice as a whole – analysing invoices line by line rather than as a whole would stop the under- and over-reporting.

Unfortunately, because of the nature of this client’s business, there was no list of products that could be classified once and for all in order to provide the analysis required. Also, the complexity of the existing line-of-business application meant we were unable to extend it to provide the tools for classifying the invoices there. What we could do, however, was to add a couple of tables alongside the invoice and invoice lines tables, which would contain the extra analysis data, and then build a tiny front-end that allowed a user to see the invoices and enter the analysis data. Entering the data for a week’s worth of invoices now took no more than five minutes, instead of the hours it took to analyse and add up the data by hand. An Excel workbook containing PivotTables and PivotCharts could then interrogate the database to present the data in many different ways. This workbook was set up to refresh itself from the database automatically whenever it was opened, so it always showed up-to-date figures. The query in the workbook joined the invoice and invoice lines tables to the new tables containing the extra analysis codes, and to the customer table to get the region data.