or this assignment, you will prepare a bank reconciliation, and then describe how the auditor would test the items on the bank reconciliation. Keep in mind, the CLIENT should perform the bank reconciliation (not the auditor). It should be performed in a timely manner (a component of good internal control).

First, we will review the bank reconciliation process and have you prepare the bank reconciliation using excel. (10 points)

Then, you will put on your auditor hat and describe the substantive procedures that are performed on the bank reconciliation. (15 points)

Thomas Consulting received the September 30th bank statement with the following monthly activity:

Balance at 8/31/2020	$68,922
Deposits	162,500
Checks paid	(187,412)
NSF checks	(800)
Auto withdrawal - loan payment automatically deducted from account (includes $225 in interest)	(5,125)
Bank service fees	(50)
Balance at 9/30/2020	$38,035

On 9/30/2020, the cash account ledger balance was $41,773.

Deposits in transit were as follows;

• 9/18 $3,200
• 9/29 $2,461
• 9/30 $2,757

All checks posted in the ledger cleared the bank except for those totaling $10,205. Also, a $500 deposit from a customer was mistakenly recorded as a $50 debit to cash and credit to accounts receivable.

Required:

1. Using excel, prepare a Bank Reconciliation for Thomas Consulting as of 9/30/2020.
2. You can use any layout to prepare your Reconciliation, just be sure your adjusted/corrected cash balance reconciles. Don't submit a Bank Reconciliation that doesn't reconcile.
3. Formatting Rules: format your numbers with the thousands separator and no decimals. Do NOT format dollar signs on every number.
4. In the same excel file, use a new sheet to record any necessary journal entries to adjust the cash account.
5. Lastly, put on your auditor hat and describe how the auditor will test the items on the bank reconciliation. Be specific - I am looking for the substantive procedures that the auditor would perform. (Refer to pages 244 - 248 in the text. The Bank Reconciliation on page 245 is also a good example. It illustrates the substantive testing performed on the bank reconciliation items.) Again, what are the substantive procedures, not internal control recommendations. Also, as the auditor, do you have any concerns or follow up questions for the client? Please respond to this in the text box in the assignment link, or you can respond using a text box within the excel file.