Imagine, you have site managers who are great with customers but not numerically savvy. You have to get them to send a weekly report but it is one of those activities they'd rather forget. And some just don't know how to use spreadsheets.

When I received this project, we had about 45 sites that were not doing the weekly report and I had to roll out the report. I initially did not know that we had previously tried rolling out this report and had failed because we could not get the managers to take appropriate steps in order to make the report work. They would persist to leave out steps. When I first saw the report, I thought "Wow! That's a lot of information to be filled out. Aren't these managers out at the site where customers could just walk in?" So I decided to do something different and build something that would take just a push of the button (in a figurative sense), and the report would just build itself.

The little movie below (my amateurish effort) shows the whole sequence that 70 managers now go through every week. The opening shots show the file before the program does its magic. Then you will see files with downloaded data being opened first and then the main report. The macro will run and does its magic and then the file will be saved. On my machine, this whole process takes less then 3 minutes. I had to cut some pieces out in order to keep the size down.

Now, every week we have roughly 70 managers running the report first thing Monday morning, and if they had kept up during the week, the report is done in probably 15 minutes. Some of these managers had been doing the reports manually and are quite happy to have this automated.

To collect the data into a summary report, I created another program that would go to our network and pull in the files. That is about a 5 minute process. It also tells us who hadn't done their report yet and who didn't do everything as they should. It took us about a year to get everybody on board (my boss and I had to keep at them every week until they got into the habit of doing it) but we are now much closer to nirvana.

The advantages of this system are:

  • The program forces them to take certain steps in order for the program to work.
  • Everybody has to use the same set of data.
  • Automation decreases the number of errors.
  • The program does some checks and balances to make sure the numbers add up
  • Instead of taking upwards to an hour (depends on the size of the site), the program takes about 5 minutes.
  • We are now processing 70 reports instead of 30 reports into a summary management report.