Using Excel to Prepare a Bank Reconciliation

PROBLEM

The following information relates to Ceilo Limited's Cash account.

Reconciled cash balance from

June's bank reconciliation

Amounts from the general ledger:

July cash receipts

July cash payments

Amounts from the July bank statement:

Balance, July $31$

Outstanding cheques

Deposit in transit

EFT collections on account that were not yet recorded on the books

NSF cheque

NSF fee

Bank service charge

$$18,920$

$21,700$

$24,300$

$19,260$

$3,630$

$1,450$

$2,170$

$1,270$

$50$

$90$

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.

Prepare the necessary journal entries required on July $31$ for Ceilo.

Cash balance per bank

Add: Deposits in transit

Less: Outstanding cheques

Reconciled cash balance per bank

Cash balance per books

Add: EFT collections on account

Less: NSF cheque and fee

Reconciled cash balance per books