Function: SUM; formula: Subtract; Cell Referencing
Some cells are merged.
Using Excel to Prepare a Bank Reconciliation
PROBLEM
On May 31, Lopet, Inc. received its bank statement from the East Coast Savings Bank. A cheque from one of Lopet's customers that had been deposited during the last week of May was returned with the bank statement as NSF. The company had written and mailed out cheques that had not yet cleared the bank. Cheque #791, which was a payment for advertising expenses, was incorrectly recorded in the general ledger. During the month, one of JB's customers paid their account using an electronic funds transfer. The cash receipts for May 31 had been deposited in the night drop slot at the bank on the evening of May 31. These were not reflected on the bank statement for May. The following amounts were derived from comparing the bank statement and the accounting records for May.
	General ledger cash balance, May 31	$ 8,600
	Bank statement balance, May 31	12,200
	May 31 night bank deposit	1,650
	Cheques mailed out not yet cleared the bank	4,235
	NSF cheque	1,120
	Bank service charges	55
	Cheque #791 actual amount	230
	Cheque #791 amount recorded in general ledger	320
	EFT from customer received by bank	2,100

	Student Work Area
	Required: Provide input into cells shaded in yellow in this template. Use mathematical formulas with cell references to the Problem and work area as indicated. Use the SUM function with cell referencing to add amounts.
	a.	Prepare the bank reconciliation for Lopez at May 31. Show amounts that are subtracted as negative amounts.
		Lopez, Inc.
		Bank Reconciliation
		As of May 31
		Add:
		Less:
		Add:
		Less:	Service charge
	b.	What cash should Lopez report on its statement of financial position as at May 31?
		Cash on statement of financial position		$ -
	c.	Prepare the journal entries necessary as a result of the bank reconciliation prepared in part a.
					Debit	Credit
			To adjust for EFT
			To adjust for cheque error
			To adjust for service charges
			To adjust for NSF cheque