Archived Information
Accounting and Auditing
Technical Questions and Solutions, 2001-2002 End of Year Pupil and Financial Report
11/8/2002
School officials are encouraged to contact DOE about particular difficulties of a technical nature (downloading and uploading, macros, printing, cell references, contents of locked data cells provided by DOE, etc). If any systematic problems are confirmed, this document will identify them and explain how to correct them if necessary.
For the data or formula errors noted below, the corrections will be automatically made by DOE upon receipt of your district's file. You are not required to make any of these changes to your file, although it's recommended that you do insert the net school spending data in item 1 because that is an important calculation that may have an impact upon your FY03 budget process. In any case, for anyone who wishes to make any of the changes to their district's file, instructions are given below. First, the particular worksheet involved must be unprotected: "Tools, Protection, Unprotect Sheet." After making the changes, re-protect the sheet: "Tools, Protection, Protect Sheet, OK". (No password is required.)
FY02 Net School Spending Requirement and FY01 Carryover Into FY02 - "Reports" Sheet
It was DOE's intent to provide the FY02 required net school spending and the current carryover amount from FY01 on each district's file. In creating the files, the amounts were inadvertently set to zero.
Your district's FY02 net school spending requirement and FY01 carryover amounts are listed here. All operating districts have a requirement, but most do not have a carryover.
The affected cells are in the "reports" sheet, cells F29 (line 16, "FY02 Required Net School Spending") and F30 (line 17, "FY01 Carry-Over Into FY02"). Enter your district's FY02 net school spending requirement in cell F29. If your district has a FY01 carryover into FY02, enter that amount in cell F30.
Schedule 4 edits on "eoy02" sheet.
In column L, the total for each line is compared to equivalent lines in schedule 1 or schedule 3. If there is a substantial difference, the cell is formatted in red with the amount of the discrepancy shown as well. The same comparison is done on the "edits" sheet, so those on schedule 4 are redundant (and will be removed next year). Three of the checks on schedule 4 have formula errors. Cell L917 should insert a reference to cell F182 in the middle of the parenthetical expression. The new formula should read: =K917-(F169+F171+F176+f182+F186+F190+F205+F207+F213). Cell L918 should insert a reference to cell F230 in the middle of the parenthetical expression. The new formula should read =K918-(F218+F223+F228+F230+F236+F241). Cell L921 should be replaced with the following formula: =K921-(F751-F634). Schedule 2 edits on "eoy02" sheet
In cell K597 the total assessment for a regional school district is compared to Schedule 1, cell M17, "Assessments Received by Regional Schools."
Schedule 13 total staff, Librarians and Media Center Directors
In cell j1060, the formula refers to the wrong column.
"Reports" Per pupil expenditure table, Pupil Services line (row 71)
The pupil services calculation refers to a hidden sheet that adds up all of the pupil services items from the "eoy02" sheet. One of the references on that sheet is incorrect.
- Unhide the calculations sheet. ("Format, Sheet, Unhide, calcmisc").
- Unprotect the sheet.
- The formula in E47 should read =eoy02!L751.
- Re-protect the sheet, then re-hide the sheet ("Format, Sheet, Hide").
Schedule 1 municipal expenditures for Library Services
Schedule 1 municipal expenditures for Library Services (cell L473) should refer to schedule 3, where it has already been entered.
Instructions, printed blank copy of End of Year Pupil and Financial Report, page 42 (Schedule 1 Part II, C.2, Expenditures From Federal Grants, State Grants and Special Funds)
What is labelled as Item 7 should be included as a separate paragraph under item 6. The paragraph should read as follows:
"Record expenditures from out-of-district tuition (Ch 71, MGL, Sect 71F) and summer school tuition (Ch 71, MGL, Sec 71E).
Item 7 should refer to the subsequent paragraph, "Record expenditures from Athletics..."
The "Print Schedule 3 Schools" macro on the Print Options user form-used to print all of a district's schools in one print job--does not work properly.
When this macro is activated it prints the same data on each school. This is the result of a problem with the macro code itself.
Users can still print their school reports one at a time using the "Print this School" macro on the Schedule 3 worksheet. However, if you would like to correct this problem with your file copy the following code:
Application.Run (my_run_file & "!get_data1")
Application.Run (my_run_file & "!get_data2")
Application.Run (my_run_file & "!get_data3")
Return to the Excel workbook and go to Tools, Macro and then select Macros. This will produce a list of the macros in the file. Highlight the macro called "print_sch3all" and then select Step Into. This will bring you into the Visual Basic code.
Find the following line in the code:
Application.Run (my_run_file & "!search_lookup")
Paste the three lines that you have copied below this line. In the tool bar above go to Run and select Reset. Close Visual Basic and Return to Excel.
The macro should now work properly. Schedule 13, Professional Development Substitutes should not be protected and shaded.
- Cell K1059: Format, cells, no color, ok. Format, cells, protection, locked (box next to "locked" should be made blank by clicking on it), ok.
- Correct the formula in cell K1060: =round(sum(k1053:k1059),1)
- Correct the formula in cell N1059: =round(h1059+k1059,0)
FY02 enrollment aid was not included as state grant revenue.
If your district was one of the thirty districts that received an enrollment aid grant in FY02, that aid amount should be added to whatever amount is already shown in the undistributed column of line 510, DOE Administered State Grants, cell L44. The amount of your district's aid can be seen here.
We have received a few calls from districts who said that they thought a grant was missing or duplicated. Our best advice is to carefully review the information shown on your district's grant listing at http://finance1.doe.mass.edu/schfin/grants/grant02.aspx. That listing is the source for what was reported on your report. The grants cells on the Schedule 1 revenue section are not protected, so that if you need to add or change a grant amount, you should feel free to do so.. FY02 Expenditures - All Fund Types (summary table at bottom of eoy02 sheet)
School Committee debt service reference should be the row number, not the line number, from Schedule 1.
Municipal instructional subtotal should not include District Leadership and Administration.
Reminder about uploading to DOE using the DOE Dropbox application. It is necessary to change the file name on your hard drive.
This is a clarification, not a change, to the existing uploading procedures. For most districts the upload has worked successfully, but some districts have run into problems because they did not change the name of the file as described in the Dropbox upload instructions. As a result, the file they uploaded is saved as a blank file, and they needed to go through the upload one more time.
Before uploading the file, please rename it on your hard drive. The file name of 02eoyXXX.xls, where XXX is your school district's LEA #, should be named 02eoyXXX_in.xls. If you do not add the "_ins" to the file name, or if you attempt to rename the file within the upload window in your web browser, the upload will fail. It must be done in advance.
"Comparison of Selected Data Items" in "reports" sheet.
Two cells do not display data properly in Schedule 3, Athletic And Other Revolving Funds.
There are two cells within the Schedule 3 worksheet that do not display data properly. Entering data in either of these cells for one school displays the same information in other schools. Unless your district has expenditures in these two specific cells, this problem can be ignored. DOE will make the correction when your file is submitted, but if you wish to correct it prior to that, here is how to do so. The cells are:
- 3471 Contracted Services (04), function code 2325, under column 17 Athletics and Other Revolving Funds, cell U71
- 3473 Other Salaries (03), function code 2330, under column 17 Athletics and Other Revolving Funds, cell U74
Return to the Excel workbook and go to Tools, Macro and then select Macros. This will produce a list of the macros in the file. Highlight the macro called "get_data2" and select Step Into.
Line 3471
Go to Edit, Find, Find What: 'line 2325 -4
After the line: Range("t71") = Sheets("convert").Range("t42")
Copy and insert: Range("u71") = Sheets("convert").Range("u42")
Line 3473
Go to Edit, Find, Find What: 'line 2330 -3
After the line: Range("t74") = Sheets("convert").Range("t43")
Copy and insert: Range("u74") = Sheets("convert").Range("u43")
Go to Run and select Reset. Close Visual Basic.
Saving the Excel file will also save the changes to Visual Basic.
"Edits" sheet, row labels in edit number 11, column H.
In cells H479 through H493, the item description contains the wrong Schedule 13 column number, although the labels and cell references are otherwise correct. The correct column numbers are shown below.
| 5420 |
6 - Librarians & Media Center Directors |
| 5420 |
7 - Professional Development, Unassigned |
| 5300 |
7 - Professional Development, Reg Ed |
| 5310 |
7 - Professional Development, Sped |
| 5320 |
7 - Professional Development, Bilingual Ed |
| 5410 |
7 - Professional Development, Occup Ed |
| 5300 |
8 - Reg Ed Guidance |
| 5310 |
8 - Special Ed Guidance |
| 5320 |
8 - Bilingual Ed Guidance |
| 5410 |
8 - Occup Ed Guidance |
| 5300 |
9 - Reg Ed Psychological |
| 5310 |
9 - Special Ed Psychological |
| 5320 |
9 - Bilingual Ed Psychological |
| 5410 |
9 - Occup Ed Psychological |
| 5421 |
4 - Instructional Substitutes |
Schedule 1 transportation expenditure instructions.
The instructions on page 24, for lines 1461-1469 say, in the fourth sentence, to "report transportation expenditures for pupils transported to and from Preschool Programs in Column 8." However, column 8 (undistributed) was intentionally shaded this year. These pupils should be reported in columns 1 through 5, depending upon which program they are in.
"Reports" sheet Foundation Budget Comparisons
Cell C100 (School Committee Books and Equipment) is missing one row from the eoy02 sheet (testing and assessment, other expenses, row 266). Edit long formula in this cell, so that the expression "SUM(eoy02!E265:eoy02!H265)" becomes "SUM(eoy02!E265:eoy02!H266)" Cell D99 (Municipal Miscellaneous) needs to be changed to pick up municipal tuition. The correct formula should be :
=daypct*(eoy02!L470+eoy02!L490+eoy02!L491+eoy02!L492+eoy02!L493)+sum(eoy02!e513:eoy02!i513)
Please accept our apologies for the inconvenience.
|
State Government · State Services 
