Pivot Tables II Questions – MA DESE End of Year Financial
1. Create a pivot table to match Schedule 1 of the MA DESE End of Year Financial Report. For this pivot table, use the worksheet tab ‘FY12 DESE Master’ from the provided file. Use ‘Final DESE Func’ as a row field with ‘Final DESE Object’ as a second row to the right of ‘Final DESE Func’. Also, use ‘Final DESE Program’ as a column field and ‘FY12 Final Amount’ as a data field.
2. Using the above pivot table, what was the total spending for DESE Function 1410, Business and Finance? How is the total spending for DESE Function 1410 broken down by DESE Program?
3. How is the spending in DESE Function 2220, Department Heads-Building Level, broken down by DESE Object? Move the field ‘Account Code’ to the right of ‘Final DESE Object’ as a row field. Which Account Codes are under DESE Function 2220 with DESE Object 5? Move the new row field ‘Account Code’ off the pivot table.
4. What is the total spending for DESE Function 5200, Benefits Active Employees? All of DESE Function 5200 should be coded to DESE Object 6. Recode any spending in DESE Function 5200 from the original data (FY12 DESE Master) that is in other DESE Objects to Object 6 and refresh the pivot table.
5. Create a pivot table which looks the same as the DESE report but uses ‘Grade Level’ as a column instead of ‘Final DESE Program’. How much of the total spending for DESE Function 2415 (Other Instructional Materials) was spent in the high schools (HS)?
6. Create a pivot table which compares the spending for FY11 and FY12 by DESE Function. How much did the total spending on Classroom Substitutes (DESE Function 2325) increase or decrease from FY11 to FY12?