A spreadsheet formula is correct only if it matches accounting logic, and the spreadsheet cannot check that for you. Test each total and ratio with a hand calculation and one logical rule before you trust it.
This lesson is part of SPM Accounting. It works with your records from keeping an evidence and transaction log.
What are the three quick checks?
Apply all three to every key figure.
- Hand total: add the figures yourself and compare.
- Range check: check that the formula covers the first and last row.
- Logic check: check that a rule holds, such as debits equal credits, or gross profit is sales minus cost of sales.
Worked example 1: a missed row
Weekly sales in cells B2 to B5 are RM1 200, RM850, RM640 and RM1 310. The formula =SUM(B2:B4) returns RM2 690.
Hand total: 1 200 + 850 + 640 + 1 310 = RM4 000. The formula missed B5, the last week, so the correct formula is =SUM(B2:B5).
The range check would catch it, because the formula ends one row early. The hand total would catch it too, because RM2 690 is less than RM4 000.
Worked example 2: a wrong divisor
Sales are RM6 000 and gross profit is RM1 500, after RM4 500 for the cost of sales. The margin on sales should be 1 500 ÷ 6 000 × 100 = 25%.
A formula that divides by cost of sales gives 1 500 ÷ 4 500 × 100 = 33.3%. That figure is the markup on cost, not the margin on sales. The logic check is to ask, “a percentage of what?” and check that the answer is sales.
The mistake of trusting the number
A neat figure with two decimal places looks reliable. The spreadsheet shows it the same way whether the formula is right or wrong.
Treat every formula as a claim that needs evidence. Write one sentence beside key results, for example “Gross profit = sales less goods sold = RM1 500, checked by hand”.
Check yourself
A trial balance lists debits in C2:C9 and credits in D2:D9. The debit formula is =SUM(C2:C9) and the credit formula is =SUM(D2:D8).
The credit total is RM15 800 and the debit total is RM18 300. The credit in D9 is RM2 500. What is wrong?
Answer
The credit formula stops at D8, so it leaves out D9, the last row. Adding D9: 15 800 + 2 500 = RM18 300, which equals the debit total.
The corrected formula is =SUM(D2:D9), and the trial balance agrees.
What to study next
Next, practise your checking habits on the coursework support practice set. Record any slip in the mistake log and paper error review.
If you want a teacher to go through these checks on different figures, see online one-to-one Accounting tuition.