Improved method for reconciling accounts

This is what I do as well...if only there was a security function that would make sure people didn't back date received material... I do have a problem with people doing that... I keep yelling at them and they just keep doing it...

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On Behalf
Of Karen Brodniak
Sent: Monday, November 07, 2005 6:12 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Improved method for reconciling accounts.


We prefer to capture and post everything for the month on the first day of
the following month. The reason is that Vantage will correct your cost of
sales and get rid of any purges if anything is posted or corrected on the
job after shipment, as long as you have not captured and posted. My reports
almost always match the GL because I do not allow anyone to back date parts
received, inventory transactions, etc. Reports like Received But Not
Invoiced, DMR Pending, and Inspection Pending must be ran at the end of the
month after everyone has left for the day to match the GL.

Karen Brodniak
Accounting Manager
425-742-7011 X44
Fax 425-353-8945
karen.brodniak@...
-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On Behalf Of
Mark Wonsil
Sent: Monday, November 07, 2005 2:54 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Improved method for reconciling accounts.


> ... If you have a better
> methodology please, p l e a s e , p l e e e a s e ... let
> us all in on it.

From experience with other systems, I have seen the issue of balancing
accounts with many transactions. More than one company decided to post to
the G/L weekly. This way, there are fewer transactions to wade through. In
one case, a company posted to the G/L DAILY. Posting more often makes the
reconciliation easier because the transactions are still fresh in user's
minds.

Mark W.



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links



----------------------------------------------------------------------------
--
YAHOO! GROUPS LINKS

a.. Visit your group "vantage" on the web.

b.. To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service.


----------------------------------------------------------------------------
--



[Non-text portions of this message have been removed]




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Links
A recurring discussion in this forum is: How do I reconcile a
Vantage GL Account. Having just spent several days ignoring my real
job I think that I have an improvement over "run the Inventory/WIP
Reconciliation Report then find the problem". This method works OK
when a single transaction has gone haywire, it's a case of spot the
dollar value in the report and start tracking it down. But what if
two transactions are causing the problem? You are down to manually
matching debits & credits.
What if you have hundreds of problems and tens of thousands of
transactions?

What if it is that black box, the AP Clearing account???

Following a change in personnel, our AP Clearing Account recently
took a dive into improbable figures. It has never reconciled with
the Goods Received Not Invoiced Report but somehow stayed sort of
believable till now – 9months since go live. If you have a better
methodology please, p l e a s e , p l e e e a s e Â….. let
us all in on it.

Steps, using AP Clearing as an example:

1. Run the GL Report for AP Clearing, set it to all periods in
the current year, print to Preview.
2. Use Excel to open the Detail.DBF file in your temporary
Vantage folder (c:\epicor\mfgwrk in our case)
3. As AP Clearing is a very volatile account, I repeated step 1
for the prior year to take us back to inception and join the two
data sets. If you choose not to get all the GL data you will need
to separately reconcile the goods received not invoiced at your
start date.
4. In Excel sort the data by description. Locate all the
Periodic Posting Process transactions & delete them
5. In Vantage run the Inventory/WIP Reconciliation report for
the AP Clearing account for the same date range as used for your GL
Reports. GL Posting Status = Only Posted to GL. Print to Preview
6. Open the RPTxxxxx.txt file in the temporary vantage folder
with Excel.
7. Choose the fixed width option then drag the field breaks
about so as you get a reasonable data import.
8. Sort the data in descending date order. All the page
headings and other dross will float to the top of the page where it
can be quickly deleted.
9. You may wish to spend some time re-arranging the columns in
preparation for joining of the GL data.
10. Paste the GL data into the Reconciliation sheet, ensuring
that the debit & credit columns align.
11. Add a new column containing the formula: debit - credit.
Title it the Non-Accountants Column or maybe 21st Century Accounting
column. (As an engineer I find it odd that accounting packages
still mimic the arcane practices of bookkeepers in the middle
ages). Add a second column; call it Absolute, containing the
formula: debit + credit.
12. Select the entire data area including your new columns and
generate a pivot table with the Absolute value as a row and the Sum
of the Non-Accountants column as data.
13. In a balanced account, all the totals in the pivot table
should be zero. If you double click on any non zero total you get a
new sheet containing the transactions for that value, one or more of
which will be the culprit.

