Log out Go to App
Go to App

Learning Materials

Features

Discover

Chapter 7: Q35CT (page 426)

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

Expert verified

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

Step by step solution

01

Definition of bank reconciliation statement

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

02

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.

03

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)

Unlock Step-by-Step Solutions & Ace Your Exams!

  • Full Textbook Solutions

    Get detailed explanations and key concepts

  • Unlimited Al creation

    Al flashcards, explanations, exams and more...

  • Ads-free access

    To over 500 millions flashcards

  • Money-back guarantee

    We refund you if you fail your exam.

Start your free trial

Over 30 million students worldwide already upgrade their learning with Vaia!

One App. One Place for Learning.

All the tools & learning materials you need for study success - in one app.

Get started for free

Most popular questions from this chapter

Preparing a bank reconciliation and journal entries

The May cash records of Donald Insurance follow:

Cash Receipts Cash Payments

Date Cash Debit Check No. Cash Credit

May 4 \( 4,230 1416 \) 890

9 520 1417 120

14 530 1418 630

17 1,950 1419 1,090

31 1,840 1420 1,420

1421 900

1422 670

Donald’s Cash account shows a balance of \(17,750 at May 31. On May 31, Donald

Insurance received the following bank statement:

Deposits and other Credits:

May 10

May 1

May 5

May 15

May 18

May 22

Checks and other Debits:

8

11 (check no. 1416)

19

22 (check no. 1417)

29 (check no. 1418)

31 (check no. 1419)

May

May

May

May

May

May

May 31

Ending Balance

Beginning Balance

EFT \) 450

NSF

EFT

BC

\( 18,730

1,700

890

1,100

120

520

4,230

530

1,950

\) 14,400

9,380

375

630

1,900

35 (5,050)

Bank Statement for May

SC

Explanations: BC–bank collection; EFT–electronic funds transfer;

NSF–nonsufficient funds checks; SC–service charge

Additional data for the bank reconciliation follow:

a. The EFT credit was a receipt of rent. The EFT debit was an insurance

payment.

b. The NSF check was received from a customer.

c. The \(1,700 bank collection was for a note receivable.

d. The correct amount of check 1419, for rent expense, is \)1,900. Donald’s controller

mistakenly recorded the check for $1,090.

Requirements

1. Prepare the bank reconciliation of Donald Insurance at May 31, 2018.

2. Journalize any required entries from the bank reconciliation

Identifying internal controls. Consider each situation separately. Identify the missing internal control procedure from these characteristics:

• Assignment of responsibilities

• Separation of duties

• Audits

• Electronic devices

• Other controls (specify)

a. While reviewing the records of Quality Pharmacy, you find that the same Team member orders merchandise and approves invoices for payment.

b. Business is slow at Amazing Amusement Park on Tuesday, Wednesday, and Thursday nights. To reduce expenses, the business decides not to use a ticket taker on those nights. The ticket seller (cashier) is told to keep the tickets as a record of the number sold.

c. The same trusted team member has served as a cashier for 12 years.

d. When business is brisk, Fast Mart deposits cash in the bank several times during the day. The manager at one store wants to reduce the time employees spend delivering cash to the bank, so he starts a new policy. Cash will build up over weekends, and the total will be deposited on Monday.

e. Grocery stores such as Convenience Market and Natural Foods purchase most merchandise from a few suppliers. At another grocery store, the manager decides to reduce paperwork. He eliminates the requirement that the receiving department prepare a receiving report listing the goods actually received from

the supplier.

Accounting for petty cash transactions

Suppose that on June 1, Rockin’ Gyrations, a disc jockey service, creates a petty cash

fund with an imprest balance of \(300. During June, Michael Martell, fund custodian,

signs the following petty cash tickets:

Petty Cash

Ticket Number Item Amount

1 Postage for package received \) 30

2 Office party 25

3 Two boxes of stationery 20

4 Printer cartridges 15

5 Business dinner 65

On June 30, prior to replenishment, the fund contains these tickets plus cash of \(140.

The accounts affected by petty cash payments are Office Supplies, Entertainment

Expense, and Postage Expense.

Requirements

1. On June 30, how much cash should this petty cash fund hold before it is replenished?

2. Journalize all required entries to (a) create the fund and (b) replenish it. Includeexplanations.

3. Make the entry on July 1 to increase the fund balance to \)325. Include an explanation

Applying internal control over cash receipts by mail. Review the internal controls over cash receipts by mail presented in the chapter. Exactly what is accomplished by the final step in the process, performed by the controller?

This case is based on an actual situation. Centennial Construction Company, headquartered in Dallas, Texas, built a Rodeway Motel 35 miles north of Dallas. The construction foreman, whose name was Slim Chance, hired the 40 workers needed to complete the project. Slim had the construction workers fill out the necessary tax forms, and he sent their documents to the home office. Work on the motel began on April 1 and ended September 1. Each week,Slim filled out a time card of hours worked by each employee during the week. Slim faxed the time cards to the home office, which prepared the payroll checks on Friday morning. Slim drove to the home office on Friday, picked up the payroll checks, and returned to the construction site. At 5 p.m. on Friday, Slim distributed payroll checks to the workers.

Requirements

1. Describe in detail the main internal control weakness in this situation. Specify what negative result(s) could occur because of the internal control weakness.

2. Describe what you would do to correct the internal control weakness.
See all solutions

Recommended explanations on Business Studies Textbooks

Managerial Economics

Read Explanation

Strategic Direction

Read Explanation

Organizational Behavior

Read Explanation

Human Resources

Read Explanation

Managers

Read Explanation

Introduction to Business

Read Explanation
View all explanations

What do you think about this solution?

We value your feedback to improve our textbook solutions.

Study anywhere. Anytime. Across all devices.

Sign-up for free