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

Thursday, December 10, 2015

Technology Transfer Legal Payment Review and Invoicing

Executive Summary:

Background and Problem-

The BYU Technology Transfer Office deals with patenting of research of inventions developed at BYU.  In order to get these inventions and discoveries patented we deal with outside law firms to do the prosecution and we receive invoices from them on work they do for us.  In order to keep track of the different technologies that are being patented and the legal fees that are being accrued we manually input the invoice data into an Access database.  From this database we have to pull information that generates reports to show how much is being paid to each law firm, so that payment can be approved by both the licensing professionals associated with each technology, as well as BYU General Counsel and the BYU VP of Research. 
In order to develop these reports the accounting students would have to manually pull in the information from Access to an excel sheet and split each amount into categories so that the licensing professionals can review the bills related to the specific technologies they deal with.  After making these reports, having them reviewed and signed, and payment is made, the accounting students would then create invoices manually for each licensee of the technology that has contract with BYU to use the patented technology.  This has created many errors in the past, that have had to be corrected and my hope is to eliminate human error involved with the creation of invoices.

Solution


In order to solve this problem I developed a process that would automatically generate the reports for each licensing professional to review, with a chart of the amounts paid in each operating unit (each operating unit is related to a technology) and the amounts that will be invoiced for reimbursement by the licensee of that technology, if any.  The macro also created the invoices that will be sent to the licensee’s that need to reimburse our legal expenses.  In order to not have human error in the entry and tracking of invoice amounts, the macro also opens and populates a separate excel table that is connected to a table in Access, thus eliminating the need for human entry.

No comments:

Post a Comment

Blog Archive