This basic method should work with other accounts. In the case of
AR Clearing you don't need to join the GL data, all transactions are
in the Inventory/WIP reconciliation.

The above is really not ideal but better than anything else I have
tried. Can anyone suggest any improvements? For example:

1. What we really need is a report that states what
transactions have not cleared the account. As far as I can tell,
Vantage does not tag transactions in the general ledger to enable
this to happen.
Under my method we have 72 transactions at $32, 2 of which are
unbalanced. We still have to manually match to find them.
2. Does any one know which tables hold the WIP reconciliation
and GL data? It would be more convenient to get these via an ODBC
Query.

How did we fare? 14,184 transactions, 1,404 out of balance. About
250 of these are valid received, not invoiced. It was only random
chance that AR Clearing didn't skew off earlier. Someone is in for
a busy month cleaning this up.

Maybe we should run a competition for the biggest mess in AP
Clearing. Can anyone beat our 8% error rate?
I should have mentioned that the method below is for V6.1 & Crystal
Reports


--- In vantage@yahoogroups.com, "bakerprovan" <bfindlay@t...> wrote:
>
> A recurring discussion in this forum is: How do I reconcile a
> Vantage GL Account. Having just spent several days ignoring my
real
> job I think that I have an improvement over "run the Inventory/WIP
> Reconciliation Report then find the problem". This method works
OK
> when a single transaction has gone haywire, it's a case of spot
the
> dollar value in the report and start tracking it down. But what
if
> two transactions are causing the problem? You are down to
manually
> matching debits & credits.
> What if you have hundreds of problems and tens of thousands of
> transactions?
>
> What if it is that black box, the AP Clearing account???
>
> Following a change in personnel, our AP Clearing Account recently
> took a dive into improbable figures. It has never reconciled with
> the Goods Received Not Invoiced Report but somehow stayed sort of
> believable till now – 9months since go live. If you have a better
> methodology please, p l e a s e , p l e e e a s e Â…..
let
> us all in on it.
>
> Steps, using AP Clearing as an example:
>
> 1. Run the GL Report for AP Clearing, set it to all periods in
> the current year, print to Preview.
> 2. Use Excel to open the Detail.DBF file in your temporary
> Vantage folder (c:\epicor\mfgwrk in our case)
> 3. As AP Clearing is a very volatile account, I repeated step 1
> for the prior year to take us back to inception and join the two
> data sets. If you choose not to get all the GL data you will need
> to separately reconcile the goods received not invoiced at your
> start date.
> 4. In Excel sort the data by description. Locate all the
> Periodic Posting Process transactions & delete them
> 5. In Vantage run the Inventory/WIP Reconciliation report for
> the AP Clearing account for the same date range as used for your
GL
> Reports. GL Posting Status = Only Posted to GL. Print to Preview
> 6. Open the RPTxxxxx.txt file in the temporary vantage folder
> with Excel.
> 7. Choose the fixed width option then drag the field breaks
> about so as you get a reasonable data import.
> 8. Sort the data in descending date order. All the page
> headings and other dross will float to the top of the page where
it
> can be quickly deleted.
> 9. You may wish to spend some time re-arranging the columns in
> preparation for joining of the GL data.
> 10. Paste the GL data into the Reconciliation sheet, ensuring
> that the debit & credit columns align.
> 11. Add a new column containing the formula: debit - credit.
> Title it the Non-Accountants Column or maybe 21st Century
Accounting
> column. (As an engineer I find it odd that accounting packages
> still mimic the arcane practices of bookkeepers in the middle
> ages). Add a second column; call it Absolute, containing the
> formula: debit + credit.
> 12. Select the entire data area including your new columns and
> generate a pivot table with the Absolute value as a row and the
Sum
> of the Non-Accountants column as data.
> 13. In a balanced account, all the totals in the pivot table
> should be zero. If you double click on any non zero total you get
a
> new sheet containing the transactions for that value, one or more
of
> which will be the culprit.
>
> This basic method should work with other accounts. In the case of
> AR Clearing you don't need to join the GL data, all transactions
are
> in the Inventory/WIP reconciliation.
>
> The above is really not ideal but better than anything else I have
> tried. Can anyone suggest any improvements? For example:
>
> 1. What we really need is a report that states what
> transactions have not cleared the account. As far as I can tell,
> Vantage does not tag transactions in the general ledger to enable
> this to happen.
> Under my method we have 72 transactions at $32, 2 of which are
> unbalanced. We still have to manually match to find them.
> 2. Does any one know which tables hold the WIP reconciliation
> and GL data? It would be more convenient to get these via an ODBC
> Query.
>
> How did we fare? 14,184 transactions, 1,404 out of balance. About
> 250 of these are valid received, not invoiced. It was only random
> chance that AR Clearing didn't skew off earlier. Someone is in
for
> a busy month cleaning this up.
>
> Maybe we should run a competition for the biggest mess in AP
> Clearing. Can anyone beat our 8% error rate?
>
We have a report called po lines received but not invoiced. This report
should match gl account unvouched accts payable. Usually if there is a
difference it is a date issue. The accounts payable clerk will not
enter invoices past our closing date. She will wait until we close
completely for that month. This usually takes two days. Our report and
gl account unvouched accts payable usually equals or off cents.











