Spreadsheet Issues, 2018-2019 End of Year Financial Report

FY19 End of Year Financial Report (EOYR) workbooks are currently available in the MassEdu Gateway.

We encourage school officials completing the FY19 EOYR to contact DESE about any technical difficulties they encounter (downloading and uploading, macros, printing, cell references, etc). If such problems are confirmed, this document will identify them and explain how to correct them if necessary.

Spreadsheet Issues:

1. Two of the validation checks on the excess cost calculator are incorrect. In Part B, the validation check did not include Purchase of Land Buildings (7100, 7200). In Part C, the validation check is not using the correct cells from the eoy19 tab, it should tie out to excel cell F628. In Part F, the validation check includes all the IDEA expenditures and should only include School Committee and City/Town expenditures. As noted in the excess cost calculator please be aware that DESE is only asking for 240 IDEA funds to be reported in the EOY19 tab, any other IDEA funds included in the calculator would cause the validation check to work improperly. We will correct these validation cells when you submit your report.

2. The charter facilities aid amounts on Schedule 1, line 170, column 5, are not correct. We will correct these amounts when you submit your report. You can see the correct facilities aid amounts in Column L of the final FY19 charter tuition workbook .

3. FY18 Grant Revenue for the Federal USVI/Emergency Impact Aid Program (Fund Code 312) was incorrectly included in the Schedule 1, I. Revenues section of the FY19 EOYR (excel cell I38). We will remove these Federal Grant Revenue amounts when you submit your report.

4. In the Comparison section of the Reports tab, specifically, Schedule 3 Instruction Expenditures, School Committee (Excel rows 149 – 183), the FY18 data incorrectly includes Federal and State grants as well as revolving and Special Revenue Funds. It should have only included FY18 School Committee expenditures. We have provided an additional resource , found on the FY19 EOYR landing page, which provides corrected FY18 instructional data. Please copy and paste your FY19 instructional data into the file and use it as a resource when conducting your EOYR reviews. We will also correct these amounts when you submit your report.

5. DOR's final FY19 charter school tuition assessments and aid payments were not correct for some districts. For the assessments, if you see a difference between the amount populated in the EOYR on Schedule 1 lines 1790 or 2230, column 6 and what you were actually assessed (positive or negative), the EOYR is correct and reflects what you should have been assessed. On the charter aid side, if the total difference between the amount populated in the EOYR on Schedule 1 line 170, column 6 and what you were paid is different than the discrepancy between the incorrect and correct facilities aid number populated in the EOYR (see item 2 above), then you did not receive the correct aid payment. DOR will be adjusting tuition assessments and aid payments for the impacted districts in FY20 to correct the FY19 errors. We will be posting more information on what these adjustments will be once it becomes available.

6. On rows 255 and 258 of the Reports tab, the FY19% of FY18 formulas, column G, are incorrect. Please review the figures shown manually and we will update the formulas in column G once we receive your district's FY19 EOYR.

7. Some of the late FY18 DESE administered grant revenue drawdowns were not prepopulated on the FY18 EOYR, which is raising questions related to revenue reporting in FY19. Due to the timing of when the FY18 grant revenue data was pulled from the state accounting system, we missed some of the later FY18 grant revenue drawdowns. Once districts have identified those late FY18 drawdowns we are asking them to amend their FY18 reports with those missing revenue amounts. We have posted an updated FY18 grants file to include the amounts we excluded from the FY18 EOYR. Please use this file to verify the amounts before filing your amendment. Each spring districts are asked to amend their grants down to indicate how much of their grant will roll over to the next fiscal year (if applicable) and when they do, we reduce the encumbrance to match the grant amendment. For EOYR reporting purposes, any funds that are drawn against the encumbrance for a given fiscal year are considered part of the activity for that fiscal year. When we generated the FY19 EOYR templates, we waited as long as possible to pull the grant revenue data from the state accounting system in order to minimize excluding late drawdowns.

Last Updated: October 2, 2019