Reconciling Assets Without Losing your Mind! Nick Henry, Dalton

Reconciling Assets Without Losing your Mind! Nick Henry, Dalton State Tamya Morris, Dalton State Christy Todd, ITS

Part One -- Process Overview Christy Todd Part Two – Asset Reconciliation – Is There a Problem? Nick Henry and Tamya Morris

Agenda Reconciliation Overview AM Process Overview Reconciliation Process o Where entries come from o What causes discrepancies o Queries

Reconciliation Overview Multiple ways to reconcile Conceptually reconciliation consists of the following items: o DIST LN entries are correct and complete o DIST LN matches JRNL LN o JRNL LN account balances match Capital Ledger o Capital Ledger balances to Actuals Ledger

AM Process Overview Journal generate Journal Header and Journal Line Post Capital Ledger

DIST LN The entries in the DIST LN table come from: o Accounting Entry Creation for: o New asset transactions (ADD, ADJ, TRF, RECAT) o Depreciation Close for: o Creating monthly depr entries (DPR,PDP) What causes incorrect DIST LN entries? o Prior Period Depreciation missing o Failure to run a month end process o Incorrect dates resulting in incorrect calculations or missing entries

DIST LN How do I know if DIST LN is correct? o Compare Projected Depr to Actual Depr by comparing NBV to Dist Ln COST minus DEPRECIATION Table entries(proj) NBV COST in Dist Ln minus PDP and DPR entries in DIST LN actual value

DIST LN BQ Queries: o BOR Depr AM Det By Period BQ – Depreciation that is used in NBV o BOR Depr AM Dist Ln BQ – Depreciation Expense from Dist Ln for specified accounting period. Other Queries: o BOR AM LTD Detail – Dist Ln entries by asset for the account specified o BOR AM LTD SUMM -- Dist Ln entries by asset for the account specified summarized by year o BOR AM NBV Compare – account balance by asset (looks at NBV and DIST LN) * requires loading of depr rept and NBV tables Online pages – Review Depreciation page and Review Financial Entries page *Queries highlighted in blue are the ones used and demonstrated by Dalton State in the 2 nd half of the presentation

DIST LN to JRNL LN Jrnl Ln entries come from Dist Ln and are produced when Dist Ln entries are journal generated. What causes Jrnl Ln entries to be different from Dist Ln? o Change is made directly to journal prior to posting o AM journal is copied and posted multiple times o Failure to journal generate AM accounting entries in DIST LN (review BOR JE Pending) o Manual journal created How do I compare Dist Ln to Jrnl Ln? o BOR AM DIST JRNL RECON --Compares DIST LN entries to JRNL LN transactions for items originating from the AM module.

JRNL LN to LEDGER Ledger is populated when journals are posted What causes Ledger to be different from the Journal? o Differences should be an exception o JRNL LN feeds directly to Ledger o Differences typically caused by failure to post AM journals How do I compare JRNL LN to Ledger? o BOR AM CY RECON Compares DIST LN, JRNL LN, All Ledgers,/all periods Ties back to Asset ID from JRNL LN o BOR DEPR CAP LED TTL BQ – provides the sum of the depreciation expense (890100) posted to the Capital Ledger for a specified period.

Capital Ledger to Actuals Ledger Differences typically caused by: o Timing issues -- AP journal posted to Actuals before AM journal posted to Capital o On-line Journal posted to wrong ledger How do I find differences in Capital and Actuals Ledgers? o BOR AM CAP ACT RECON – AM transactions from ALL sources recorded in Capital and Actuals Ledgers. Excludes period 0 Excludes GAAP Ledger info Includes JRNL HEADER data in addition to Capital and Actuals Ledger Info o BOR CAP ACT DTL BQ – provides Sum per chartstring from CAPITAL and ACTUALS Ledgers for Capital Assets (not in 890100 Depreciation Expense and 843200 Library Collections).

Additional Queries BOR AM CAP DTL BQ – Provides transactions from the Cost Table for specified period of time BOR VCHR ACCT LN BQ – Provides AM transactions from the Vchr Acctg Line table (Account begins with 8 excluding Library Collections) BOR SVP AM DTL – SVP data from Cost, Asset, and Asset Acquisition Detail BOR SVP ACTUALS DTL – SVP data (account begins with ‘7432’ or ‘7442’) from Journal Header and Journal Line BOR AUDIT AM BAL SHEET DETAIL – This is an auditor query that provides all transactions from Dist Ln for a specified account range and period of time. Additional asset data provided from Asset and Asset Acq Det tables.

Asset Reconciliation: Is There A Problem? Presented By: Nick Henry and Tamya Morris

