An automated Excel Cost Report, I first developed in 2012, continues to satisfy the client (a large mining company) even though I left the business in 2017.
Background. The customer required a weekly Cost Report which provided the basis and detail to support weekly invoiced charges but the method, used by my boss, to create the report was time consuming, the results error prone and the customer frequently dissatisfied (resulting in delayed payment).
The old method used was to take a weekly extract, copy it into Excel, continually filter the data, move filtered data to separate sheets, until there was nothing left and then summarise the data on a single sheet.
Frustrated with the demands on his time, the boss asked me to take over the task. I recognised the opportunity to improve the task, through automation, but also knew that the client was likely to do further analysis once they received our report so agreed on the condition that I could meet with the client first.
I worked with the client, established their criteria rules (unit types, charge categories, metrics etc) and got to work on the report.
What used to take up to 3 hours per week is now handled in minutes with a few easy button clicks which trigger automation:
- Refresh Invoice Dates List
- Select Invoice Date
- Get data, apply cost codes and summarise (at this point a quick view of summary to validate)
- Save report to folder (as a record)
- Email to Client (automated process wraps up Excel report and attaches to email addressed to client’s nominated approver).
Simple concepts – identify the rules and required output then automate. Rinse, repeat. Everybody wins and continues to win …more than 12 years later!