ACT2291 Excel Assignment Bank Reconciliation Using Conditional Formatting Lab Insight: An important internal control for cash is the reconciliation of the cash general ledger account to the bank statement on a regular basis. We call this a bank reconciliation. To perform a reconciliation, the company needs to reconcile the cash balance recorded in its general ledger (GL) with the cash that the bank collected or charged (disbursed) without the company's knowledge. At the same time, the company needs to reconcile the cash balance on the bank statement with the transactions recorded in the GL, but not known at the bank. We also use reconciliations to find recording errors that either the bank or the company have made. In this lab, we use conditional formatting that can be used to find items that need to be reconciled. Required: 1. Find the items that need to be reconciled between the company general ledger and the bank statement. Ask the Question: Can we use conditional formatting to highlight reconciling items for a bank reconciliation? Master the Data: We Consult You has cash transactions recorded in the general ledger and cash transactions reported on the bank statement, including checks, deposits, bank notes, NSF checks and bank fees. There are several different ways the company and the bank might report different cash items, including the following: Outstanding checks are checks written by the company but not yet processed by the bank. Outstanding deposits are deposits recorded by the company but not yet processed by the bank. NSF Checks are checks reported as received by the company, but the bank does not recognize them because the check writer has insufficient funds. Notes (loans made to customers collected by the bank) and interest (owed on notes from the customers and collected by the bank) collected by banks. The company may not find out about these items until it receives the bank statement. Bank Service Fees are fees the bank charges for their banking services and checking accounts. The bank deducts these fees directly from the company's checking account. The company may not find out about the fee until it receives the monthly bank statement. Errors are sometimes made by the company or the bank in recording a transaction. Accountants do not find these errors until they compare the amounts for each transaction on the general ledger and each transaction on the bank statement. Perform the Analysis: Open Excel File ACT2291 Excel assignment Data.xlsx Use Excel's conditional formatting function to find the items that need to be reconciled. Conditional formatting applies a format (or a highlight) to cells differently depending on the content of the individual cells. File Home Insert Page Layout Formulas Data Review View Tell me what you Calbri Custom Parte Font Vernon J. Richards...Share Conditional Cal formatting Table-Styles-format Delete Sort & Find & -Fier-Select- Number Styles Cells Eding In this lab you will use conditional formatting to find GL cash transactions that are different than those reported by the bank. In this case, conditional formatting reveals the unique items in the two columns of numbers that will be important to our cash reconciliation. Step 1: Copy the data to a new worksheet called "working data". Next, hold the Control Key and highlight both columns of numbers. Then, select Conditional Formatting and then click on New Rules: Select "Format only unique or duplicate values" and select "unique" under Format all. Click on the Format...command to set the color fill you would like for the unique values. Select OK, and the screen will format the unique numbers as follows, highlighting the numbers in each column that need to be reconciled: The cells highlighted those items that need to be reconciled. The cells in the general ledger column highlighted are items recorded by the company but not by the bank. The checks highlighted would be considered outstanding checks. The deposits highlighted would be considering outstanding deposits. The cells in the bank statement column highlights are items recorded by the bank but not by the company. The cells pertaining to the same check or deposit number highlighted in both the general ledger and the bank statement column are considered to be errors. Share the Story: Bank reconciliations are a basic internal control used by companies to ensure that their cash transactions are properly recorded. By comparing the cash transactions to the independent records of a bank, the company and its auditors can be more certain that its cash transactions are properly recorded. The company may also use this to identify bank errors. General Ledger/Company Books We Consult You Cash Transactions March 31, 2022 Bank Statement Transaction Type Transaction Type Check 1762 1,414.13 Deposit 1224 127.98 Check 1763 1,783.98 Deposit 1228 2,985.12 Check 1764 2,041.73 NSF Check 1213 2,645.05 Check 1765 987.34 NSF Check 1217 924.17 Check 1766 1,501.19 NSF Fees 1 25.00 Check 1767 2,278.18 NSF Fees 2 35.00 Check 1768 4,398.01 Deposit 1227 1,819.13 Check 1769 1,349.56 Deposit 1225 289.75 Check 1770 2,430.61 Deposit 1226 699.46 Check 1771 1,513.19 Deposit 1223 2,073.14 Check 1772 2,985.09 Deposit 1229 993.09 Check 1773 865.23 Deposit 1230 3,012.23 Check 1774 152.90 Check 1762 1,414.13 Deposit 1223 2,073.14 Check 1764 2,041.73 Deposit 1224 127.98 Check 1765 987.34 Deposit 1225 289.75 Check 1766 1,501.19 Deposit 1226 699.46 Check 1767 2,478.18 Deposit 1227 1,819.13 Check 1768 4,398.01 Deposit 1228 2,985.12 Check 1769 1,349.56 Deposit 1229 993.09 Check 1770 2,430.61 Deposit 1230 3,012.23 Note Collect 1 1,500.00 Deposit 1231 1,264.20 Interest Earned 11.05 Deposit 1232 1,265.38 Deposit 1233 2,613.29