School Report Generator: Automating a Manual Process
A school produces end-of-semester reports for 900 students. Teachers enter marks into a spreadsheet; office staff then merge them into a document template, one student at a time, taking about 4 minutes each. Errors occur when a student has a missing mark or an extra subject row, which shifts data into the wrong field. The office wants an automated generator. Constraints: marks must stay in the existing spreadsheet because teachers will not learn a new tool; reports must be checked by a teacher before release; and personal data cannot leave the school network. A developer proposes a script that reads the spreadsheet, fills a template, and outputs PDFs to a shared drive.
1. Evaluate the proposed automation against the stated constraints and identify the validation the script must perform.
Read the worked answer
Constraint check:
- Keep the spreadsheet: the script reads it, so teachers' workflow is unchanged. Satisfied.
- Teacher check before release: outputting PDFs directly to a shared drive risks release without review. The script should write to a review folder and require a teacher sign-off step before files move to the release folder.
- Data stays on network: a local script writing to a school drive satisfies this, provided it does not call an external service. Confirm the template and any libraries do not send data out.
Validation needed: The observed errors come from structural problems, so the script must check before generating:
- every student has a mark for each required subject;
- no extra or missing rows per student;
- marks fall within the valid range;
- student identifiers match the enrolment list.
Handling failures: Rather than guessing, the script should list problem students and skip them for manual fixing. Generating a wrong report is worse than generating none.
Time saving: roughly 60 hours of manual merging per semester is replaced, but the review step must remain.
2. The office asks for a count of how many reports were generated and how many failed validation. Explain how the script should report this and why silent skipping would be a poor choice.
Read the worked answer
Reporting design: The script should write a summary at the end of each run: total students processed, reports generated, students skipped with the reason for each, and a timestamp. The list of skipped students should name the student and the specific fault, such as missing mark in Subject X or duplicate row. This turns an invisible failure into a work item the office can act on.
Why silent skipping is poor: If the script quietly omits students, the office may assume all 900 reports exist. Missing reports are then discovered by parents or at the last minute, and the cause is harder to trace because no record exists. Silent failure also hides a systematic problem, such as a whole class with a mislabelled column, which would otherwise be caught immediately.
Link to constraints: A clear summary supports the teacher review step, because reviewers know which reports are provisional or absent. It also leaves an audit record on the school network, consistent with keeping personal data local. The summary should avoid printing full marks in logs, listing only identifiers and fault types.