stock take

According to the Straits Times report dated 22 Jul 2021, the Auditor-General found there was a wastage of public funds at HPB over excess fitness trackers. They found 268,000 excess fitness trackers. A full stock count discovered even more excess trackers, totaled 341,000.

The report mentioned that:

  1. The receipt and distribution of trackers involved manual processes and multiple external parties;
  2. There was no central monitoring of the movement and stock of trackers;
  3. Records maintained were incomplete; and
  4. There was no periodic reconciliation of records with physical stock on hand.

If you have collected a tracker on-site before, you are likely to understand that the operation could be quite messy.

  • The HPB guys needed to manage the long queue, especially during the weekends, under the hot weather. They have to direct the people to the various counters issuing the trackers;
  • They guys needed to change over and taking breaks;
  • They have to check for the collectors’ eligibility, issue the tracker and sometimes teach the collectors how to use the tracker.

But these cannot be excuses for the lapse. The problem with the monitoring was the collection of data. Each distribution counter could have used a simple spreadsheet and record down the serial number of the tracker and the collector’s identity numbers. When it was done, send the entire spreadsheet to one coordinator. If there were 20 stations, there should be 20 spreadsheets. If there were 5 collection centres, there would be 100 spreadsheets.

The coordinator needed to open the 100 spreadsheets, copy and paste them into a master spreadsheet. It was that simple but tedious. And it was a daily process which happened at the end of each day. Not easy when you were probably tired after working for the whole day, under the hot sun.

But if the coordinator knew how to use Excel Power Query, it would be an entirely different story again. All he needed to do is to put all the 100 spreadsheets into a folder, use the “Get data from a folder” function and all the 100 spreadsheets would align themselves and get consolidated into a simple data record for Pivot Table. And when the second day came, just put the 2nd 100 spreadsheets into the folder and click refresh. The report would have been updated instantly. Just a minute job.

It is hard to believe but it’s true. But you need to master Power Query first.

Are you ready to make your life easier and be that smart executive who uses technology to get things done?