Archived Information

Accounting and Auditing

Technical Questions and Solutions, 2002-2003 End of Year Pupil and Financial Report

School officials completing the FY03 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.

If you downloaded your file on or before August 11^th, there are two problems as described below. If you downloaded your file after August 11^th, they have already been fixed on your spreadsheet, and the password has been changed.

1. Edit sheet "Run Edit Program" macro.

   The "Run Edit Program" button in the "edits" worksheet requires a password. We want districts to look closely at these edits, so we certainly did not intend for the program to be password protected. The password is "eoy03key".

2. Summary table cell references on "eoy03" sheet.

   The cell references in columns L and M, row 1229 and rows 1240 thru 1257 count two columns worth of data from Schedule 1. Each should refer to one column only. These cells will be corrected by DOE after the file is submitted. However, if you wish to make the corrections prior to submission, follow the instructions below.

   Unprotect the worksheet using the following commands: "Tools, Protection, Unprotect Sheet". The password is "eoy03key". Enter the following formula corrections.

   Cell	Corrected Formula
   L1229	=L533
   M1229	=M533
   L1240	=L545
   M1240	=M545

   Copy cells L1240 and M1240 down through row 1257.

   After making the changes, re-protect the sheet using "Tools, Protection, Protect Sheet" and entering the password "eoy03key". At the prompt, you should confirm the password a second time.

   Certain data items that DOE inserted on district EOY files were not correct. Ninety-eight districts are affected by at least one of these issues. Business administrators in affected districts were sent letters showing the correct amounts, during the week of September 8th. DOE will also correct the numbers when the files are submitted.

3. FY03 Transportation aid amounts in Schedule 1

   The amounts actually paid during FY03 were correct, but the wrong file was used to insert the data into cell L33, line 140 on the EOY file. Seventy-three districts were affected.

4. FY03 School Choice data--some vocational pupils were counted in regular education.

   The correct tuition amounts were charged during FY03 and reported on various DOE school choice publications. However, in preparing the EOY data, a filtering error caused 242 pupils and $1.2 million in tuition in eleven receiving districts to be assigned to regular education instead of vocational education. This affected 27 sending districts' data as well. EOY lines 630, 1780, 2220, 5006 and 5071 were impacted, as well as cells i558 and i572 on the edits sheet.

5. Negative FY03 charter reimbursements should not have been set to zero.

   Although there was no state appropriation for FY03 charter reimbursements, adjustments to FY02 reimbursements were processed during FY03 on a pro-rated basis, up to the point where the negative adjustments funded the positive ones. In order for the EOY aid amounts to balance with what was actually paid, it is necessary to show "negative" aid in cell L34, line 170 on Schedule 1. This change affected twelve districts for a total of $57,776.

6. The following printing problem has also been brought to our attention.

   In the print macro activated by "ctrl P" the fourth button in the first column is "Print Schedule 3 schools". That macro was inadvertently password protected. Those districts that downloaded the file before August 11^th, can use password "eoy03key". The remaining districts will have to rely upon the individual school printing macro in the upper left hand corner of the "schedule 3" worksheet.

7. A data cell was inadvertently protected.

   The Schedule 13 data cell K1059, Professional Development substitutes, is password protected. Districts that downloaded their file before August 11th can unprotect the cell using the password "eoy03key". For files downloaded after August 11th, districts should provide the number of pd substitutes in the "comments" sheet with a note to that effect. DOE will insert that information in Schedule 13 after the file has been submitted.

8. Edits sheet description changes

   Cell C493 should be "1069 & 1163".
   Cell D493 should be "Substitute salaries and expenses".
   Cell D502 should be "Col 8: Estimated Prof Development 04".
   Cell F502 should be "Estimate for FY04 prof development is missing".

9. Schedule 1 Municipal Library Services cell reference change

   Cell L493 on the "eoy03" sheet should be "=N670+N698".
   DOE will correct this formula for affected districts once their file is submitted.

Please accept our apologies for the inconvenience.