Today we found somebody fudging the numbers.
Normally it's hard to catch on to tricks but today my boss wanted to look at some trends for 2016 and she saw some stuff that looked weird. She asked me to investigate. She was wondering why a certain metric kept coming up as the same number for a number of weeks. I saw what she was looking at and started to drill down into the details for the months of October through December. I started to pull the weekly details and tracked how the numbers of the various metrics played out from week to week, and I could see why the main metric barely budged. I could see the various numbers making up the main metric change so they weren't the same numbers from week to week. It just so happened that at the metric level, it was unchanging for a few weeks. My boss thought that maybe the manager was using the same report from week to week but for last week, I could see that the manager actually ran the reports for use in last week's report. I need to verify that for other weeks, she ran the reports but I suspect she did.
No, we found something else. We saw evidence that once the report was done, the report still had errors but the error message was deleted or the sum formula was written over, so I couldn't pick up on the fact that the report had errors. When digging into the details on a week by week basis, I could see that some numbers weren't adding up. So when numbers did not add up, I went to the main Excel program and compared it to the final report. There I found that some things were deleted or formulas were overwritten. Multiple instances of such occurrence meant that the deletions/over writings were deliberate.
Because we fear that others may be doing the same thing, I'm going to have to figure out how to password protect the actual report but leave certain items unlocked for the inevitable necessary corrections. I have to lock it down and yet give enough flexibility. I also have to check to see if I can still open up the reports and copy them into my Summary report with my own collecting macro. I'm thinking I couldn't do it before but maybe I'm wrong. If I'm wrong, I can add to the closing macro a code to lock and password protect the newly created report upon closing the Excel file.
So this weekend I'm going to have to work on that.