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.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more