The Stall Accounts That Did Not Add Up
Priya, a Class 11 commerce student, keeps the accounts of her school consumer-club stall at the three-day annual fete. Her spreadsheet has the columns Item, Rate, Day 1, Day 2, Day 3 and Amount, with eight items. After the fete, the cell adding all amounts shows 1,450, but the cash box contains 8,450. On checking she finds that some quantity cells were typed after an apostrophe (for example '12) and several rate cells were typed as ₹20 rather than 20. Three rows show 0 in the Amount column.
Yesterday she shared the file with the setting "Anyone who has the link can edit", and a friend sorting the sheet deleted a whole column of Day 2 figures. She has no other copy. This morning an email arrives: "School Fete Accounts - your account will be frozen unless you verify within 24 hours", with a button asking for her school email password and an attachment named accounts_final.xlsm.
1. Explain why three rows show 0 in the Amount column and why the grand total is 1,450 instead of 8,450. Name the data-type problem in each case and give the exact correction, including the formula you would use.
Read the worked answer
Cause 1 - quantities stored as text. A leading apostrophe forces the cell to be text, so '12 is the characters "1" and "2", not the number twelve. In =Rate*SUM(Day1:Day3), SUM ignores text and returns 0, so the row amount becomes 0. That is exactly the three zero rows.
Cause 2 - rates stored as text. "₹20" contains a currency symbol, so it is text. Multiplying text by a number gives #VALUE!, and SUM over the Amount column silently skips those error rows. The 1,450 is therefore the sum of only the few rows that were typed correctly - it is not a wrong total, it is an incomplete one. The missing 7,000 sits in the text-typed rows.
Corrections: select the columns and use Data → Text to Columns → Finish (or Paste Special → Multiply by 1) to convert text to numbers; strip the ₹ symbol with Find and Replace, and format the cell as Currency instead of typing the symbol. Verify with =ISNUMBER(C2), which returns TRUE only for real numbers.
Correct formula: in Amount, =B2*(C2+D2+E2). Note the trap: =B2*C2+D2+E2 prices only Day 1 and then adds raw quantities (bags) to rupees - a units error hidden inside a plausible formula. Grand total: =SUM(F2:F9), and it must then reconcile to the 8,450 in the cash box.
2. Rewrite the sharing settings and backup routine for the file. Explain what 'least privilege' means in this situation and why 'anyone with the link can edit' caused the loss.
Read the worked answer
Revised settings:
- Priya keeps the master file; she shares a copy for data entry, or gives named people specific rights.
- Default link setting: "Restricted - only people I add can open", role Viewer.
- Editor rights go to at most one named person (the club secretary), never to "anyone with the link".
- Protect the sheet (Data → Protect sheet) so headers and formulas cannot be deleted; data entry happens only in unlocked cells.
Backup routine: keep the file in cloud storage so File → Version history can restore a deleted column; additionally download a dated copy (accounts_fete_day1.xlsx, day2, day3) to a pen drive after each day, and keep one offline copy.
Least privilege means each person gets only the access their task needs: a friend who is sorting rows needs no right to delete a column. "Anyone with the link can edit" fails for three reasons - it cannot be audited (you cannot tell who deleted what), deletion propagates instantly to everyone, and the permission is broader than the task. Version history is a recovery tool, not a substitute for correct permissions.
3. Analyse the email. List three phishing signals and state exactly what Priya should do and what she must not do.
Read the worked answer
Signals:
- Threat plus deadline - "frozen unless you verify within 24 hours" is engineered to make her act before thinking. Genuine school notices do not threaten account closure by email.
- It asks for a password. No school, bank or service provider needs a password sent through a link. This single signal is conclusive.
- The .xlsm attachment. Macro-enabled workbooks can run code when macros are enabled, so opening it can install something even without typing a password.
- Supporting signals: a generic greeting, a sender address that does not match the school domain, and a button whose real destination URL is hidden.
What she must do: not click the button, not open or enable macros in the attachment, and not type the password. Report it to the class teacher and the school's computer teacher or IT coordinator, and verify the claim by phone or in person at the office. If she has already clicked or typed the password, she should tell an adult immediately, change the password from a different device, and run an antivirus scan.
What she must not do: reply to the mail, forward it to friends as a warning without checking, or "just check" the link out of curiosity.