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.
Northwestern IT Administrative Systems
A budget is reconciled when…
For every chart string, we can say: |
For every financial transaction, we can say: |
|
|
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.
Helpful Contacts
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 |
Helpful Reports
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?
|
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. |
Transaction Descriptions on the GL008
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
|
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 |
Common Inquiries - Procedures
Purchase Inquiries
Task |
Steps and Instructions |
Run a report of all open encumbrances in Cognos |
Find all open encumbrances associated with your chart string.
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).
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).
|
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.
|
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:
To cancel/close a Purchase Order:
|
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.
|
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.
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.
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.
|
Find the updated purchase order after your PO Change was approved |
Use the Purchasing menu to view change requests.
|
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.
|
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.
|
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.
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.
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.
|
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.
|
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.
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.
|
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.
|
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.
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 |
|
Find a Sponsor Payment |
For Sponsored Projects it is necessary to locate sponsor payments.
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.
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.
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.
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.
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
- Detect payroll suspense issues in Cognos reports or NUFinancials inquiries.
- Gather the information you need to correct issues in MyHR.
- Complete payroll journals in myHR, one per each employee.
- Complete a funding source change, if appropriate.
Task |
Steps and Instructions |
Step 1 |
In Cognos, run the GL008 or PED reports.
In NUFinancials, use the Ledger inquiry.
|
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 7, or consultant@northwestern.edu.