Answered You can hire a professional tutor to get the answer.
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 f
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.
I. Read the G/L text transaction file into excel using the vertical line as the delimiter.(I have done this part and the file name is book1) This file has debits and credits by account number. However, the account number is not in the same format as the 2010 TB, which shows the CORE account number without extraneous added number sets. Sort the file by account number. Insert 6 columns next to the account number column, then use text to columns to isolate the core 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 numbers and balances into the above sheet. Leave a columns between the 2010 data and your net debits and credits. Be sure to also copy in the account numbers from your PIVOT table when calculating net debits/credits for 2011. Go to the 2011 Trial Balance excel file and copy the ending balances into this same sheet to the right of the 2010 TB amounts.
IV. Build a SUMIF to ask if the column with the account number from the PIVOT table data equals the account number in the 2010 TB and then sum the net amounts and the 2010 balances. Label this column G/L Roll-Forward and place this column next to the 2011 ending balances from the 2011 Trail balance and see if the amounts equal by using a V-lookup or a simple IF statement function. You should have about the first 80 or so accounts that match before we see problems with the underlying data. This is fine. It is not a result of your work, it is the expense accounts in the GL extract that do not exist in the Post closing Trial Balance.
