Budget Reconciliation for NUFinancials

This overview explains what budget reconciliation is, how to do it, and what resources are available to assist.

For every chart string, we can say:

  • Chart string has spending authority
  • Spending fits the purpose of the chart
  • Available balance is string.
  • Spending rate is ok.

For every transaction, we can say:

  • We recognize that.
  • It was the right thing to do.
  • It was done correctly.
  • It was complete.
  • It was categorized correctly.
  • It was timely.

Essential Budget Reconciliation Reports

  • Financial Summary and Budget Summary reports in Cognos enable you to monitor balances.
  • GL068 – chart string balances
  • GL005 – chart string and account balances
  • GM044 – balances per sponsored project in your department(s)
  • GM045 – financial summary with balances per chart string and account

Financial activity is detailed in the GL008.

  • Transactions sum to the same amounts shown on summary reports.

Using the GL008 for Reconciliation 

  • Use the GL008 in Cognos Connection
    • In period or Year-To-Date (YTD) mode with the GL005
    • In YTD mode with the GM045 to match grant Year-To-Date summary
  • Use the GL008 Ad Hoc Express in Query Studio
    • Over multiple years or with a custom date range to match grant Life-To-Date summary
    • Access to Query Studio is by manager approval
  • Export to Excel for further analysis

Northwestern IT Role in Reconciliation

Problem Solving

  1. Identify issue
  2. Research issue
  3. Determine resolution
  4. Take action

Reconciling Revenue

  • Monitor performance on the GL005
    • Budget versus actual receipts to date
  • Monitor transactions on the GL008
    • See receipts by chart string and account
    • Match GL008 to Cash Receipt Ticket (CRT)
    • See incoming revenue adjustments due to:
      • Miscounts, bad checks, disputed charges

Compare GL008 and CRT

recon01

  • CRT 14992 on 8/25 - late fees from camping gear rental 

recon02

  • CRT 14992 on 8/25 -game room revenue from billiards

recon03

  • Adjustments appear on account 40702.

recon04

Accounts Receivable

  • NUFinancials AR functionality is used with grants where sponsors pay in response to an invoice.
    • Accounting Services for Research and Sponsored Projects (ASRSP) manages AR in NUFinancials:
      • Creating customer numbers for sponsors, creating invoices, and recording payments
    • Invoices and payments are listed on the Cognos GM091.
      • Because this is not “revenue” it does not appear on the GL008.
    • Invoices, paid and unpaid, appear in NUFin’s AR Item List.
    • Payments are summarized on the GM045.

GM091 Sponsor Payments Received Report

  • Invoices and payments by contract nbr (Award ID) and project
  • Deposit date, invoice, check number, and amount.

recon05

NUFinancials AR Item List

Invoices and payments by contract nbr (Award ID)

recon06

GM045: Total Payments Applied

  • In general, payments build to the award

recon07

Payroll Reconciliation

  • Monitor balances on the GL005
  • Monitor changes in salary funding sources:
    • Chart string status (inactive, closed)
    • Project start and end dates (gaps)
    • Non-existent chart strings (yet to be setup)
    • Allocation percentages (don’t add up to 100%)

Payroll Suspense

  • All employee compensation is funded by one or more chart strings (called funding sources).
  • When funding sources do not equal 100% of salary, the balance is charged to the parent department chart string:
    • Usually Fund 110, Financial Dept ID, ChartField1SUSP
    • Could be another chart string named on grant project

From MyHR to NUFinancials

  • After each payroll run, MyHR sends a journal to NUFinancials to post in the General Ledger
    • Journal is scanned for errors and corrected by Accounting Services and HR as necessary
  • After error corrections, payroll data appears in NUFinancials inquiries
  • The next day, payroll data appears in Cognos reports
    • Users with "Salary Access" can drill to employee detail

Finding Suspense Issues

  • VISTA Report – NWPAY067
    • See suspense amounts before the payroll run
  • GL008 – Revenue and Expense Activity Report
    • See amounts after they are charged to SUSP
  • NUFinancials – General Ledger Inquiry
    • Lookup amounts after they are charged to SUSP

SUSP on the GL008

recon08

NUFinancials Ledger Inquiry

  • Suspense transactions are also visible in NUFinancials

recon09

Resolving Suspense Issues

  1. Use the Employee Paycheck Inquiry
    • Get the information you need for steps 2&3.
  2. Issue one Payroll Journal per employee
    • Remove amounts from suspense
    • Journal appears with the offsetting (negative) amount in NUFinancials and Cognos BI
  3. Complete a Funding Source Adjustment
    • Ensure salary is covered by an active chart string

Tuition Commitments

  • Many students receive a commitment from the University to pay all or a portion of tuition.
  • Commitments may come from:
    • Deans or Chairs at the Department or School level
    • Professors in non-sponsored projects
    • Principle Investigators in sponsored projects
  • Typically, tuition expenses are budgeted

