Cognos and NUFinancials Budget Reconciliation

This guide details the types of errors that can be identified in budget reconciliation, how to find them, and how to resolve them.

What is Budget Reconciliation?

Various University offices contribute to a complete picture of budget reconciliation. 

A budget is reconciled when… 

For every chart string, we can say:

For every financial transaction, we can say:

  • Chart string has spending authority.
  • Spending fits the purpose of the chart string.
  • Available balance is ok.
  • Spending rate is ok.
  • We recognize it.
  • It was the right thing to do.
  • It was done correctly.
  • It was completed.
  • It was categorized correctly.
  • It was timely.

Office of Budget and Planning

To learn more about departmental budget reconciliation, visit the Office of Budget and Planning website and download Budget Reconciliation Procedures. 

Snapshot

A budget is reconciled when all transactions are:

  • Recognizable
  • Right
  • Reasonable
  • Reviewed 

Sponsored Research

To learn more about designing effective reconciliation processes in your department, visit the Office for Research – Planning, Finance and Communications website. A resource guide for post-award administration is available at the Sponsored Research website.

Snapshot

Additional cost principles may apply to grant budgets. Purchases must be:

  • Allowable
  • Allocable
  • Reasonable
  • Consistent

University Policies for Financial Management

Refer to the University Policies web page for guidance for both financial transactions and financial record retention.

Under the heading Financial Management, find the document Record Retention Schedule. Where the Official Repository is the “Appropriate Department” you find guidelines for your department for each type of record, and you find the duration for which records should be retrievably stored. 

Office for Audit and Advisory Services

Take advantage of training for establishing effective business operations. 

Below are contacts listed beside the scope of inquiries to which they can respond. 

Contact

Scope of Inquiry

Phone Numbers

Northwestern IT Administrative

Reporting, System Problems, Security, General

1-HELP (1-4357)

 Accounts Payable

Direct Payment Requests, Wire Transfers, Supplier Invoices

1-7339

Procurement and Payment Services (PPS)

Change PO Requests, iBuyNU Errors, Bidding Policy, Tax Exempt Status

1-8120

Financial Status

 Match Errors, CUFS Issues, Financial Operations Listserv

7-3777

Procurement and Payment Services (PPS)

Supplier Requests

1-5338

Procurement and Payment Services (PPS)

Procurement Card, My Wallet

1-5340

Accounting Services

Actuals Journals

1-5340

Accounting Services

COA Maintenance for non-sponsored projects, 1099 Forms, W-9 Forms, Stop Payments

1-5337

Accounting Services

Asset Management

1-2737

Accounting Services Transaction Support

Journal Processing, Supplier Setup, Payment Requirements, Petty Cash

1-4707 (4SOS)

ASRSP

Allowable Travel Expenses for Sponsored Projects

1-4697

ASRSP

Sponsored Project Journals

1-4237

ASRSP

COA for Sponsored Projects

1-0113

Budget Office

Budget Journal Entries

1-2812

University Services

Ask for the manager for the type of service transaction you want to inquire about

1-7569

The Northwestern IT Administrative Systems website has lists, descriptions, and exhibits of reports available to staff inside Cognos Connection, many of which are helpful during budget reconciliation activities.

Monthly Reports 

Report

Description

GL068-GL074

Financial Summary

A series of Financial Summary reports enable reconciliation at the school and unit level. The reports display all chart strings related to the department number that you request. Revenue and Expenses, both budgeted and actual, are shown with totals and subtotals by Fund and Department.

GL005

Summary Budget Status

The report displays a financial summary for one or more chart strings that you request. It answers two questions (1) what is the balance and (2) what is the net?

  • First, given the budget and actuals to-date, the balance is displayed horizontally.
  • Second, given revenue and expenses, the net is displayed vertically.

GL008

Revenue and Expense Activity

The report displays all transactions belonging to one or more chart strings that you request.

The GL008 is a companion of the GL005. It matches when run for the same chart string(s) and period. The GL008 can be run for one period or for the year-to-date.

Supply Chain Reports

Report

Description

SC016 – Open Encumbrance Report

Run the report by Budget Period, Fund, and any one of three addition elements of the chart string – Department, Project, and Account.

