Problem 6 Bank Reconciliation (File: BANK REC)

Learning Objectives:

Prepare a bank reconciliation and the necessary adjusting entries.

Analyze bank and book data to solve for incomplete data.

Interpret differences.

Create a chart plotting book and bank balances over time.

Problem Data

Additional Information: Check No. 5904 is correctly drawn for $2,090 to pay for computer equipment; however, the record keeper misread the amount and entered it in the accounting records with a debit to Computer Equipment and a credit to Cash of $2,060. The NSF check shown in the statement was originally received from a customer, S. Nilson, in payment of her account. Its return has not yet been recorded by the company. The credit memorandum is from the collection of a $1,500 note for Chavez Company by the bank. The bank deducted a $15 collection fee. The collection and fee are not yet recorded.

Requirements: 1. Prepare the Bank Reconciliation. Open the file called BANK REC. After analyzing the bank statement and the company data, prepare the September 30, 2018 bank reconciliation USING PROPER form for this company. (Note: Cash is a companys most liquid asset. Just as you would not want anyone touching your cash without a good explanation, neither do businesses take adjustments to cash lightly.) PROVIDE VERY SPECIFIC details and information on any adjustments to cash. The check figure for adjusted cash is $18,271.45. Save your File as BANK REC1.

2. Prepare the journal entries (in dollars and cents) on another tab or worksheet. (Label the worksheet tab appropriately.) Journal entries are needed to adjust the book balance of cash to the reconciled balance. Re-save your work.

3. Manipulate the Data. On a new tab that you create called October, copy over the bank reconciliation you just completed for September. Then erase the September data and use the worksheet to complete the following bank reconciliation for the October 31, 2018 bank reconciliation statement:

Adjusted Balance $ 7,608

Balance per bank $ 7,950

Balance per books ?

Checks outstanding $ 547

Deposits in Transit ?

Service Charges $ 16

Compute the missing items using basic math principles.

Also in the tab, list two reason why the discrepancy may have existed at August 31? (Lines provided so you can jot down notes.)

Dont forget to record your answers in the chart.

5. Chart: Prepare a line chart to show both the monthly ending bank balance and the adjusted book balance from July 31 October 31. Give the chart a proper 3-line title. Place the chart on a new worksheet. Call the worksheet Chart. Print the chart. Include relevant data labels. Re-save your work.

6. Formulas: Create a new tab and call it formulas. Copy over your original completed bank rec for September. Show the formulas by using Control and Tilde. You may need to widen columns to show me your complete formulas. Re-save your work.

From the September 30 Bank Statement PREVIOUS BALANCE TOTAL CHECKS AND DEBITS TOTAL DEPOSITS AND CREDITS CURRENT BALANCE 16,800.45 9,620.05 11,272.85 18,453.25 CHECKS AND DEBITS Date No. Amount 09/03 5888 1,028.05 09/04 5902 719.90 09/07 5901 1,824.25 09/17 600.25 NSF 09/20 5905 937.00 09/22 5903 399.10 09/22 5904 2,090.00 09/28 5907 213.85 09/29 5909 1,807.65 DEPOSITS AND CREDITS Date Amount 09/05 1,103.75 09/12 2,226.90 09/21 4,093.00 09/25 2,351.70 09/30 12.50 IN 09/30 1,485.00 CM DAILY BALANCE Date Amount 08/31 16,800.45 09/03 15,772.40 09/04 15,052.50 09/05 16,156.25 09/07 14,332.00 09/12 16,558.90 09/17 15,958.65 09/20 15,021.65 09/21 19,114.65 09/22 16,625.55 09/25 18,977.25 09/28 18,763.40 09/29 16,955.75 09/30 18,453.25 From Chavez Company's Accounting Records Cash Receipts Deposited Cash Date Debit Sept. 5 12 21 25 30 1,103.75 2,226.90 4,093.00 2,351.70 1,682.75 11.458.10 Cash Disbursements Check Cash No. Credit 5901 1.824.25 5902 719.90 5903 399.10 5904 2,060.00 5905 937.00 5906 982.30 5907 213.85 5908 388.00 5909 1.807.65 9,332.05 Debit Cash Date Explanation PR Aug. 31 Balance 30 Total receipts RI2 30 Total disbursements D23 Acct. No. 101 Credit Balance 15,278.15 26,736.25 9,332.05 17,404.20 Sept. 11,458.10 From the September 30 Bank Statement PREVIOUS BALANCE TOTAL CHECKS AND DEBITS TOTAL DEPOSITS AND CREDITS CURRENT BALANCE 16,800.45 9,620.05 11,272.85 18,453.25 CHECKS AND DEBITS Date No. Amount 09/03 5888 1,028.05 09/04 5902 719.90 09/07 5901 1,824.25 09/17 600.25 NSF 09/20 5905 937.00 09/22 5903 399.10 09/22 5904 2,090.00 09/28 5907 213.85 09/29 5909 1,807.65 DEPOSITS AND CREDITS Date Amount 09/05 1,103.75 09/12 2,226.90 09/21 4,093.00 09/25 2,351.70 09/30 12.50 IN 09/30 1,485.00 CM DAILY BALANCE Date Amount 08/31 16,800.45 09/03 15,772.40 09/04 15,052.50 09/05 16,156.25 09/07 14,332.00 09/12 16,558.90 09/17 15,958.65 09/20 15,021.65 09/21 19,114.65 09/22 16,625.55 09/25 18,977.25 09/28 18,763.40 09/29 16,955.75 09/30 18,453.25 From Chavez Company's Accounting Records Cash Receipts Deposited Cash Date Debit Sept. 5 12 21 25 30 1,103.75 2,226.90 4,093.00 2,351.70 1,682.75 11.458.10 Cash Disbursements Check Cash No. Credit 5901 1.824.25 5902 719.90 5903 399.10 5904 2,060.00 5905 937.00 5906 982.30 5907 213.85 5908 388.00 5909 1.807.65 9,332.05 Debit Cash Date Explanation PR Aug. 31 Balance 30 Total receipts RI2 30 Total disbursements D23 Acct. No. 101 Credit Balance 15,278.15 26,736.25 9,332.05 17,404.20 Sept. 11,458.10