Transcript Implementing Shadow Accounting Systems NCURA February 26, 2008 Tim Quigg, Associate Chair for Administration and Finance Department of Computer Science, UNC-Chapel Hill.

Implementing
Shadow Accounting Systems
NCURA
February 26, 2008
Tim Quigg, Associate Chair for Administration and Finance
Department of Computer Science, UNC-Chapel Hill
Arguing with an Auditor is Like
Wrestling with a Pig in Mud...
After a while, you realize the pig
ENJOYS it!
The Importance of Financial
Planning at the Departmental
Level Cannot be Overstated!
Question 1: Where are we now?
Question 2: Where are we going?
Financial Planning
Question 1: Where are we now?
Critical need for accurate, detailed and current view
of financial status (by project, lab and department)
•
•
If central institutional accounting records provide this –
fine!
If not, you need a departmental system (“shadow
accounting”).
Financial Planning
Question 2: Where are we going?
Projecting expenditures/revenue into the future
(for fiscal and project year)
•
•
•
Special attention to personnel
– Plan to cover soft money people for year
– Note institutional policy for “layoffs”
Special attention to fixed expenses, e.g., equipment,
rent, software, service contracts
If central institutional system doesn’t help, perhaps
you need a departmental system!
Are Central Accounting
Systems Adequate?
“Relying upon poor data from a central
system to manage the financial affairs of your
department is like relying upon the tide to
steer your ship.”
It Just Doesn’t Make Sense!
Shadow Accounting Systems
“A shadow accounting system is a set of
records maintained at a local level
independent of the official records”
•
•
•
Have existed in some form (probably
forever)
PIs keeping account balances in lab books
Personal computers fostered an explosion
of shadow systems
Central Accounting Systems:



Remember, central systems are first
designed to meet institutional needs
Department needs are secondary (or
nonexistent)
Central systems often don’t provide
everything that is needed to “run a
department”, for example:
•
•
Give you the flexibility to produce the reports
that you need
ENCUMBER SALARIES THROUGH BUDGET
PERIOD
When To Implement A
Shadow Accounting System?

When central system
•
•
•
•
Does not provide enough detailed
information,
The information is not readily available in a
usable form,
When there is too much lag time between
incurring an obligation and it being posted to
the central records, and
If the benefits (dollars and time) out weigh the
costs!
Additional Advantages

Tracking Extra Information on Transactions
•
•

Purpose of expense, e.g., graduate student
education fund, faculty recruiting
Category of expense, e.g., supplies for particular
class
Maintaining sub-accounts internally for a
single central account
•
•
Departmental “earmarks” for gift or institutional
accounts
May be easier to keep in one account (less
institutional hassle and paperwork)
“Dos”





Do be sure to enter transactions into your
system before they “leave your office” (paper
or electronic)
Do be sure that your records are secure and
stored in a safe form and location
Do perform a regular, thorough reconciliation
to official records using a reconciliation
worksheet
Do print and store reports in a form that are
useful in audit situations
Do train more than one person on the proper
use of the system.
“Don’ts”



