da 201 course tim quigg, associate chairman department of computer science unc at chapel hill salt...
Post on 21-Dec-2015
214 views
TRANSCRIPT
DA 201 Course
Tim Quigg, Associate ChairmanDepartment of Computer Science
UNC at Chapel Hill
Salt Lake CityOctober 25, 2004
Financial Planning and Shadow Accounting
Copyright © 2004, SRA International and Sharon Kiser, Steve Lawrence, Tim Quigg, Lawrie Robertson, Cary Thomas, Sheila Vrana, and Mark Wdowik. All rights reserved.
DA 201 Course
Arguing with an Auditor is LikeWrestling with a Pig in Mud...
After a while, you realize the pig ENJOYS it!
DA 201 Course
The Importance of Financial Planning at the Departmental Level Cannot be Overstated!
Question 1: Where are we now?Question 2: Where are we going?
DA 201 Course
Financial Planning
Question 1: Where are we now?
Critical need for accurate, detailed and current view of financial status (by project and department-wide)
• If institutional accounting records provide this – fine!
• If not, you need a departmental system (“shadow accounting”).
DA 201 Course
Financial Planning
Question 2: Where are we going?Projecting expenditure/revenue forecasts into the future (for fiscal year)
• Special attention to personnel– Plan to cover soft money people for year– Note institutional policy for “layoffs”
Name Funding Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May JunWashington, G. GRIP 24% 14th
Lincoln, A. DOE-F 15%GRIP 60% 14thMIP 10%STC/Teleimmersion 15%
Bush, G. DOE-F 12.5%I-FLOW 50%NANO 25%STC/Teleimmersion 12.5%DOE-F 50%STC/Teleimmersion 50%100%
Clinton, B. GRIP 100% 14th
Ford, G. DOE-F 20%STC/Teleimmersion 13%
Eisenhower, D. MIP 100%
Johnson, L. STC/Teleimmersion 100%
Truman, H. GRIP 100% 14th
Reagan, R. MIP 100%
Roosevelt, F. DOE-F 30%I-FLOW 20%NANO 10%MIP 10%STC/Teleimmersion 30%
Personnel Funding Chart
Name Funding Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May JunLarry 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%
Personnel Funding Chart
DA 201 Course
Question 2: Where are we going?Projecting expenditure/revenue forecasts into the future (for fiscal year)
• Special attention to personnel– Plan to cover soft money people for year– Note institutional policy for “layoffs”
• Special attention to fixed expenses– Equipment (or software) maintenance
contracts, rent
Financial Planning
DA 201 Course
Do You Need A Departmental System?
Remember, central systems are first designed to meet institutional needs
Department needs are secondary Central systems often don’t provide
everything that is needed to “run a department”, for example:• Store backup for purchase quotes (your auditors
will want to see these)• Store vacation and sick leave data• Give you the flexibility to produce the reports that
you need• ENCUMBER SALARIES THROUGH BUDGET PERIOD
DA 201 Course
Are Central Records 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!
DA 201 Course
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
DA 201 Course
When To Implement A Shadow Accounting
System? When central records
• Do 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!
DA 201 Course
Additional Advantages Tracking Extra Data 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)
DA 201 Course
“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.
DA 201 Course
“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!
DA 201 Course
Goal
Financial Reconciliation Process
DA 201 Course
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)
Financial Reconciliation Process
UNC Central Report
UNIVERSITY OF NORTH CAROLINA - CHAPEL HILL FINANCIAL RECORDS SYSTEM
FBM090 - C2 SUMMARY ACCOUNT STATEMENT FOR 08/31/00 DIST# REPORT DISTRIB = 3276 INTERACTIVE GRAPHICS FOR MOLECULAR STUDIES & MICROSCOPY
REPORT PAGE 2531 PROGRAM ID FBM0929 ACCOUNT PAGE 1
ACCT: ATTRI
5-31136 0-110-32761
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: SUB -------- BUDGETS -------- -----------------
- ACTUALS ------------------ OPEN BALANCE
CODE DESCRIPTION ORIGINAL REVISED CURRENT MONTH FISCAL YEAR PROJECT YEAR COMMITMENTS AVAILABLE 1001 C/G PERSONNEL 395,190.00 255,069.96 255,069.96 1112 EPA NON-TEACH - 54,859.36 21,120.22 35,799.04 54,859.36 1113 EPA NON-TEACH - 9 365.19 2,369.00 4,034.78 9,365.19 1117 MO STU/OTHER NON 42:792.35 15,888.63 25,264.44 42,792.35 EPA NON-TEACH SA 107,016.90 39,377.85 65,098.26 107,016.90 1212 SPA ON CAMPUS 31,377.76 2,420.16 4,791.55 7,694.83 23,682.93 1251 SPA SEVER WAGES 15.38 4.84 9.58 15.38 SPA EMPLOYEE SAL 31,393.14 2,425.00 4,801.13 7,710.21 23,682.93 1411 NON-STUDENT WAGE 300.00 300.00 300.00 300.00 1451 STUDENT WAGE 1,410.00 468.75 915.00 1,410.00 TEMP EMPLOYEE WA 1,710.00 768.75 1,215.00 1,710.00 TOTAL PERSONNEL 395,190.00 395,190.00 42,571.60 71,114.39 116,437.11 23,682.93 255,069.96 1800 STAFF BENEFITS 60,086.00 41,545.31 41,545.31 1812 SOCIAL SECURITY 5,483.63 1,410.22 2,520.07 4,292.87 1,190.76 1813 SOC SECUR - HOSP 1,433.46 406.44 704.25 1,154.98 278.48 1822 STATE RETIREMENT 4,087.85 759.83 1,301.56 2,401.63 1,686.22 1831 MED INSURANCE OT 413.28 118.00 236.00 413.28 1832 MEDICAL INSURANC 738.68 128.25- 214.81 738.68 1833 HMO/HEALTH PLANS 2,596.30 281.97 582.74 898.37 1,697.93 1836 MEDICAL INS GRAD 469.02 207.99 469.02 469.02 1872 TIAA RETIREMENT 2,194.84 684.17 1,444.65 2,194.84 1873 TIAA-HEALTH PLAN 531.69 416.85 474.66 531.69 1876 ORP FIDELITY 359.10 359.10 359.10 359.10 1892 COMPOSITE BENEFI 232.84 85.13 142.21 232.84 STAFF BENEFITS 60,086.00 60,086.00 4,601.45 8,449.07 13,687.301, 4,853.39 41,545.31 1920 PROF CONSULTING 5,500.00 5,500.00 5,500.00 2000 SUPPLIES & MATER 9,500.00 7,384.00 7,384.00 2311 EDUCATIONAL SUPP 2,116.00 1,644.24 1,753.67 1,809.66 306.34 SUPPLIES / MATER 9,500.00 9,500.00 1,644.24 1,753.67 1,809.66 306.34 7,384.00 3100 TRAVEL 8,317.00 5.90- 5.90-
FR04B/PCB-08 Rev.,PCB-02:9
UNC Central ReportDATE RUN 09/08/00 UNIVERSITY OF NORTH CAROLINA - CHAPEL HILL REPORT PAGE 2539TIME RUN 11:09:05 FINANCIAL RECORDS SYSTEM PROGRAM ID FBM092FBM091 REPORT OF TRANSACTIONS FOR 08/31/00 ACCOUNT PAGE 5
DIST# REPORT DISTRIB = 3276INTERACTIVE GRAPHICS FOR MOLECULAR STUDIES & MICROSCOPY
FR04C/PCB-0890
ACCT: 5-31136 TO:
DEPT: 3276 CB 3175, SITTERSONSUB 2ND J.E. OFFSET BUDGET CURRENT BATCHCODE DESCRIPTION DATE TC~ REF. REF. ACCOUNT ENTRIES REV/EXP COMMITMENTS REF. DATE1876 J W Smith 08/31 042 M02 REG 179.55 APRM02 00083
J W Smith 08/31 042 M02 119 8.55 APRM02 00083J W Smith 08/31 042 JE2412 171.00 APRM02 00083
1876 CM TOTAL ORP FIDELITY 359.101892 PAYROLL RESERVE (16) 08/11 042 B03 REG 2.91 APRB03 00081
PAYROLL RESERVE (16) 08/25 042 B04 REG 3.47 APRB04 00082PAYROLL RESERVE (16) 08/31 042 M02 REG 78.75 APRM02 00083
1892 CM TOTAL COMPOSITE BENEFITS 85.131920 CNA 08/15 020 2000509 5,500.00 CGOL99 000811920 CM TOTAL PROF CONSULTING
FEES 5,500 00
2000 CNA 08/15 020 2000509 9,500.00 CGOL99 000812000 CM TOTAL SUPPLIES & MATERIALS2311 IEEE MAGAZINES 08/11 050 K399887 1,115.00 CGLLL 00081
IEEE INC 08/18 048 K399887 920261 1,115.OO 1,115.00- APC419 00081TAPE RESOURCES INC 08/10 050 K409250 519.30 CGLLL 00081TAPE RESOURCES INC 08/17 048 K409250 916738 519.30 519.30- APC415 00081DELL MARKETING LP 09/06 050 K434469 306.34 DP5003 00090D Davis 08/14 050 K435666 7.73 CGLLL 00081D Davis 08/22 048 K435666 921540 7.29 7.73- APC425 00082NEWARK ELECTRONICS 08/11 050 K435730 2.65 CGLLL 00081NEWARK ELECTRONICS 08/18 048 K435730 920163 2.65 2.65- APC419 00081
2311 CM TOTAL EDUCATIONAL SUPPLIES 1.644.24 306.34
3100 CNA 08/15 020 2000509 8,317.00 CGOL99 000813100 CM TOTAL TRAVEL 8,317.00
3121 R M Hall 08/24 048 T012732 923834 210.00 996.00- APC430 00082F P Albright 08/16 048 T012747 915818 349.79 349.79- APC413 00081C Wall 08/23 048 T012751 922967 206.00 206.00- APC428 00082C Wall 08/28 050 T012751 474.00- ENC912 00082
3121 CM TOTAL OUT/STATE TRANS-AIR 765.79 2.025.79-
3122 R M Hall 08/24 048 T012732 923835 63.85 APC430 00082F P Albright 08/16 T012747 915818 87.75 APC413 00081
3122 CM Total OUT/STATE TRAN-GROUN 151.603124 R M Hall 08/24 048 T012732 923835 570.55 APC430 00082
F P Albright 048 T012747 915818 692.65 APC413 00081C Wall 08/16 048 T012751 922967 232.77 APC428 00082
3124 CM TOTAL OUT/STATE SUB-LODGIN 10495.97
DA 201 Course
Financial Reconciliation Process
Step 2
Prepare shadow data (financial reports and transaction data) in the matching format as the central data.
Computer Science Department Report
Selection Criteria: Account Budget Report Report Run On: 10/10/00 11:00:47JAM Account=5-00000 Account 5-00000 PI Name Donald Duck Current Budget Period 5/15/00 5/14/01 NIH - GRIP – PRIME Budget Code Description Budget Encumbered Expensed Balance 1000 Personnel 395,190.00 30,192.39 116,437.11 248,560.50 1800 Faculty/Staff Benefits 60,086.00 4,621.33 13,687.30 41,777.37 1900 Con sultants/Temporaries 5,500.00 5,500.00 2000 Supplies & Materials 9,500.00 3,345.28 1,809.66 4,345.06 3100 Travel 8,317.00 4,542.90 5,317.90 (1,543.80) 3130 Foreign Travel 2,900.00 756.43 2,143.57 3200 Communication 500.00 16.28 45.92 437.80 3400 Printing/Binding 24.72 (24.72) 3800 Computer Services 49,858.00 9,399.48 6,356.34 34,102. 1 8 3900 Miscellaneous 600.00 463.25 136.75 4400 Maintenance Contracts 10,000.00 9,606.72 393.28 5000 Equipment 38,000.00 38,000.00 6500 Educational Grants 13,153.00 13,153.00 8900 Indirect Costs 243,764.00 178,559.62 65,204.38 0.00 8982 Restricted Funds 18,307.00 18,307.00 0.00
I Total for Account: 5-00000 855,675.00 258,591.00 210,103.01 386,980.
Selection Criteria:Object Code=231 1, Object Set=UNC, Account=5-31136 Account Breakdown Report Report Run On:10/11/00 2:16:50PM
Account
Budget Code 2000
5-31136 NIH - GRIP - PRIME
Supplies & Materials
5/12/00 6/30/00 VISA CARD CHARGES NONE JET VISA04 NONE 1,862.90 55.99
5/19/00 8131/00 GUTHOLD - IEEE REPRINTS 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
7/18/00 HDA APPLE K430340 NONE 286.20
7/20/00 INV# 387776 MARKERT HARRIS( K431865 NONE 246.917/26/00 8/31/00 INV# 410860217 DELL STONEM k434469 NONE 306.347/31/00 8131/00 REIMBURSE FOR FREEDM, K435666 NONE 0.44 7.29
REFRESHMENTS FOR NIH SITEVISIT
7/31/00 8/31/00 YELLOW LED'S/K411941NEWARK HARRIS( K435730 NONE 2.658/9/00 MINI-CIRCUITS INV# 792054 BRUMBA K440673 NONE 457.888/9/00 MINI-CIRCUITS INV# 796875 K440683 NONE 170.708/30/00 INV# P49056420103MICROWA K451954 NONE 149.00
9/22/00 VISA CARD CHARGES NONE NONE VISA03 NONE STINE 116.58
Total for Budget Code 2000 0.00 3,596.95 1,809.66
Balance Available for Budget Code 2000 (5,406.61)
3,596.95 1,809.66
DA 201 Course
Step 3Compare differences in expenditures/balances by object code between central and departmental financial reports
• This will alert you to particular areas of discrepancy
Summary of Discrepancies
Central Records Departmental Records Difference
Personnel 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
DA 201 Course
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 in the comparison of financial reports.
Financial Reconciliation Process
DA 201 Course
Every discrepancy must be researched
and explained!
IMPORTANT
The Reward is Worth the Effort
DA 201 Course
What Are Likely Explanations For These Discrepancies?
Timing errors
Posting errors
Processing errors (the form is still sitting on your desk!)
DA 201 Course
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.
DA 201 Course
Reconciliation Unmatched central items “got past
you” and need to be recorded in the shadow system, or explained out in a reconciliation worksheet.
The Right Tool Does Matter!
Our Motto: Whistle While You Work
That’s
All Folks!