Internal Audit Purchasing Card Applications

Executive Summary

I created my VBA application for the Office of Compliance and Audit (OCA), which is also where I work. The Office of Compliance and Audit serves the internal audit function for BYU. One of the functions of the OCA is to perform financial audits of departments and colleges on campus. As part of departmental audits, we perform an audit of purchasing card (Pcard) transactions within the department.

The database we download Pcard data from does not allow us to download all the department transactions in one report, so we have to download multiple reports and combine them later. Other problems with the raw data from the system include a multiple line format that makes using pivot tables and filtering impossible. Also, the best way we have to download reports creates problems because transactions from other departments can be downloaded with our data. The system I built uses an already existing macro that reformats the reports to one line, and then combines all of the reports into one centralized report. I then have another macro that uses Vlookup within VBA to compare the account number of the transaction to a list of account numbers for the department located in a separate file.

The last part of the VBA application helped to eliminate very mundane, repetitive steps needed to get the data ready for an audit. First, there is an application that creates standard pivot tables that we use to analyze the data. Second, there is an application that searches the notes of the transactions for key words and highlights any transactions that use the word; the application then highlights this transaction for easy filtering to review all the data. Third, once we have decided on a sample from the transactions, the application can copy over the desired transaction data to a schedule used in actually performing the audit.

Normally this process would have taken a student auditor 1-2 days to perform depending on the size of the department. This VBA application cuts that time down to minutes, not including the time spent analyzing the results.

Full writeup: NFrakes - Project Writeup.pdf

VBA Application: Pcard Macro Project.xlsm

Three files with raw fictionalized transaction data: Demonstration-Fund 11.xls, Demonstration-Fund 19.xls, Demonstration-Fund 23.xls

Fictionalized department tree: Demo-Dept Tree.xls

Lead schedule: Demo-Lead Sched.xlsx