Don’t assume that magnetic files will always
be available for printing the report the
auditor is going to request!
Don’t assume that just because you recorded
the transaction correctly in YOUR system,
that it got posted correctly in THEIR system!
Don’t assume that the person who uses the
system today will ALWAYS be around
tomorrow!
Financial Reconciliation Process
Goal
Manual or Electronic System
Financial Reconciliation Process
Step 1
Obtain central data (financial reports and
transaction data) in a form that you can match
with the shadow system (typically by account for
a particular accounting period)
UNC Central Report
FBM090 - C2
ACCT: 5-31136
ATTRI 0-110-32761
SUB
CODE
1001
1112
1113
1117
1212
1251
1411
1451
1800
1812
1813
1822
1831
1832
1833
1836
1872
1873
1876
1892
1920
2000
2311
3100
SUMMARY ACCOUNT STATEMENT FOR
DIST# REPORT DISTRIB = 3276
INTERACTIVE GRAPHICS FOR MOLECULAR STUDIES & MICROSCOPY
REPORT PAGE 2531
UNIVERSITY OF NORTH CAROLINA - CHAPEL HILL
PROGRAM
FINANCIAL RECORDS
SYSTEMID FBM0929
08/31/00
ACCOUNT
PAGE 1
TO:
CB 3175, SITTERSON
SPON:NAT CTR FOR RES RESR
BUD PD:051500-051401 AWARD: $855,675.00
CON#:5-P41-RRO2170-17 CAN#:
PRJ PD:050184-051401
FROE:OOOO FR:081201 PY:530846
SPON CODE:DHHS
TR:081201
PRIME #:531136 OVHD-ON:H 4500 OFF:
-------- BUDGETS -------- ----------------ACTUALS -----------------OPEN
BALANCE
DESCRIPTION
ORIGINAL
REVISED CURRENT MONTH
FISCAL YEAR
PROJECT YEAR
COMMITMENTS
AVAILABLE
C/G PERSONNEL
EPA NON-TEACH EPA NON-TEACH MO STU/OTHER NON
EPA NON-TEACH SA
SPA ON CAMPUS
SPA SEVER WAGES
SPA EMPLOYEE SAL
NON-STUDENT WAGE
STUDENT WAGE
TEMP EMPLOYEE WA
TOTAL PERSONNEL
STAFF BENEFITS
SOCIAL SECURITY
SOC SECUR - HOSP
STATE RETIREMENT
MED INSURANCE OT
MEDICAL INSURANC
HMO/HEALTH PLANS
MEDICAL INS GRAD
TIAA RETIREMENT
TIAA-HEALTH PLAN
ORP FIDELITY
COMPOSITE BENEFI
STAFF BENEFITS
PROF CONSULTING
SUPPLIES & MATER
EDUCATIONAL SUPP
SUPPLIES / MATER
TRAVEL
395,190.00
395,190.00
60,086.00
60,086.00
5,500.00
9,500.00
9,500.00
8,317.00
255,069.96
54,859.36
9 365.19
42:792.35
107,016.90
31,377.76
15.38
31,393.14
300.00
1,410.00
1,710.00
395,190.00
41,545.31
5,483.63
1,433.46
4,087.85
413.28
738.68
2,596.30
469.02
2,194.84
531.69
359.10
232.84
60,086.00
5,500.00
7,384.00
2,116.00
9,500.00
5.90-
255,069.96
21,120.22
2,369.00
15,888.63
39,377.85
2,420.16
4.84
2,425.00
300.00
468.75
768.75
42,571.60
35,799.04
4,034.78
25,264.44
65,098.26
4,791.55
9.58
4,801.13
300.00
915.00
1,215.00
71,114.39
54,859.36
9,365.19
42,792.35
107,016.90
7,694.83
15.38
7,710.21
300.00
1,410.00
1,710.00
116,437.11
1,410.22
406.44
759.83
118.00
128.25281.97
207.99
684.17
416.85
359.10
85.13
4,601.45
2,520.07
704.25
1,301.56
236.00
214.81
582.74
469.02
1,444.65
474.66
359.10
142.21
8,449.07
4,292.87
1,154.98
2,401.63
413.28
738.68
898.37
469.02
2,194.84
531.69
359.10
232.84
13,687.301,
1,190.76
278.48
1,686.22
1,644.24
1,644.24
1,753.67
1,753.67
1,809.66
1,809.66
306.34
306.34
23,682.93
23,682.93
23,682.93
255,069.96
41,545.31
1,697.93
4,853.39
41,545.31
5,500.00
7,384.00
7,384.00
5.90-
UNC Central Report
DATE RUN
TIME RUN
FBM091
09/08/00
11:09:05
UNIVERSITY OF NORTH CAROLINA - CHAPEL HILL
FINANCIAL RECORDS SYSTEM
REPORT OF TRANSACTIONS FOR 08/31/00
DIST# REPORT DISTRIB = 3276
INTERACTIVE GRAPHICS FOR MOLECULAR STUDIES & MICROSCOPY
REPORT PAGE 2539
PROGRAM ID FBM092
ACCOUNT PAGE 5
FR04C/PCB-0890
ACCT: 5-31136
TO:
DEPT: 3276
SUB
CODE DESCRIPTION
DATE
1876 J W Smith
08/31
J W Smith
08/31
J W Smith
08/31
1876 CM TOTAL ORP FIDELITY
1892 PAYROLL RESERVE (16)
08/11
PAYROLL RESERVE (16)
08/25
PAYROLL RESERVE (16)
08/31
1892 CM TOTAL COMPOSITE
BENEFITS
1920 CNA
08/15
1920 CM TOTAL PROF
CONSULTING
FEES
2000 CNA
08/15
2000 CM TOTAL SUPPLIES &
MATERIALS
2311 IEEE MAGAZINES
08/11
IEEE INC
08/18
TAPE RESOURCES INC
08/10
TAPE RESOURCES INC
08/17
DELL MARKETING LP
09/06
D Davis
08/14
D Davis
08/22
NEWARK ELECTRONICS
08/11
NEWARK ELECTRONICS
08/18
2311 CM TOTAL EDUCATIONAL SUPPLIES
TC~ REF.
042
042
042 JE2412
042
042
042
2ND
J.E. OFFSET
REF.
ACCOUNT
M02 REG
M02 119
B03 REG
B04 REG
M02 REG
020 2000509
5,500.00
5,500 00
020 2000509
9,500.00
050
048
050
048
050
050
048
050
048
K399887
K399887
K409250
K409250
K434469
K435666
K435666
K435730
K435730
519.30
921540
7.29
920163
2.65
1.644.24
R M Hall
F P Albright
C Wall
C Wall
CM TOTAL OUT/STATE
08/24
08/16
08/23
08/28
TRANS-AIR
R M Hall
F P Albright
CM Total OUT/STATE
R M Hall
F P Albright
C Wall
CM TOTAL OUT/STATE
08/24 048 T012732 923835
08/16
T012747 915818
TRAN-GROUN
08/24 048 T012732 923835
048 T012747 915818
08/16 048 T012751 922967
SUB-LODGIN
3122
3124
3124
CGOL99 00081
916738
3121
3122
CB 3175, SITTERSON
BATCH
COMMITMENTS
REF.
DATE
179.55
APRM02 00083
8.55
APRM02 00083
171.00
APRM02 00083
359.10
2.91
APRB03 00081
3.47
APRB04 00082
78.75
APRM02 00083
85.13
CGOL99 00081
1,115.OO
CNA
CM TOTAL TRAVEL
08/15 020 2000509
048
048
048
050
CURRENT
REV/EXP
920261
3100
3100
3121
BUDGET
ENTRIES
T012732 923834
T012747 915818
T012751 922967
T012751
1,115.00
1,115.00519.30
519.30306.34
7.73
7.732.65
2.65306.34
8,317.00
8,317.00
CGLLL
APC419
CGLLL
APC415
DP5003
CGLLL
APC425
CGLLL
APC419
00081
00081
00081
00081
00090
00081
00082
00081
00081
CGOL99 00081
210.00
349.79
206.00
765.79
63.85
87.75
151.60
570.55
692.65
232.77
10495.97
996.00349.79206.00474.002.025.79-
APC430
APC413
APC428
ENC912
00082
00081
00082
00082
APC430 00082
APC413 00081
APC430 00082
APC413 00081
APC428 00082
Financial Reconciliation Process
Step 2
Prepare shadow data (financial reports and
transaction data) in the same format as the
central data.
Computer Science Department Report
Selection Criteria:
Account=5-00000
Account Budget Report
Report Run On: 10/10/00 11:00:47JAM
Account
5-00000
PI Name Donald Duck
Current Budget Period
5/15/00
5/14/01
NIH - GRIP – PRIME
Budget Code Description
1000
1800
1900
2000
3100
3130
3200
3400
3800
3900
4400
5000
6500
8900
8982
I
Personnel
Faculty/Staff Benefits
Con sultants/Temporaries
Supplies & Materials
Travel
Foreign Travel
Communication
Printing/Binding
Computer Services
Miscellaneous
Maintenance Contracts
Equipment
Educational Grants
Indirect Costs
Restricted Funds
Total for Account: 5-00000
Budget
Encumbered
Expensed
Balance
395,190.00
60,086.00
5,500.00
9,500.00
8,317.00
2,900.00
500.00
30,192.39
4,621.33
116,437.11
13,687.30
3,345.28
4,542.90
49,858.00
600.00
10,000.00
38,000.00
13,153.00
243,764.00
18,307.00
9,399.48
1,809.66
5,317.90
756.43
45.92
24.72
6,356.34
463.25
178,559.62
18,307.00
248,560.50
41,777.37
5,500.00
4,345.06
(1,543.80)
2,143.57
437.80
(24.72)
34,102. 1 8
136.75
393.28
38,000.00
13,153.00
0.00
0.00
855,675.00
258,591.00
16.28
9,606.72
65,204.38
210,103.01
386,980.
Selection Criteria:
Object Code=231 1, Object Set=UNC, Account=5-31136
Account
Account Breakdown Report
5-31136
Report Run On:10/11/00 2:16:50PM
NIH - GRIP - PRIME
Budget Code 2000
Supplies & Materials
5/12/00
6/30/00
VISA CARD CHARGES
5/19/00
8131/00
GUTHOLD - IEEE REPRINTS
NONE
JET VISA04
NONE
1,862.90
55.99
K399887
NONE
1,115.00
5/31/00
8/31/00
TAPES/FUJI/SONY/PANASONIC
TAPERESC HARRISC K409250
NONE
519.30
6/29/00
7131/00
INV# 1747678
NEWARK HARRIS( K422113
NONE
28.30
6/29/00
7/31/00
INV# 1775603
NEWARK HARRIS( K422137
NONE
29.30
6/29/00
7/31/00
INV# 1798294
NEWARK HARRIS( K422144
NONE
4.20
6/29/00
7/31/00
INV# 1753606
NEWARK HARRISC K422145
NONE
47.63
HDA
APPLE
K430340
NONE
286.20
INV# 387776
INV# 410860217
REIMBURSE FOR
REFRESHMENTS FOR NIH SITE
VISIT
YELLOW LED'S/K411941NEWARK
MINI-CIRCUITS INV# 792054
MINI-CIRCUITS INV# 796875
INV# P49056420103MICROWA
MARKERT HARRIS( K431865
DELL
STONEM k434469
FREEDM, K435666
NONE
NONE
NONE
246.91
306.34
0.44
HARRIS( K435730
BRUMBA K440673
K440683
K451954
NONE
NONE
NONE
NONE
457.88
170.70
149.00
NONE
NONE
7/18/00
7/20/00
7/26/00
7/31/00
8/31/00
8131/00
7/31/00
8/9/00
8/9/00
8/30/00
8/31/00
9/22/00
VISA CARD CHARGES
Total for Budget Code 2000
Balance Available for Budget Code 2000
NONE
VISA03
STINE
7.29
2.65
116.58
0.00
3,596.95
3,596.95
1,809.66
(5,406.61)
1,809.66
Financial Reconciliation Process
Step 3
Compare expenditures (or balances) by object code
between central and departmental financial reports.
Look for particular areas of discrepancy!
Summary of Discrepancies
Central Records
Personnel
Departmental Records
Difference
255,069.96
248,560.50
6,509.46
Supplies
7,384.00
4,345.06
3,038.94
Travel
2,137.67
593.87
1,543.80
Financial Reconciliation Process
Step 4
Best approach - compare all transactions for the
period and “check off” those that match.
Compromise approach (use in situations where
volume is enormous and accounting staff
resources are limited) - compare all transactions
for the period in the object codes where
discrepancies were noted.
IMPORTANT
Every discrepancy
must be researched
and explained!
What Are Likely Explanations
For These Discrepancies?

Timing errors

Posting errors

Processing errors (the form is still
sitting on your desk!)
Reconciliation




Confirm the timing items by inquiry to the
central system. If you can’t find them, you
will need to do more research.
Confirm posting errors to the central system
and get them fixed.
Correct departmental processing errors.
Keep a list of items that should be corrected
in the next accounting cycle.
Reconciliation

Unmatched central items “got past
you” and need to be recorded in the
shadow system, or explained out in a
reconciliation worksheet.
Bonus System for planning,
tracking and presenting salary
source information!

Accounting reports don’t get it
Personnel Funding Chart
Name
Washington, G.
Funding
GRIP 24%
Lincoln, A.
DOE-F 15%
GRIP 60%
MIP 10%
STC/Teleimmersion 15%
Jefferson, T.
DOE-F 12.5%
I-FLOW 50%
NANO 25%
STC/Teleimmersion 12.5%
DOE-F 50%
STC/Teleimmersion 50%
100%
Clinton, B.
GRIP 100%
Ford, G.
DOE-F 20%
STC/Teleimmersion 13%
Eisenhower, D.
MIP 100%
Johnson, L.
STC/Teleimmersion 100%
Truman, H.
GRIP 100%
Reagan, R.
MIP 100%
Roosevelt, F.
DOE-F 30%
I-FLOW 20%
NANO 10%
MIP 10%
STC/Teleimmersion 30%
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
14th
14th
14th
14th
Jun
Personnel Funding Chart
Name
Larry
Funding
DOE-F 20%
STC 8%
MCNC 5%
STC 28%
MCNC 33%
ITR-F 14%
(NEW)DOE-F 14%
ITR-Brown/Welch 5%
ITR-F 38%
(NEW)DOE-F 38%
Curley
TRUST 20%
STC 60%
STC 35%
MCNC 5%
GRIP 20%
ONR-Bishop 20%
ITR-F 20%
(NEW)DOE-F 5%
ITR-N 35%
KECK-OVHD 35%
ONR Bishop 10%
ITR-F 40%
(NEW)DOE-F 30%
Moe
TRUST 10%
STC 72%
MCNC 18%
STC 32%
DOE-M 100%
ONR-Bishop 20%
GRIP 23.5%
(NEW)DOE-F 33%
ITR-Brown/Welch 23.5%
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Personnel Funding Chart
Name
Kiser, S.
$150,000
Funding
State (100%)
Acct. #
2-32761
Lawrence, S.
$15,586
State (100%)
2-32761
Quigg, T.
$12,207
State (100%)
2-32761
Vrana, S.
$150,000 State ( 69%)
Salary
2-32761
Kenan Professor (6%)6-60024
CISMM (10%)
5-31865
CISMM (8%)
5-32690
IBR (7.5%)
5-37125
IBR (9.5%)
5-37125
ITR (7.5%)
5-47737
25% on grants
Wdowik, M.
$20,348
Vargas, A.
$457,318 Federico Gil (18%)
33% on grants
State (100%)
Neurosurgery (3%)
CS 51788 (2%)
DARPA (10%)
State (45%)
NLM (6%)
CS 55807 (2%)
Livermore (14%)
NLM (33%)
CS 55807 (15%)
State (5%)
State (14%)
NLM (37%)
CS 55807 (16%)
State (39%)
NLM (20%)
CS 55807 (8%)
2-32761
6-62301
5-51788
2-32761
5-35751
2-32761
5-55807
2-32761
5-42519
5-55807
2-32761
2-32761
2-32761
5-55807
2-32761
2-32761
5-55807
2-32761
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Burn Rate
Maximum Award Amount
350,000
300,000
Incremental Funding
250,000
200,000
Project Budget
Actual Expense
150,000
100,000
End
Date
50,000
Jan-01
Feb-01
Mar-01
Apr-01
Incr Budget
May-01
Cum Exp
Jun-01
Jul-01
Proposal Budget
Aug-01
Sep-01
Total Budget
Oct-01
Nov-01
Cum + Proj
Dec-01