When a spreadsheet stops being safe for invoicing
Two partners run a computer repair business from Qormi, issuing about 240 invoices a year. Everything is tracked in one Excel workbook called invoices.xlsx. The invoice number is typed by hand in column A, the net amount in B, and the VAT formula in C is =B2*0.18, copied down; the total in D is =B2+C2. The partners email the file back and forth, so there are now versions named invoices_final, invoices_final2 and invoices_AC. Last month one invoice number appeared twice, three rows showed a VAT of €0.00 because the formula had been overwritten with a value, and an accountant's copy showed #REF! after a column was deleted. There is no backup except a copy on a USB stick that is 'usually' in the drawer. The accountant receives a printout at year end, and the VAT return is prepared by adding up column C by hand.
1. Identify the design weaknesses that allowed each error, and propose Excel controls that would prevent them.
Read the worked answer
Trace each error to a design choice. (1) Duplicate invoice number: the number is typed by hand, so nothing prevents reuse. Fix: issue numbers from a register or sequentially by formula, add a data-validation rule, and use COUNTIF to flag any number already used. (2) VAT of €0.00: a formula was overwritten with a value, which happens when data entry and calculation share unprotected cells. Fix: keep the VAT rate in one named cell, reference it (=B2*VAT_RATE), lock formula cells with sheet protection and leave only input cells unlocked. (3) #REF!: a column was deleted under a formula. Fix: convert the range to an Excel Table and use structured references such as =[@Net]*VAT_RATE, which survive ordinary row and column operations. (4) No audit trail: add an entry date and a locked flag, or keep monthly read-only copies with version history. (5) Totals by hand: replace with SUMIFS or a pivot table, and add a reconciliation cell proving that the sum of invoices equals the VAT return figure. The core weakness is that one sheet was doing data entry, calculation and reporting with no separation between them.
2. Explain why emailing the workbook back and forth causes problems, and propose a better arrangement.
Read the worked answer
Emailing invoices_final, invoices_final2 and invoices_AC back and forth guarantees divergence: each partner edits a different copy, there is no record of which is authoritative, and a VAT return can be prepared from a stale file. Two people editing separate copies simultaneously creates conflicting invoice numbers and duplicated entries, and there is no reliable way to merge them afterwards. The minimum fix is a single authoritative file on a shared service - OneDrive or SharePoint, or a small office NAS - with version history and one named editor at a time, plus a rule that no copy is ever attached to an email. Better still, separate entry from storage: enter invoices through a form or a shared table so the number sequence is issued centrally, rather than in a file that can be copied. Whatever is chosen needs automatic backup, not a USB stick that is 'usually in the drawer'; the 3-2-1 rule of three copies, on two media, with one off-site, is a reasonable target, with a restore test at least once a term. Version history also provides a basic audit trail that emailed copies cannot.
3. Advise whether the business should move to dedicated accounting or invoicing software, justifying your answer.
Read the worked answer
Move when the cost of the control work exceeds the cost of software, and this business is close to that point. Even a well-protected workbook still lacks immutable sequential invoice numbering, an audit trail of who changed what, and a dependable link between invoices, payments and the VAT return. For 240 invoices a year, a small accounting package or online invoicing service costs little and enforces rules that currently depend on the partners' discipline: numbers issued once and never reused, VAT computed by the software, credit notes linked to their invoice, and a VAT report that reconciles to the ledger. Maltese VAT rules expect proper records and invoices with the required particulars, and a workbook that has shown duplicated numbers and zero-rated VAT rows is weak evidence in an inspection. Migration needs care: agree a cut-off date, keep the old workbook read-only for the statutory retention period, enter opening balances, and run both systems in parallel for one VAT period to prove the figures agree. The partners should also agree who owns the ledger, because software will not fix a two-person process with no owner.