Automate Report Generation Project

Introduction

This project is based on a real business problem that is related to an accounting task at College of Fine Art Center at BYU. Every month accounting manager will need to analyze reports generated from TotalTrans, which includes all income and expenses from the beginning of the year to the date of the completed month. Because this report includes so many transactions (more than 3000 transaction as of September 30, 2011), performing an analysis involves many procedures to break down the transactions into each unit, and then each account and corresponding individual subclass. Most of the procedures are routine, having one click or as few clicks as possible to generate the report will be very effective.

Executive Summary

The purpose of this project is to break down all transactions by “Operating Unit”, “Account”, “Subclass”, and lastly by “Month”. Operating Unit is department; Account is the category of transaction (such as supplies, rental expense, salaries); Subclass is the separation of discretionary budgets, faculty research funds, or special project expenses.

The user can specify what operating unit, account, subclass, or month for the report, it will then automatically generate a chart to show the profit and loss for the specific unit and calculate the subtotal for the report. A userform has been created to allow user input search keywords and to allow choosing “Quick Find”, “Quick Totals”, and “Generate Report”. The code that was written is flexible because many functions are created to allow user to modify more easily. This is expected to save about 6-10 hours monthly for the analysis of the TotalTrans. Because of flexibility of the code, most of them will be applicable to use in other reports generated monthly; therefore, with consideration of all other reports and this TotalTrans report, it is anticipated to reduce approximately 10-15 hours per month for generating reports.

http://files.gove.net/shares/files/11w/kc241/Final_Project_Documentation.pdf

http://files.gove.net/shares/files/11w/kc241/Final_Project-Final.xlsm

P.S. Most of the information are modified and replaced with other incorrect data.