Budget Reconciliation for NUFinancials

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

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

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

• Adjustments appear on account 40702.

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.

NUFinancials AR Item List

Invoices and payments by contract nbr (Award ID)

GM045: Total Payments Applied

• In general, payments build to the award

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, ChartField1–SUSP
  • 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

NUFinancials Ledger Inquiry

• Suspense transactions are also visible in NUFinancials

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”. 

Drill to Tuition Detail on the GL008

• 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

SC027 – Match Exception Report

Match Process

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

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

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.

SC028 – Voucher Activity

• Monitor online vouchers in aggregate.

• 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

Internal Charges on the GL008

Knowing the transaction description is key. 

Actuals Journals

Journal Resources

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