SteveAccounts
New Member
- Joined
- May 26, 2013
- Messages
- 1
Hey Guys,
A part of my job involves reconciling supplier statements with Dynamic GP Creditor data. I also have to reconcile using two systems (2012 GP, 2013-Current GP) due to data transfer issues between the two. Very long process and looking for efficiency suggestions. Please see the process for one supplier reconciliation below:
Step 1: Upload supplier statement into sheet 1
Step 2: Export creditor data from GP 2013 (all transactions) into sheet 2
Step 3: Create new column next to document number header (in sheet 2) to separate text from letters. Trans. are entered into GP in the following format to identify which bank account they are to be paid out of:
C- 'Invoice Number' (C Account)
A- 'Invoice Number' (A Account)
T- 'Invoice Number (T account)
eg. C-256, we would pay this invoice out of the C Account.
Step 4: Highlight all C- invoice numbers and use 'Text to Columns' and 'Fixed Width' to dump all of the invoice numbers into the column created in step 2. Repeat for other accounts and cut all other document numbers without account references into the the new column. Is there a faster way to do this?
Step 5: Create a VLOOKUP (in a new column) in sheet 2 to return data from the statement in sheet 1. Look-up value is the first cell in the new column created in sheet 2 (i.e. invoice number), lookup table is the invoice number column in sheet 1.
Step 6: Copy formula down in sheet 2 and filter for returned values. Then filter these by GP document status, which is the first column in sheet 2 (Open (Payable) or History (Paid)):
Returned Value- Open on GP- Payable (O/S on both ledgers)
Returned Value- History on GP- Paid (Non-payable, query this with supplier)
Copy and paste returned values which are 'Open' into worksheet 3. (Forms basis of payment)
Copy and paste returned values which are 'History' into worksheet 4 for reference.
Step 7: Filer for NA values (Invoice numbers that could not be returned from sheet 1) and then filter these by Open and History:
Non-Returned Value (NA)- Open- (Query why these items do not appear on supplier statement when they appear as Open (payable) on GP 2013)
Non-Returned Value (NA)- History- Non-Payable (This is expected as supplier statement should only show outstanding items)
Copy and paste Non-Returned (NA) values which are 'Open' into worksheet 5 for reference.
Is there any way to reduce the amount of processing in Step's 6 & 7?
Step 8: Create a VLOOKUP on worksheet 1 to return data from worksheet 2 (ensuring all filters are turned off). Look-up value is the first invoice number in the invoice number column and the look up table is the created invoice number column in worksheet 2.
Step 9: Filter for returned values and non-returned values:
Returned values are on both ledgers (Forms basis of payment)- worksheet 6
Non-Returned Values- (Missing invoices-or paid on GP 2012)- worksheet 7
Copy and paste each of the above into separate worksheets for reference.
Step 10: Check amounts payable and numerical count/ presence of invoices between worksheet 3 (GP Data) and worksheet 6 (statement data).
Create a VLOOKUP on worksheet 6 to return payable values from worksheet 3. These should match with the values in worksheet 6 and return identical values. If not then highlight these in both worksheets and omit from payment until a resolution is reached.
Create a VLOOKUP in worksheet 6 to return invoice numbers from worksheet 3. These should return identical values. If not then the item has been paid, 'History' and will not be returned from worksheet 3.
Also if the sum of payable values do not agree between worksheets 3 & 6, then comparing numerical counts between the sheets will highlight duplicate entries if all values were returned.
Step 11: Historical Checks. (i.e. Have these invoices been paid?)
Create two new worksheets to accommodate for 2013 'History' and 2012 'History'. (Paid Data)
Copy and paste History data from worksheet 2 into 2013 History worksheet.
Export 2012 History data from 2012 GP into 2012 History worksheet. (Carry out the same process to separate bank reference from invoice numbers)
Step 12: Finalization
Create a VLOOKUP in a separate column in worksheet 3 to return 'History' invoice data from 2013 History worksheet.
Create a VLOOKUP in a separate column in worksheet 3 to return 'History invoice data from 2012 History worksheet.
Highlight any returned values and omit from payment.
Step 13: Double checks
Create a VLOOKUP from worksheet 7 (missing invoices- or paid in GP 2012) to return values from History 2012 worksheet. This serves as an indicator as to what is truly missing from the ledger.
Any Excel efficiency or workflow suggestions would be greatly appreciated and I look forward to hearing from you
SteveAccounts
A part of my job involves reconciling supplier statements with Dynamic GP Creditor data. I also have to reconcile using two systems (2012 GP, 2013-Current GP) due to data transfer issues between the two. Very long process and looking for efficiency suggestions. Please see the process for one supplier reconciliation below:
Step 1: Upload supplier statement into sheet 1
Step 2: Export creditor data from GP 2013 (all transactions) into sheet 2
Step 3: Create new column next to document number header (in sheet 2) to separate text from letters. Trans. are entered into GP in the following format to identify which bank account they are to be paid out of:
C- 'Invoice Number' (C Account)
A- 'Invoice Number' (A Account)
T- 'Invoice Number (T account)
eg. C-256, we would pay this invoice out of the C Account.
Step 4: Highlight all C- invoice numbers and use 'Text to Columns' and 'Fixed Width' to dump all of the invoice numbers into the column created in step 2. Repeat for other accounts and cut all other document numbers without account references into the the new column. Is there a faster way to do this?
Step 5: Create a VLOOKUP (in a new column) in sheet 2 to return data from the statement in sheet 1. Look-up value is the first cell in the new column created in sheet 2 (i.e. invoice number), lookup table is the invoice number column in sheet 1.
Step 6: Copy formula down in sheet 2 and filter for returned values. Then filter these by GP document status, which is the first column in sheet 2 (Open (Payable) or History (Paid)):
Returned Value- Open on GP- Payable (O/S on both ledgers)
Returned Value- History on GP- Paid (Non-payable, query this with supplier)
Copy and paste returned values which are 'Open' into worksheet 3. (Forms basis of payment)
Copy and paste returned values which are 'History' into worksheet 4 for reference.
Step 7: Filer for NA values (Invoice numbers that could not be returned from sheet 1) and then filter these by Open and History:
Non-Returned Value (NA)- Open- (Query why these items do not appear on supplier statement when they appear as Open (payable) on GP 2013)
Non-Returned Value (NA)- History- Non-Payable (This is expected as supplier statement should only show outstanding items)
Copy and paste Non-Returned (NA) values which are 'Open' into worksheet 5 for reference.
Is there any way to reduce the amount of processing in Step's 6 & 7?
Step 8: Create a VLOOKUP on worksheet 1 to return data from worksheet 2 (ensuring all filters are turned off). Look-up value is the first invoice number in the invoice number column and the look up table is the created invoice number column in worksheet 2.
Step 9: Filter for returned values and non-returned values:
Returned values are on both ledgers (Forms basis of payment)- worksheet 6
Non-Returned Values- (Missing invoices-or paid on GP 2012)- worksheet 7
Copy and paste each of the above into separate worksheets for reference.
Step 10: Check amounts payable and numerical count/ presence of invoices between worksheet 3 (GP Data) and worksheet 6 (statement data).
Create a VLOOKUP on worksheet 6 to return payable values from worksheet 3. These should match with the values in worksheet 6 and return identical values. If not then highlight these in both worksheets and omit from payment until a resolution is reached.
Create a VLOOKUP in worksheet 6 to return invoice numbers from worksheet 3. These should return identical values. If not then the item has been paid, 'History' and will not be returned from worksheet 3.
Also if the sum of payable values do not agree between worksheets 3 & 6, then comparing numerical counts between the sheets will highlight duplicate entries if all values were returned.
Step 11: Historical Checks. (i.e. Have these invoices been paid?)
Create two new worksheets to accommodate for 2013 'History' and 2012 'History'. (Paid Data)
Copy and paste History data from worksheet 2 into 2013 History worksheet.
Export 2012 History data from 2012 GP into 2012 History worksheet. (Carry out the same process to separate bank reference from invoice numbers)
Step 12: Finalization
Create a VLOOKUP in a separate column in worksheet 3 to return 'History' invoice data from 2013 History worksheet.
Create a VLOOKUP in a separate column in worksheet 3 to return 'History invoice data from 2012 History worksheet.
Highlight any returned values and omit from payment.
Step 13: Double checks
Create a VLOOKUP from worksheet 7 (missing invoices- or paid in GP 2012) to return values from History 2012 worksheet. This serves as an indicator as to what is truly missing from the ledger.
Any Excel efficiency or workflow suggestions would be greatly appreciated and I look forward to hearing from you
SteveAccounts
