ANSWER IN EXCEL

Set Up the Reconciliation

As vou enter data to prepare the worksheet, remember that vou need three columns for each side of the reconciliation and one blank column between the two reconciliations

Header: (PG-1)

• Range Al:A3: Bold formatting
• Rows 1-3 each: Merge and center columns A-G

Row 5: Enter Beginning Bank Balance: and Beginning Book Balance: (with colons) in the appropriate locations (hint: the bank side is the left side; also enter the bank statement balance as of 12/31/ZUZ6 ($16,200). (PG-2

Rows 6 and 10: Enter the Add: and Deduct: (with colons) labels in the proper cells. (PG-3)

Columns A and E width: 24 (PG-4)

Column D width: 1

Populate the Reconciliation

You will need EAS-A2-QBReport (Chapter 05 folder) to begin this section. Arrange the two windows side by side on the screen to easily drag and drop into the bank reconciliation.

Outstanding checks:

• Enter Outstanding Checks in the appropriate location in the bank reconciliation. (PG-5a)
• Column B: Drag and drop each outstanding check, in the order they appear in the QuickBooks report, to the appropriate cells. (PG-5b)

Column G: Drag and drop the book balance to its proper cell then use Paste Values to replace

the formula with its result. (PG-5c)

Deposits: Use the Data tab to import the deposits in transit from EA5-A2-Deposits (Chapter folder); enter the appropriate description in column A. (PG-6)

During the month, the bank erroneously deposited a $505 check written to Pepper Products into the bank account of Tasters Club Corp.; enter this where appropriate, using Check

Error as the description. (PG-7)

Enter these descriptions and amounts in the order shown in the appropriate locations (after any already existing items): (PG-8)

Notes Receivable

NSF Check

Bank Charges

$395

$4,000

$550

Columns Cand G:

• Ensure subtotals for each Add section are displayed in the proper locations (if a section contains only one amount, that amount is the subtotal). (PG-9a) Ihen add each to the appropriate beginning balance within formulas on row y, using Undo to prevent the existing table from extending to Column C. (PG-9b
• Ensure subtotals are displayed for each Deduct section in the proper locations. (PG-9c)

In row 15, enter formulas to calculate the adjusted balances on the bank and book sides of the

reconciliation: enter the descriptions, too (no colon). (PG-10)

Finalize the Reconciliation

All dollar amounts: Currency number format, zero decimals (Lip: Make the changes once then use Format Painter for all remaining amounts.) (PG-11)

Columns B and F width: 8 (PG-12)

Add borders: (PG-13)

Bottom Border: Cells below which a calculation is completed

Aad the Headino < Ouck orvie to the descrptions for the Deon

(tour total. (PG-14)

Create a new conditional formatting rule to the beginning bank statement balance such that the cell will contain a vellow ill color with dark yellow text it it drops below $2U,UUU. (PG-15)

Header, right section: Current Time code (PG-16)

Header, left section: Current Date code

Footer, left section: File Path code

save and close the nle.

• Using Project Grader: Save the file as EA5-A2-BankRec-eSubmission and attach it to the assignment you submit for grading.
• Not using Project Grader: Submit the file per your instructor's guidelines.

LABYRINTH