The PSAM Reconciling tool was created using Microsoft Access and is a database used to reconcile data between the PeopleSoft Asset Management (PSAM) module and the PeopleSoft General Ledger (GL).  The reconciliation includes the analysis of asset cost, accumulated depreciation, and current year depreciation expense for each class of assets, including Buildings, Equipment, Improvements, and other depreciable assets for the Boulder campus.  The PSAM Reconciling tool has a user-friendly interface where the user selects the current fiscal year, accounting period, and User/Run ID for identifying which PSAM tables to run the reconciliation against.  Two buttons execute a series of queries that pull, compile, and summarize data from several CIW tables.  The third button executes a string of VBA code that exports the data into an Excel workbook.  The VBA code creates several tabs, including a summary tab which acts as a dashboard to identify variances at a high level; a separate tab for each asset class with the comparison of the GL balances to the PSAM balances by FOPPS; and a detail tab with details for each of the 25,000+ PSAM asset IDs.  The VBA code formats, sorts, and subtotals each tab for easier viewing and analysis.

HOW DOES THIS IMPACT THE UNIVERSITY?

Reconciling the PSAM data to the GL is an important internal control over accurate financial reporting of CU’s depreciable assets worth over $1.2 billion and related $70 million in annual depreciation expense.  Each month, there is inevitably one or more assets that create a variance between PSAM and the GL due to the financial mapping associated with the asset or due to the processing of a transaction by PSAM that differs from what Property Accounting would expect from the system.  The PSAM Reconciling tool increases the efficiency of Boulder’s Property Accounting group by greatly reducing the amount of time required for the reconciliation, and allows us to identify assets which have a variance between PSAM and the General Ledger and quickly make any corrections.  It also allows us to continue learning the intricacies of PSAM and put appropriate processes in place to increase the accuracy of our asset data. 

An example of the time savings for the Boulder Property Accountant occurred in fall 2012 when there were variances identified between PSAM and the GL in cost, accumulated depreciation, and current depreciation.  The previous process used to identify the assets causing variances was to merge two large datasets in Excel, using different colors to identify the source of data, and then use Excel tools to attempt to find the asset ID(s) that were causing the variance.  The Excel spreadsheet was over 50,000 lines and had to be tediously scrolled through in order to identify the source of the variance.  Using this method, it took approximately 40 hours to identify the variant assets.  Using the PSAM Reconciling tool allowed the Property accountant to identify the variant assets in less than 3 hours.

IMPLEMENTATION STATUS:

The tool was created and began being used by the Boulder campus in April 2012 to reconcile the initial PSAM conversion data.  Beginning in FY13, the Boulder campus started using the tool to perform monthly reconciliations and adjustments.  The tool was shared with the UCD Finance Office, who tailored the database for use on the Denver campus and has been using the tool since January 2013.

Submitted by Laura Ragin on behalf of Stefanie Furman, Accounting and Business Support, University of Colorado Boulder