Question

Internal Control and Cash Using Excel for a Bank Reconciliation Lori Anders of Wilderness Associates is getting ready to prepare the October bank reconciliation. The cash balance on the books of Wilderness Associates on October 31 is $3,546. Lori reviews the bank statement, and notes the cash balance at October 31 of $2,445. The bank statement also reveals that the bank collected a note receivable on behalf of Wilderness Associates -- the principal was $1,500 and the interest was $15. One customer's check for $29 was returned by the bank for insufficient funds. Two additional items on the bank statement were the monthly EFT for the utilities, $250, and the bank service fee of $12. Lori notes that the cash deposit made on October 31 of $3,300 does not appear on the statement, and that three checks totaling $975 had not cleared the bank account when the bank statement was prepared. Use the blue shaded areas on the ENTER-ANSWERS tab for inputs.

Mostly stuck around the first part

Answer 1

Answer:

The bank reconciliation and Journal entries are as follows:

Wilderness Associates Bank Reconciliation statement For the month Ending October 31, 2018 Bank Bank balance as per Bank statement $2,445 Add: Deposit in Transit $3,300 $5,745 Less: Outstanding Check $975 Adjusted balance $4,770 Books Balance as per company's cash book $3,546 Add: Collection of notes receivable - Principal Interest Income on notes receivable $1,500 $15 $1,515 $5,061 Deduct: Bank service charges NSF check returned EFT for payment of utilities expenses $12 $29 $250 $291 $4,770 Adjusted Balance Date Account /Explanation Debit Credit $1,500 10/31/2018 Cash Notes Receivable To record notes receivable collected by bank $1,500 $15 10/31/2018 Cash Interest Revenue To record interest earned on the notes receivable $15 $12 10/31/2018 Miscellaneous Expenses Cash To record bank service fees $12 $29 10/31/2018 Accounts Receivable Cash To record NSF check returned by bank $29 $250 10/31/2018 Utilities Expense Cash To record payment of utilities by EFT $250

The above excel with 'show formula' is as below (it is not possible to accommodate in one image; it is in 2 images)

D E F G H А вс I Wilderness Associates Bank Reconciliation statement For the month Ending October 31, 2018 5 Bank 6 Bank balance as per Bank statement 2445 7 3300 =H6+H8 975 8 Add: Deposit in Transit 9 10 11 Less: Outstanding Check 12 13 Adjusted balance 14 15 Books 16 Balance as per company's cash book =H9-H11 3546 17 18 Add: 19 1500 Collection of notes receivable - Principal Interest Income on notes receivable =F19+F20 =H16+H21 23 Deduct: Bank service charges NSF check returned EFT for payment of utilities expenses =F24+F25+F26 =H22-H27 28 Adjusted Balance

B C D E F G A 28 Adjusted Balance 29 30 HT =H22-H27 31 Date Account /Explanation Debit Credit 34 10/31/2018 =F19 35 Cash Notes Receivable To record notes receivable collected by bank =F19 36 37 38 10/31/2018 39 40 Cash =F20 =F20 Interest Revenue To record interest earned on the notes receivable 43 42 10/31/2018 43 Miscellaneous Expenses Cash To record bank service fees =F24 44 45 46 10/31/2018 =F25 47 Accounts Receivable Cash To record NSF check returned by bank =F25 48 50 10/31/2018 Utilities Expense Cash To record payment of utilities by EFT =F26