Lesson 7: Bank reconciliation: find outstanding checks

Practice exercises for accountants, checked instantly in your browser.

A bank reconciliation explains the difference between the bank statement and the books. Two common reasons are checks you wrote that have not cleared (outstanding checks) and deposits the bank has not recorded yet (deposits in transit).

COUNTIF(cleared list, check number) returns how many times a check appears on the statement. Zero means it is still outstanding, so =IF(COUNTIF($E$2:$E$5,A2)=0,"Outstanding","Cleared") labels each check. Then the adjusted bank balance = statement balance + deposits in transit − outstanding checks.

Worked example: =COUNTIF($E$2:$E$5,5101)
Returns 1 because check 5101 appears on the statement once.

Exercises

Exercise 1

Willow Creek Landscaping's check register is in A:B and the checks that cleared the bank are in E2:E5. In C2, return Outstanding or Cleared and fill to C8.

Exercise 2

In H3, total the outstanding checks.

Exercise 3

In H4, calculate the adjusted bank balance from H1, H2 and H3.