Sponsored Chart String Reconciliation

Friday, April 22, 2011 WCAS-McC-VPR Centers Research Administration

Why reconcile?

• • • • • • Keep PI(s) informed on a monthly basis Monitor spending rate (over or under) Review charges to ensure they are allowable, allocable, consistent, reasonable Track cost share revenue and expense Review status of charges (requisitions are received, encumbrances are resolved, payroll not in suspense, journals are processed) Plan for future expenditures not encumbered (stipend, tuition, post doc salary and fringe benefits, PI summer salary, PI academic year salary)

Applications and their Uses

NU Reference Tools for Reconciling

•

PROJECT CAFÉ (reconciling your budget quick reference guide):

http://cafe.northwestern.edu/documents/training/gl/723/72 3QuickRefGuide.pdf

•

FINANCIAL OPERATIONS (budget reconciliation process):

http://www.northwestern.edu/financial-operations/policies procedures/purchasing/BudgetReconciliationProcedures.pdf

•

ARSP (principal investigator certification checklist):

http://www.northwestern.edu/asrsp/PI_exp_cert_check.pdf

•

AUDITING (monthly assessment checklist):

http://www.northwestern.edu/auditing/how/index.html

Café- Quick Reconciliation Guide

• • Guide of “how to’s”: Manage, track, and correct transactions – – Requisition, PO, voucher, expense report Sponsor invoices and payments – Expense Reports and expenditures – Journals, pcards – Payroll suspense http://cafe.northwestern.edu/documents/training/gl /723/723QuickRefGuide.pdf

Financial Operations- Budget Reconciliation Process – FAQ’s

1. Should every line of the budget statement be reconciled? – A budget reconciliation process should be established to ensure that transactions are authorized, accurate and complete. – Due to the complexity of some operations, materiality thresholds can be established to ensure that transactions are reviewed, at least within the 90-day timeframe.

FAQ’s (cont)

2. How does a department accomplish reconciliations for multiple chart strings? – Divide the reconciliation process by account code, chart string manager or chart string to distribute the responsibility.

3. How long should budget statements and reconciliations be maintained? – Budget statement documentation (electronic or hard copy) should be maintained for 8 years.

FAQ’s (cont).

4. How does one involve the chart string manager in the reconciliation process? – Monthly, the chart string manager should be provided the reconciliation and the actual Cognos report for review and sign-off on the reconciliation. – The sign-off could be electronic or a wet signature.

5. When are my budget statements available? – The monthly closing calendar is available on the Financial Operations web site.

**

note that the GM045 will show inconsistencies prior to close

http://www.northwestern.edu/financial-operations/policies procedures/purchasing/BudgetReconciliationProcedures.pdf

ASRSP Certification Checklist

• A-21 Principles: – Allowable; Allocable; Reasonable: Consistently applied – Review Include: • Effort – Maximus • Payroll- Vista • • • • Expenses- GL008 & source documents Cost Sharing- Fulfilled F&A-Appropriate Program Income-Reported http://www.northwestern.edu/financial-operations/policies procedures/purchasing/BudgetReconciliationProcedures.pdf

http://www.northwestern.edu/auditing/how/in dex.html

Upcoming Updates to the GM045

• • • • • New column “F” for expenses that are budget-checked but not yet posted to the general ledger. F&A will be calculated for these transactions.

Ability to drill to the GL008 from the GM045 GCFA name (ASRSP Grant Administrator) added Budget line attributes will show if a budget line is closed or restricted to the budgeted amount.

Restricted budget amounts removed from direct cost subtotal to provide a more immediate view of direct cost balance available for expenditure.

Departmental Perspective- Life Sciences Overview

• • • • • Created a practical report, reviewed and fostered discussions between the faculty and LSFS staff Analyzed operational structure (revamped) and optimized resources Overall benefits from our reconciliation approach LSFS Reconciliation Report Package (hand held visuals for readability) Summary/Suggestions

Created Practical Report with Faculty

– Formed a faculty reconciliation committee – Obtained useful information from their perspective regarding report content – We created the design layout together – They enlightened us of their needs/wants as services from LSFS to allow them to minimize their efforts on administrative work and maximize their efforts on research – The process created advocates on behalf of the LSFS. Stressed to colleagues the importance of creating a relationship/collaboration process with their designated go to person

Analyzed Operational Structure and Optimized Resources

