ADE Automated Data Exchange Basic Overview

ADE: Automated Data Exchange • Basic Overview • Your Software Vendor • Submitting a financial file to CDE • Interpreting ADE errors: 1 st and 2 nd 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) fd 0880. 01 a fd 0880. 01 c “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 – 1 st level errors revolve around account code issues – 2 nd 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 1 st level “clean up” edits gives you a great head start – Being at 2 nd level Ade edits when your auditor arrives allows him or her to give you adjusting journal entries to correct/address those 2 nd 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 000002175 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 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 1 st 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 (FY 10 -11) beginning fund balance (which is a formula driven figure) and it is comparing it to the ending fund balance reported a year ago (FY 09 -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 1 st, 2011 - and booked as revenue in July: FY 11 -12. Unacceptable Reconciling Statement: Simply stating: “Timing Issue”. While the difference is a timing issue, you need to give the specifics.

ADE: Reports • There a host of reports produced for you when you reach 2 nd 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. fd 0970. sac File Status - Status of data file at CDE fd 0970. dbc Data Report - Management Integrity Report fd 0970. 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 fd 0970. dpc Data Report - Charter School Auditor's Integrity Report fd 0970. dgc Data Report - CDE-5 Child Nutrition Programs fd 0970. dic Data Report - Local, State, Intermediate & Federal Grants fd 0970. djc Data Report - Bolded Balance Sheet Report fd 0970. dqc Data Report - Charter School Bolded Balance Sheet Report fd 0970. dmc Data Report - Grant Revenue Received Reconciliation Report fd 0970. cbc Data Report - Indirect Cost Rate fd 0970. ebc Edit Report - Edit Checks - Errors and/or Warnings fd 0970. 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/sfcofrs 11. 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/Financial. Reporting. FY 2010 -11. htm