Consolidating Branch Account Balances Safely
A branch has to send a daily summary of five high-value accounts to the regional office. The clerk has an Excel workbook with columns: Account Number, Customer Name, Balance, and Status. The five balances are ₹12,40,000; ₹8,75,500; ₹3,20,750; ₹19,05,000; and ₹5,60,250. The clerk plans to (i) email the workbook to a personal Gmail address to work from home, (ii) put all five account numbers in the “To” line so every recipient can see them, and (iii) use a formula that adds the balances manually as =1240000+875500+320750+1905000+560250. The branch manager wants a formula that updates automatically if a balance changes, a file format that preserves layout for the regional office, and a secure transmission method. The office has a shared drive and an official email system with restricted recipients. The clerk also has a PDF printer and a password-protected zip tool.
1. Identify three unsafe or inefficient practices in the clerk’s plan and state the corrected action for each.
Read the worked answer
- Sending the workbook to a personal Gmail address is unsafe because customer account data leaves the bank’s controlled systems. Correct action: use the official email system or the bank’s shared drive, with access limited to authorised regional office staff.
- Putting all account numbers in the “To” line exposes customer data to every recipient and defeats confidentiality. Correct action: send to a restricted group or use BCC where appropriate, and share only the minimum necessary data.
- The manual addition formula =1240000+875500+320750+1905000+560250 does not update if a balance changes and is prone to typing errors. Correct action: keep the balances in cells C2:C6 and use =SUM(C2:C6).
2. Write the Excel formulas to total the balances and count how many balances exceed ₹10,00,000, assuming the balances are in C2:C6.
Read the worked answer
Total balances: =SUM(C2:C6)
Count of balances above ₹10,00,000: =COUNTIF(C2:C6,">1000000")
In the COUNTIF formula, the comparison is written as a text criterion inside double quotes. Excel uses 1000000 without Indian comma separators inside formulas. The SUM formula updates automatically if any balance in C2:C6 changes, unlike the clerk’s manually typed addition.
3. Which file format and transmission route should be used for the regional office? Explain the trade-off between PDF and XLSX, and how to protect a zip password.
Read the worked answer
If the regional office only needs to view or print the summary, send a PDF because it preserves the layout and is harder to alter accidentally. If the office must verify formulas or update balances, send the XLSX workbook so the formulas remain usable. In either case, transmit through the official email system to restricted recipients or place the file on the bank’s shared drive with limited access; do not use personal Gmail. If a password-protected zip is required, send the password through a separate channel, such as a phone call or another authenticated message-not in the same email as the zip file-so that one compromised email does not expose both the file and its password.