Payroll Mailout: A Broken Spreadsheet Link, a Mangled CSV and an OTP Caller
You handle payroll correspondence at a branch. The salary sheet has 240 rows. In the master file, cell D2 contains =B2*$C$2 and is filled down to D241, where C2 holds the reimbursement rate of 1,250. You email a copy to a colleague, who reports two things: every D cell in her copy shows 0, and a second copy circulating in her office has D241 reading =B241*C241. Separately, the employee master has been exported as CSV: payroll numbers now display as 6.02E+11 and some bank account numbers that ended in 0 have lost that digit. Your colleague suggests retyping all 240 account numbers. Your draft plan for the employee mailing is to put all 240 addresses in "To", attach the file and its password in the same message, and place a copy on a public drive link so anyone who missed the email can download it. A caller claiming to be from the payroll vendor then asks you to read out an OTP just sent to your phone to "unlock the sheet".
1. Diagnose both spreadsheet symptoms - the column of zeros and the altered D241 - and explain the fix.
Read the worked answer
Both symptoms are about references, not arithmetic.
The zeros. =B2*$C$2 was copied into a file where C2 is empty. The dollar signs only lock a reference - they stop it shifting when the formula is filled or copied - they do not carry the value of C2 across files. Multiply anything by an empty cell and you get 0. So D is not wrong; the input it depends on never arrived. Fix: distribute the whole workbook, or convert the results to fixed values with Copy → Paste Special → Values, and check one row by hand (B2 × 1,250) before sending.
The altered D241. The copy shows =B241C241 - the anchors are gone. That happens when someone retypes or re-enters the first formula without the dollar signs and then fills down, so each row picks up its own C cell. Verify by opening D2 in that copy: if D2 reads =B2C2, the anchoring was lost at the source and every row below is suspect. Rebuild the first formula with $C$2 and refill rather than editing 240 cells.
2. Explain why the CSV shows payroll numbers as 6.02E+11 and drops the final zero of some account numbers, and evaluate the colleague's proposal to retype all 240 numbers.
Read the worked answer
The CSV itself is fine; the loss happens at import. A spreadsheet guesses the type of each column. A 12-digit string looks like a number, so it is stored as one, displayed in scientific notation, and held to about 15 significant digits. An account number is not a quantity - it is an identifier - but once it is parsed as a number, leading and trailing zeros have nowhere to live, and Excel will drop them.
Retyping 240 numbers is the worst option: it introduces 240 chances of a transcription error into the one field where an error sends money to a stranger's account. The fix is at import: set the account-number column to Text in the import wizard (or format the column as Text before opening the file, or prefix with an apostrophe), and re-export from the source system rather than repairing a damaged copy. Then verify with a checksum - count digits, confirm 240 rows, spot-check against the source - before the file goes anywhere near a payment run.
3. Assess the mailing plan and respond to the OTP request. State what you would do instead.
Read the worked answer
Every element of the plan fails somewhere:
- All 240 addresses in "To" discloses the entire staff list, with salary correspondence attached, to everyone on it - a personal-data breach and an invitation to phishing. Use BCC, or better, individualised mail-merge messages batched within the bank's mail limits.
- Password in the same email as the file removes the protection entirely; anyone who intercepts the message has both halves. Send the password through a separate channel, or use a bank-approved secure-share tool.
- Public drive link makes salary data open to anyone with the URL. Use a restricted share with named recipients and an expiry.
The OTP call. Never read it out. An OTP is the second factor protecting your own account or the sheet; the only party that needs it is the person holding the registered device. A caller who has the password but asks for the OTP is exactly what social engineering looks like. Hang up, verify through the vendor's published number or the IT helpdesk, and report the call. Then escalate the mailing plan to your supervisor before sending anything.