Final Project: Automated Invoice Calculation and Delivery System (Lowell Smith)

Executive Summary

Purpose of this project:  Create an automated system for extracting from our DB the services provided in a given month, adjusting the pricing according to the CPI Index, creating individual invoices and detailed reports for each customer, providing summary files of revenue and services for accounting and the executive team, and delivering the invoices to our customer contacts.  When done with the click of one button, this will save our company money by significantly reducing employee involvement, the likelihood of manual errors, and consequently strengthening the integrity and professionalism of our company from a financial / billing perspective. 

Company:  University Health Resources (“UHR”) is an advisory firm that provides guidance to its customers throughout the United States.  They are a real company, and their name has been changes. For this project we will assume they have contracts with 14 highly reputable universities.  A university such as BYU calls upon them to provide counseling in handling specific problems that occur in their day-to-day operations.  UHR is a leader in these services yet are still very much a small business.  Executives see no need to upgrade their rudimentary financial system, and as a result their billing and in-house accounting is done manually through Microsoft Excel.  This lack of desire to purchase financial software presents multiple problems:

·        Pricing structure.  When UHR contracts to provide services, they determine a service rate per case handled.  The rates for each university vary depending on how well their negotiations went during the contracting period.  After determining a rate, the price adjusts annually based off the Consumer Price Index (CPI).  UHR has not established a procedure for updating this information.

·        Manual data extraction.  When invoicing occurs each month, UHR manually copies case data from their Microsoft Access DB and sorts and filters for each client.  This manual process is lengthy, laborious, and ripe for user error, sometimes resulting in billing a client incorrectly by thousands of dollars

·        Invoice delivery.  As each monthly invoice is calculated, it is sent to the client via email.  An employee in UHR’s Accounting department types out each invoice by hand and checks to guarantee that it is sent to the right contact and that the relevant fields on the invoice (CPI-adjusted price, case volume, and client contact information) are manually entered.  As they work with over 1,000 clients in actuality, it is clearly cumbersome, and errors in pricing and accuracy are normal.  More and more customers are asking for detailed reports to accompany their monthly invoice so they can see just what services were provided.  Creating this report manually is laborious.  Customers are also frequently billed at wrong rates and must be sent revised invoices.  Similarly, customers with low case rates occasionally are billed at higher case rates and call UHR upset they are being billed incorrectly.  This lack of professionalism does not foster trust in UHR’s services.

One of my goals in working with UHR was to see how VBA can improve their billing procedures to avoid such catastrophic mistakes.  If this system can be streamlined, they will save significant employee expenses on a monthly basis, improve their accuracy, as well as their corporate reputation.