The report lists all open encumbrances associated with the chart string requested. Information from the Purchase Order and associated Requisition also appears.

Three columns on the report (Receiver Status, Voucher ID, and Match Status) help users understand why an encumbrance remains open.

SC026 – Expenses by Submission & Workflow Status

Run the report by any part of the Department Tree, or by Department ID. If necessary, export to Excel where you can sort by Project ID.

The report provides a view of Expense Reports that have not been paid for a given department or tree node. Two sections divide transactions into Unsubmitted/Returned Expense Reports and Expense Reports still in Workflow.

Employees are identified by name and EMPLID.

SC027 – Match Exception Report

Run the report by Department ID, and for further granularity, enter an Invoice Number, Supplier ID or Requester ID.

The report includes only Vouchers with match exceptions and displays each line on the Voucher that has an exception. (A Match Exception is a discrepancy in quantity, unit price, or amount when the Voucher is compared to the PO and Receipt. Thresholds of $100 or 25% apply.) Two columns name and describe the Match Exception rule that applies to the line in question.

SC028 – Vouchers Report

Run the report by Department ID and date ranges. Specify Payment Requests Regular Vouchers, or All.

The report displays all Vouchers matching your criteria with Workflow Status, payment information, and the transaction originator.

The Description column on the GL008 displays useful information about the transaction but requires some interpretation. The field has a 50-character maximum with parts that vary per the source transaction. Use the table below to interpret the field. 

If the source transaction is…

Then the Trans- action

And the Description column displays…

Requisitions

