Archived Information

Accounting and Auditing

Spreadsheet Issues, 2003-2004 End of Year Pupil and Financial Report

11/17/2004

School officials completing the FY04 End of Year Pupil and Financial Report are encouraged to contact DOE about any technical difficulties they encounter (downloading and uploading, macros, printing, cell references, contents of locked data cells provided by DOE, etc). If such problems are confirmed, this document will identify them and explain how to correct them if necessary.

Once a district has submitted its initial EXCEL workbook, DOE will populate Schedule 11 from SIMS data submitted this past summer. DOE will also correct the formula errors listed below. The district should then download the updated file from the DOE Security Portal. For instructions on how this process is to work, see the following document:

http://finance1.doe.mass.edu/account/EOY04_sch11.html

1. Schedule 11 special education subtotals on the "eoy04" sheet.

   The special education column subtotals in columns E, F and G do not include row 1062, (line 5033-all ages, substantially separate). They should. The cells are not locked, so school officials completing the report should modify the formulas in cells E1068, F1068 and G1068:

   Cell	Current formula	Corrected formula
   E1068	=round(sum(e1058:e1061)+e1066,0)	=round(sum(e1058:e1062)+e1066,0)
   F1068	=round(sum(f1058:f1061)+f1066,1)	=round(sum(f1058:f1062)+f1066,1)
   G1068	=round(sum(g1058:g1061)+g1066,1)	=round(sum(g1058:g1062)+g1066,1)

   DOE will automatically overwrite these formulas with the correct ones after the file is submitted.

2. Schedule 1 line 140 Pupil Transportation Aid: cherry sheet estimates were used instead of actual payments.

   This error affects regional school districts. The correct number will be entered by DOE upon submission of the file. Upon request, DOE will email the corrected file back to school officials.

3. Edit 12: correspondence of pupils and costs.

   Rows 892 to 896, which check correspondence of special ed pupils in Schedule 11 and Schedule 4, can be ignored if there are any pupils in cell i895, because the subtotal in cell I896 does not include i895.

   Rows 897 to 901, which check correspondence of special ed pupils at private day schools, can be ignored if there are any pupils in cell i897. Pupils at collaboratives should not have been included in this particular edit.

4. Summary table on eoy04 sheet - formula error.

   Cells N1269 down to N1295, Other Local Receipts, reference various lines of Column N of schedule 3 column N, which is total expenditures. The formulas should refer to column M of schedule 3 instead. DOE will correct the error upon submission.

5. Use "Print This School" button instead of "Print Schedule 3 Schools".

   The "Print Schedule 3 Schools" button on the Print Options box is not working properly. Figures from a few cells are not being updated as the print macro moves from one school to the next. Data entered in these cells on one school will be repeated on other schools. To avoid this problem, print your schools one at a time using the "Print this School" button. This problem only affects your ability to print all of your schools.

6. IMPORTANT! Net school spending FY05 (reports sheet) should count estimated FY05 circuit breaker revenue as a school revenue.

   Cell C57 should add FY05 circuit breaker revenue to other school revenues, but does not. This may have important budgetary implications for districts whose FY05 net school spending is near the requirement!

   You cannot change the formula on your file because it is protected. DOE will make the correction upon submission. To determine the effect for your district, first find the estimated FY05 amount shown in the "Net Reimbursement" column (K) in the recent circuit breaker posting:

   http://finance1.doe.mass.edu/seducation/05cb_pmt_initial.xls

   (This is a better estimate than the amount currently shown in cell C55 of the net school spending table-that was still a rough estimate as of July when the files were created.) Enter the new amount into c55. It will be automatically apportioned among the various school committee expenditure functional spending categories above. Then subtract this same amount from the net school spending shown in cell E58. The remainder is your corrected net school spending amount for FY05.

Please accept our apologies for the inconvenience.

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

• Schedule 7 line 4320 "Payments From Revolving and Special Funds" : riders in column 8 are intended to be a subset of those shown in the rest of the schedule. They are already included in the total.