a. Create a spreadsheet to reconcile bank statements. Organize it as follows: i. Ending balance, per bank
Question:
a. Create a spreadsheet to reconcile bank statements. Organize it as follows:
i. Ending balance, per bank statement ii. Deposits not included in bank statement (provide space for up to 7)
iii. Checks outstanding that have not yet cleared (provide space for up to 20)
iv. Eees charged, per bank statement V. Interest earned, per bank statement vi. Adjusted bank balance (this should reflect the effects of items ii-v)
vii. Balance per books
b. Add appropriate data validation rules to ensure spreadsheet accuracy. Test your spreadsheet by entering the following data:
i. Ending balance per bank statement = $97,525.33 ii. Deposits not included in bank statement = $15,000 and $25,000 iii. Checks outstanding that have not yet cleared:
1. check number 3455 for $2,256.75 2. check number 3487 for $13,567.95 3. check number 3501 for $14,775.00 iv. Fees charged by bank = $25 V. Interest earned = $21.95 vi. Result of your formula vii. Balance per books = $106,928.68
c. Tab to another blank worksheet and name it “checks cleared.” Follow the instruc¬ tions on the Letter to the Editor “Using VLOOKUP to do Bank Reconciliations,” on page 14 of the March 2006 issue of the Journal of Accountancy to create a worksheet that will automatically identify any checks outstanding from last month that have not yet cleared, as well as any checks that were cashed for a dif¬ ferent amount than originally recorded (you can access archived copies of the Journal ofAccountancy on the AICPA’s Web site at www.aicpa.org). Test your work by entering the following data:
Step by Step Answer:
