Get documents about "Sample Bank Account Cancellation Letter Back Office Reconciliation
Description
Sample Bank Account Cancellation Letter document sample
Document Sample
Back Office Reconciliation
Presenter: Tom Clark
April 9th, 2009, 3:00 PM EST
Please remember to call in to this webinar at:
(712) 338-7030, Access Code 459-607-764
Stand by, as we will begin in just a few moments…
Using the Webinar technology
Q&A
This box will allow you to forward questions
to a panelist to be answered. Any
Questions that we don‟t get to in the time of
this Webinar will collected and addressed
by close of day, Friday, April 10th.
Polling
This box will allow you to
participate in a set of Polls
throughout the Webinar.
Future Webinars
Folio Reconciliation
May 18st
1:00 MDST
Back Office Reconciliation – Why?
Business owners REQUIRE accurate information
Outside auditors REQUIRE good detail
Balance Sheet accounts must be SUBSTANTIATED to
match business data to outside world
Reconciled accounts provide TANGIBLE evidence of
business performance.
Reconciled accounts can be VERIFIED
To maintain CONSISTENCY, all accounting systems
must be reconciled regularly.
Today’s Agenda
•What does it mean to be “Out of Balance”
•The Balancing Reports:
PRO, ERD, TRD, CRD and Clearing Accounts
How to research and find the “Out of Balance”
•When and how Journal Entries can be a good thing
•Bank Reconciliation
Trust Accounting Rules
Rules to trust like gravity
All Folio related transactions must pass through the advance deposit
account and be associated with a folio.
All Owner related transactions must pass through the Due to/from
Owners account and be associated with a property.
All Property Management Accounts Payable transactions must pass
through the Property Management Accounts Payable Account.
1. Cost Codes with vendor codes must credit PM A/P.
2. Cost Codes that credit PM A/P must have a vendor code.
All Clearing Accounts should have a zero balance at the end of the
month. (exception: owner transit account )
Journal Entries should not be posted to Advance Deposits, Due
to/from Owners or one of the Accounts Payable accounts.
Back Office Reconciliation – Why?
Q: So what does it really mean when I am out of balance?
A: When you are out of balance, it means that the detail of the transactions
(i.e. the GL entries) don’t match the posted transaction!
Or you may have broken one of the fundamental rules we just looked at.
To better understand what that means, lets take a minute to look at a
transaction in Avmain and see what it is made up of!
What does it mean when I am out of balance?
Costcodes control how the accounting functions
Costcode :*CT :
Description :Acct'g $/Mo. :
Audit|Mgr Code
GL Debit Account 1 : 3000: :0: : 0: Due To Owners
GL Credit Account 1 : 2300: :N: : 0: Accounts Payable
GL Debit Account 2 : 0: :N: : 0:
GL Credit Account 2 : 0: :N: : 0:
AP Vendor Code :TCPMI: Basalt Property Mgt.
Cost Code Type (A,E,M,O,T) :A: Automatic Costcode
Statement Sort Code :G: Expense Area on Owners
Annual Stats Code (0-9) :0: MISCELLANEOUS EXPENSE
DB(1), CR(-1) to Owner,Tenant: 1: Long-Term Application : 0:
Invoice Replace Field : 0:
TA %, Rate $, Markup % : 0.0000: Prededuct :Y:
Authorized for Front Desk :N: TA, Rate, or Markup Code : :
Allow posting to Properties :Y: Allow posting to Folios :N:
Exclude from Revenue :N:
What does it mean when I am out of balance?
Expenses to Tenants or Properties
Enter Property Code :A1000:
Tenant Name : : #: 0:
Enter Costcode :*CT :
Enter Inv/Ref Number :ASDF :
Enter Description L1 :Acct'g $/Mo. :
Enter Description L2 : :
Enter Date of Sale :08/31/08:
Enter Pymt Due Date : / / :
Enter Amount in $ : 5.00:
Date Entered :08/31/08: Stmt :08/08:
Transaction Number : 4205:
AP Vendor :TCPMI: Acct : 3000: $ : 5.00:
Paid on : / / : Acct : 2300: $ : -5.00:
GL Date :08/31/08:
The PMX records are the “Posted Transactions” you see on a folio, or at the
owner‟s S-D. The Pairs of SYX records are the portion of the transactions that are
posted to the General Ledger Accounts (i.e. show up in the Posting Journal).
What does it mean when I am out of balance?
Delete Byte : 0: Record Number : 672800:
Posting Flag :P: Record Type :A: TA Byte : 84:
Vendor Code :TCPMI: Cost Code :*CT :
PMX First Distr Record #:
Description
286967:
:Acct'g $/Mo.
Paid Distr Record # :
:
:
Change Date :10471: 08/31/08 Anncode :0:
Invoice :9901 : : :
Amount : 5.00: Owner : 0: HistSeq : 0:
Folio : 0: Property :QR509:
PM File Date : 10471: 08/31/08 Rev Type : :
Sale Date : 10471: 08/31/08 Nights : 0:
Delete Byte : 0: Record Number : 286967:
Account Number : 3000:
Record Type :D:
SYX1 GL Date
Amount
: 10471: 08/31/08
: 5.00: :
X File Record # : 672800:
Next Distr Record # : 286936:
Delete Byte : 0: Record Number : 286936:
Account Number : 2300:
Record Type :X:
GL Date : 10471: 08/31/08
SYX2 Amount
X File Record #
:
:
-5.00: :
672800:
Next Distr Record # : 0:
Above is the PMX/SYX transactions, viewed from diagnostics
What does it mean when I am out of balance?
What is means is that some “ingredient” is missing when
compared to the transactions posted to that specific account.
So our journey today will take an in depth look at what the
details are for each of the back office areas of Avmain and
what to do when your Ledger doesn’t balance with detail.
“Ledger Balances With Detail” = GOOD!
“Ledger out of balance with detail by” = BAD *
* With one exception we will look at later!
“Ledger Balances with Detail”
First Resort is a self balancing double entry accounting program.
With cost codes, folios, owner statements and payable invoices a
record of the transaction is created that matches the posting on
the general ledger.
Reconciled accounts are matched to a Monthly Report:
Owner Balances - P-R-O Due to Owners Report
Property Vendor A/P - E-R-D PM Payables Due Report
T/A Accounts Payable - T-R-D T/A Payables Due Report
Business Accounts Payable - C-R-D Vendor Payables Due Report
Clearing Accounts - G-R-P Posting Journal
Sales Taxes - RF# 2 Posting Journal + Audit Report
Cash - G-R-P Bank Reconciliation Report
Owner Balances & Percentages Report (P-R-O)
What causes Out of Balances with the PRO in FRS?
1. Consistency errors: Within FRS if you have an inadvertent shut down of your software caused by
the PC/Network freezing up, power outages, etc, transactions can be miss-posted or other kinds of
problems. For your GL to be in balance the posted transactions must always have accounting pair
(SYX) transactions with it.
2. Non Property Management Transactions: If you post a transaction to Business Accounts Payable
or Receivable and the second half of the transaction is posted to the Due to/From Owners accounts.
3. Folio Transactions: If you post a transaction to a folios and the second half of the transaction is
posted to the Due to/From Owners accounts (other than automatically posted, hard coded *xx
costcodes)
4. General Ledger Entries: Any Journal Entries, Cash Receipt or Check Disbursement will cause an
problem.
Numbers 2-3-4 are prime examples of what happens when you break the
“Rules to accept like Gravity:
All Owner related transactions must pass through the Due to/from
Owners account and must be associated with a property.
Owner Balances & Percentages Report (P-R-O)
Date
List all
Balances
Owner Balances & Percentages Report (P-R-O)
Reconciling the PRO report
A. Print the Owner Balance Report (PRO) using A for all balances.
B. On the summary page, does it say “Ledger balances with detail”?
C. If “Yes”, go on to the next account to reconcile.
D. If “No”, locate and fix discrepancies using the following techniques:
1. Confirm that this is the first month the report goes out of balance by running the report for the
previous month. Continue go in back in time till you find the first month it goes out of balance.
2. If the Chart of Accounts contains only Balance Sheet accounts for the rental/escrow accounts:
a. Print a posting journal of the Due to Owners Accounts, #______, for the month you are trying to
balance and look for any entries that were made through modules other than Property
Management (letter other than „P‟ showing under „Srce‟).
b. Also in the posting journal, locate any Tenant charges that incorrectly appear in Due to
Owners. These transactions are identified by a folio number appearing before the description
instead of a property code appearing before the description.
3. Run a Consistency Report (HK) to see if there are linkage errors causing the out of balance.
4. Run a copy of the posting journal to a text file. Open the text file in Excel and sort the transactions in
property code order. Compare the posting journal to the List of Property Transactions (PRE) one line
version report. Locate any entries that appear on one of the reports and not the other. Also locate
transactions where the value differs between the reports.
E. Reprint the Owner Balance Report (PRO).
Reconciling the PRO report – Step 1
It is very important to keep running the report back in time to find out
where the account first went out of balance. This is critical for you as it
tells you when to look for the out of balance entries. Making a log of the
changes and reviewing the month to month differences allows you to
focus on the value of the entries that may be causing the errors.
Reconciling the PRO report – Step 2
09/03/08 16:28:21 TClark Property Management Inc V10.25
Posting Journal for Account 3000 for 01/01/08 to 08/31/08 a. Source Code
Other than “P”
Trandate Srce Cust Description Ref Amount
-------- ---- ----- ------------------------------ ----- ---------
ACCOUNT 3000 Due To Owners OPENING BALANCE = 0.00 The Fix:
08/15/08 G Fix PRO Balance per CPA JE 6 525.00
Post an off
setting entry
JOURNAL ENTRIES = 525.00
opposite of
08/30/98 P TCPMI F 1594, ,CCod w/ Bad Acctng -100.00 this one.
08/30/98 P A1400 1, 6,Payment From Owner -150.00
09/03/08 16:28:21 TClark Property Management Inc V10.25
b. Tenant Charges Posting Journal for Account 3000 for 01/01/08 to 08/31/08
Posted to Due to
Trandate Srce Cust Description Ref Amount
Owner account -------- ---- ----- ------------------------------ ----- ---------
ACCOUNT 3000 Due To Owners OPENING BALANCE = 0.00
The Fix: Reverse
08/15/08 G Fix PRO Balance per CPA JE 6 525.00
process the folio.
Adjust the cost JOURNAL ENTRIES = 525.00
code & reprocess 08/30/98 P TCPMI F 1594, ,CCod w/ Bad Acctng -100.00
the folio 08/30/98 P A1400 1, 6,Payment From Owner -150.00
Reconciling the PRO report – Step 3
Transactions posted to an Owner but the transaction
is NOT hitting the General Ledger
What is Missing ?
The posting to
the Accounts
Payable
Account
The structural relationship within the transaction has been lost. This must
be recreated.
Reconciling the PRO report – Step 3
Transactions posted to an Owner but the transaction
is NOT hitting the General Ledger
The Fix: Run the “CONSISTENCY CHECK” report (H – K)
Reconciling the PRO report – Step 3
The Fix: Get the results of the report (H – K) and then Call
Instant Software First Resort Support to discuss.
Reconciling the PRO report – Step 4
The last procedure to follow if your PRO is still out of balance is to compare the
Posting Journal for the Owners Account vs. the List of Property Transactions (P-
R-E) report. To do this, one method is to:
•Run a copy of the posting journal to a text file.
•Open the text file in Excel and sort the transactions in property code order.
•Run the PRE report like this:
Print or Display a List of Property Transactions
For a Month You Specify
Enter Month/Year (MM/YY) :08/09:
Enter 1 for one line version :1:
Print a Single Property per Page :N:
Specify complexes: Blank for all : :
Enter DISPLAY, PRINT, VIEW, FILE, EMAIL or CANC:P:
Compare the each units transactions found on the posting journal to each units
transactions on the List of Property Transactions (PRE) report and:
•Locate any entries that appear on one of the reports and not the other.
•Locate transactions where the value differs between the reports.
Reconciling the ERD report
What are some of the causes Out of Balances with the A/P in FRS?
1. Consistency errors: Within FRS if you have an inadvertent shut down of your software caused by
the PC freezing up, power outages, etc, transactions can be miss-posted or other kinds of problems.
For your GL to be in balance the posted transactions must always have accounting pair (SYX)
transactions with it. Run just like we demonstrated earlier
2. The *AP costcode: If you close the month first, and then cut Owner Checks from a final balance, the
first half of the *AP transactions (Debit Due To/From Owners, Credit PM A/P) happens in the closed
month, and the second half of the transaction (Debit PM A/P Credit Cash) occurs with the date you
cut the check. That is why on the bottom of the ERD report it always says: "Difference should match
the Owner Check Total for this month".
3. Hand-checks: If you show transactions paid by Hand-checks, but fail to post the matching General
Ledger Disbursement (G-E-D).
4. Costcode setup: Costcodes that are setup as Expense costcode (including many of the *xx ones
too) must always credit 2300 and have a vendor code. (Remember the Rules to Accept Like
Gravity?). The *AP costcode (see #2) is unique in that it the only costcode in FRS that is allowed to
post to the 2300 account without a vendor.
5. Prepayment of Transactions: When you pay a vendor whose transaction is dated in a future month.
6. Journal Entries: Any Journal entries will cause an OOB because they are not posted to a vendor.
7. General Ledger Entries: General Ledger Receipts (G-E-R) can cause an OOB if they are not paired
with a Credit to a Vendor that has been shown as paid by hand-check.
Reconciling the ERD report
A. Print the Property Management Accounts Payable Due Report (ERD).
B. Does the end of the report state “Ledger balances with detail”?
C. If “Yes”, go on to the next account to reconcile.
D. If “No”, does the difference match the owner check total for this month?
E. If the difference is NOT the owner check total (from #D above), then locate and fix discrepancies using
the following techniques:
1. Confirm that this is the first month the report goes out of balance by running the report for the
previous month. Continue go in back in time till you find the first month it goes out of balance.
2. Print a posting journal for Property Management Accounts Payable accounts, #______, for the
month you are trying to balance and look for any entries that were made through modules other
than Property Management (letter other than „P‟ showing under „Srce‟). A „G‟ under „Srce‟ is
correct if a hand check was issued for an invoice and that invoice does not appear on the due
report anymore. Verify hand checks by running the Payables Paid by Hand Check Report: ERH
3. Locate any AP invoices that have been paid with a hand check that still show on the AP Due
report. Verify hand checks by running the Payables Paid by Hand Check Report: ERH
4. Locate any hand checks that debit AP for which there is no invoice in the AP system or the
invoice was not flagged as “paid by hand-check”.
5. Locate any entries that do not have a vendor code under the „Cust‟ column. Automatic payments
to Owner (*AP) should be the only ones you see.
Reconciling the ERD report
E. If the difference is NOT the owner check total (from #D above), then locate and fix discrepancies using
the following techniques (Continued):
6. Do a review of your Cost Code list @ P-T-C. Look for:
a. Any entries that hit your A/P account but do not have a vendor. Typically the only cost-code this
is okay for is the *AP.
b. Any cost-codes that have a vendor but do not hit the A/P account.
7. Run a Consistency Report (HK) to see if there are linkage errors causing the out of balance
F. Reprint the Property Management Accounts Payable Due Report (ERD).
Reconciling the ERD report– Step A
DATE
Use “Y” when doing reconciliation
Reconciling the ERD report– Step B
Reconciling the ERD report - Step D *AP
“Difference should equal the owner check total the month”
What does this mean?
08/31/08 First Resort Software V10.25
Property Management Accounts Payable Due Report as of 08/31/08
Saledate Prop/Tnt Invoice # Description Tran # Amount Due
Totals Current 30 Days 60 Days 90 Days Total Due
2614.65 75.03 368.10 2263293.35 2266351.13
Balance per GL in Account 2300 -2275216.12
Ledger out of balance with detail by 8864.99.
Difference should equal the Owner Check Total for this month
Reconciling the ERD report - Step D *AP
First Resort processes Payments to Owner transactions (*AP)
through the Accounts Payable account.
The first part of the *AP transaction is posted when Owner Checks
are Posted. It is dated the last day of the closed month.
The second part of the *AP transaction is posted when Owner
Checks are Posted. It is dated the day the check is cut.
Reconciling the ERD report - Step D *AP
Enter Property Code :A1200:
Enter Owner Number : 1:
Enter Costcode :*AP :
Enter Inv/Ref Number :0808 :
Enter Description L1 :Payment To Owner :
Enter Description L2 : :
Enter Date of Payment :08/31/08:
PMX Enter Amount in $
Date Entered
: 80.00:
:09/12/08: Stmt :08/08:
Transaction Number : 13119:
AP Vendor : : Acct : 3000: $ : 80.80: Acct : 2300:
Paid on :09/12/08: Acct : 2300: $ : -80.00: Acct : 1000:
Check Number : 6903:
GL Date :08/31/08:
Delete Byte : 0: Record Number : 646412:
Account Number : 3000:
Record Type :D:
GL Date : 10471: 08/31/08
SYX1 Amount
X File Record #
:
:
80.00: :
2099040:
Next Distr Record # : 286936:
Delete Byte : 0: Record Number : 646474:
Account Number : 1000:
Record Type :P:
GL Date : 10483: 09/12/08
SYX3 Amount
X File Record #
:
:
-80.00: :
2099040:
Next Distr Record # : 646443:
Reconciling the ERD report - Audit the *AP
1. Set the Audit
2. Run the Audit Report
(R-F) for the Last day of
the month.
COSTCODE *AP Payment To Owner ACCOUNT 3000 Due To Owners
08/31/08 P Payment To Owner 12443 A1000 1530.84
08/31/08 P
08/31/08 P
Payment To Owner
Payment To Owner
12444 A1200
12445 A1600
220.00
475.00
3. Remember to
08/31/08 P
08/31/08 P
Payment To Owner
Payment To Owner
12446 FIFTY
12447 HOTTB
611.01
135.00
subtract off any
08/31/08 P
08/31/08 P
Payment To Owner
Payment To Owner
12448 THREE
12449 THREE
60.00
60.00
*AP Voided
08/31/08 P
08/31/08 P
Payment To Owner
CHECK #5000 VOIDED
12450 THREE
12443 A1000
60.00
-1530.84
Transactions
COST CODE *AP Payment To Owner SUBTOTAL 1621.01
Reconciling the ERD report - Step D *AP
Trandate Src Description Fol/Prop Amount
COSTCODE *AP Payment To Owner ACCOUNT 3000 Due To Owners RF#5 Audit
08/31/07 P Payment To Owner 12443 A1000 1530.84
08/31/07 P Payment To Owner 12444 A1200 220.00
08/31/07 P Payment To Owner 12445 A1600 475.00 You are in
08/31/07 P Payment To Owner 12446 FIFTY 611.01
08/31/07 P Payment To Owner 12447 HOTTB 135.00
balance if
08/31/07 P Payment To Owner 12448 THREE 60.00 these two
08/31/07 P Payment To Owner 12449 THREE 60.00
08/31/07 P Payment To Owner 12450 THREE 60.00 values are
08/31/07 P CHECK #5000 VOIDED 12443 A1000 -1530.84
COST CODE *AP Payment To Owner SUBTOTAL 1621.01
equal.
TOTAL 3151.85
08/31/08 First Resort Software V10.25
Property Management Accounts Payable Due Report as of 08/31/08
Saledate Prop/Tnt Invoice # Description Tran # Amount Due
Totals Current 30 Days 60 Days 90 Days Total Due
2614.65 75.03 368.10 2263293.35 2266351.13
Balance per GL in Account 2300 -2275216.12
Ledger out of balance with detail by 3151.85
Reconciling the ERD report – Step E1
Run the ERD Report back in time to find out where the account first went
out of balance. Log this information along with the *AP values until you
find the first month that the Difference goes out of balance.
Reconciling the ERD report - Printed Checks
Note the two pairs of SYX transactions:
Reconciling the ERD report - Hand Checks
Select specific
invoice(s) for
payment.
Mark this invoice
“H” as paid by
Hand Check
Enter the date of
payment and the
Hand Check #
Enter Y to confirm
the date
Reconciling the ERD report - Hand Checks
Invoice is
marked as paid
by Hand Check
What‟s the most common A/P Problem?
Reconciling the ERD report - Hand Checks
Showing the invoice as paid
by hand check does not
disburse funds.
The Fix: You also need
to enter the hand check
information @ G-E-D!
What account number occupies:
“Acct :1: 0:” ???
What value, (-) or (+) ???
Reconciling the ERD report - Step E2 E-R-H
E-R-H
How do I know Please
what Hand Checks
I have written?
Reconciling the ERD report - Step E2 E-R-H
This report then shows you all the Invoices you have posted by choosing
the “H” to be paid by Hand Check option.
Reconciling the ERD report - Step E2-E3-E4
E–R-H vs. G–R–P
Reconciling the ERD report - Step E5-E6a&b
Srce = P Note the Vendor Codes *AP transactions (no Vendor code)
Reconciling the ERD report - Step E5-E6a&b
Expense Costcode
Missing the Vendor Payment Costcode w/
Code a Vendor Code
Reconciling the ERD report - Step E7
Other issues and ideas
Keep in mind that if you have a cost code that does not have a vendor code
(or that vendor code is marked “Deleted” @ E-A) then it will not show on the
E-R-D report!
Just like the PRO, the errors could be due to consistency issues, so you may
have to run the H-K Consistency Check.
Reconciling the TRD report
DATE
Use “Y” when doing reconciliation
Reconciling the TRD report
A. Print the Property Management Accounts Payable Due Report (T-R-D).
B. Does the end of the report state “Ledger balances with detail”?
C. If “Yes”, go on to the next account to reconcile.
D. If “No”, then locate and fix discrepancies using the following techniques:
1. Print a posting journal for Travel Agent Accounts Payable accounts, #______, for the month
you are trying to balance and look for any entries that were made through modules other than
Property Management (letter other than ‘P’ showing under ‘Srce’). A ‘G’ under ‘Srce’ is correct
if a hand check was issued for an invoice and that invoice does not appear on the due report
anymore.
2. Locate any AP invoices that have been paid with a hand check that still show on the AP Due
report. Verify hand checks by running the Paid by Hand Check Report (E-R-H).
3. Locate any hand checks that debit AP for which there is no invoice in the AP system or the
invoice was not flagged as “paid by hand-check”.
4. Locate any entries missing an IATA Number (Pxxxxxxxx) under the ‘Cust’ column.
5. Run a Consistency Report (H-K) to see if there are linkage errors causing the out of balance.
E. Reprint the Property Management Accounts Payable Due Report (T-R-D).
F. If a discrepancy still exists, consult with your Accountant as to the appropriate adjustment.
Reconciling the TRD report
Trandate Srce Cust Description Reference Amount
ACCOUNT 2400 Travel Agents Accounts Payable OPENING BALANCE = -105.00
07/31/07 P15000000 A1600 , 7,T.A. To Be Paid 07 1718 -100.00
12/31/07 P14000000 F 1735,12,FIFTY 1735 07/27 12 1735 -100.80
10/31/08 F 1732,10,A1000 1732 05/11 10 1732 -5.00
ACCRUALS = -205.80
NET CHANGE = -205.80
ACCOUNT 2400 Travel Agents Accounts Payable ENDING BALANCE = -310.80
Missing IATA Number!
Reconciling the CRD report
DATE
Use “Y” when doing reconciliation
Reconciling the CRD report
A. Print the Property Management Accounts Payable Due Report (C-R-D).
B. Does the end of the report state “Ledger balances with detail”?
C. If “Yes”, go on to the next account to reconcile.
D. If “No”, then locate and fix discrepancies using the following techniques:
1. Print a posting journal for Business Accounts Payable accounts, #______, for the month
you are trying to balance and look for any entries that were made through modules other
than Business A/P (letter other than ‘C’ showing under ‘Srce’). A ‘G’ under ‘Srce’ is correct
if a hand check was issued for an invoice and that invoice does not appear on the due
report anymore.
2. Locate any AP invoices that have been paid with a hand check that still show on the AP
Due report. Verify hand checks by running the Payables Paid by Hand Check Report (C-R-H).
3. Locate any hand checks that debit AP for which there is no invoice in the AP system or
the invoice was not flagged as “paid by hand-check”.
4. Locate any entries that do not have a vendor code under the ‘Cust’ column.
5. Run a Consistency Report (H-K) to see if there are linkage errors causing the out of
balance.
E. Reprint the Property Management Accounts Payable Due Report (C-R-D).
F. If a discrepancy still exists, consult with your Accountant as to the appropriate adjustment.
Reconciling the CRD report
Trandate Srce Cust Description Reference Amount
ACCOUNT 2350 Business Accounts Payables OPENING BALANCE = 0.00
10/20/08 C NLA CHECK AP # 2001 VOIDED CK 2001 100.00
11/23/08 C ASKIC 11854198-01 #8 TESTDUP 1443.00
11/23/08 C ASKIC 11854187-01 # 9 1443.00
12/30/08 C NLA 123 100.00
12/30/08 C TCPMI CHECK AP # 2003 VOIDED CK 2003 100.00
REVERSE ACCRUALS = 3186.00
10/29/08 P ASKIC W 1034, ,A1000 WO 1034 -200.00
DISBURSEMENTS = -200.00
Letter other than „C‟ showing under „Srce‟
Reconciling Clearing Accounts
Chart of Accounts PTC Costcode List
Assets CCode Description Debit Credit
1000 Cash *GD T.A. Prededuct 2200 2000
*GE T.A. To Be Paid 2200 2400
1100 Long Term Cash *GN T.A. To Be Paid 2200 2400
Liabilities *GP T.A. Prededuct 2200 2000
2000 Advance Deposits *GR T.A. Prededuct 2200 2000
*GZ T.A. To Be Paid 2200 2400
2100 Long Term Deposits PTO Payment To Owner 3000 2899
2200 Due to/from Operating REIMB Reimbursement To Owner 2899 3000
2300 Accounts Payable WOBIL Work Order Billing 3000 2799
WOINC Work Order Income 2799 2300
2400 Travel Agent Payable XFF+ Xfer From ADVDEP To Clear 2000 2899
2500 Sales Tax Payable XFF- Xfer From Clear to ADVDEP 2899 2000
2600 Security Deposits Held XFFL+ Xfer From LT ADVDEP To Clear 2100 2899
XFFL- Xfer From Clear to LT ADVDEP 2899 2100
2700 Last Month‟s Rent XFO+ Xfer From Owner to Clearing 3000 2899
2799 Work Order Transit XFO- Xfer From Clearing to ADVDEP 2899 2000
2899 Clearing for Transfers XS-EX Xfer from SecDep to Clear 2600 2899
2999 Owner Transit Account
Owner Equity
3000 Due to/from Owners
Reconciling Clearing Accounts - Sample
09/09/08 11:23:52 TClark Property Management Inc V10.25 Page 1
Posting Journal for Account 2899 for 08/01/09 to 08/31/09
Trandate Srce Cust Description Reference Amount
1. Posting Journal
-------- ---- ----- ------------------------------
ACCOUNT 2899 Tenant/Owner Clearing
---------- -----------
OPENING BALANCE = 0.00
for clearing shows
ACCOUNT 2899 Tenant/Owner Clearing
NET CHANGE =
ENDING BALANCE =
0.00
0.00
no activity.
Miscellaneous Income to Properties
Enter Property Code :A1000: Aspen Grove Condo #1100
Enter Costcode :REIMB:
Enter Inv/Ref Number : :
2. Owner is reimbursed Enter Description L1 :$$$ For Broken Lamp :
Enter Description L2 : :
for tenant damages. Enter Date of Payment :09/09/08:
Enter Amount in $ : 150.00:
Date Entered :09/09/08: Stmt :09/08:
Transaction Number : 1:
Acct : 2899: $ : 150.00:
Acct : 3000: $ : -150.00:
09/09/08 11:23:52 TClark Property Management Inc V10.25 Page 1
Posting Journal for Account 2899 for 08/01/09 to 08/31/09
Trandate Srce Cust Description
-------- ---- ----- ------------------------------
Reference Amount
---------- -----------
3. Balance
ACCOUNT 2899 Tenant/Owner Clearing
09/09/08 P
OPENING BALANCE =
A1000 , 9, $$$ For Broken Lamp
0.00
150.00
changed! What
DISBURSEMENTS =
NET CHANGE =
150.00
150.00
happened?
ACCOUNT 2899 Tenant/Owner Clearing ENDING BALANCE = 150.00
Reconciling Clearing Accounts - Sample
Payments to and from Tenants Lester Tester
Tenant Name
Enter Costcode
:
:XFF+ :
: #: 1733:
To Fix: Post the
Enter Inv/Ref Number :A1000 : Xfer From ADVDEP 2 Clear
Enter Description L1 :Payment for Lamp : “other side of the
Enter Description L2 :Tenant: Tester :
Enter Date of Payment :09/09/08:
Enter Amount in $ : 150.00:
equation”.
Date Entered :09/09/08:
Transaction Number : 1:
Acct : 2000: $ : 150.00:
Acct : 2899: $ : -150.00:
Posting Journal 09/09/08 11:23:52 TClark Property Management Inc V10.25 Page 1
now has the Posting Journal for Account 2899 for 08/01/09 to 08/31/09
Trandate Srce Cust Description Reference Amount
-------- ---- ----- ------------------------------ ---------- -----------
same balance ACCOUNT 2899 Tenant/Owner Clearing
09/09/08 P F 1733, ,Payment for Lamp
OPENING BALANCE =
A1000
0.00
-150.00
at the end of 09/09/08 P A1000 , 9, $$$ For Broken Lamp
RECEIPTS = -150.00
150.00
DISBURSEMENTS = 150.00
the month as at NET CHANGE = 0.00
ACCOUNT 2899 Tenant/Owner Clearing ENDING BALANCE = 0.00
the beginning.
Reconciling Clearing Accounts w/ Excel
A. Go to G-R-P and run the posting journal from the last time it was in balance
B. Open in Excel, the Text File we just created
1. Click on File, Open
2. Remember to change the Files of type: to Text Files.
3. Change the Look in: to the directory where their data lives.
4. Highlight the Text File we are going to analyze, and then click Open
C. Use the Import Wizard to organize data
1. Step 1 of 3 Click on “Fixed Width”
2. Step 2 of 3 Choosing line breaks:
D. Prepping the Excel Spreadsheet:
1. Data sort by date to remove page breaks
and fix other issues.
2. Enter the ABS function
3. Copy ABS to all amounts
4. Data sort by ABS value
E. Perform Data Analyses
1. Compare the amounts and look for pairs.
2. Locate amounts with no pairs.
Reconciling Clearing Accounts w/ Excel
Sample 2899
Account
Unmatched Pair
Unmatched Pair
Out of Balance
When Journal Entries Are A Good Thing
Bank Charges
What needs to
be done next?
Credit Card Fees
When Journal Entries Are A Good Thing
A deposit to the Cash Account must be made to reimburse
the expenditure of bank charges and credit card fees.
Bank Reconciliation – Use the Posting Journal
The Posting Journal for you bank account is one of your best sources of information for
double checking deposits and checks in transit.
One of the key items on the Posting Journal is making sure you show the invoice details
for checks in your bank account.
Change to “F”
Don‟t forget that sometimes sending the posting journal to a text file and opening it in
Excel is one of the quickest way to locate the items you are looking for. This is especially
true if your posting journal is longer that 2000 rows of data (roughly 35 viewed pages)
Bank Reconciliation – Use the Posting Journal
Trandate Srce Cust Description Reference Amount
-------- ---- ----- ------------------------------ ---------- -----------
ACCOUNT 1000 Cash bank Account # 25951444 OPENING BALANCE = 85293.78
09/01/08 P A1000 1,12,Payment from Owner 1858.00
09/04/08 P F 1736, ,Cash Payment A1400
RECEIPTS =
896.00
2754.00
Show invoice
09/12/08 P
09/12/08 P
TCINC MULTIPLE, Cleaning Fee - Owne
TCPMI MULTIPLE, Management Fee
CK
CK
6904
6905
-259.00
-567.69
detail set to
09/12/08 P TRAV MULTIPLE, Cancellation Insura CK 6906
DISBURSEMENTS =
-547.03
-1553.72
“N” = NO
NET CHANGE = -1951.57
ACCOUNT 1000 Cash bank Account # 25951444 ENDING BALANCE = 86494.06
Trandate Srce Cust Description Reference Amount
-------- ---- ----- ------------------------------ ---------- -----------
09/12/08 P TCINC F 1731, ,Cleaning CK 6904 -9.00
09/12/08 P TCINC F 1729, ,Cleaning CK 6904 -75.00
09/12/08 P TCINC F 1730, ,Cleaning CK 6904 -75.00
09/12/08 P TCINC F 1737, ,Cleaning CK 6904 -75.00
09/12/08 P TCINC A1200 , 1,Cleaning Fee - Owne CK 6904 -25.00
09/12/08 P
09/12/08 P
TCPMI F 1735, ,Vacationguard Insur
TCPMI F 1729, ,Reservation Fee
CK
CK
6905
6905
-102.04
-55.50
Show invoice
09/12/08 P
09/12/08 P
TCPMI F 1730, ,Reservation Fee
TCPMI F 1733, ,Cancellation Fee
CK
CK
6905
6905
-31.25
-25.00
detail set to
09/12/08 P
09/12/08 P
TCPMI F 1737, ,Reservation Fee
TCPMI F 1738, ,Reservation Fee
CK
CK
6905
6905
-200.00
-8.90
“Y” = YES
09/12/08 P TCPMI F 1734, 1,Package Income CK 6905 -100.00
09/12/08 P TCPMI A1200 , 1,Management Fee CK 6905 -45.00
09/12/08 P TRAV F 1726, ,Cancellation Insura CK 6906 -60.71
09/12/08 P TRAV F 1729, ,Cancellation Insura CK 6906 -103.45
09/12/08 P TRAV F 1730, ,Cancellation Insura CK 6906 -60.71
09/12/08 P TRAV F 1737, ,Cancellation Insura CK 6906 -322.16
Bank Reconciliation
• Bank “as of” statement date
• Un-cleared checks date: Must go back in time to capture all
(Please note that the farther back in time you go, the longer it takes to run!)
• Deposits in transit
• Yes for Query for outstanding checks (F- Fix cleared checks)
Bank Reconciliation
•The Deposits are correct if the Front Desk Audit Reports are balanced on a daily basis.
•Automatic checks are correct except for when you have duplicate check numbers
•Any hand checks must be recorded through a (G-E-D) General Ledger Distribution entry
•Bank charges, credit card fees, NSF checks, interest income must be recorded as journal
entries.
•Can reconcile any defined bank account in system setup:
•Query for Outstanding checks Y for F to fix checks cleared in error.
•Void checks are automatically cleared.
•Deposits in Transit – Cannot distinguish between credit card payments and check
deposits.
Bank Reconciliation - Check Cleared in Error
Reconciliation
• Reconcile reports monthly: Advance Deposits (RRA), Due to
Owners (PRO), Accounts Payable Due (ERD/CRD, TRD), Bank
Reconciliation (GRR)
• If you find an out of balance, first run the report for the
previous month to see if it’s in balance.
• If not, keep running back to find the first month goes out.
• More on Reconciliation in May (Folio Reconciliation)
Any Questions?
Thank you for attending today’s Webinar.
You will be emailed a link to a URL so you can download a copy
of this presentation shortly.
The next FRS Webinar will be on Monday, May 18th.
We hope you will plan join us!
