University Student VBA Projects: Tiffany Yeates VBA Project

Tiffany Yeates

MBA 614/IS 520

Final Project

Executive Summary

I work in the BYU Grants and Contracts Accounting office as an accounting clerk. Our office accounts for and monitors the expenditures on research grants and contracts at Brigham Young University. These grants and contracts have a variety of different sponsors, including multiple federal and state government agencies, large corporations, non-profit organizations, and individual sponsors. Part of our function is accepting payment for expenses on research projects. With several governmental agencies which are sponsors, the payment method is through what is called letter of credit (LOC). Usually we receive payments for multiple projects in a one or two lump sum from the agency per month. At the end of each month a reconciliation of the payments received, expenses, claim on cash account, account payable, and accrued payroll, to make sure that the correct amount of money was ordered and received. This information is found using reports that are saved each month and forming pivot tables from these reports and then inputting the information into a reconciliation sheet. Additionally, we have an excel file that the assistant manager updates when payment is received, that information is also pulled from. Two reports from each agency are used, and three pivot tables are analyzed. One pivot table shows the all the expenditures from various projects under each agency sponsors. Another pivot table looks at each project operating unit, its close date, and the amount charged to that project during the month. These first two pivot tables are printed out and submitted to the office assistant manager for review. The third pivot table shows the amounts in the claim on cash account, accounts payable, and accrued payroll for each project. Ultimately, when comparing the balance using payments and expenditures to the amounts from the third pivot table, these sums should reconcile to zero. The system I built automates these entire system, forming the pivot tables, pulling the information and placing it in the correct location on the reconciliation sheet, printing the appropriate pivot tables, and display a message whether the it reconciled or not.

Implementation

To utilization my automated reconciliation the user clicks the LOC tab, found to the left of the developer tab and clicks the LOC Reconiliation tab.

I created a userform that allows the user to input information about the month they are reconciling. This information in then utilized in the code to fill information in the appropriate places. Also on this userform are separate buttons for each agency that must of the LOC reconciled. Once a button is clicked it cannot be clicked again, this prevents a user from clicking and reconciling one of the agencies twice.

Payment. For each agency reconciliation the code opens the file that contains payment information for each agency. Information input in to the userform is used to find the correct month sheet in this workbook. This is what each month sheet looks like:

The payment for each agency is listed in the row of the day it was transferred and in the appropriate column. I created a loop to look through each row of each column to find a cell that was not empty and contained a payment. This payment was assigned to a variable and the number of the transfer date in the same column was also assigned to a variable. The reconciliation sheet simply lists the days of each month not formatted as a date, under a month header.

I used a previous loop to find the starting row of the month that is being reconciled, by using the string that was input in the userform to find the cell that contains that same strings. The loop assigned the row number to a variable.

For y = 1 To 550

If LocRecon.Sheets("DHHS").Cells(y, 2) = month Then z = y

If LocRecon.Sheets("DHHS").Cells(y, 2) = month Then Exit For

The loop that was used to find the payment in the payment file takes the payment variable and uses they day variable to match the payment to the cell, next to the correct corresponding day. The variable with the row number is used in this loop to limit the loop to the rows that occur within the 30 rows below each month header row. In the reconciliation sheet no two agency sheet have months that start on the same row as another sheet. So the loop to find the row of the month header is used under each agency payment subroutine procedure, as is the loop to find the payments made for the agency during the month and assign them to the correct cell in each agency worksheet.

Expenditures. In another subroutine procedure, my code opens one of the reports and creates a pivot table from the data. This pivot table shows the expenses incurred listed by the date they were incurred. Similar to the payment loop, I created a loop to look at this pivot table and take the expense for each day and assign it to the correct cell in the reconciliation sheet. Unlike the payment file where the days are listed according to the day of the month, not date, the pivot table shows an actual formatted date. I used the split function to separate the day of the month from the month and year. The day was then assigned a variable that could be matched up to the day listed in the reconciliation. The images below illustrate how the format of how days are listed differs between the pivot table and the reconciliation sheet.

I copied the pivot table to another sheet with the variable name printpage. This sheet would be printed later. Once the pivot table was copied, I cleared the pivot table and assigned new fields for the rows. Subtotals were removed from this pivot table and it two was copied to printpage. Rows in the second copied pivot table that contained operating units that had closed prior to the month close date are highlighted yellow, and a title of the agency and month and year were input to the top of the page. A subroutine called printPivots is called and a message box asking the user if they want to print appears. If yes is clicked, the sheet is print so that it fits on one page.

Debits and Credits Pivot. I created a subroutine procedure that opens another report from each agency. A pivot table is created that shows each project operating unit and the associated claim on credit, accounts payable, and accrued payable account balances. Some of the amounts in the pivot table are incredibly small fractional amounts that are essentially 0, because I want to only include balances from operating units that are not 0, I wrote the procedure to change these incredibly small amounts to have the value 0. A loop then looks through the chart to find not-0 balances and put them in the correct corresponding column in the reconciliation sheet. Once all of these are put in, a message box appears to state whether the reconciliation for the month was reconciled to 0.

Learning and Difficulties

One aspect I learned about a lot through this project was utilizing pivot tables. Additionally, I learned more about how to format cells and how to print using VBA. One of the greatest difficulties for me in this project was developing a way to make sure that information from the pivot tables were being placed in the correct row in the reconciliation sheet. The reconciliation sheet has about 500 rows and it is essential that the information from the pivot tables is places in the rows that are for the specific month’s reconciliation. I also had trouble with moving information from the pivot table that deals with the claim on cash, accounts payable, and accrued payroll. Sometimes all of this accounts show up in the pivot table, sometimes only one or two do. I had to allow for my code to adapt and still get the account balances in the correct column on the reconciliation sheet. One item I had wanted to include was a way to download the reports from PeopleSoft. However, as discussed in class getting reports to download from Internet Explorer is tricky. Getting the reports requires navigating through PeopleSoft and using a query to find and download 12 separate reports. I spent many hours searching through internet discussion after internet discussion, and still could not understand how to implement what I needed in my code. I ultimately decided to leave this out of my code.

Assistance

The only assistance I received on this project was from my good friend Google and its search results and our VBA textbook.

University Student VBA Projects

Thursday, December 10, 2015

Tiffany Yeates VBA Project

No comments:

Post a Comment

Blog Archive