Massachusetts Department of Elementary and Secondary Education
Go to Selected Program Area
Massachusetts State Seal
News School/District Profiles School/District Administration Educator Services Assessment/Accountability Family & Community  
> Administration Finance/Grants PK-16 Program Support Information Services  

School Finance: Accounting and Auditing

Spreadsheet Issues, 2012-2013 End Of Year Financial Report

School officials completing the FY13 End of Year Financial Report (EOYFR) are encouraged to contact ESE 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.

  1. Amendment smart form

    Amendments to FY12 can still be made using the automated amendment smart form on the secure portal. The FY13 application should be available in October.

    Do not use Firefox or Mozilla as a browser for the amendment smart form application.

    Once the FY13 smart form is ready, amendments from the previous week will be processed by the end of the day each Monday.

  2. Title 1 and Title 2A Teacher Quality federal grants duplicated for some districts.

    For some districts the Title 1 grant revenue on line 300, column 1 regular education was double counted when the line was populated by ESE. Also, for some districts the Title 2 Part A Teacher Quality grant was duplicated in column 5 undistributed. After discovering the duplication the grants report available on the end of year website was corrected.

    FY13 End of Year Report page

    ESE will automatically fix the error once the report is submitted. If you want to make the correction yourself, please indicate on the comments sheet that you have done so.

  3. Row numbers in booklet don't match eoy spreadsheet, schedule 7

    Correct row numbers in the booklet should be:

    Linecorrect row
  4. Printing schedule 7 and 19: Line 7320 is cut off from the school committee page, and lines 7690 and 7750 are cut off on the city and town page. Either manually set up those two pages to print the extra lines, or else change the print macro.

    To change the print macro, find the macro menu box 1. Select "printsch19" and hit "edit". In the section named "s19p1" change the range from "$a$1066:$j$1098" to "$a$1066:$j$1099". In the section named "s19p2" change the range from "$a$1109:$j$1141" to "$a$1109:$j$1143". Hit "alt" and the "F11" keys to leave the macro. Remember to save the file.

    Schedule 7 has a similar problem with the last two lines. Select the "s7p1" macro and change the range from "$A$1045:$m$1054" to "$A$1045:$m$1056".

  5. Extraordinary maintenance limit is now $150,000 per project, not $100,000 as indicated in the booklet instructions.

  6. Schedule 19 school committee heading in the "eoy13" sheet of the workbook should be "Annual School Budget 2013-2014"" not "Annual School Budget 2012-2013".

  7. Three year comparisons on the "reports" sheet. Line 190 "Insurance for Active Employees" is referencing the employee separation costs line from schedule 19, not the active employees insurance line. This will be corrected by ESE after the file is submitted.

  8. Schedule 7 total expenditure column 5 does not total correctly for the two new homeless transportation lines 4283 and 4285. This may cause edit number 1 to appear. Ignore the edit if the disparity between schedules 1 and 7 equals the sum of the amounts entered in columns 1 and 2 on the two Schedule 7 lines. The formula will be corrected by ESE after the file is submitted.

  9. Obtaining amended ESE copies of FY13 End of Year Financial reports.

    We will post the final version of every district's FY13 report to the dropbox in July, 2014. In the meantime if you need a copy reflecting all amendments to date, contact Roger Hatch at 781-338-6511 and he will post the current file.

  10. The ESE Security Portal recently changed its web address. The printed materials distributed at the workshops had the old address. The new address is

  11. There were 22 districts whose final FY12 charter reimbursements necessitated repayments to the Commonwealth in the December 2012 local aid distribution. Those repayments were handled as a tuition charge on the state treasurer's statement, but were not included in the populated amounts on the charter tuition lines 1790 or 2230 on schedule 1. The districts were notified by email and the correction will be made by ESE upon submission of the file.

  12. Edit 9 has a formula error in e776. If this causes the edit to appear for your district, ignore the edit and ESE will correct the formula upon submission of the file.

  13. Regional district bonus aid and impact aid will be added by ESE to row 190 Foundation Reserve upon submission of the file, for the nine districts that received payments from either of those accounts.

  14. The charter facilities aid on line 170 erroneously includes small amounts of facilities tuition paid directly to charter schools by the Commonwealth (in cases where the student had been at a private school the previous year). The correct amount can be at this link on the school finance web site.

    Select the second spreadsheet which is named FY13 CHARTER TUITION PAYMENTS AND REIMBURSEMENTS FOR SENDING DISTRICTS. The amount currently on the eoy file is in column Q. For the 94 districts where column J is lower, the amount in column J will be inserted onto the eoy file upon submission.

Helpful Hints

  • If you get a macro error on schedule 3, it may be that you are using a version of the file that has been saved with a name other than the standard "12eoy***.xls" (where the *** represents your three-digit lea code). Save the file under the original name and the problem usually disappears.

  • Enabling macros: macros must be enabled for the schedule 3 update or print macros 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 print menu: hit "control p".

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



Last Updated: September 26, 2013
E-mail this page| Print View| Print Pdf  
Massachusetts Department of Elementary and Secondary Education Search · Site Index · Policies · Site Info · Contact ESE