University Student VBA Projects: Automating an Accounting Process

Purpose

The goal of this project is twofold: (1) Provide a link between an online sales database and the accounting system. (2) Using information retrieved from those databases, create, format and print payment statements to accompany monthly checks sent to merchants.

Company Background

The selected company is an online deal site similar to Groupon and Living Social with a main focus on the Utah market. Just prior to my employment at the company, it was acquired from the former owners by its primary creditor due to solvency issues.

The business model is simple (1) customers purchase discounted deals on the website and present the certificates to the merchants to receive the products (food, spa treatments, carpet cleanings, etc.). (2) Each month, the company sends a portion of the sales proceeds to the merchant to compensate them for the services offered. This payment is calculated based off the number of sales and a predetermined split per sale. All of these transactions are processed and stored on the website.

Problem

All transactions are tracked in an online database maintained by a third party, and the only access to this data is through preexisting web reports which are then manually transferred to the accounting system. This results in the accountant manually entering hundreds of entries each month. Past turnover has resulted in inconsistent treatment of monthly entries as well. Overall, the manual process resulted in a time consuming process that only muddied the economic picture due to inconsistencies. It can only be imagined that the poor state of the accounting information system was a contributing factor in the solvency issue faced by the former owners.

Solution

In order to automate the process as much as possible, I have create procedures in MS Excel which process the reports from the web portal, perform the necessary manipulations and calculations, and saves the output in a format and file type that can be updated to MAS 500, the accounting system. This includes writing at least one procedure for each of the following process:

Creating new merchants in the accounting system
Calculating cost of goods sold and inputting vouchers into the accounting system
Inputting debit memo vouchers for returns
Calculating and recording any sales/ cost of goods sold discounts
Creating statements to send with the monthly payments to vendors.

For purposes of this project, I have chosen to focus on the new merchant process as well as the creation of the monthly payments statements.

The new merchant process extracts the merchant information for all items sold in the current month and compares it to existing vendors in the accounting system. If it is unable to match the information, it allows the user to match the merchant to an existing vendor or to mark it as a new merchant through the use of the same user form. The procedure then uses this information to create and populate an uploadable file, which the user then uploads into the accounting system to create the new vendors.

To create the monthly payment statements, a different procedure compiles sales, refunds, and discounts information from the current month as well as prior months. This information is grouped by deal, month and subtotaled by merchant. Payment information is grouped by payment week, and the weekly subtotal by merchant equals the amount of the check scheduled to be mailed on that week.

Once the information has been compiled and sorted, the totals can be automatically compared to the scheduled payments in the accounting system. After differences have been researched and corrected, the user can then use the procedure to format the statement in an aesthetically pleasing format.

Finally, the statements are printed by the procedure in the same order that the checks will be printed in order to allow easy matching and mailing of both in the same envelope. In addition, a PDF of each statement is saved to a network location and unique subfolder based on the merchant name. This allows for easy retrieval in the event that the customer has a question about their statement or payment amount.

Result:

The results of this automation process including procedures not outlined in detail are also twofold (1) Time savings were approximately 56 hours. A reduction from 60 hours to 4 hours with the majority of the existing time aimed at resolving quality assurance and accuracy issues which are flagged by the processes. (2) Increased consistency resulting in decreased merchant inquiries. Merchant inquiries decreased from 3-4 inquiries per day with 3 or 5 payment corrections each month to 2-5 inquiries per month and no payment corrections each month.

Download the following files for a detailed overview and to run parts of the Macro yourself:

University Student VBA Projects

Wednesday, April 16, 2014

Automating an Accounting Process

1 comment:

Blog Archive