Excel | Accounting homework help

You see four files. 1) 2010 year end Trial Balance in excel format 2) General ledger extract file for 2011 in Text format  3) 2011 year end Trail Balance in PDF format 4)2011 year end TB in excel format. We will use these files to accomplish a General Ledger roll forward, 2010 to 2011, to attest to the validity of the 2011 trial balance figures. This is a common test and all your tables should ultimately be in the same workbook on different sheets.

I.                    Read the G/L text transaction file into excel using the vertical line as the delimiter. This file has debits and credits by account number. However, the account number is not in the same format as the 2010 TB. Sort the file by account number. Insert 6 columns so your data is not over written and then use text to columns to isolate the core account number (insert 6 columns to the right of the account number so you do not overwrite any data in the table) (see the trial balance to identify the core portion of the account number) in a single column. You may delete the extraneous columns that are meaningless.

II.                  Create a PIVOT table to determine total debits and credits by account number. Copy the account number, debit and credit columns to three other columns on the same sheet and find the net by account in a separate column.

III.                Open the 2010 Trail balance file. Copy the account and net columns from II above into that sheet. Leave a couple of columns between the 2010 data and your net debits and credits. Be sure to also copy in the account numbers from above. Go to the 2011 Trial Balance excel file and copy the ending balances into this same sheet. 

IV.                Build a SUMIF to ask if the column with the account number from the PIVOT table data equals the account number in column A, then sum the net amounts and add column C.   Label this column RollForward and place this column next to the 2011 ending balances from the 2011 Trail balance and see if the amounts equal.