bakerprovan wrote:

> I should have mentioned that the method below is for V6.1 & Crystal
> Reports
>
>
> --- In vantage@yahoogroups.com, "bakerprovan" <bfindlay@t...> wrote:
> >
> > A recurring discussion in this forum is: How do I reconcile a
> > Vantage GL Account. Having just spent several days ignoring my
> real
> > job I think that I have an improvement over "run the Inventory/WIP
> > Reconciliation Report then find the problem". This method works
> OK
> > when a single transaction has gone haywire, it's a case of spot
> the
> > dollar value in the report and start tracking it down. But what
> if
> > two transactions are causing the problem? You are down to
> manually
> > matching debits & credits.
> > What if you have hundreds of problems and tens of thousands of
> > transactions?
> >
> > What if it is that black box, the AP Clearing account???
> >
> > Following a change in personnel, our AP Clearing Account recently
> > took a dive into improbable figures. It has never reconciled with
> > the Goods Received Not Invoiced Report but somehow stayed sort of
> > believable till now - 9months since go live. If you have a better
> > methodology please, p l e a s e , p l e e e a s e .....
> let
> > us all in on it.
> >
> > Steps, using AP Clearing as an example:
> >
> > 1. Run the GL Report for AP Clearing, set it to all periods in
> > the current year, print to Preview.
> > 2. Use Excel to open the Detail.DBF file in your temporary
> > Vantage folder (c:\epicor\mfgwrk in our case)
> > 3. As AP Clearing is a very volatile account, I repeated step 1
> > for the prior year to take us back to inception and join the two
> > data sets. If you choose not to get all the GL data you will need
> > to separately reconcile the goods received not invoiced at your
> > start date.
> > 4. In Excel sort the data by description. Locate all the
> > Periodic Posting Process transactions & delete them
> > 5. In Vantage run the Inventory/WIP Reconciliation report for
> > the AP Clearing account for the same date range as used for your
> GL
> > Reports. GL Posting Status = Only Posted to GL. Print to Preview
> > 6. Open the RPTxxxxx.txt file in the temporary vantage folder
> > with Excel.
> > 7. Choose the fixed width option then drag the field breaks
> > about so as you get a reasonable data import.
> > 8. Sort the data in descending date order. All the page
> > headings and other dross will float to the top of the page where
> it
> > can be quickly deleted.
> > 9. You may wish to spend some time re-arranging the columns in
> > preparation for joining of the GL data.
> > 10. Paste the GL data into the Reconciliation sheet, ensuring
> > that the debit & credit columns align.
> > 11. Add a new column containing the formula: debit - credit.
> > Title it the Non-Accountants Column or maybe 21st Century
> Accounting
> > column. (As an engineer I find it odd that accounting packages
> > still mimic the arcane practices of bookkeepers in the middle
> > ages). Add a second column; call it Absolute, containing the
> > formula: debit + credit.
> > 12. Select the entire data area including your new columns and
> > generate a pivot table with the Absolute value as a row and the
> Sum
> > of the Non-Accountants column as data.
> > 13. In a balanced account, all the totals in the pivot table
> > should be zero. If you double click on any non zero total you get
> a
> > new sheet containing the transactions for that value, one or more
> of
> > which will be the culprit.
> >
> > This basic method should work with other accounts. In the case of
> > AR Clearing you don't need to join the GL data, all transactions
> are
> > in the Inventory/WIP reconciliation.
> >
> > The above is really not ideal but better than anything else I have
> > tried. Can anyone suggest any improvements? For example:
> >
> > 1. What we really need is a report that states what
> > transactions have not cleared the account. As far as I can tell,
> > Vantage does not tag transactions in the general ledger to enable
> > this to happen.
> > Under my method we have 72 transactions at $32, 2 of which are
> > unbalanced. We still have to manually match to find them.
> > 2. Does any one know which tables hold the WIP reconciliation
> > and GL data? It would be more convenient to get these via an ODBC
> > Query.
> >
> > How did we fare? 14,184 transactions, 1,404 out of balance. About
> > 250 of these are valid received, not invoiced. It was only random
> > chance that AR Clearing didn't skew off earlier. Someone is in
> for
> > a busy month cleaning this up.
> >
> > Maybe we should run a competition for the biggest mess in AP
> > Clearing. Can anyone beat our 8% error rate?
> >
>
>
>
>
>
>
> Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
> have already linked your email address to a yahoo id to enable access. )
> (1) To access the Files Section of our Yahoo!Group for Report Builder
> and Crystal Reports and other 'goodies', please goto:
> http://groups.yahoo.com/group/vantage/files/.
> (2) To search through old msg's goto:
> http://groups.yahoo.com/group/vantage/messages
> (3) To view links to Vendors that provide Vantage services goto:
> http://groups.yahoo.com/group/vantage/links
>
>
> ------------------------------------------------------------------------
> YAHOO! GROUPS LINKS
>
> * Visit your group "vantage
> <http://groups.yahoo.com/group/vantage>" on the web.
>
> * To unsubscribe from this group, send an email to:
> vantage-unsubscribe@yahoogroups.com
> <mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/>.
>
>
> ------------------------------------------------------------------------
>
>
>
> __________ NOD32 1.1200 (20050823) Information __________
>
> This message was checked by NOD32 antivirus system.
> http://www.eset.com