Requisition ID (REQ#)

Supplier Name / Item Description

Purchase Order

Purchase Order (PUR#)

Supplier Name / Item Description

Expense Report

Expense Report ID

Employee Name / Description from the Detail tab

Voucher

Voucher ID

Supplier Name / Invoice # / Item Description

Payment Request

Voucher ID

Supplier Name / Invoice # / Item Description

 

Journals

 

Journal ID

Journal Source/ Reference/ Journal Line Description Common actuals journal source codes

  • AGY = Agency Journal
  • BAL = Balance Sheet Journal
  • COR = Correction Journal
  • CRT = Cash Receipt Ticket
  • TRF = Transfer Journal
  • ISJ = Internal Sales Journal
  • ITN = NUIT
  • ASC = Accounting Services
  • RCG = Recharge Journals
  • UVS = University Services

Transactions from Journal Spreadsheet

Journal ID

ASC / Header description field of the journal spreadsheet/ Line description field from that line of the journal spreadsheet

 University Services (mail)

Journal ID

UVS / Job # / Domestic US mail / Date of service

University Services (VWR)

Journal ID

UVS / Invoice # / Invoice date / Item ordered

University Services (travel)

Journal ID

UVS / Invoice # / Traveler name / Departure date / Airport codes

Purchase Inquiries

Task

Steps and Instructions

Run a report of all open encumbrances in Cognos

Find all open encumbrances associated with your chart string.

  1. Cognos > Public Folders > FFRA Reports > School > Supply    Chain.
  2. Click the report name SC016: Open Encumbrance Report.
  3. Select a Fiscal Year and click Next.
  4. Enter your chart string and click Finish.

Refer to "SC016: Open Encumbrances Report" reference.

 

Lookup one purchase using Manage Requisitions

Find one purchase with the Requisition ID or the Purchase Order ID. (Find them in Transaction ID column of the GL008 report).

  1. NUFinancials Requisitions tile > Manage Requisitions.
  2. Enter the Requisition ID (REQ#) or the PO ID (PUR#).
  3. Adjust Date From and Date To if necessary.
  4. Remove your NetID if the requisition was entered by someone else.
  5. Click Search.
  6. Click the triangle to display the lifespan.
  7. Click the illuminated icons for more information.

Refer to the "Manage Requisitions (Requesters)" training guide.

Lookup any transaction related to a purchase.

Find one purchase with the Requisition ID, Purchase Order ID, or Voucher ID. (Find them in Transaction ID column of the GL008 report).

  1. Go to NUFinancials > Nav Bar > Accounts Payable > Review Accounts Payable Info > Vouchers > Voucher.
  2. Search for voucher by Supplier Name and/or Dept ID.
  3. In Actions Menu, select Review Document Status.
  4. Review the lifespan, and related documents.
  5. Click View Invoice to see invoices in OnBase.

About purchase orders

Be aware that Procurement and Payment Services may combine several requisitions to the same supplier on a single purchase order.

Purchase Edits and Corrections

Task

Steps and Instructions

Edit or cancel a requisition using Manage Requisitions

You can correct a requisition before the Purchase Order is created, which occurs after the Requisition is fully approved.

  1. NUFinancials Nav bar > Manage Requisitions.
  2. Search by Requisition ID.
  3. Click Select Action > Edit Requisition.
  4. Make corrections, budget check, save, and resubmit into workflow. Refer to the "Edit Requisitions" training guide.

 

Correct or close/cancel a purchase order

After a requisition is approved in workflow, it is sourced to a purchase order and assigned a Purchase Order ID. It may be combined on the purchase order with lines from other requisitions to the same supplier. After sourcing, it is dispatched to the supplier.

It is possible to discover the need to change a purchase order after it was sourced to the supplier.

Before you change an existing purchase order, you must have the Purchase Order ID, which you can find on reports such as the GL008 and SC016 or in inquiries, such as Manage Requisitions.

To correct a Purchase Order:

  1. NUFinancials > Nav bar > Purchasing > Purchase Orders > Add/Update POs.
  2. Make changes to the purchase order
  3. Click Save. The Purchase order will route to PPS for review and approval, then to department approvals, and possibly to PPS (over $25K), ASRSP (SubK), ORS (Hazardous) for approvals. The PO will be re-dispatched to the supplier, as applicable.

To cancel/close a Purchase Order:

  1. NUFinancials > Forms PO Change Request
  2. Complete form and submit for approval.

Find the updated purchase order after your PO Changes

You cannot view PO changes in Manage Requisitions after making changes. Instead, use NUFinancial’s Purchase Order Inquiry inside the Purchasing menu.

  1. NUFinancials > Nav Bar > Purchasing > Purchase Orders > Add/Update POs. The Purchase Order Inquiry appears.
  2. Enter the PO ID (PUR#) and Search.

 

Is there a mistake on the invoice?

What if you spot an error on the supplier’s invoice?

Contact the supplier to make the correction, reissue the invoice, and send to Accounts Payable. Do not enter a receipt if the invoice is incorrect.

Is there a mistake on the Voucher?

What if you spot an error on the voucher created by Accounts Payable? Contact Accounts Payable to make the correction.

Match Exceptions and PO Changes

Task

Steps and Instructions

Run a report of all Match Exceptions in Cognos

Follow the steps below to research all match exceptions associated with your chart string. A “match exception” is a significant difference between the payment voucher (supplier’s invoice) and the purchase order.

  1. Cognos > Public Folders > FFRA Reports > Schools > Supply Chain.
  2. Click the Report Title SC027: Match Exceptions.
  3. On the prompt page, enter your chart string and click Finish.

Refer to "SC027: Match Exception Report Explanation" job aid.

 

Lookup Match Exceptions

Follow the steps below to lookup one match exception, or to generate a list to investigate one at a time. This inquiry is an alternate view of the report above.

  1. NUFinancials Nav Bar > Accounts Payable > Review Accounts Payable Info > Vouchers > Voucher.
  2. Enter a date range in the Invoice Date fields.
  3. In the Match Status field, select Match Exceptions Exist.
  4. Enter one or more other criteria:
  • Enter the Department ID in the Department field to generate a listof match exceptions in your department.
  • Enter a PUR # in the Purchase Order field to investigate one at a time.
  • Enter a Voucher ID to investigate one at a time.
  1. Click Search. If any match exceptions exist, they appear in alist.
  2. For any given match exception, click the Match Workbench icon to view the match exception reason.

Refer to the "Match Exception Query" job aid.

Correct a purchase order

Some match exceptions may be due to a mistake on the purchase order. Did you quote the wrong price? If so, you may need to change the purchase order to resolve the match exception.

  1. NUFinancials > NavBar >Purchasing > Purchase Orders > Add/Update POs
  2. Search for PO
  3. Enter changes to PO and submit for approval. Refer to the "Submit a Request to Change a Purchase Order"

Find the updated purchase order after your PO Change was approved

Use the Purchasing menu to view change requests.

  1. NUFinancials > Purchasing > Purchase Orders > Review PO Information > Purchase Order.
  2. Enter the PO ID (PUR#).

Is there a mistake on the invoice?

What if you spot an error on the supplier’s invoice?

Contact the supplier to make the correction, reissue the invoice, and send to Accounts Payable.

Is there a mistake on the Voucher?

What if you spot an error on the voucher created by Accounts Payable? Contact Accounts Payable to make the correction.

After corrections are introduced into NUFinancials, match exceptions are resolved overnight. 

Payment Vouchers

Purchase orders, direct payment requests, visitor’s expense reports, and contracted services forms all utilize vouchers to generate payments. The voucher duplicates information on the supplier’s invoice. 

Task

Steps and Instructions

Run a report of all voucher activity

Find a list of all vouchers in within your department in the Cognos report, Voucher Activity by Dept ID. The list shows voucher details, workflow status, and if applicable, payment ID and payment date.

  1. Cognos > Public Folders > FFRA Reports > Schools > Supply Chain.
  2. Click the Report Title SC028: Voucher Activity by DeptID.
  3. Enter required prompts and click Finish.

 

Lookup one or more vouchers using the AP Voucher Inquiry in NUFinancials

ALSO

Scan a list of vouchers for payment status

Find all vouchers related to your department, limited by invoice date range. The list that you create also shows payment status.

  1. NUFinancials > NavBar > Accounts Payable > Review Accounts Payable Info > Vouchers > Voucher.
  2. Confirm that Business Unit "NWUNV" is entered and enter Supplier SetID "SHARE."
  3. Enter an Invoice Date range and Department ID, and Search.

Lookup a Payment Request in NUFinancials

Find the original voucher in NUFinancials using the Voucher ID from the GL008 report. You can find status, payment, and all detail.

  1. NUFinancials NavBar > Payment Request Center > Find Existing Value.
  2. Search by Submitter

View PO. Refer to "View Payments to Suppliers & Non-Employees".

Expense Reports

Because of the large number of people involved in expense entry and approval, expense reports are a common issue during budget reconciliation.

Task

Steps and Instructions

Run a report of all expense report activity

Find a list of all expense reports and workflow statuses in your department or school in the Cognos expense reimbursement report.

  1. Cognos > Public Folders > FFRA Reports > Schools > Supply Chain.
  2. Click the Report Title SC026: Expenses by Submission & Workflow Status.
  3. Enter required prompts and click Finish.

Refer to "SC026 - Expenses by Submission & Workflow Status"

Find an expense report

You can find an expense report using the Expense Report ID found on the GL008 or SC026.

  1. NUFinancials > Expense Report tile > My Expense Reports

Find an expense report using someone’s name or Employee ID

You can find all expense reports for a given employee with the employee’s name or Employee ID.

  1. NUFinancials > Expenses tile > My Expense Reports > change to the other person with drop-down menu in upper left corner.

Create or modify expense reports for yourself or for others for whom you are a proxy

To find or create expense reports in NUFinancials for yourself or for someone else for whom you are proxy, you may navigate through Expenses tile.

  1. NUFinancials > Expenses tile > MyExpense Reports. You can switch to others whom you create reports for with the drop-down in the upper left corner (by your name)
  • You can modify expense reports in Pending, Saved, or Sent Back status.

Refer to "Expense/Advance Payment Status".

Correct an expense report prior to approval

If the expense report has not been approved, there may be issues with the report itself or missing documentation.

  1. NUFinancials Expenses tile > My Expense Reports
  • If there are errors on the report, the approver must “Send Back” the expense report. Then you can make changes and re-submit the expense report for approval.
  • If there are missing documents, scan the originals and store them in a directory on your computer. Then, just as above, edit the sent back expense report and attach the scanned electronic documents. You may keep paper originals but do not send paper to Accounts Payable.

Deposits / Cash Receipt Tickets (CRTs)

Deposit transactions are accessible to view and correct from Finance & Budgeting tab of the NUFinancials portal. 

Task

Steps and Instructions

Monitor revenue from the CRT on reports

Find a summary of revenue by chart string and account on the GL005. Find revenue transactions by chart string on the GL008.

  • Cash Receipt Tickets appear under the account named on the CRT.
  • The CRT number is in the Description column of the GL008. Use this number to lookup the CRT in NUFinancials.
  • The Journal ID associated with the CRT is in the Transaction ID column. Note! The Journal ID starts with the three letters C-R-T for easy recognition. Use this number to correct the chart string, if necessary, in a Correction Journal.
  • Adjustments to revenue made by Depository Services, the bank, or credit card companies also appear on the GL008.

 

Lookup one or more CRTs using the employee NetID

You can view any Cash Receipt Ticket in NUFinancials using the CRT number or an employee NetID.

  1. NUFinancials > NavBar > NU Interfaces and Processes > Cash Reciept Ticket.
  2. Select the Find an Existing Value tab.
  3. Use the NetID of the person who entered or committed the CRT in the User ID field, if you do not have the CRT number.
  4. Click Search.

Refer to "Receiving and Depositing Revenue."

Sponsor Invoices and Payments

Some grants and research projects collect funds by billing the sponsor upon completion of milestones. 

Task

Steps and Instructions

Find a Sponsor Invoice

  1. NUFinancials NavBar > Billing > Review Billing Information > Details.
  2. Search for the invoice using any of the available fields.
  3. Click on Lines tab to view the invoice lines. Click the Next button to see each expense.

 

Find a Sponsor Payment

For Sponsored Projects it is necessary to locate sponsor payments.

  1. NUFinancials NavBar > Accounts Receivable > Customer Accounts > Item Information > Item List
  2. Search by Contract ID (Award ID).
  3. Select an invoice (check box) > select View Items & Item Activity from Item Action list.
  4. Click View All > View Payment ID = Check #.

Refer to "Managing Sponsored Projects."

Correct Posted Transactions

If a transaction is approved and posted to the General Ledger with a mistake in the chart string, the next step in reconciliation is to correct the chart string using a Correction Journal. 

Task

Steps and Instructions

Find the Original Journal ID of a Voucher

There are two ways to find the Journal ID associated with a voucher.

  1. Use the HTML version of the GL008.
  • Locate the voucher and click the Transaction ID of the voucher. A new window opens with voucher details. If there are multiple journals, look for one with Journal ID starting with PV. Write down the Journal ID.
  1. Use the Accounting Entries inquiry in NUFinancials.
  • NUFinancials Main Menu > Accounts Payable > Review Accounts Payable Info > Vouchers > Accounting Entries.
  • Enter the Voucher ID and Search.
  • Select the Journal Information tab and write down the Journal ID.

Refer to "Finding Original Journal IDs…" job aid.

 

Find the Original Journal ID of an expense report or cash advance

There is one way to find the Journal ID associated with an expense report or cash advance.

  1. NUFinancials NavBar > Travel & Expenses > Manage Accounting > View/Adjust Accounting Entries > Expense Reporting Acctg Entries.
  2. Enter the Expense Report ID or Cash Advance ID.
  3. Select the Journal Information tab and write down the Journal ID.

Enter that Journal ID in the Original Journal ID field as you are creating the Correction Journal.

Refer to "Finding the Original Journal ID…" job aid.

Find the Original Journal ID of a Cash Receipt Ticket (CRT)

The Original Journal ID of a CRT is in the Transaction ID column of the GL008 report. The Journal ID starts with the three letters CRT. Write down the Journal ID.

Correct a posted actuals transaction using a Correction Journal (COR)

 

Note: the COR is not used with ISJ or agency chart strings (beginning with a fund in the 700 series).

If a transaction was posted to the General Ledger with a wrong chart string, you can use the Correction Journal to categorize the transaction within the correct chart string and account.

  1. NUPortal > Financial > Accounting or NavBar NU Actuals Journals
  2. Select Correction Journal and click Create Journal.
  3. Enter the Original Journal ID associated with the transaction.
  4. Enter the wrong chart string and amount on Journal Line 1.
  5. Insert a second journal line and enter the correct chart string and amount.

Follow Journal Rules to enter debits and credits correctly. Refer to "Creating Correction Journals"

Correct a posted actuals transaction involving an Agency chart string

 

 

Note: Agency chart strings have a Fund in the 700 series and a Project ID starting with the digit 7.

If an agency transaction was posted to the General Ledger with a wrong chart string, you can use the Agency Journal to categorize the transaction within the correct chart string and account.

  1. NUPortal > Financial > Accounting.
  2. Select Agency Journal and click Create Journal.
  3. Enter the Original Journal ID associated with the transaction.
  4. Enter the wrong chart string and amount on Journal Line 1.
  5. Insert a second journal line and enter the correct chart string and amount.

Follow Journal Rules to enter debits and credits correctly. Refer to "Creating Agency Journals"

Correct Posted Actuals Journals

Did you or a colleague make a mistake in an Actuals Journal? Like all transactions, Actuals Journals are visible on the GL008 report. Use the report to monitor activity and amounts, and to ensure activity is properly categorized within the correct chart string and account.

Task

Steps and Instructions

Correct a Correction

Did you or a colleague make a mistake in a Correction Journal? If so, you must reverse the mistake in a subsequent Correction Journal.

Correct a Transfer

Did you or a colleague make a mistake in a Transfer Journal? If so, you must reverse the mistake in a subsequent Transfer Journal.

Dispute an internal sales charge

Recharge centers and other departments use a Spreadsheet Journal to charge your chart string for the goods and services they provide.

See all charges on the GL008, along with the Journal ID. The first three letters of the Journal ID are a "mask" that identifies the journal creator. Use those three letters, the mask, to find the journal creator’s name.

Look for the name on the Financial Operations website at: http://www.northwestern.edu/financial-operations/policies- procedures/finance/financejournalupload.html. Once at the website, read the Overview and click the hyperlink at the end of the sentence, "Current registrants and assigned masks can be found here."

Once there, look for the list of journal "registrants" and masks.

Correct an internal sale

Did you or a colleague make a mistake in an Internal Sales Journal? If so, use the Internal Sales Journal to correct the mistake. Once inside the journal, click Correction Sales Journal.

Correct an agency journal

Did you or a colleague make a mistake in an Agency Journal? If so, use the Agency Journal to correct the mistake. Once inside the journal, click Correction Agency Journal.

Payroll Suspense

Payroll suspense occurs when payroll funding sources do not cover employee earnings due to a chart string inactivation, chart string closure, project end date, or a non-existing chart string.

Suspense means that employee earnings are charged to a default chart string associated with the parent HR Department, normally department operations, 110 – Financial Department ID – SUSP – Account Nbr. You can view suspense in Cognos reports and NUFinancials inquiries.

  • Vista reports enable you to spot potential suspense issues before they They give you time to correct errors before the payroll run – before they become suspense issues.
  • The Cognos GL008 and the NUFinancials Ledger Inquiry enable you to find suspense charges after the payroll run – after expenses posted to the General Ledger.
  • myHR enables you to find all information necessary to correct suspense issues with payroll journals and funding source changes, as appropriate. 

Process Overview

  1. Detect payroll suspense issues in Cognos reports or NUFinancials inquiries.
  2. Gather the information you need to correct issues in MyHR.
  3. Complete payroll journals in myHR, one per each employee.
  4. Complete a funding source change, if appropriate. 

Task

Steps and Instructions

Step 1

In Cognos, run the GL008 or PED reports.

  1. Public Folders > School > Monthly Financial > GL008 Revenue and Expense Activity Report.
  2. Enter a timeframe (fiscal year and period range).
  3. Enter the parent fund and department in search criteria: Fund, Department, and “SUSP” in ChartField 1.
  4. Click Finish.

In NUFinancials, use the Ledger inquiry.

  1. NavBar > General Ledger > Review Financial Information > Ledger.
  2. Add an inquiry name or use a saved search.
  3. Enter a timeframe (fiscal year and period range).
  4. Enter the parent fund and department in search criteria: Fund, Department, and "SUSP" in ChartField 1.
  5. Click Save > Search.

Step 2

In myHR, use the employee paycheck inquiry to get all information required to complete payroll journals.

Step 3

In myHR, complete payroll journals, one per employee. After the payroll journal is posted to the General Ledger, a negative reversing line appears in NUFinancials. The next business day, it appears in Cognos reports.

Step 4

In myHR, complete a funding source change, if necessary, to prevent further suspense issues.

Find more information about myHR in myHR Learn.

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




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