O Califomia State Uni.. A Enrolled Agent (EA). 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 Chavez Company most recently reconciled its bank statement and book balances of cash on August 31, 2018 and it reported two checks outstanding, No. 5888 for $1,028.05 and No. 5893 for $49425. The%llowing information is available for its September 30, 2018, reconciliation. From the September 30 Bank Statement PREVOUS BALANCE I TOTAL CCKS AND DEanrs | TOTAL DEPOSITS AND CRDTS cunnsNT BALANCE 16,800.45 9,620.05 1,272.85 18,453.25 CHECKS AND DEBITS DEPOSITS AND CREDITS Date No. Amount 09/03 5888 1,028.05 09/04 5902 719.90 09/07 5001 1,824.25 DAILY BALANCE 1,103.75 09/05 09/122.226.90 09/21 08/31 09/03 16,800.45 5,772.40 15,052.50 16,156.25 14,332.00 16,558.90 15,958.65 15.021.65 19,114.65 16,625.55 18,977.25 18,763.40 16,955.75 18,453.25 4,093.00 60025NSF 09/25 2.351.70 0/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 09/05 12.50IN007 1.486.00 CM109/12 09/30 0900 09/20 09/21 09/22 09/25 09/28 09/30 Califomia State Uni... Enrolled Agent (EA.. From Chavez Company's Accounting Records Cash Receipts Deposited Cash Disbursements Cash Credit Check Cash Debit Date 5901 5902 5903 5904 5905 5906 5907 5908 5909 824.25 719.90 399.10 2,060.00 937.00 82.30 213.85 88.00 Sept.5 ,103.75 2,226.90 4,093.00 2351.70 1,682.75 1.458.10 25 30 9,332.05 Acet. No. 101 PRI Debit | Credit Balance 15.27815 26.73625 933205 7,404.20 Date Explanation A Baance Sapt 30 Total recepss R12 11458.10 30 Total dsbursements D23 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 ds/Problem%206%20Bank%20Reconciliation%20Directions%20%20(1).pdf O Califomia State Uni.. A Enrolled Agent (EA. Chavez company by the bank. The bank deducted a IS collection Tee. 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 company's 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 gny adiustments to cash. The check figure for adjusted cash is $18,271.45 Save your File as BANK REC1. 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. 2. 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 Balance per books Checks outstanding 547 Deposits in Transit Service Charges $ 7,950 $ 16 ViewSonic x Problem P6-Bank Reconcliatior Microsoft Word-Problem 6 Ban + oads/Problem%206%20 Bank%20Reconciliation%20Directions%20%20(1).pdf O Calfomia State Uni. A Enrolled Agent (EA. Compute the missing items using basic math principles. Save your work as BANK REC3. 4. Open the tab called Chart Analysis. On the screen, a column chart appears illustrating relative book and bank balances at July 31 and August 31. In the tab, list at least least two reasons why the discrepancy between book and bank balances may have existed at July 31? (Lines provided so you can jot down notes.) Also in the tab, list two reason why the discrepancy may have existed at August 31? (Lines provided so you can jot down notes.) Don't 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 per 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 Also in the tab, list two reason why the discrepancy may have existed at August 31? (Lines provided so you can jot down notes.) Don't forget to record your answers in the chart 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 5. 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 worlk Upload your File: After reviewing your most recent file for completeness, and making sure all parts have been answered, Save the file as BANK REC FINAL +Your last name. Upload the final file using Canvas to me for grading Summary of What's Due: (I will be looking at your one submitted file. However, make sure that your work is set up to print out correctly. These are the "Printouts" that I will be reviewing in your final file.) Printout of completed template for the September bank reconcillation Printout of the journal entries . Printout of the October bank reconciliation ViewSonic O Califomia State Uni.. A Enrolled Agent (EA). 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 Chavez Company most recently reconciled its bank statement and book balances of cash on August 31, 2018 and it reported two checks outstanding, No. 5888 for $1,028.05 and No. 5893 for $49425. The%llowing information is available for its September 30, 2018, reconciliation. From the September 30 Bank Statement PREVOUS BALANCE I TOTAL CCKS AND DEanrs | TOTAL DEPOSITS AND CRDTS cunnsNT BALANCE 16,800.45 9,620.05 1,272.85 18,453.25 CHECKS AND DEBITS DEPOSITS AND CREDITS Date No. Amount 09/03 5888 1,028.05 09/04 5902 719.90 09/07 5001 1,824.25 DAILY BALANCE 1,103.75 09/05 09/122.226.90 09/21 08/31 09/03 16,800.45 5,772.40 15,052.50 16,156.25 14,332.00 16,558.90 15,958.65 15.021.65 19,114.65 16,625.55 18,977.25 18,763.40 16,955.75 18,453.25 4,093.00 60025NSF 09/25 2.351.70 0/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 09/05 12.50IN007 1.486.00 CM109/12 09/30 0900 09/20 09/21 09/22 09/25 09/28 09/30 Califomia State Uni... Enrolled Agent (EA.. From Chavez Company's Accounting Records Cash Receipts Deposited Cash Disbursements Cash Credit Check Cash Debit Date 5901 5902 5903 5904 5905 5906 5907 5908 5909 824.25 719.90 399.10 2,060.00 937.00 82.30 213.85 88.00 Sept.5 ,103.75 2,226.90 4,093.00 2351.70 1,682.75 1.458.10 25 30 9,332.05 Acet. No. 101 PRI Debit | Credit Balance 15.27815 26.73625 933205 7,404.20 Date Explanation A Baance Sapt 30 Total recepss R12 11458.10 30 Total dsbursements D23 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 ds/Problem%206%20Bank%20Reconciliation%20Directions%20%20(1).pdf O Califomia State Uni.. A Enrolled Agent (EA. Chavez company by the bank. The bank deducted a IS collection Tee. 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 company's 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 gny adiustments to cash. The check figure for adjusted cash is $18,271.45 Save your File as BANK REC1. 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. 2. 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 Balance per books Checks outstanding 547 Deposits in Transit Service Charges $ 7,950 $ 16 ViewSonic x Problem P6-Bank Reconcliatior Microsoft Word-Problem 6 Ban + oads/Problem%206%20 Bank%20Reconciliation%20Directions%20%20(1).pdf O Calfomia State Uni. A Enrolled Agent (EA. Compute the missing items using basic math principles. Save your work as BANK REC3. 4. Open the tab called Chart Analysis. On the screen, a column chart appears illustrating relative book and bank balances at July 31 and August 31. In the tab, list at least least two reasons why the discrepancy between book and bank balances may have existed at July 31? (Lines provided so you can jot down notes.) Also in the tab, list two reason why the discrepancy may have existed at August 31? (Lines provided so you can jot down notes.) Don't 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 per 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 Also in the tab, list two reason why the discrepancy may have existed at August 31? (Lines provided so you can jot down notes.) Don't forget to record your answers in the chart 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 5. 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 worlk Upload your File: After reviewing your most recent file for completeness, and making sure all parts have been answered, Save the file as BANK REC FINAL +Your last name. Upload the final file using Canvas to me for grading Summary of What's Due: (I will be looking at your one submitted file. However, make sure that your work is set up to print out correctly. These are the "Printouts" that I will be reviewing in your final file.) Printout of completed template for the September bank reconcillation Printout of the journal entries . Printout of the October bank reconciliation ViewSonic