Bank reconciliation

This question continues on from question 1 above.

The bank reconciliation for Mask Up at 30 April 2022 is shown below:

Bank reconciliation as at 30 April 2022
Cash balance as per bank statement 30 April 2022		$32,900.00
Add: outstanding deposits		$100.00
		$33,000.00
Less: Unpresented cheques	Cheque amount
- Chq 26	$55.00
- Chq 30	$200.00
- Chq 31	$500.00	$755.00
Cash balance as per ledger		$32,245.00

The May 2022 bank statement appeared as follows:

Date	Description	Deposit	Withdrawal	Balance
1/05/2022	Opening balance			$32,900.00
1/05/2022	Deposit	100.00		$33,000.00
2/05/2022	Deposit	300.00		$33,300.00
2/05/2022	Cheque 26		55.00	$33,245.00
2/05/2022	Cheque 31		500.00	$32,745.00
3/05/2022	Deposit	2,180.00		$34,925.00
3/05/2022	Cheque 33		430.00	$34,495.00
6/05/2022	Cheque 34		3,800.00	$30,695.00
9/05/2022	Deposit	5,800.00		$36,495.00
9/05/2022	Cheque 36		660.00	$35,835.00
12/05/2022	Deposit	1,332.00		$37,167.00
14/05/2022	Deposit	2,136.00		$39,303.00
14/05/2022	Cheque 37		500.00	$38,803.00
16/05/2022	Deposit	3,200.00		$42,003.00
19/05/2022	Deposit	550.00		$42,553.00
25/05/2022	Deposit	3,200.00		$45,753.00
25/05/2022	Cheque 39		3,140.00	$42,613.00
28/05/2022	Deposit	1,065.00		$43,678.00
29/05/2022	Cheque 40		200.00	$43,478.00
30/05/2022	Deposit	1,200.00		$44,678.00
31/05/2022	Bank fees		25.00	$44,653.00
31/05/2022	Interest	14.68		$44,667.68
31/05/2022	Closing balance			$44,667.68

Mask Ups accounting records for May 2022 showed the following:

Cash payments journal:				Cash receipts journal:
					Amount
Date	Chq number	Amount		Date	banked
1/05/2022	32	$195.00
2/05/2022	33	$340.00		1/05/2022	$300.00
2/05/2022	34	$3,800.00		2/05/2022	$2,180.00
4/05/2022	35	$511.00		8/05/2022	$5,880.00
8/05/2022	36	$660.00		11/05/2022	$1,322.00
10/05/2022	37	$5,000.00		13/05/2022	$2,136.00
17/05/2022	38	$870.50		24/05/2022	$3,200.00
22/05/2022	39	$3,140.00		27/05/2022	$1,056.00
24/05/2022	41	$1,266.00		29/05/2022	$1,200.00
31/05/2022	42	$972.00		31/05/2022	$1,600.00
		$16,754.50			$18,874.00

The current balance in the bank ledger account (after the above receipts and payments have been entered) on 31 May 2022 is: $32,245.00 + $18,874 deposits - $16,754.50 withdrawals = $34,364.50.

Mr and Mrs Stitch have come to you for help, as they are having trouble reconciling the bank account as at 31 May 2022.

The bank did not make any errors.

Required:

i) In an Excel worksheet (and not in Xero), prepare the bank reconciliation as at 31 May 2022. (4 marks)

ii) In an Excel worksheet, prepare the journal entries to correct any errors made by Mr and Mrs Stitch, and to record transactions that have not yet been entered into the businesss accounting records. (Note: The correction of any errors relating to the recording of cheques should be made to Accounts payable. The correction of any errors relating to deposits (other than interest received) should be made to Accounts receivable.) (4 marks)

iii) In an Excel worksheet, prepare the bank ledger account (using the T-Account format) for May 2022, to determine the balance in the account after the above journal entries have been recorded and posted. (2 marks)