Reconciling Voyager Acquisitions with your Institutions Accounting System

Reconciling Voyager Acquisitions with your Institution's Accounting System Jim Edstrom Harper College Library November 28, 2012 The Process: 1. Run voucher report 2. Sort vouchers by number and fund category 3. Total fund categories within each voucher 4. Copy detailed voucher fund amounts into balanced spreadsheet 5. Run report of bills paid by Harper College Accounts Payable Department 6. Check bills against balanced spreadsheet

Harper College Fund Allocations: • Audiovisual materials • Media replacement materials • Print monographs • Serials • New programs

Local Harper College fund code Harper College Voyager Acquisitions Ledger

Fund number 0001 -2010 -024 -5404001 (Audiovisual materials) Total $374. 95 Fund number 0001 -2010 -024 -5405000 (Print materials) Total $30. 00 Voyager voucher with multiple Fund codes sent to Harper College Accounts Payable Department

Harper College “balanced spreadsheet” reconciling Voyager with Accounts Payable expenditures

Running vouchers report—setting filters

Impromptu Report for Compiling List of Vouchers

Impromptu Voyager vouchers report exported to Excel

Vouchers report with all “Invoice ID” cells filled in with voucher numbers

Column A: Voucher number

Column B: Invoice number

Column C: Status date

Columns D-I: Voyager amounts (individual fund categories) and totals of each voucher

Columns J-O: Amounts paid by College Accounts Payable Department and total of each voucher

Column P: Date paid (by Accounts Payable)

Columns R-W: Difference between Voyager and Accounts Payable amounts (individual fund categories and total discrepancies)

• Columns added for Library fund codes and date status • “Institution Fund ID” moved to column “I” • Price from column “K” calculated and formatted as currency in column “J” (formula: +K 2*. 01)

Formula for calculating price as currency copied to every cell in column “J”

Spreadsheet sorted by Invoice number and Institution Fund ID

Sorted spreadsheet

Adding up multiple lines in an invoice (formula)

Adding up multiple lines in an invoice (value)

Totals from a multiple-line, multiple-fund invoice (formulas)

Totals from a multiple-line, multiple-fund invoice (values)

Pasting lines from voucher report into balanced spreadsheet (step 1)

Pasting lines from voucher report into balanced spreadsheet (step 2)

Pasting lines from voucher report into balanced spreadsheet (step 3)

Amounts from Harper Accounts Payable “Oracle” system Amounts from Voyager ledger “Balanced” spreadsheet with amounts from Voyager ledger and “Oracle” system

Voyager Ledger Institution Fund ID 0001 -2010 -024 -5404004 -000000 -000 Oracle “General Ledger” Report

Institution Fund ID 0001 -2010 -024 -54004 -000000 -000 “Oracle” journal entries report

Invoice number Journal entries report—copying the invoice number

Searching for invoice number from Oracle in “balanced spreadsheet” Using Excel “Find” function (<Ctrl> <F>)

“Oracle” invoice number found!

Voucher amount $15. 21 = voucher amount paid by Accounts Payable!

Matching amount pasted from Column E (Voyager) into Column K (Accounts Payable)

Total amount paid by Accounts Payable and date paid Difference between Voyager Total paid in and Accounts Payable amounts voucher so far (Column E minus Column K) (Total of columns R-V)

Voucher no. 14807 paid by Accounts Payable

Voucher no. 14807—Voyager amount and matching amount paid by Accounts Payable

Total amount paid by Accounts Payable and date paid Difference between Voyager and Accounts Payable amounts (Column E minus Column K) Total paid in voucher so far (Total of columns R-V)

Voyager vouchers Current Oracle balance Difference

• The difference between amounts in Voyager and paid by Accounts Payable should always equal zero. • Possible reasons they don’t: • Entire bill paid out of one category instead of multiple categories • Paid out of a fund from another department • Vendor sends bill directly to Accounts Payable • Fund from another department entered • Save the spreadsheet as a new file each month to record each time it “zeroes out”

In Sum: 1. We run a report of all vouchers processed and approved since the last time we balanced the books. 2. We sort the vouchers by number and then within each number by fund category. 3. We total each fund category within each voucher. 4. We copy the detailed voucher fund amounts into our balanced spreadsheet. 5. We run a report of bills paid from the Harper College Accounts Payable Department’s Oracle system. 6. We check those bills against the balanced spreadsheet.

Questions? Contact information: Jim Edstrom jedstrom@harpercollege. edu Harper College Library 1200 West Algonquin Road Palatine, IL 60067 847 -925 -6763 (Ask me for a balanced spreadsheet template )