The Canteen Sales Spreadsheet
A school canteen records daily sales in a spreadsheet. Each row has a date, an item name, a quantity, and a price. Staff sometimes type the item name differently, such as 'meat pie', 'Meat Pie', and 'meatpie'. The canteen manager wants a weekly report of the top five selling items and total revenue. A student volunteer builds a pivot table, but the top-five list includes 'meat pie' and 'Meat Pie' as separate items, and the revenue total does not match the daily takings. The manager needs a report that is accurate enough to decide which items to stock next term.
1. Explain why the pivot table produces an incorrect top-five list and how the data should be prepared.
Read the worked answer
The pivot table groups rows by the exact text in the item column. Because 'meat pie', 'Meat Pie', and 'meatpie' are different strings, each becomes its own group, splitting one item's sales across several rows. The top-five list is therefore wrong: a popular item may not appear at all, while less popular variants appear separately. The fix is data cleaning before analysis: standardise item names to a single agreed form, using a lookup table or a controlled list of valid items. After standardising, the pivot table groups correctly. This shows that the quality of the analysis depends on the quality of the data, and that cleaning must happen before aggregation, not after.
2. The revenue total does not match the daily takings. Suggest two likely causes and how to test each.
Read the worked answer
One likely cause is inconsistent prices for the same item, for example a price entered as 3.50 in one row and 3.5 in another, or an old price left in some rows. This can be tested by listing distinct prices per item and checking for more than one. Another likely cause is missing or duplicated rows, such as a day entered twice or a sale omitted. This can be tested by comparing the row count and daily totals against the till records. A third possible cause is a formula that does not cover the full range. Each cause can be checked independently, which is better than guessing, because the manager needs to know which fix restores trust in the report.
3. Recommend how the canteen should record data in future to prevent these problems.
Read the worked answer
The canteen should replace free-text item entry with a drop-down list of valid items, so names cannot vary. Prices should be pulled from a single price table rather than typed per row, so a price change is made once. The spreadsheet should include a validation check, such as a daily total compared with the till, so errors are found the same day rather than at the end of term. These changes move error prevention to the point of entry, which is cheaper than cleaning afterwards. The trade-off is a small amount of setup work and staff training, but the report becomes reliable enough to support stocking decisions.