Tuition Reconciliation

  • Monitor balances on the GL005/GM045
    • Per chart string and account 78020/78021
    • Budget, encumbrances, paid tuition expenses
  • See detail on the GL008
    • Per chart string and account 78020/78021
    • Student names
    • Award amount
    • Expected disbursement dates

Tuition Award Process

  1. NU makes a commitment to a student.
  2. Enter tuition award in Student Enterprise System (SES).
    • SES sends nightly journal to NUFinancials
  3. See tuition encumbrances and disbursement data in NUFinancials and Cognos BI reports.
    • Encumbrances are visible:
      • next business day in NUFinancials
      • subsequent business day in Cognos

Tuition Encumbrances on the GL008

Encumbrance listed as “Various Transactions”. 

recon10

Drill to Tuition Detail on the GL008

recon11

  • Per student:
    • Amount in Fall, Winter, Spring, or Summer columns
    • Expected Disbursement Date
    • Total Encumbrance

Tuition Tracking over Time

  1. If tuition is paid
    • Encumbrance is relieved (reduced to zero)
    • Payment amount moves to expense column
  2. If tuition is unpaid
    • Encumbrance is relieved

Research Issues…

  • Cognos (all activity)
    • SC016 – Open Encumbrance Report
    • SC027 – Match Exceptions Report
  • NUFinancials (one at a time)
    • Manage Requisitions
    • Voucher Inquiry > Payment Inquiry
    • Lifecycle Viewer

SC016 – Open Encumbrance Report

recon12

SC027 – Match Exception Report

recon13

Match Process

recon14

Tolerance

  • Applied to differences between PO and Voucher
  • Each line is checked for differences:
  • Quantity x Unit Price = Amount
  • Tolerance is 25% or $100, whichever is less
  • Both Unit Price and Amount are checked
    • Difference under threshold? Payment is
    • Difference over threshold? No
Differences over threshold are called a Match Exception.

What can prevent completion of a purchase?

  • Requisition not fully approved (purged > 90 days)
  • Voucher has Match Exception
    • The order was not received (non-catalog)
      • Receipts required for non-catalog orders over $500
    • A difference in quantity between voucher and receipt
    • A difference in price between voucher/PO exceeds tolerance
      • Purchase Order may contain a human error
      • Invoice may contain a human error
      • Voucher may contain a human error

Reading Match Exceptions

  • When a voucher has a Match Exception:
    • Explanatory messages are sent to you via email
    • Explanations appear in system reports and inquiries:
      • Cognos BI SC027 Match Exception Report
      • NUFinancials Voucher Inquiry
NU makes no partial payments, so any line with a Match Exception prevents payment of the entire voucher.

Resolving Match Exceptions

  • Potential Solutions
    • Purchase Order Change Request
      • Update any field (for example, unit price and/or quantity)
      • Cancel lines or cancel the entire purchase order
    • Update Receipt
      • Add or remove a receipt
    • Request a revised invoice from vendor

Other Accounts Payable

  • Payments to employees (non-payroll)
    • Expense Reimbursements
    • Cash Advances/Travel Authorizations
  • Payments to non-employees
    • Direct Payment Requests
    • Visitor Expense Reports
    • Contracted Services for U.S. Residents

Research Issues…

  • Cognos (all activity)
    • SC026 Expense Reports
    • SC028 Voucher Activity
    • Use each report alongside the GL008
  • NUFinancials (one at a time)
    • Inquiries are available, but the reports above are more effective.

Online Vouchers and Expense Reports on the GL008

  • The encumbrance column (Pre-Enc/Enc)
    • Does not apply to Online Vouchers & Expense Reports
    • Applies only to requisitions and purchase orders
  • The Transactions column
    • Amount appears when transaction enters workflow
    • Amount disappears if Sent Back or Denied
    • Expense is settled (paid) when you also see:
      • GL Post Date on the GL008
      • Payment ID and Payment Date on the SC028

SC026 – Expense Reports

  • Monitor expense reports in Cognos.

recon15

recon16

SC028 – Voucher Activity

  • Monitor online vouchers in aggregate.

recon17

Internal Charges

  • Recharge centers (and some departments) with a high volume of internal sales use a Journal Spreadsheet to charge for goods and services
  • Charges appear on your budget statements without a review and approval via workflow
  • If you do not recognize a charge, you may dispute the charge by contacting the person directly.

Journal Spreadsheet

recon18

Internal Charges on the GL008

Knowing the transaction description is key. 

GL008 Column

Journal Indicator

Transaction Type

Journal

Transaction ID

Journal Mask

Description

Journal Source

Actuals Journals

Recon19

Journal Resources

  • Financial Operations Listserv
  • Financial Operations website
  • Monthly Closing Calendar
  • MyHR/KB training articles

For additional assistance please contact the IT Support Center at 847-491-4357 (1-HELP) option 2, or consultant@northwestern.edu.




Keywords:recon   Doc ID:111583
Owner:ESAF GL Team .Group:Northwestern
Created:2021-06-09 16:18 CDTUpdated:2021-08-06 16:54 CDT
Sites:Northwestern
Feedback:  0   0