Develop Your Skills EA5-D1
Develop Your Skills EA5-D1
In this exercise, you will create a bank reconciliation for City Music World for the month ended December 31, 2026. The reconciliation will be partly based on these figures: Bank Statement Balance (12/31/2026) equals $35,550; Notes Receivable equals $1,400; NSF Check equals $528; Bank Charges equals $150. During the previous month, the company erroneously recorded a check written by City Music World for $650 instead of the actual $950.
Start Excel. Open a Blank Workbook and save the file in your Chapter 05 folder as: EA5-D1-BankRec
Enter this data and apply bold formatting to all three cells:
Cell A1 City Music World
Cell A2 Bank Reconciliation
Cell A3 ‘December 31, 2026
Highlight the range A1:G3, choose Home→Alignment→Merge and Center →Merge Across, and center-align the content.
Enter this data:
Cell A5 Beginning Bank Balance:
Cell E5 Beginning Book Balance:
Cells A6 and E6 Add:
Cells A9 and E9 Deduct:
By first entering these elements, you have established a structure for the reconciliation. As you reconcile items, you can enter each within its proper section.
Set the width of columns A and E to: 24
Hint: The Ribbon path is Home→Cells→Format→Column Width.
Set the width of column D to: 1
Type the bank statement balance of 35550 in cell C5.
Open EA5-D1-QBReport from your Chapter 05 folder.
The beginning book balance is derived from the company’s records as of December 31, 2026. This file, which represents a report generated by QuickBooks, displays the activity and ending cash balance for the month of December.
In the QuickBooks report window, choose View→Window→Arrange All.
Click Vertical and then click OK.
In the bank reconciliation window, type Outstanding Checks in cell A10.
In the QuickBooks report window, click cell N6 and then click the cell border and drag its contents to cell B10 in the bank reconciliation window.
This $3,200 figure represents the first of three outstanding checks in the QuickBooks report. Column K displays a checkmark for those checks that have cleared the bank, so to identify the outstanding checks you must look for those that do not have a checkmark in column K.
Continue to drag and drop contents from the QuickBooks report to the bank reconciliation as noted:
QuickBooks report location Bank reconciliation location
Cell N8 Cell B11
Cell N10 Cell B12
Cell O14 Cell G5
Cell O14 in the QuickBooks report shows the company’s book balance at month end. This is the starting point for the company side of the reconciliation and so is dropped in cell G5. Note that cell G5 now contains the formula used to determine this balance in the QuickBooks report. You will now remove the formula.
In the bank reconciliation, click cell G5 and choose Home→Clipboard→Copy, choose Home→Clipboard→Paste →Paste Values→Values, and tap Esc.
The formula that resulted in $33,344 appearing in cell G5 has been replaced with the value itself.
Close the QuickBooks report, choosing Don’t Save when prompted.
The drag-and-drop actions removed the cell contents from the QuickBooks report. By not saving the file when closing, you are retaining all content.
In the bank reconciliation, select the row 7 header, scroll to the row 8 header, and release the mouse button. Right-click either of the highlighted row headers and choose Insert.
Three deposits in transit must be imported to the bank reconciliation. As there was only one row available for the inclusion of this data, two additional rows are added to accommodate the necessary information.
Click cell B6 choose Data→Get & Transform Data→From Text/CSV, navigate to EA5-D1-Deposits in your Chapter 05 folder, and click Import.
The necessary deposit-in-transit information, which is also included in the QuickBooks report, has been consolidated in this text document.
Click Transform Data.
This opens the Power Query Editor, through which data can be modified before the import is completed.
Choose Home→Manage Columns→Remove Columns.
This step deletes the first column, which was highlighted when the Power Query Editor was opened. We do not want to display deposit dates within the bank reconciliation.
Choose Home→Close→Close & Load →Close & Load To and then select Existing Worksheet in the Import Data window and click OK.
Because you previously selected cell B6, the date is imported to this location.
Choose Table Design→Table Style Options→Header Row to remove the imported header in cell B6.
Click Banded Rows to remove the shaded rows and then close the Queries & Connections task pane.
NOTE! Tables, like the one created as part of the import process here, are used to manage data in Excel. They will be discussed in Chapter 6.
In cell A7, type: Deposits in Transit
Save your file.
Unless otherwise directed, always keep your file open at the end of each exercise.
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
