School Finance and District Support

Spreadsheet Issues, 2016-2017 End of Year Financial Report

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

We encourage school officials completing the FY17 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. DOR's FY17 local aid distribution did not reflect the virtual school administration fees ($75 for each student enrolled at a virtual school) that districts were obligated to pay. These amounts were included in the school choice tuitions that we pre-populated in the workbooks, but when the reports are submitted we will reduce the school choice tuitions to match the local aid distribution. There were 234 districts impacted by this error, but for most the difference was less than $1,000. We will increase the school choice tuition amounts in the FY18 local aid distribution to correctly account for the FY17 virtual school administration fees.

  2. The text at the top of the Schedule 19 City/Town and Estimated Revenues schedules reference the 2016-2017 budget, but they should refer to the 2017-2018 budget. We will correct this text when we process the reports.

  3. In edit 4 on the edits worksheet, which checks the correspondence between revenues and expenditures from grants and other funds, some of the EOYR line and column labels are incorrect. This error only impacts the text descriptions, not the formulas that test for discrepancies within the report. The table below shows the cells that were effected, including the current text and the correct text. We will make the necessary corrections when we process the reports.

    ColumnRowCurrent textCorrect text
    C154Schedule 1, C.2, Column 3Schedule 1, C.2, Column 4
    G154Schedule 1, C, Line 471Schedule 1, C, Line 481
    C238Schedule 1, C.2, Column 4Schedule 1, C.2, Column 5
    C322Schedule 1, C.2, Column 5Schedule 1, C.2, Column 6
    C406Schedule 1, C.2, Column 6Schedule 1, C.2, Column 7
    C490Schedule 1, C.2, Column 7Schedule 1, C.2, Column 8
    C574Schedule 1, C.2, Column 8Schedule 1, C.2, Column 9
    C663Schedule 1, C.2, Column 10Schedule 1, C.2, Column 11
  4. On the reports tab line 248, the total FY17 expenditures for the IDEA maintenance of effort (MOE) calculation are not correct. The underlying issue is on the calcmisc worksheet. If you unhide that worksheet, the formula in cell E568 references EOY17!J630, but it should refer to EOY17!K630, to capture total Circuit Breaker expenditures. We will correct this formula when you submit your report.

  5. On the reports tab line 131, the total FY17 DESE administered federal grant expenditures are not correct. The formula references EOY17!G630, but it should sum EOY17!E630, EOY17!F630, and EOY17!G630. We will correct this formula when you submit your report.

Helpful Hints

  • The FY17 amendment smart form is available on the MassEdu Gateway, under School Finance in the application list. Amendments to last year's FY16 reports can still be made. Do not use Firefox or Mozilla when completing the amendment smart form.

  • If you get a macro error on Schedule 3, it is usually caused by saving the file with a name other than 17eoy001.xls (the 3-digit number in the filename represents each district's LEA code). Also, if there is a "(1)" appended to the end of the filename—resave the workbook with the original filename. Be sure to save to Excel 2003 (.xls) and not a later version.

  • Macros must be enabled for the Schedule 3 update or print functions to work. Some spreadsheets have security set at the highest level in the tools\macros\security menu. A lower level usually works, although in some cases it is a local network problem. The Windows 7 command sequence is developer\macro security\enable all macros\ok\file save. Save the file and then reopen.

  • Remember that there is a customized print menu: Hit control p. Your own printer settings control the size.

  • If numbers appear on the screen as asterisks, use a higher zoom level (view\zoom).

Contact Us

Massachusetts Department of Elementary and Secondary Education
75 Pleasant Street, Malden, MA 02148-4906

Voice: (781) 338-3000
TTY: (800) 439-2370


Disclaimer: A reference in this website to any specific commercial products, processes, or services, or the use of any trade, firm, or corporation name is for the information and convenience of the public and does not constitute endorsement or recommendation by the Massachusetts Department of Elementary and Secondary Education.