Agenda Reconciliation of SVP purchases ( 3,000- 4,999) in AM module to the Actuals ledger. Reconciliation of capital purchases ( 5,000 )on the Actuals Ledger to the Capital Ledger. Reconciliation of Dist LN to JRNL LN. Reconciliation of AM accumulated depreciation accounts (16x900) to the Capital Ledger. Reconciliation of accumulated (16x900) depreciation in AM module to depreciation expense (890100) on the Capital Ledger by year and by accounting period.

Topic Reconciliation of SVP purchases ( 3,000- 4,999) in AM module to the Actuals Ledger.

AM SVP Purchases Query: BOR SVP AM DTL This query provides SVP transaction details for any additions in the AM module.

AM SVP Purchases (cont’d) Query criteria pulls data by SVP category and prompts for accounting dates.

ACTUALS SVP Purchases (cont’d) Query: BOR SVP ACTUALS DTL This query provides journals from the Actuals ledger for SVP accounts.

ACTUALS SVP Purchases (cont’d) Query criteria pulls data for SVP accounts (7432% and 7442%) from Actuals Ledger and prompts for journal dates.

Results for SVP Note: The AM & Actuals ledger SVP accounts should agree

Topic Reconciliation of capital purchases ( 5,000 )on the Actuals Ledger to the Capital Ledger.

Reconcile Capital Purchases Query: BOR CAP ACT DTL BQ This query provides journals from the Capital and Actuals Ledgers.

Reconcile Capital Purchases (cont’d) Query criteria pulls data from Capital and Actuals Ledgers and prompts for accounting period, fiscal year, and business unit for accounts like 8%.

Results for Capital Purchases Sort by Ledger, then sum by total amount to see if there is a variance. No te: Exclude any library collection expense accounts 8432% and any other unnecessary accounts (i.e. 81 8xxx le ase/ purchase).

TOPIC Reconciliation of DIST LN to JRNL LN

DIST LN to JRNL LN Query BOR AM DIST JRNL RECON This query compares AM DIST LN entries to GL JRNL LN transactions for items the AM module. originating from

DIST LN to JRNL LN Query criteria pulls data from GL Distribution status and prompts for business unit and fiscal year.

Results for DIST LN to JRNL LN Below are the results to compare DIST LN to JRNL LN using a pivot table. This was a rounding issue on the DIST LN and an DBI was applied.

Topic Reconciliation of AM accumulated depreciation accounts (16x900) to the Capital Ledger.

Reconcile Accumulated Depreciation Query: BOR AUDIT AM BAL SHEET DETAIL This query provides journals from the Asset Management module.

Reconcile Accumulated Depreciation (cont’d) Query criteria pulls data from Capital Ledger and prompts for fiscal year, account numbers, accounting periods, and business unit. Note: For account numbers use 161000-169999 and always run as accounting periods 1-12.

Reconcile Accumulated Depreciation (cont’d) Part One – Use Pivot table AM 16x900 Accounts & Totals Part One – Trial Balance Capital Ledger

Reconcile Accumulated Depreciation (cont’d) Part Two – Reconciliation

Difference - Accumulated Depr The difference between Asset Management and Capital Ledger for accumulated depr accounts should be the 10% Residual Value (YE-21) and/or Library Accum Depr (YE16/YE-17).

Difference - Accumulated Depr (cont’d) Example: 10% Residual Value (YE-21) for Accum Depr for Buildings and Capital Leases.

Topic Reconciliation of accumulated (16x900) depreciation in AM module to depreciation expense (890100) on the Capital Ledger by year and by accounting period.

Accum Depr vs Depr Exp BOR DEPR AM DIST LN BQ This query provides monthly AM accumulated depreciation transactions in DIST LN table by accounting period.

Accum Depr vs Depr Exp (cont’d) Query criteria pulls by the depr expense account (890100) from the AM DIST LN and prompts for accounting period, fiscal year and business unit.

Results for Accum Depr Query: BOR DEPR AM DIST LN BQ Results for monthly Asset Management depreciation:

Accum Depr vs Depr Exp (cont’d) BOR DEPR CAP LED TTL BQ This query provides the depreciation expense total recorded in the Capital Ledger by accounting period.

Accum Depr vs Depr Exp (cont’d) Query criteria pulls data from the Capital Ledger by depr expense account (890100)and prompts for accounting period, fiscal year and business unit.

Results for Depr Exp Query: BOR DEPR CAP LED TTL BQ Results for monthly Capital Ledger depreciation:

Depr Results Combined AM and Capital Ledger depreciation results combined by accounting period:

Accum Depr vs Depr Exp Query: BOR AUDIT AM BAL SHEET DETAIL This query provides yearly AM accumulated depreciation totals.

Accum Depr vs Depr Exp (cont’d) Query criteria pulls data from Capital Ledger and prompts by fiscal year, accounts, accounting periods, and business unit.

Depr Results by Year Pivot table from AM query (DEPR & PDP journals only) Trial Balance – Capital ledger

Questions