Work in progress. Currently what is needed is the rollover for the SY2017-18 to the SY2018-19 so this is what is documented here. The remaining of the document will be done within this month of September 2018.

At the start and end of each school year, data is entered into the annual school census workbook which can then be uploaded into the Pacific EMIS database. This data is then available in a consistent data warehouse for all stakeholders to access through the web app, android app, website, hard copy reports, etc.

This is not a step-by-step how to document. Most Data and IT staff are comfortable working in excel, understand the schools data model and make rapid transition across to the workbook. This document therefore will aim to explain the key differences and tools.

The workbook main objective is to improve the quality of data collected while still collecting granular student data in an challenging Pacific environment where many locations and schools still have no Internet connectivity.

• Remote/Offline Data Entry by design: it can be updated by schools remotely and sent to a central data office, or the central data office can help fill up this data by communicating with schools. It aims at collecting students & enrollments, staff and their latest qualifications, schools and their resources and WASH data. It is updated and sent twice a year: in September/October as official enrollment for the year and in June/July the same data is simply updated with the outcomes of the year (i.e. graduations, drop outs, transfers, etc.)
• Quality Control: The workbook also aims at improving data by providing some basic data validation and other quality control mechanism.
• Rollover: a facility to rollover enrollment data into a new year, setting up enrolments based on each individual student’s outcomes from the previous year greatly increasing speed and ease of data submission in challenging environments.
• Merge: a facility to merge workbooks to import one or more secondary workbooks. This merge feature can be used in a number of ways including merging whole workbook data into a newer master workbook that contains new features and bug fixes, merged individually completed workbook part (e.g. a single schools workbook) into a large workbook (e.g. a district or national workbooks)

Has the workbook constantly undergoes improvements it is important to make sure you are always using the latest and greatest version. Currently the best way to do this is to TODO

The rollover function allows you to anticipate enrolments for a new school year. The function is based around the Completed and Outcome columns (Outcomes view) and the ‘From’ column in the Enrolment.

This is how it works.

• Students who successfully completed any Grade (Completers) are re-enrolled in the next Grade.
• Students who did not successfully complete (Non-Completers) are either of the following cases:
  • Repeaters are re-enrolled in the same Grade
  • Dropouts, Expelled students and international transfers are not re-enrolled.
• You then need to process any extra enrolments by adding them manually (e.g. all the new ECE and any other local or international transferred in enrolments).

• Create a new, empty, clean Workbook. See How to create a ‘clean’ Workbook (for Rollover or as a temporary, Secondary Workbook for merging back into a Master.)
• Rename the new, empty, clean Workbook according to the Workbook naming convention for a Beginning of Year Workbook: i.e. for the Public School System's Mango School this could be PSS Mango School 20170907 BOY2018_19.xlsm.
• The number 20170907 is the version of workbook: it is represented by the the date on which that version (functionality) was created and can also be found inside the workbook in sheet Settings.
• There are two critical workbooks at this point for this particular school. See sample below as demonstration:
  • Last Year Workbook PSS Mango School 20170907 EOY2017_18.xlsm
  • New Year Rollover Workbook PSS Mango School 20170907 BOY2018_19.xlsm
• Make sure MS Excel is closed and that you have no workbooks open.
• Open the New Year Rollover Workbook PSS Mango School 20170907 BOY2018_19.xlsm (double check this carefully!)
• Open the Last Year Workbook (i.e. PSS Mango School 20170907 EOY2017_18.xlsm)
• Switch to the New Year Rollover Workbook (i.e. PSS Mango School 20170907 BOY2018_19.xlsm)
• Select the Rollover Worksheet (As below)

• In the combo box, select the Last Year Workbook.
• Press the Rollover button
• The Rollover is now being performed. Depending on the speed of the computer, this could take quite some time–up to half hour or even hours for very large workbooks. Most schools in the Pacific would have this done fairly quickly however.
• When finished verify the New Year Rollover Workbook (i.e. make sure that Completers are enrolled in the next Grade. For non-completers make sure that Repeaters are reenrolled at the same Grade. Check that Year 12 Completers, all dropouts and expelled students have not been re-enrolled.) Refer to the section Basic Rollover Logic

This is typically needed for Rollover or Merge (e.g. as a temporary, Secondary Workbook for merging back into a Master.)

• Make a copy of an existing workbook.
• Open the new workbook.
• In the New Workbook, on the Schools, Students, School Staff and WASH Worksheets click the button Delete All Rows… and press OK to delete all the rows.
• Save the new cleaned up workbook
• You now have a new, empty workbook that you can use for:
  • As a secondary Workbook for data entry that can be merged back into a Master Workbook
  • As a new year “rollover” Workbook
  • As a test/training workbook