Canteen lunch orders move to a shared sheet
At a school of 780 students, the canteen takes lunch orders on paper forms collected at 9:00 am. The manager wants to move to a Google Sheet shared with three staff. Each row would hold the student's name, tutor group, order, price, and any allergy note. On the first trial day, two staff edit the same rows and one person's changes disappear. A student's allergy note is visible to every editor, and a parent has asked who can see it. Some orders arrive with the same student listed twice, and the daily total is calculated by a formula that misses rows added after 2:00 pm. The manager has 30 minutes a day for admin, no budget for new software, and must follow the school's privacy policy.
1. Recommend whether the canteen should keep one shared sheet or split the data into more than one table. Explain the trade-off using the duplicate-order and allergy-visibility problems.
Read the worked answer
Splitting the data is the better choice, but only into as many tables as the manager can maintain. A Students table (StudentID, name, tutor group, allergy note) and an Orders table (date, StudentID, item, price) removes repeated name and allergy text. A lookup can then check that each StudentID exists.
Duplicates become visible: a COUNTIFS on date and StudentID flags a second order for the same student on the same day, and a validation rule can block it. Allergy visibility is solved by keeping the allergy column in the Students table and sharing only the Orders range, or a restricted view, with the three staff.
The trade-off is time. Two tables and a lookup take longer to set up than one sheet, and staff must enter an ID rather than a name. With 30 minutes a day, that setup cost is justified because it removes the two errors with the highest risk: a wrong allergy note and a duplicate charge. If the manager cannot maintain two tables, the minimum is a separate restricted allergy sheet and a duplicate-check rule.
2. Describe two controls that would reduce the risk of lost edits and duplicate orders without buying new software, and explain the limitation of each.
Read the worked answer
Control 1: use a Google Form for order entry, with a dropdown of items and a required StudentID, writing to the sheet. This reduces simultaneous editing because staff submit rather than type into cells, and every order gets a timestamp. Limitation: a form does not by itself stop the same student ordering twice, and staff still need to see the current list to check, so they will open the sheet anyway.
Control 2: protect the formula and header rows, and add data validation plus conditional formatting that highlights duplicate StudentID/date pairs. Limitation: protection can be removed by anyone with edit rights, and highlighting is a warning, not a block. A custom formula validation rule is stronger, but it can reject legitimate corrections and needs testing.
Version history is a useful safety net for lost edits, but it only recovers changes after someone notices the loss. None of these controls replaces a clear rule about who edits which rows and when.
3. A parent asks who can see their child's allergy note. Explain what the school should change and why, referring to privacy principles.
Read the worked answer
The school should explain that an allergy note is personal information, so access should be limited to the people who need it to prepare food safely, not every editor of the sheet. The practical change is to move allergy notes into a separate sheet or column range shared with only the canteen manager and the staff who serve food, while the three editors see names, orders and prices only.
Under the Privacy Act 2020, the school must collect personal information for a lawful purpose, keep it secure, and give the student or parent access to it. The reply should say what is stored, why it is needed, who can see it, how long it is kept, and how to request a correction. It should also confirm that the sheet is not shared by public link outside the school domain.
This is both a privacy fix and a practical one. Fewer people editing the allergy column means fewer accidental changes, which matters more than convenience when a student's safety depends on the note being correct.