Automated Report Creation – FF&E Survey Business

Introduction

Leveraging a custom solution, a company with a long data logging process slashed its processing time. They saved 3 hours on every project equating to 56 days of work, virtually eliminating errors and boosting project capacity. They now automatically import csv data, have reduced manual entry and have an automated report created instantly.

Problem

The business’s key service involved them going onto a site and log all items found on the premises using their own sourced solution. Subsequently, they downloaded a CSV data export in order to copy paste it into a new file. This stage required a lot of processing, some of the processing included:

  • Each row would have data about an item but this could have a quantity higher than 1.  If the quantity was greater than 1, they would have to create a new row for each instance of that item. If the data fulfilled certain criteria, they would need to apply color coding based on those specifications.
  • Exports were not in a user-friendly format. This meant that the business had to convert details like description and color manually.
  • A combination of dimensions, name and colour translates to a predefined item code.
  • The business had to summarize the data using formulas.

This process caused the following issues:

  • Some reports contained 1000s of rows, this meant that each report could take multiple days to complete.
  • Only one person knew the complete process. There was no documentation of all the codes and descriptions.
  • It is very easy to make mistakes with this level of manual data entry/manipulation.
  • The likelihood of data changing was high, requiring the report to be thoroughly checked and updated.
Photo showing manual report creation on paper.

Solution

We built an automated and self-service solution that the company could rely on whenever they needed it. We built the solution within Excel using VBA code (Visual Basic for Applications) and it contained the following features:

  • Simple import of exported CSVs – The user pressed a single button and selected the file from the popup file explorer window, this would automatically export the data into a template report. Ideally this automatic via code, through an api, but the software did not have this functionality available at the time.
  • The system stored codes and other miscellaneous knowledge in the template. This updated itself automatically with new examples as they were added, reducing manual entries.
  • The system expanded instances with multiple items into their own rows, applying automatic colouring.
  • The solution replaced most manual entries with code, performing operations automatically after import.

Finally, the data was automatically fed into a dashboard that summarised the data and required no manual input. They can now generate an automated report with just a click of a button.

Outcome

The clients process which would require them to have days off site could now be completed before the end of the day, saving 56 days of work in just 1 year. Mistakes are now very rare and only exist from a small amount of manual entry that is still required. This not only saves time but also gives the client peace of mind as their work is always of the highest quality. The client could now focus on providing their best service and allowed them to complete more projects.

Ready To Take The Next Step?

Let’s start your journey to operational excellence today, it is time to be more efficient and profitable. Let’s grow together!