Receipt Reconciliation

Preface

I was working as a data analyst in the tobacco industry in 2019. As such, I was part of the analytics team handling most inbound and outbound sales related data. In addition, we were responsible for assisting point-of-sale data requests. I provided upgrades to existing procedures as well as introduced new solutions. Most of my methodology was based on MS Office; however, certain problems demanded more vigorous methods among them database based data collection.

1. The Problem #top

Pre-Covid19 there was a lot of account executive customer interaction. Account Executives were trying to increase sales by being task with talking to distributors and final point-of-sales locations (POS's). While travelling, the cost of travel, food, and others were reimbursed. The process of reimbursement frequently resulted in full inboxes and miscommunication. Our team was task with improving this process, and I was selected to take the lead.
Receipts per Month: 1000-2000
Receipt Reconciliation Method: manual inbox extraction & checking with bank

2. Solution #top

a) Introduction #top

Given the problem, the idea was that the receipts/data had to be collected and centralized before anything could be accomplished. The account executives were active in most states/timezones so delegating this task to some employee was not an option. There are existing apps which would solve the problem, but they are usually build around an individual notion not on a company level.

b) Requirements #top

      Most IT based projects follow a pattern. They start off with a problem to be solved, followed by a preposition of a solution, followed my adjustments. The key to success in DevOps-nature is not getting derailed in the process. Check out my Agile Software Development certification. This in mind, I asked what people wanted and needed:

Receipt Creators:
      This group was unenthusiastic of manually writing every receipt in a paper based submittal form in order to send it in on a weekly basis. Their need was a take picture-and-send solution.
Accounting:
      This group was tasked with reconciling receipts with the company bank statement. The problem was that the receipts would not appear orderly and timely. This causes problems with accounting cycles.
System Demands:
      The system had to be reliable, cheap, and able to backup.

c) Building Solution #top

After a communication, planning, and a setup phase, I had access to a Linux based webserver from where I could use a LAMP-Server setup. Among which we utilized a free MySQL database setup. I build a login web page in a web application format. The setup was as easy as scanning a QR-code and saving the link to the screen of the receipt creators phone.
After login utilizing the "user table", a button could be clicked allowing a photo to be taken (blob) and details added via fields. The receipt record was then saved in the "receipts table". This was a general table where all receipts were collected. Important columns I added in the MySQL DB table where: id, userid, rdate, astus, pic (blob). The “astus" was the “Accounting STatUS”. These are number indicators 0-7 as you can see below in the illustration.

This was the functional logic of receipts thru the database which consisted of three Tables: users, receipts, archive. This logic guided my database schema. The users in the user-table were functionally differentiated by their user-id (the id column of table was int non-auto-increment). The users could interact via simplistic communication. Overall this project was very successful in completing the initial request.