Accounting and Auditing

Spreadsheet Issues, 2007-2008 End of Year Financial Report

October 21, 2008

School officials completing the FY08 End of Year Pupil and Financial Report (EOYPFR) are encouraged to contact DOE 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 can be made using the automated amendment smart form on the secure portal.

   Do not use Firefox as a browser for this application.

   Amendments from the previous week will be processed by the end of the day each Monday.

2. Edit 2 cell reference problem: private residential tuitions.

   On rows 67 and 68 of the edits sheet the cell references for the amount of tuition paid to residential schools are not correct: they refer to the edits sheet instead of the "eoy08" sheet. Ignore these lines on the edit sheet: ESE will fix the references when the file is submitted.

3. Edit 2 cell reference problem: vocational tuitions by school committee.

   On rows 47 to 55 of the edits sheet the total tuition does not include school committee tuition for vocational students. Ignore this edit: ESE will fix the references when the file is submitted

4. Edit 1 tuitioned-in full-day regular ed kindergartners paying for 2^nd half of day.

   Any full-day kindergartners coded as KT in element DOE016 of October 2007 SIMS, and "01" (resident/member) in DOE013 were not included in the SIMS count in cell g25. This will likely create an edit for any district properly reporting kindergarten tuition on schedule 1. Ignore the edit; the kindergartners will be added upon submission of the file.

5. Label on Schedule 7 line 4070: The label on the spreadsheet should read "3-5 yr olds, all placements (doe032)"

6. Label on edit 8: the label on g779 on the edits sheet should refer to Schedule 4 column 9, not column 10.

7. Label on edit 1: the labels in cells c24, c27 and c30 should refer to line 640, not line 630.

8. Supplemental FY08 charter reimbursement.

   Chapter 302 of the acts of 2008 was signed by Governor Patrick on August 8. It included a supplemental appropriation of $4,700,178 to cover the shortfall in the FY08 charter school tuition reimbursement program.

   A one time payment to each district, where applicable, was made on August 29, 2008. The Department of Revenue's Division of Local Services published the payments at  http://www.mass.gov/Ador/docs/dls/mdmstuf/LocalAidDist/fy08charterreimbursesupp.xls.

   ESE will add the amounts to line 170 of Schedule 1 when the EOY report is submitted, because that cell is protected.

9. Edit 5 may indicate that some districts that do not belong to regionals should report regional assessments. This error will be fixed by ESE upon submission of the file.

10. Obtaining final DOE copies of FY07 end of year pupil and financial reports.

    FY07 EOYPFR's are posted on the ESE Security Portal dropbox.

11. Obtaining amended DOE copies of FY08 end of year financial reports.

    We will post the final version of every district's FY08 report to the dropbox in July, 2009. In the meantime if you need a copy reflecting all amendments to date, contact Lynn McKnight at 781-338-6511 lmcknight@doe.mass.edu and she will post the current file. Remember that amendments are processed every Monday, so wait until after that to make the request.

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 "08eoy***.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.

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