Data Pipeline Finance Webinar May 16 th

Data Pipeline: Finance § Webinar: May 16 th , 2013 § To join a webinar, please log into the meeting first at the 1 http: //connect. enetcolorado. org/psfu 1/ web address. After you log-in as a guest (please include your name and the name of the District) and have entered the meeting room, (and you are not on an extension telephone number) please click on the phone icon to have the system call you (or go through the meeting tab for audio options). If you are on an extension telephone number and/or are not using the Vo. IP option, you will need to call the 1 -866 -408 -1178 number on your phone to join the audio portion of the webinar session.

Webinar Etiquette § Mute Your Phone (*# Mutes and Un-mutes Individual Phones) § Enter your Name and your District when logging into the webinar (no password will be needed) § If you have a question or comment either: § Type it in the Chat Box for a response OR § Raise Your Hand: § A presenter will call your name § After your name is called, un-mute your phone and speak your question/comment § Please be respectful of others time; we may need to 2 have a separate conversation later to best help you

Introductions § CDE Staff Introductions § williams_a@cde. state. co. us § 303 -866 -6843 Support at: datapipeline. support@cde. state. co. us 3

Discussion Topics: May 16 th § Discussing Phase II Financial Pipeline Testing: § § § 4 § § Open to all Districts and BOCES May 20 t h – 31 s t Discussing Pipeline passwords and Local Access Managers (LAM) - the person at your District who assigns Pipeline passwords Submitting a financial data file via Pipeline Accessing Pipeline reports (Edit Error Reports, Auditor’s Integrity Report, Bolded Balance Sheet Report etc) Functions of Financial Data Pipeline that are new and different from the Automated Data Exchange (ADE) Discussing aspects of the Financial Data Pipeline that are similar to ADE Overview of Pipeline information on CDE website Update on Communication between CDE and

Data Pipeline: Phase II Finance § Phase II is open to everyone § § Phase II is open May 20 th through May 31 st Higher level of edit and report accuracy than Phase I § Phase II is not yet 100% - not all edits have been 100% programmed § Phase II an opportunity to familiarize yourself with Pipeline before Pipeline officially opens § You can submit a financial data file § Produce Pipeline generated reports § Get a “feel” for the financial facet of data Pipeline

Financial Data File Submission § File Submission – two ways to submit a file • You can submit your own data as long as the data file is properly formatted • • • This will require formatting on your end which Public School Finance Staff can assist you with: Directions on CDE Website Some software vendors are working towards being able to produce a properly formatted file for Phase II You can submit a standard “Template” file which CDE has been prepared for people who simply want to get a “feel” for file submission – this file is posted on the CDE website • If you use the Template file you must populate the highlighted District Code Column with your District’s Code

File Format § Financial Files for Data Pipeline are a standard 48 byte length § District Code: 4 bytes § Administrative Unit Code: 5 bytes* § Unique School Code: 4 bytes* § Fund Code: 2 bytes § Location Code: 3 bytes § SRE Code: 2 bytes § Program Code: 4 bytes § Object/Source/Balance Sheet Code: 4 bytes § Job Class Code: 3 bytes § Grant Code: 4 bytes § Amount: 13 bytes*

Administrative Unit Code § The Administrative Unit Code is a five byte field which some Districts have been reporting as a zerofilled account code field in their data for years (since ADE opened) § This field is required for all FY 12 -13 Pipeline files § For FY 12 -13 the field will be zero-filled, and there will be no edits tied to the Administrative Unit Code

Unique School Code § The Unique School Code is a four byte field which is brand new for everyone § This field is required for all FY 12 -13 Pipeline files § For FY 12 -13 the field will be zero-filled, and there will be no edits tied to the Unique School Code § There is a Unique School Code assigned for every school building at a school district – if you have ten schools in your District, you have already been assigned ten Unique School Codes

Amount § The Amount Field in your data string can accommodate up to 13 bytes § It is a numeric field § Negative dollar amounts cannot be preceded by zeroes § Formerly, ADE would have accepted the following: § 000000 -587941 § -000000587941 § Pipeline Negative Dollar Amounts must be formatted as: § -587941 § It is still a 13 byte field § Whole/positive number can still be preceded by zeros, but preceding zeros for positive dollar amounts are not necessary

File Types Accepted in Pipeline § Unlike ADE which only accepted ASCII files, Pipeline will accept the following: § txt § csv § Excel § If an Excel file is submitted, the first row of data in the file must be formatted as a header: label each column § PLEASE keep in mind that all changes, all adjusting journal entries that you need to make to pass Pipeline edit errors are made in your software system prior to submitting a file. Do not make one-sided entries.

File Names § In Pipeline, it is up to the District to name the file being submitted § You are advised to create a file naming system that allows you the ability to track and locate the files you have submitted with ease § File_1_Finance § File_2_Finance § Etc… 12

Software Vendors § Information on standard file length and proper file formatting has been communicated with all known software vendors § It is essential that everyone is able to submit the standard 48 byte, properly formatted file for FY 12 -13 Pipeline Submissions § It is my understanding that some software vendors are working towards being able to create properly formatted files for districts wanting to submit a file during Phase II testing 13

Passwords § Before you are able to submit a file in Pipeline you need passwords § CDE no longer assigns passwords § Passwords are assigned by Local Access Managers (LAMS) § Password process information has been sent to all LAMs 14

Similarities: ADE vs. Pipeline § Data still sent to CDE from District’s system § Finance: Still a Periodic Collection: § Opens in September 2013 (August of 2014) § Districts submit as many times as needed before the December 31 st Statutory Deadline

Differences: ADE vs. Pipeline § For the Financial facet of Pipeline, the differences are primarily enhanced function § More types of files accepted § Greater control at the District level § More ways to view data/errors 16

Process § Submitting a financial file in Pipeline remains quite similar to what everyone is used to seeing in ADE § Your software system creates a file § You log into Pipeline with passwords § You submit the file § The file is subject to two levels of edits 17 § First tier edits: address account code issues § Second tier edits: address dollar amounts § You produce reports (EG – the Auditor’s Integrity Report) § You can submit data as many times as needed § Your end goal is to transmit and finalize data which matches your annual independent audit

Process: Data Roll-Up § One big difference between ADE and Pipeline is that Districts now Roll-Up the data at the District level § Data file is submitted § 1 st tier edit errors are fixed § Once 1 st tier edits are corrected and a revised file is submitted, the District must initiate the roll-up process § Finance December § Status Dashboard: click green “Submit” button § Click green “Roll Up Data” button § Message: Data for LEA marked complete successfully § Correct 2 nd tier errors, initiate roll-up process again § When “Rollup Validation Errors” are zero, you click “Submit to CDE” and this 18 finalizes your data

Roll Up Process § This now done at the District level because you now have the ability to Append data § You can submit data in smaller batches § EG – you could submit one fund at a time § EG – a District with a Charter School or Schools can submit the District’s data first. Clean up any errors, and append additional Charter data onto the District’s already “clean” data as that Charter School information becomes available § Note: as we get deeper in Data Pipeline, a webinar will be held prior to September to discuss Appending data 19

CDE Website § CDE Home Page: http: //www. cde. state. co. us/index_home. htm § For Educators § K-12 Education § Data Bases and Reporting § Data Pipeline § Periodic Collections § Finance December § There is also a link on the School Finance page 20

CDE Website § File Layout and Definitions § Business Rules (edits) § Upcoming Webinars and Other Trainings § Recordings of Previously Held Webinars and Corresponding Powerpoints § Formatted “Template” file for Phase II file submission § You will need to populate the yellow highlighted column with your 4 digit District code 21

CDE Website § Submitting a Finance December Pipeline file is done at the following link: § https: //cdx- test. cde. state. co. us/pipeline/ § Please not that this is a testing URL, and the URL for 22 Pipeline will be different when Pipeline officially opens in September