Tweet
So, this happened about 12 months ago. I wasn't sure about posting it at the time as I didn't know if it would be taken further. however, I've spoken unto the "Dear Leader" and I can share it subject to his previewing it.
The background is somewhat long winded but fairly essential to understanding the tale.
We are a small company owned by a family (not mine) and operating 3 stores. As we are so small, our accounts are still done the old fashioned paper way. We have EPOS and things but every day the daily summary is done longhand where we pull all the relevant figures for the day together (cash total, till reading, cheques total etc.). Each week these are added up and transferred to a proper accounts book which is given to the accountants at the year end. Last year we began computerising the accounts to simplify things. As Sage is expensive and has a lot of features we don't need or can't use (no internet at work) the accounts are to be done using a variety of Excel Spreadsheets.
I set up the initial sheets fairly quickly and the boss did look at them and was mightily pleased. As his confidence with them grew, we began to add additional features and functions that would increase the efficiency of the system and reduce the time it took to do them. For example, we added a sheet laid out to match the format of the weekly lotto invoice. All we now do is copy the figures into the relevant boxes and it checks them, keeps the running total and highlights discrepancies.
Each of our stores has a terminal for processing bill payments such as utility bills, phone top ups and so on. The money we take for these is banked into a temporary holding account and taken via direct debit once a week. The company then pay the commission earned each month direct into the main trading bank account.
I suggested that it would be useful to track how much of the turnover was down to this terminal and track the direct debit amounts so we could reconcile the bank statements more easily. This spreadsheet was duly knocked together and it works by tracking how much was entered via that department key on the till at all three shops on a daily basis and totalling them at the relevant points. Bear in mind that this figure is being taken from the EPOS Roll not the stand alone bill payments terminal each night.
I copied the spreadsheet to the correct folder on the accounts PC and it immediately started filling itself with the figures from so far in the current trading year. Showing the boss how to read the figures to compare them against the bank statement we realised that the figures were not correct. They were £5 short one week, £10 another and so on. It totally a discrepancy of about £100 over a trading year.
My initial response was that it may be a keying error by the till operator. After all, no one is perfect and things could occasionally be rung into the wrong department. I was tasked by the boss with solving this riddle.
My first attack was to find where the errors were occurring.
By knocking together a spreadsheet to summarise the weekly figure for each store from the EPOS and requesting a weekly breakdown of the sales figures by terminal from the provider, I was able to compare them . After a week of analysing the figures (this was fitted around my usual tasks) I found the store.
Having found the store I needed to find out what transactions were being missed. I met with reps from the provider who showed me how to produce a daily transaction list for each day. I compared the daily till reading to the daily overnight polling report from the terminal and slowly built up a picture of the days that errors were made.
I presented this to the boss who gave me permission to begin the tedious process of printing a daily summary and checking each transaction off against the till roll for the day. I went to the store in question and began.
For the first few hours I merely printed out the summaries for each day from the terminal. All was going well so I went for lunch, fully intending on my return to start matching them to the till roll. When I returned from my dinner break, the afternoon part-timers had arrived and one was intrigued as to what I was doing. Lets call her Amy. Amy was a good worker who had been here about 12 months. She was fairly quiet, got on with the job and was well regarded by both colleagues and customers.
I merely said there were some accounting discrepancies and began my comparisons in earnest. I then found that the missing transactions on the till roll didn't appear to have matching over-rings on other departments with the same time stamp. Realising that I was getting in over my head I texted the boss and asked him to come over as soon as possible. I didn't want to ring as there were 7 people working that afternoon so I didn't want gossip to start spreading.
The boss arrived, I took him in the office and laid out everything I had found. I showed him in detail how I tracked down the missing transactions and had printed duplicates of the various missing transactions. There appeared to be no pattern to the errors. One week it was a missing electric meter token, the next a missing phone top up and so on. We realised that 3 of the missing ones were for a phone top up to the same mobile.
When you top up a phone with a card, the money goes straight to the mobile and the receipt prints on the corresponding mobile phone number.
The boss decided to jump straight in with both feet and rang the number from the receipt. Amy's phone was in her pocket and began ringing. As we turned to look at her she ran out of the shop.
We never saw her again. She never returned for her wage. She wouldn't take our calls and she never came back for her few bits and pieces in her locker.
The background is somewhat long winded but fairly essential to understanding the tale.
We are a small company owned by a family (not mine) and operating 3 stores. As we are so small, our accounts are still done the old fashioned paper way. We have EPOS and things but every day the daily summary is done longhand where we pull all the relevant figures for the day together (cash total, till reading, cheques total etc.). Each week these are added up and transferred to a proper accounts book which is given to the accountants at the year end. Last year we began computerising the accounts to simplify things. As Sage is expensive and has a lot of features we don't need or can't use (no internet at work) the accounts are to be done using a variety of Excel Spreadsheets.
I set up the initial sheets fairly quickly and the boss did look at them and was mightily pleased. As his confidence with them grew, we began to add additional features and functions that would increase the efficiency of the system and reduce the time it took to do them. For example, we added a sheet laid out to match the format of the weekly lotto invoice. All we now do is copy the figures into the relevant boxes and it checks them, keeps the running total and highlights discrepancies.
Each of our stores has a terminal for processing bill payments such as utility bills, phone top ups and so on. The money we take for these is banked into a temporary holding account and taken via direct debit once a week. The company then pay the commission earned each month direct into the main trading bank account.
I suggested that it would be useful to track how much of the turnover was down to this terminal and track the direct debit amounts so we could reconcile the bank statements more easily. This spreadsheet was duly knocked together and it works by tracking how much was entered via that department key on the till at all three shops on a daily basis and totalling them at the relevant points. Bear in mind that this figure is being taken from the EPOS Roll not the stand alone bill payments terminal each night.
I copied the spreadsheet to the correct folder on the accounts PC and it immediately started filling itself with the figures from so far in the current trading year. Showing the boss how to read the figures to compare them against the bank statement we realised that the figures were not correct. They were £5 short one week, £10 another and so on. It totally a discrepancy of about £100 over a trading year.
My initial response was that it may be a keying error by the till operator. After all, no one is perfect and things could occasionally be rung into the wrong department. I was tasked by the boss with solving this riddle.
My first attack was to find where the errors were occurring.
By knocking together a spreadsheet to summarise the weekly figure for each store from the EPOS and requesting a weekly breakdown of the sales figures by terminal from the provider, I was able to compare them . After a week of analysing the figures (this was fitted around my usual tasks) I found the store.
Having found the store I needed to find out what transactions were being missed. I met with reps from the provider who showed me how to produce a daily transaction list for each day. I compared the daily till reading to the daily overnight polling report from the terminal and slowly built up a picture of the days that errors were made.
I presented this to the boss who gave me permission to begin the tedious process of printing a daily summary and checking each transaction off against the till roll for the day. I went to the store in question and began.
For the first few hours I merely printed out the summaries for each day from the terminal. All was going well so I went for lunch, fully intending on my return to start matching them to the till roll. When I returned from my dinner break, the afternoon part-timers had arrived and one was intrigued as to what I was doing. Lets call her Amy. Amy was a good worker who had been here about 12 months. She was fairly quiet, got on with the job and was well regarded by both colleagues and customers.
I merely said there were some accounting discrepancies and began my comparisons in earnest. I then found that the missing transactions on the till roll didn't appear to have matching over-rings on other departments with the same time stamp. Realising that I was getting in over my head I texted the boss and asked him to come over as soon as possible. I didn't want to ring as there were 7 people working that afternoon so I didn't want gossip to start spreading.
The boss arrived, I took him in the office and laid out everything I had found. I showed him in detail how I tracked down the missing transactions and had printed duplicates of the various missing transactions. There appeared to be no pattern to the errors. One week it was a missing electric meter token, the next a missing phone top up and so on. We realised that 3 of the missing ones were for a phone top up to the same mobile.
When you top up a phone with a card, the money goes straight to the mobile and the receipt prints on the corresponding mobile phone number.
The boss decided to jump straight in with both feet and rang the number from the receipt. Amy's phone was in her pocket and began ringing. As we turned to look at her she ran out of the shop.
We never saw her again. She never returned for her wage. She wouldn't take our calls and she never came back for her few bits and pieces in her locker.
Comment