please provide step by step and excel tables with complete explanation

Lance Company controls its cash by depositing cash receipts daily and making all disbursements by cheque. After all the posting for the month of July 2022 was completed, the cash balance in the general ledger account on July 31 was $18,580. The bank statement for the month ended July 31 received from the First National Bank showed the balance to be $24,400. The following data are available for the purpose of reconciling these balances: a. Cash receipts for July 31 amounting to $1,800 have been placed in the night depository and do not appear on the bank statement. b. Bank memos previously not available to the company are included with the bank statement. A memo for an NSF cheque, originally received as payment for an account receivable of $9,000, is included. A memo for bank charges of $30 is also included. Another memo advises the company that $10,100 has been deposited to the account, ($10,200 less a bank charge of $100). This represents the net proceeds of a collection the bank had made on behalf of the company on a $10,200 note receivable. c. Cheques written during the month but not included in the bank statement are no. 1154,$500 no. 1192, $1,500; no. 1193, \$950; no. 1194,$2,000. d. Cheque no. 890 is recorded in the bank statement in the amount of $6,500. Under further investigation you find that our bookkeeper recorded the amount incorrectly in our accounting records. He recorded the amount at $5,600. The cheque was for Maintenance Expense. e. Cheques outstanding at the end of June included cheques no. 1014 or $1,600 and no. 1016 for $2,500. Cheque no. 1014 cleared the bank statement; cheque no. 1016 did not. Required: 1. Prepare a bank reconciliation on July 31. 2. Prepare the necessary adjusting journal entries required to make the cash account in the general ledger agree with the adjusted cash balance on the July bank reconciliation. Lance Company has estimated its bad debts at 2 per cent of net credit sales. During 2022, the company decided to calculate the required balance for the allowance for doubtful accounts at year-end, December 31 , by aging its accounts receivable. The review suggested a required balance of $8,000 would not be collectible. The following data, which already have been recorded in the company's general ledger, are also available: The Allowance for Doubtful Accounts general ledger account reported the following balances: January 1, 2021$8,000(Cr) and Net credit sales of $300,000. Required: Prepare journal entries to record: 1. The amount of bad debt expense for the year 2021 2. The bad debt expense on December 31,2022 3. The collection on June 5,2022