[Non-text portions of this message have been removed]
> ... If you have a better
> methodology please, p l e a s e , p l e e e a s e ... let
> us all in on it.

From experience with other systems, I have seen the issue of balancing
accounts with many transactions. More than one company decided to post to
the G/L weekly. This way, there are fewer transactions to wade through. In
one case, a company posted to the G/L DAILY. Posting more often makes the
reconciliation easier because the transactions are still fresh in user's
minds.

Mark W.
We prefer to capture and post everything for the month on the first day of
the following month. The reason is that Vantage will correct your cost of
sales and get rid of any purges if anything is posted or corrected on the
job after shipment, as long as you have not captured and posted. My reports
almost always match the GL because I do not allow anyone to back date parts
received, inventory transactions, etc. Reports like Received But Not
Invoiced, DMR Pending, and Inspection Pending must be ran at the end of the
month after everyone has left for the day to match the GL.

Karen Brodniak
Accounting Manager
425-742-7011 X44
Fax 425-353-8945
karen.brodniak@...
-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On Behalf Of
Mark Wonsil
Sent: Monday, November 07, 2005 2:54 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Improved method for reconciling accounts.


> ... If you have a better
> methodology please, p l e a s e , p l e e e a s e ... let
> us all in on it.

From experience with other systems, I have seen the issue of balancing
accounts with many transactions. More than one company decided to post to
the G/L weekly. This way, there are fewer transactions to wade through. In
one case, a company posted to the G/L DAILY. Posting more often makes the
reconciliation easier because the transactions are still fresh in user's
minds.

Mark W.



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links



----------------------------------------------------------------------------
--
YAHOO! GROUPS LINKS

a.. Visit your group "vantage" on the web.

b.. To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service.


----------------------------------------------------------------------------
--



[Non-text portions of this message have been removed]