A Report Automation Case Study
When a fast-growing Canadian construction materials distributor first came to us, they were spending approximately 16 hours a week manually updating a key report in Google Sheets to track sales quote and help forecast revenue.
The reason they had been doing it manually that way was that they could only get key details from sales quotes they had created by opening them in the accounting system, or saving them as PDF files and then manually entering the data into the Google sheet.
From the Google sheet, they would then update the status of each quote (open, converted to sales order, invoiced, or lost) and then they would use it to help forecast revenue.
The manual process meant that it was difficult (actually impossible, in many cases) to interrogate the report and "slice and dice" the sales quotes.
The critical nature of tracking and analyze sales quotes for this client meant that we started right there.
Our initial sprint (a sprint is a span of time - 2 weeks in our case - where we focus on delivering one or two very specific things to completion) focused on replacing the manual process of looking at PDF's and transcribing information into the Google sheet with an automated daily process.
Our first-sprint dashboard delivered an automated process of reading the PDF's, extracting the data, and uploading into the client's dashboard (accessible via computer, their phone, or tablet).
We also added a 'write-back' feature to the dashboard so that the Sales Manager could update the status of any sales quotes 'on-the-fly' and have those changes immediately reflected in the reports based on those sales quotes.
What the client really loved was that we had pulled ALL the data from the PDF's, not just the data that was being manually pulled and added to Google sheets before we started working together.
This meant, for example, that the owners could assess open sales quotes and the individual item level.
This allowed them to spot developing trends (like trending new colours) before they show up in any sales reports. In turn, that insight allowed them to have data-driven discussions with their suppliers to ensure they could maintain adequate supply of trending 'hot' items, instead of being unable to get enough once a trend was fully established in the market.