– Realized the reconciliation process of chart string(s) is not a one person task – Optimized resources to deliver acceptable reconciliation report(s) – Created as many helpful tools to minimize confusion and mistakes – Maintain an electronic repository to collect and house information (personnel, visas, purchases) pertaining to chart strings

Analyzed Operational Structure and Optimized Resources (cont.)

– Utilize PeopleSoft system to generate reports and create adhoc reports – Link responsibilities of individuals for greater checks and balances – Set an expectation for everyone to analyze their processes/tasks as they are performing them (helps with time management, critical thinking, process improvement and self empowerment)

Benefits from Life Sciences Reconciliation Approach

– – – – – – Buy-in from faculty (approximately 80%) to participate and take ownership of their financial responsibilities – Greater perspective/understanding and review of their financial status Relationship and respect building Improved communication (verbal and written) Greater appreciation for the services being provided Scheduled quarterly meetings with faculty Monthly financial overview with department chairpersons

Life Sciences Report Package

– – – – – Monthly reconciliation report Back-up documentation also provided to faculty Optimize format of faculty summary Follow-up/Key findings Report Start preparing for the next month reconciliation

Dr. Gregory House Chartstrings

Grant Accounts Agency/Project End Date NIH / 2-28-2013 NRSA / 5-31-2014 NSF / 7-31-2011 American Cancer Center /10-31-2012 Searl Funds / 4-30-2015 HHMI / 8-31-2012 FUND 620 610 610 650 650 650 PeopleSoft Chart String DEPT. ID 4012345 PROJECT 60004321 ACTIVITY 01 4012345 4012345 4012345 4012345 4012345 60004322 60004323 60004324 60004325 60004326 01 01 01 01 01 Name/Description Discretionary Gift Endowment Start-Up Cost Sharing Released Funds Discretionary Accounts FUND 171 320 430 171 190 171 PeopleSoft Chart String DEPT. ID 4012345 PROJECT 10004321 ACTIVITY 01 4012345 4012345 10004322 10004323 01 01 4012345 4012345 4012345 10004324 10004325 10004326 01 01 01 Updated 4/20/11

Examples

Life Sciences- Summary and Suggestions

– – – – – Buy-in from faculty Analyze your resources (if necessary, restructure position responsibilities, automation) Optimize format of faculty summary Individuals have to be trained to analyze the data Request and embrace constructive feedback to foster open communication

Departmental Perspective- Chemistry

• • Based on PI preference, each PI receives a hard copy and/or electronic copy of the monthly reconciliation via mail and/or e-mail with a copy of the monthly GM045s, GL005s, and GL008s. – Electronic copies sent via e-mail include notes or areas of concern related to reconciliation.

– Hard copies sent via mail include a memo with notes or areas of concern related to reconciliation.

Monthly meetings are set up with PI, Research Coordinator, or both.

Chemistry Reconciliation Process

• The monthly reconciliation spreadsheet includes: – Summary of Accounts: This is a summary of the PI’s grant and discretionary chart string account balances by project.

– Current Personnel and Salary: This is a summary of salary expenses, encumbrances, and tuition charges for all student employees and research staff.

Chemistry Reconciliation Process

• • Review GM045, GL005, and GL008 statements at month close to verify all expenses and encumbrances are accurate. – Ensure the expenses are allocable, allowable, reasonable, and consistent.

– Confirm payroll expenses and encumbrances are accurate based on funding entered by reviewing Chemistry paytable, VISTA reports, and/or GL008 drill down. Enter revenue, expense, encumbrance, payroll, and tuition data from financial statements into each PI’s reconciliation spreadsheet by project.

Chemistry Reconciliation Process

• • • Adjust balance and manually add additional encumbrances and expenses by project as necessary, such as: – Tuition Encumbrance - GM045 does not encumber tuition past current quarter – Funding changes to graduate student stipends, post doc salary, and fringe that affect the current month total expenditures and encumbrances (i.e. suspense) Finalize spreadsheet by confirming adjusted balance, direct cost balance, and spending rate.

Point out areas to PI where there might be issues.

Summary of Accounts Spreadsheet

*Note: Personalization to PI in PI Reference column

Current Personnel and Salary Spreadsheet

• The Current Personnel and Salary spreadsheet includes: – – Student or Research Level Employee Name Appointment End Date – Chart String where appointment is funded – Salary/Stipend amount month by month based on funding entered – Quarterly tuition amount for students based on entered and accepted data in SES

QUESTIONS?