These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, December 6, 2012

Receipt Collection Threshold Analysis

Executive Summary

My VBA project is the direct result of a consulting project I performed for a large corporate client as part of a quality management class.

The project for our client involved their policy for turning in receipts for transactions on company credit cards. In a nutshell, this project was designed to balance the cost and hassle of scanning and processing a receipt with the risk of making errors in sales tax accrual. These errors could take the form of either over or under accruing taxes. Over accruals leads to too much tax being paid by our client, while under accruals can lead to penalties and extra interest should a state tax auditor discover it. Our client does not wish to avoid any taxes, but understanding that errors will inevitably occur, they wanted to know if there was some sort of threshold below which the risk for over-accruals or fines was less than the cost of processing a receipt.

We received 2 years’ worth of transaction data from our client for their corporate card holders’ transactions. Because this is sensitive data for our client, I have elected not to submit this data to the blog (A very limited set of disguised data is included--just enough for the process to run). My solution required two major operations. The first was to determine how many scans were necessary at any given collection threshold. I was told that employees are required to turn in receipts monthly, so I assumed that they would not be scanning any more frequently than that. The transPerAcct() macro generates a unique list of active card holders from the data, then counts how many transactions each employee had over the threshold. It then parses the date, and categorizes the purchases into months. Finally, it counts the number of months in which an employee had any transaction over the threshold.

The second major operation was to determine the actual tax liability and the estimated risk of mis-accrual. To accomplish this, the code adds a column for the tax charge, multiplying the transaction amount with the state tax rate. Some of the data sheets we were given did not contain any reference to the tax rate—in those cases the code pulls the average US sales tax rate. That total tax liability is the modified by certain values on the “Main” tab—The % of transactions with a trusted vendor, and the % of taxes paid at Point of Sale. Both of these are measures to estimate the risk of over-accrual.

The cost of gathering receipts is combined with several variables on the “Main” tab to generate a total cost of processing at any given collection threshold. The total tax burden is modified by an assumed error rate and the calculated penalty is and added to the calculated over-accrual. The point at which the sum of the tax liability and the receipt processing cost is the lowest is the appropriate receipt collection threshold.  Because the receipt holders and the accounting department generally operate with different thresholds, I used 2 data tables to populate 2 graphs showing how the numbers changed with the threshold for any given set of assumptions.

In order to provide some further intuition into the data, the import process also generates a histogram of the transactions by amount. This allows someone analyzing the data another angle into how to manage the balance between high dollar/ low volume transactions and low dollar/high volume transactions. 

No comments:

Post a Comment

Blog Archive