Question

Using Excel for a Bank Reconciliation

Download an Excel template for this problem online in MyAccountingLab or athttp://www.pearsonhighered.com/Horngren.

Lori Anders of Wilderness Associates is getting ready to prepare the October bank reconciliation.

The cash balance on the books of Wilderness Associates on October 31 is \(3,546.

Lori reviews the bank statement, and notes the checking account balance at October 31 is \)2,445. The bank statement also

reveals that the bank collected a note receivable on behalf of Wilderness Associates—the principal was \(1,500 and the interest

was \)15. One customer’s check for \(29 was returned by the bank for insufficient funds. Two additional items on the bank statement

were the monthly EFT for the utilities, \)250, and the bank service fee of \(12. Lori notes that the cash deposit made on

October 31 of \)3,300 does not appear on the statement, and that three checks totaling $975 had not cleared the bank account

when the bank statement was prepared.

Requirements

1. Prepare the bank reconciliation for Wilderness Associates at October 31, 2018. Format appropriate cells with dollar signs and double

underlines. Use Excel formulas to calculate subtotals and totals.

2. Journalize the entries based on the bank reconciliation. For dollar amounts, use cell references on bank reconciliation

Short answer

The adjusted balance of the bank reconciliation statement is $4,770.

Step-by-step solution

Definition of bank reconciliation statement

The bank reconciliation statement is the statement prepared to remove the errors of bank balance and cash book balance.

Bank reconciliation statement

Wilderness Associates
Bank Reconciliation Statement
October 31, 2018
Bank Side		Book Side
Particulars	Amount	Particulars	Amount
Balance as per bank	$2,445	Balance as per cash book	$3,546
Add:		Add:
Outstanding Deposits	$3,300	Interest Revenue	$15
		Bank collection	$1,500
Deductions:		Deductions:
		Utilities expense	250
Outstanding Checks	$975	NSF Cheque	$29
		Service Charge	$12
Adjusted Balance on August 31	$4,770	Adjusted Balance on August 31	$4,770

In the bank reconciliation statement, the opening balance as per bank and cashbook is$2,445 and $3,546. You add the outstanding deposit and deduct the outstanding checks to find the adjusted balance on the bank side. You add rent receipts and note collection to the book balance on the book side. After this, you deduct the NSF check, incorrect recording of the check, EFT payment, and service charge. After making these adjustments on both sides, you got the adjusted balance of $4,770.

Journal Entries

Date	Particulars	Debit	Credit
October 31	Cash	$15
	Interest Revenue		$15
	(To record the interest revenue)
October 31	Accounts Receivable	$29
	Cash		$29
	(To record NSF Check)
October 31	Cash	$1,500
	Accounts Receivable		$1,500
	(To record the receipt of account receivable)
October 31	Bank Charges	$12
	Cash		$12
	(To record the payment of bank charges)