Transcript ADE: Automated Data Exchange • Basic Overview • Your Software Vendor • Submitting a financial file to CDE • Interpreting ADE errors: 1st and 2nd.

ADE:
Automated Data Exchange
• Basic Overview
• Your Software Vendor
• Submitting a financial file to CDE
• Interpreting ADE errors: 1st and 2nd Level
• Fixing your Errors: Fatal and Warnings
• Properly reconciling “Warning” errors you cannot fix
• ADE Reports
ADE: Basic Overview
• ADE is the way your district/BOCES
transmits financial (and other, IE Human
Resources) data to CDE via the internet
– A good place to read about the financial facet
of ADE is on the CDE website:
https://cdeapps.cde.state.co.us/doc_fd_overview.htm
ADE: Basic Overview continued
• You submit financial files here:
https://cdeapps.cde.state.co.us/
• You need passwords to submit ADE data
• If you don’t know your district’s passwords call
CDE: 303-866-6843
• Only one set of passwords for financial data
transmissions is assigned to each district, and
those passwords are usually set up for the
business manager or bookkeeper: the person
who will be doing the actual ADE submissions
ADE: Software Vendors
• You should have a phone number and
contact person (support staff) for someone
at your software company
• If you do not yet know how to create a
Financial December ADE data file using
your financial software, please contact
your software support person ASAP.
ADE Data File
• Your ADE Financial December data file
will be named with an “a” or a “c”
extension – based on your type of
software (my example uses District 0880)
fd0880.01a
fd0880.01c
“a” files are 44 bytes of information in the
account string, “c” files are 39 bytes
ADE: Edit Errors
• The ADE system runs the data you submit
against edits. If you fail an ADE edit you
are given a warning error message.
• ADE has two levels of errors
– 1st level errors revolve around account code
issues
– 2nd level errors revolve around dollar
amounts: CDE says you need to match a
particular $$ amount, and you have not
ADE Data Submissions
• Please begin your ADE data submissions
BEFORE your auditor arrives at your
district.
– Passing 1st level “clean up” edits gives you a
great head start
– Being at 2nd level Ade edits when your auditor
arrives allows him or her to give you adjusting
journal entries to correct/address those 2nd
level edits
ADE:
st
1
Level Errors
• First level errors deal with account code
issues
• The way errors are written indicates how
you should interpret them. IE, if the error
begins with the word “Job”, then the edit
has found a problem with a Job Class
code.
ADE
st
1
Level Edit Example
• JOB CLASS CODE must be valid if Object Code
is 0100-0299
0270 10 500 23 2320 0221 000 2011 0000000002175
This edit is taking issue with the 000 job class code.
This is a Salary/Benefit Account (Object 0221 is
Medicare). The edit requires the account to have a
valid job class code: IE 201 for a teacher.
ADE
st
1
Level Edit Example
• Source 4000 may not be reported with this grant. Source
4000 is Federal Revenue from CDE. Source 4010 is
Federal Revenue from State agencies other than CDE.
Source 4020 is Federal Revenue direct from the Federal
Government.
1110 11 910 00 0000 4000 000 4041 0000000426223 9893
The edit does not allow Source 4000 (Federal Revenue from
CDE) to be reported with Grant 4041 (a Federal Grant that CDE
does not sent to Districts)
ADE 1st Level Edit Example
• Locations 800-899 can not be accepted with
Objects 0100-0299
1110 11 800 00 1791 0150 000 3113 0000006128290
Locations 800-899 represent District Wide Costs
that are not directly related to the level of service
provided by the district, and thus we would not
expect to see a Salary or Benefit coded with that
range of Locations.
ADE:
st
1
Level Errors Example
• Funds 25, 31-39, 51-59 can not be
accepted with Programs 0001-2099
1110 25 663 12 1791 0170 330 3130
Instructional Programs 0001-2099 cannot
be accepted with the transportation fund
(25), Debt Service Funds (31-39), or
Enterprise Funds (51-59)
nd
2
Level ADE Edits
• Deal with Dollar Amounts
– What CDE is picking up as dollar
amounts in your accounts
– Comparing dollar amounts in your
accounts to CDE figures
nd
2
Level Edit Example
• ASSETS(8100-8251) must = Liabilities(74017590) + Fund Equity(6700-6880), for funds 1085.
Fund=51
30,527.74 not = 1,836.80 + 54,612.26
56,449.06
This edit is adding liabilities and fund equity in
Fund 51 and seeing if the total matches the
assets reported in Fund 51.
nd
2
Level Edit Example
• BEGINNING FUND BALANCE (BFB) (WARNING: you must provide
documentation to CDE justifying this variance) for the current year
must equal the previous year's Ending Fund Balance (EFB). To
allow for rounding, the BFB may be within $1 of the EFB. BFB is
calculated as the EFB (includes prior period adjustment) plus
expenditures minus revenues.
Fund 51 current BFB=34,220.78, previous EFB=8,299.46
This edit is looking at the current year (FY10-11) beginning fund
balance (which is a formula driven figure) and it is comparing it
to the ending fund balance reported a year ago (FY09-10).
Continued
163,911.77 Expenditures
184,303.25 Revenue
54,612.26 Ending Fund Balance/Fund Equity
54,612.26 + 163,911.77 - 184,303.25 = 34,220.78
ADE:
nd
2
Level Errors continued
• State Share (State Equalization). The amount the district has
recorded as state share must equal the amount of CDE State
Share Payments. State Share equals Gross State Share less
Division of Wildlife (DOW) plus Business Incentive Agreements.
Be sure to charge applicable CDE “withholdings” as expenses;
do not “net” against Revenues received.
District S.E. = 7,360,797.01
CDE S.E.= 10,732,467.14
ADE Warning Edits
• Warning edits:
– Some Edits say WARNING
– These are edits which you do not necessarily have to
fix. CDE knows that for these edits, there may be
circumstances (IE timing issues) which prohibit you from
“fixing/passing” the edit.
– Regardless, you need to examine these and make sure
you cannot fix them
– All WARING ONLY EDITS that you cannot fix need to
have a reconciling statement prepared by you,
explaining why the edit cannot be fixed
Warning Edit Example
•
GRANT (WARNING: Provide documentation justifying this variance) The
amount the district recorded for Grants Receipts should equal amount of
CDE payments, making adjustments for accounts receivable and deferred
revenue changes from prior year to current year. When you have an option
of grants to use (IE 5027/6027) this edit will list only one grant. Edit
excludes flow through (Source 3951 & 4951). Download and print the Grant
Revenue Received Reconciliation Report: differences between CDE
Payment and District Receipt District
Grant 4010 :
Receipts=36,764.00
CDE payments=46,601.26
Continued
CDE paid the District 46,601.26 for Title IA – this is a cash basis
figure, payments made July 1, 2010 through June 30, 2011.
The District has recorded revenue of 36,764.00
Acceptable Reconciling Statement: The difference of $9,837.26 is a
late payment distributed by CDE before June 30, 2011, but the
district did not receive the payment until after July 1st, 2011 - and
booked as revenue in July:FY11-12.
Unacceptable Reconciling Statement: Simply stating:“Timing
Issue”. While the difference is a timing issue, you need to give the
specifics.
ADE: Reports
• There are a host of reports produced for you when you
reach 2nd level edit errors
•
•
•
•
•
•
•
•
•
•
•
•
•
fdaccreditationchecklist.doc - Accreditation Checklist: MUST BE COMPLETED, signed by
Superintendent and Board President.
Any 'NO' answers must be explained in detail. Download this report, do not View report.
fd0970.sac File Status - Status of data file at CDE
fd0970.dbc Data Report - Management Integrity Report
fd0970.dhc Data Report - Auditor's Integrity Report: Download this report to approve or reject data.
This final version of this report (after approving data) must be bound in the audit
fd0970.dpc Data Report - Charter School Auditor's Integrity Report
fd0970.dgc Data Report - CDE-5 Child Nutrition Programs
fd0970.dic Data Report - Local, State, Intermediate & Federal Grants
fd0970.djc Data Report - Bolded Balance Sheet Report
fd0970.dqc Data Report - Charter School Bolded Balance Sheet Report
fd0970.dmc Data Report - Grant Revenue Received Reconciliation Report
fd0970.cbc Data Report - Indirect Cost Rate
fd0970.ebc Edit Report - Edit Checks - Errors and/or Warnings
fd0970.mac Mail Message- Failed Edit checks
ADE Reports
• While all ADE generated reports are
beneficial and meaningful, the following
reports are critical for you to understand in
the ADE process:
– Auditor's Integrity Report
– Bolded Balance Sheet Report
– Grant Revenue Received Reconciliation
Report
– Accreditation Report/Checklist
ADE Reports
– Auditor's Integrity Report
This report is a required part of your audit.
It will eventually say “Final” at the bottom of
the report. All the figures on this report
must match what is presented in your audit:
Ending Fund Balance, Expenditures,
Revenues, and Beginning Fund Balance.
ADE Reports
– Bolded Balance Sheet Report
This report will not become a part of
your audit, though some auditors
choose to include it as a part of the
audit. The detailed assets, liabilities
and fund equity figures on this report
must match what is presented by your
independent auditor.
ADE Reports
• Grant Revenue Received Reconciliation Report
This report details your grant activity. It
compares what you report as grant revenues,
grants accounts receivables and grants
deferred revenue (your grant accrual figure) to
the CDE cash basis payment figure (taken
from your annual COFRS Sub-Recipient
Report)
Accreditation Checklist
• The Accreditation Checklist is a report
which you must fill out and submit with
your audit.
The questions on the report are written for
you to answer “yes” or “no” to, and any
question that you answer “no” to requires a
corrective action response to be given on
page 4 of the report
COFRS Reports
• COFRS Sub-Recipient Reports
– COFRS (Colorado Financial Reporting System) detail
the revenue that CDE sent you.
– They are produced annually and are posted in early
August on the CDE website:
http://www.cde.state.co.us/cdefinance/sfcofrs11.asp
ADE Material on the CDE Website
• Helpful ADE material is posted annually on the CDE website.
This material covers a variety of ADE figures, COFRS Reports,
a Crosswalk between the COFRS Reports and the Chart of
Accounts Grant Codes.
http://www.cde.state.co.us/cdefinance/FinancialReportingFY2010-11.htm