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

Monday, April 13, 2015

Biweekly Report Generator and Time-Sheet Data Collector

Executive Summary

Business Problem

The Internal Audit Department, in which I intern, is made up of 4 full-time employees. The organization that the Internal Audit Department serves has over 2500 employees. The Director of Internal Audit was concerned with the efficiency of internal audits because of the inability to track hours worked by auditors on particular tasks. Auditors would complete time-sheets each pay period through the human resources payroll system. In the time-sheets, the auditors would report the hours worked on particular tasks but that information is not readily available for retrospective analysis by the director. This project was to allow for the recording of hours worked by auditors in Microsoft Excel and in biweekly progress reports to the director.

Project Solution

The foundation of the project was a timesheet template created in Excel. This template allows for the auditor to record the hours worked for the primary and secondary assigned audits. Once the template is completed, the auditor can click on a button on the new “Timesheet Functions” ribbon tab to create a biweekly progress report. That begins a series of userforms that collects information regarding the work the auditor conducted over the pay period. Through the userforms a database of tables with information about the audits is updated in the timesheet template. The updating of these tables prevents the auditors from entering information more than once. The userforms use these tables to auto-fill information when it is available.

Once the userforms are completed, two things are accomplished. First, the information entered is used to create a Word Document inserting and formatting the necessary information. Second, an Excel Spreadsheet, Audit Tracking, is updated with the hours worked by the auditor and the estimated completion time of particular phases of the audits. The Audit Tracking spreadsheet collects the information and allows for pivot table analysis by the director about the efficiency of audits.

Final Project Excel File:

Final Project Write-Up:

No comments:

Post a Comment

Blog Archive