THIS INFORMATION IS FOR INTERNAL USE ONLY. PLEASE CONTACT THE APPROPRIATE OFFICE FOR GENERAL SUPPORT.

Monthly Reconciliation

The reconciliation process in the Division of Student Affairs and Office of the Provost is a crucial monthly measure to ensure the accuracy of journal transactions each month. Your finance liaison will ask you to perform a series of steps after the previous month closes (6-8 days after the last day of the month) to complete this process.

Below is a detailed step-by-step guide to completing the monthly reconciliation process. For additional helpful tips and reminders, refer to our Financial Checklist and Accounts Cheat Sheet.


Access Reports

Start at launchpad.case.edu and click on Faculty/Staff. You may be asked to login with your CWRU network ID and password.

5 3


Under Peoplesoft Financial Reports, expand the UGEN link and look for your OPR number (the SpeedType for your department). In this example, we will use the SpeedType for the Division of Student Affairs.

Under your OPR, find the link that has the following: YYYY_MM_DD_incexp_opr_dtl_XXXXXX.xls. (In our example, it is 2016_06_30_incexp_opr_dtl_221332.xls.)

Click on it to download the corresponding report as an Excel spreadsheet.


Preparing the File

When you open the downloaded file in Excel, you will notice there are three sections: Salary and Fringe, Non-Salary and Income. If needed, you can download a list of account numbers (PDF).

In the section below (Salary and Fringe) you may see green arrows to the left of the account column. Select the most upper left cell with a green arrow and highlight from there to the farthest bottom right cell to highlight as much area as possible, then click the caution sign and select “Number Stored as Text” to change the format to a numerical one.

4

1


Once you have completed this process, delete extra spaces so you only have all transactions as shown below

2



Then, highlight every transaction (all columns and lines with text/numbers) and copy and paste them to your reconciliation document (query tab).

Sometimes transactions have dates that are in a previous month when you download them. That is not a problem; an example might be a purchase requisition that was submitted in late March but didn't get paid until April. The transaction will show up on the April statement with a March date. 

We recommend copying and pasting the date into the column next to the date column and then putting in a date (April 1 20XX) that corresponds to the first of the month (April) that was downloaded. An example is below: 

Sample row
Once all the dates are filled in with the correct corresponding month, download a copy of your monthly transactions and compare the following numbers to your reconciliation document: 

  • Monthly Total Salary & Fringe
  • Monthly Total Non-Salary
  • Monthly Total Direct Cost
  • Monthly Total Revenue
  • Monthly Total Expense and Revenue
  • Year-To-Date Total Salary & Fringe
  • Year-To-Date Total Non-Salary 
  • Year-To-Date Total Direct Cost
  • Year-To-Date Total Revenue 
  • Year-To-Date Total Expense and Revenue
  • Remaining Final Balance 

If any of these are off, make sure that you copied and pasted the correct number of rows.


What to look for:
Charges Done in Error

Charges that were done in error should be noted with the event code “JournalOFF” and a correction journal should be made within 2-5 business days of downloading the reports. If there is any confusion please contact your Finance liaison.

Blank Event Codes

Event Codes are used as individual budgets to keep track of how much you are spending on a particular item or event. Each month, departments within the entire University are adopting the practice of adding event codes for better analytics of spending and to easily reference transactions. If there are blank event codes please fill them in once you download the report.

*It is recommended that event codes are filled in on the front end when doing any kind of PeopleSoft type transaction that you are doing for your department. It is best practice if you are expecting income, internal support or an expense from another department that you email them before with the detailed information including your event code.

Default transactions with the Account Number 531990 & “Online Transaction Default”

PCard transactions need to be reconciled in J.P. Morgan Chase. These need to be reconciled by the 27th of every month. If the reconciliation is not done you may get the default account. These transactions need to be changed because the system did not have an appropriate account number listed. Therefore it needs to be moved from the “unknown default account” to a known one for audit and financial reporting purposes. If you have transactions with 531990 or 539900 you need to journal them to the correct account location within a week.


Final Steps

Once you have reviewed for errors, blank event codes and default transactions, email the completed Excel sheet to your Finance liaison. They will upload your transactions into the master reconciliation sheet for your department where they can give you up-to-date information on spending patterns and budget remaining.