Reconciling Assets Without Losing your Mind! - PowerPoint Presentation

Slide1

Reconciling Assets Without Losing your Mind!

Nick Henry, Dalton StateTamya Morris, Dalton StateChristy Todd, ITSSlide2

Part One -- Process

Overview

Christy Todd

Part

Two – Asset Reconciliation – Is There a Problem

?

Nick Henry and Tamya MorrisSlide3

Agenda

Reconciliation OverviewAM Process OverviewReconciliation Process

Where entries come from

What causes discrepancies

Queries Slide4

Reconciliation Overview

Multiple ways to reconcileConceptually reconciliation consists of the following items: DIST_LN entries are correct and completeDIST_LN

matches

JRNL_LN

JRNL_LN

account balances match

Capital Ledger

Capital

Ledger

balances to

Actuals LedgerSlide5

AM Process Overview

Journal Header and Journal Line

Capital Ledger

Journal generate

PostSlide6

DIST_LN

The entries in the DIST_LN table come from:Accounting Entry Creation for:New asset transactions (ADD, ADJ, TRF, RECAT)Depreciation Close for:

Creating monthly

depr

entries (DPR,PDP)

What causes incorrect DIST_LN entries?

Prior Period Depreciation

missing

Failure to run a month end process

Incorrect

dates resulting in incorrect calculations or missing entriesSlide7

DIST_LN

How do I know if DIST_LN is correct?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 Slide8

DIST_LN

BQ Queries:BOR_Depr_AM_Det_By_Period_BQ –

Depreciation that is used in NBV

BOR_Depr_AM_Dist_Ln_BQ

–

Depreciation Expense from

Dist_Ln

for specified accounting period.

Other Queries

:

BOR_AM_LTD_Detail

– Dist_Ln entries by asset for the account specified BOR_AM_LTD_SUMM -- Dist_Ln entries by asset for the account specified

summarized by yearBOR_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 presentationSlide9

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

?

C

hange

is made directly to journal prior to

posting

AM journal is copied and posted multiple timesFailure to journal generate AM accounting entries in DIST_LN (review BOR_JE_Pending)

Manual journal created How do I compare Dist_Ln to Jrnl_Ln?BOR_AM_DIST_JRNL_RECON --Compares DIST_LN entries to JRNL_LN transactions for items originating from the AM module. Slide10

JRNL_LN to LEDGER

Ledger is populated when journals are postedWhat causes Ledger to be different from the Journal?Differences should be an exception

JRNL_LN feeds directly to Ledger

Differences typically caused by failure to

post AM

journals

How do I compare JRNL_LN to Ledger?

BOR_AM_CY_RECON

Compares DIST_LN, JRNL_LN, All Ledgers,/all periods

Ties back to Asset ID from JRNL_LN

BOR_DEPR_CAP_LED_TTL_BQ – provides the sum of the depreciation expense (890100) posted to the Capital Ledger for a specified period.Slide11

Capital Ledger to Actuals Ledger

Differences typically caused by:Timing issues -- AP journal posted to Actuals before AM journal posted to CapitalOn-line Journal posted to wrong

ledger

How

do I find differences in Capital and Actuals Ledgers

?

BOR_AM_CAP_ACT_RECON

–

AM transactions

from ALL sources recorded in Capital and Actuals Ledgers

.

Excludes period 0Excludes GAAP Ledger infoIncludes JRNL_HEADER

data in addition to Capital and Actuals Ledger InfoBOR_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). Slide12

Additional Queries

BOR_AM_CAP_DTL_BQ – Provides transactions from the Cost Table for specified period of timeBOR_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. Slide13

Asset Reconciliation:

Is There A Problem?

Presented By: Nick Henry and Tamya Morris Slide14

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

Topic

Reconciliation of SVP purchases ($3,000-$

4,999) in AM module to the Actuals Ledger.Slide16

AM SVP Purchases

Query: BOR_SVP_AM_DTL

This query provides SVP transaction details for any additions in the AM module.Slide17

AM SVP Purchases (cont’d)

Query criteria pulls data by SVP category and prompts for accounting dates.Slide18

ACTUALS SVP Purchases (cont’d)

Query: BOR_SVP_ACTUALS_DTL This query provides journals from the Actuals ledger for SVP accounts. Slide19

ACTUALS SVP Purchases (cont’d)

Query criteria pulls data for SVP accounts (7432% and 7442%) from Actuals Ledger and prompts for journal dates. Slide20

Results for SVP

Note: The AM & Actuals ledger SVP accounts should agreeSlide21

Topic

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

Reconcile Capital Purchases

Query: BOR_CAP_ACT_DTL_BQ

This

query provides journals from the

Capital and Actuals Ledgers. Slide23

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%.Slide24

Results for Capital Purchases

Sort by Ledger, then sum by total amount to see if there is a variance.

Note: Exclude any library collection expense accounts 8432% and any other unnecessary accounts (i.e. 818xxx lease/purchase).Slide25

TOPIC

Reconciliation of DIST_LN to JRNL_LNSlide26

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

originating from

the

AM

module. Slide27

DIST_LN to JRNL_LN

Query criteria pulls data from GL Distribution status and prompts for business unit and fiscal year.Slide28

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

Topic

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

Reconcile Accumulated Depreciation

Query: BOR_AUDIT_AM_BAL_SHEET_DETAIL

This query provides

journals from the

Asset Management module. Slide31

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

Reconcile Accumulated Depreciation (cont’d)

Part One – Use Pivot table AM 16x900 Accounts & Totals

Part One

– Trial Balance Capital LedgerSlide33

Reconcile Accumulated Depreciation (cont’d)

Part

Two

– ReconciliationSlide34

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 (YE-16/YE-17).Slide35

Difference - Accumulated Depr

(cont’d)Example: 10% Residual Value (YE-21) for

Accum

Depr for Buildings and Capital Leases.Slide36

Topic

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

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

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

Results for Accum

DeprQuery: BOR_DEPR_AM_DIST_LN_BQ

Results for monthly Asset Management depreciation:Slide40

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

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

Results for Depr Exp

Query: BOR_DEPR_CAP_LED_TTL_BQ

Results for monthly Capital Ledger depreciation:Slide43

Depr Results C

ombinedAM and Capital Ledger depreciation results combined by accounting period:Slide44

Accum Depr vs Depr Exp

Query: BOR_AUDIT_AM_BAL_SHEET_DETAIL

This query provides yearly AM accumulated depreciation totals.Slide45

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

Depr Results by Year

Pivot table from AM query (DEPR & PDP journals only)

Trial Balance – Capital ledgerSlide47

Questions