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.
No comments:
Post a Comment