Spreadsheet Issues, 2017-2018 End of Year Financial Report

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

We encourage school officials completing the FY18 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. The Schedule 3 macro was not updating the first two rows of data properly for districts with only one school. We corrected this issue and posted updated EOYR workbooks for these districts to the Security Portal on August 28^th. If your district only operates one school, please download and work on the updated copy of your EOYR.

2. DESE recently announced the awards for the Federal Emergency Impact Aid for Displaced Students grant program (fund code 312). Districts must obligate funds received under this program by December 31, 2018 for expenses incurred during the 2017-2018 school year. We updated the grants workbook to include these funds, shaded in blue. Districts receiving these funds should add the revenue to the undistributed column on the DESE Administered Federal grants line on their reports, line 300, column 5, and include the expenditures in column 3 on Schedule 1 C.2. Districts that served displaced students with IEPs can use these funds to charge expenses previously charged to the Circuit Breaker account. We will be provide additional guidance on allowable uses for these funds soon.

3. We did not include final FY18 grant payments that were distributed in July for expenditures encumbered as of June 30 on lines 300 and 510. We updated the grants workbook to include these funds, shaded in green. You can add these funds to the revenues already included on these lines. If they are not added, we will add them when you submit your report.

4. On the Edits worksheet, one of the formulas that tests the correspondence between regular day transportation expenditures on Schedules 1 and 7 (cell I25) does not include foster care transportation reported on line 4286. If you have foster care transportation, the two schedules will not agree by that amount. We will correct this formula when you submit your report.

5. Districts that were overpaid charter school tuition reimbursement will see a difference between the tuition reimbursement and expenditures posted on the EOYR and those posted by DOR. Since the Treasury cannot functionally request refunds from the aid account, it recoups the money by collecting it as charter tuition. While DOR cannot charge the aid account, the documents they post on line usually show the changes as if they do. DOR will be updating their web files so that their data matches the data included in the EOYRs.

6. Districts that received Circuit Breaker Extraordinary Relief will have an incorrect Circuit Breaker revenue amount in line 180, column 5. We will correct this amount when you submit your report.

7. 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.

8. The text in Schedule 18 (cell C4) and Schedule 19 (rows 1152, 1171, and 1205) that refer to incorrect fiscal years will be corrected when you submit your report.

9. Due to an error DOR did not assess districts for the FY18 special education assessment in June (see row 1770 or 2210). This charge will be assessed in September with a note that it is an FY18 charge. We will still ask you to report this as an FY18 expenditure so no change is required to your EOYR.

Helpful Hints

• The FY18 amendment smart form will soon be available on the MassEdu Gateway, under School Finance in the application list. Amendments to last year's FY17 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 18eoy001.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.

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

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

Last Updated: October 1, 2018