G/L Reconciliation Report Differences – Wrong Data Entry Dates

The G/L Reconciliation report is an invaluable tool in PCLaw to detect data inconsistencies and any corruption in the accounting data. This report is typically run at end month to spot any anomalies.

Any differences appearing on this report most likely points to data corruption. However there are cases when differences appearing are a result of data entry issues.

For firms on Accrual systems the following control accounts can get skewed due to wrong dates used at data entry:

·         Accounts Receivable (“AR”)

·         General Liabilities (“AP”)

For a mismatch in AR the cause at times can be current receipt dates used to receive payment for forward dated invoices. For example a user posts a receipt dated March against an invoice on a matter billed in April. This will definitely cause an imbalance in AR between G/L, Journal and Client balances.

When users exit a data entry screen that was used to process a backdated entry, the date stays when they re-open the same data entry window again (Receive Payment window in this case). They often times forget to change the date in the date box when opening the data entry window again to enter a new transaction.

In the case of AP a user may process a forward dated Payable using a cheque of current date. For Example: Payable is dated July and the cheque to settle the payable is dated June.

For AP Process Payables the issue is unlikely to be the date but what happens is at times Vendors send post dated invoices. For instance an ISP sends an advance invoice for internet use for the following month. Accounts Payable staff will enter the payable dated the following month and process payment in the current month. They do not realize that there is a difference in a) processing a current or old payable and b) making an advance payment to a Vendor for a future payable – there are steps for making advance payment to a Vendor.

With a little bit of vigilance above errors can be avoided.

Multi Currency - Billing

PCLaw is not a multi currency accounting program. However, it is possible to account and track multi currency transactions. I plan to discuss here in a series of blog posts about multi currency.

I begin with Billing. The hardest part of all foreign currency transactions is in billing. How do you bill clients in foreign currency and track the A/R balances?

There is a way to reverse engineer the bill templates such that you can have all charges - Fees and Expenses appear in desired currency. This is a trial and error method and unless you are a freak you should engage aCIC to design bill templates to bill clients in foreign currency. This involves creating a custom tab for putting in the currency and rate and then creating formula tokens on the templates. The problem with this is that the custom tab token information is not available in all sections of the template. To get round this few users are able to find and replace the desired token from another section to the section that lacks that token. It involves intricate steps that involve opening a template on notepad and playing around...As I say you got to be freakish to be able to do this, otherwise better to hire an expert to do it.

Once the template is designed you simply insert the custom tab and indicate the exchange rate. Then bill the matter with the charges in local currency using the correct bill template. The bill will appear in foreign currency ready to be sent out.

Note that the accounting is still in system currency. You would have to make use of the Collection Memos to indicate that the A/R is in foreign currency.

Output VAT Does Not Reconcile with Revenue – Help

This post is written primarily for Kenya and related jurisdictions where firms operate on an Accruals environment and where VAT law states that Output VAT is a function of taxable income.

After month end when firms have to file their VAT returns they have to ensure that the VAT is computed correctly and it reconciles with the taxable revenue. The simple formula is: Sales per Income Statement X VAT % = Net Output. This net Output should agree with the VAT Journal  (VAT Collectible after ITC Adjustments in PCLaw's VAT Journal).

There are a myriad of possibilities why the output VAT will not agree/reconcile after factoring any round offs. The hunting of the difference will begin after identifying what the difference is whether it is over or under stated.

VAT is a component of Fees and Disbursements Recoveries billed. One would use the elimination method to narrow down the areas that need to be looked at but some of the areas to look at:

·         G/L Statements: Run the G/L statement (non-detailed report) for first income to last income and focus on the Fees and Exps recovery G/L Accounts and see if there are any mispostings. In Fee accounts the source journal should only be BJ and WUD. For Expense Recovery G/L accounts the source should be CER and WUD. If you have entries originating from GB, PJ or GJ then these need to be looked at.

·         Write Up/Down (WUD) Journal: If there are WUD entries on the Fees and Exps recovery income G/L Accounts, run the WUD Journal and ensure that the invoices on which the Write Ups/Downs were done relate to Credit Notes and VAT accounts have been affected accordingly.

·         Invoice Journal Report: Run this report with correct layouts for Fees VAT. Throw the report to Excel and work on the Fee column. If there are any invoices billed which have no VAT charged ascertain that those were the Zero Rated Sales invoices. Then create a column for Fees VAT and compute the Fees manually using formula: Cell with Fees X VAT Rate. Then create another column for the Diff between VAT computed on the PClaw report and VAT computed manually on Excel. The difference should be zero. If there are material differences then that means that the VAT amount on Fees was altered at billing time.

·         Client Cost Journal: Run this report for the period in question filtered to include Expense Recovery entries only. On the resultant report check under the G/L Account Summary that the G/L allocations are to the correct (income) accounts.

·         Register>Expense tab:  Filter this for Unbilled  entries. This will produce all anticipated (ANT) Disbs and any Expense Recovery entries that have been unbilled. For this to work the Register should be run on the last day of the VAT month or before the billings for the following month has commenced.

·         Client Ledger Report: Run the client ledger for Disbursements only with a start and end date of the month in question. This report can be run at any time after the end month. The report must be run with the following filters under Advance Search which I have discovered to be very useful:

o   Received From/Paid To is Equal To “Expense Recovery” And Invoice Number is Greater Than XXXX where (XXXX equals the first invoice number of the following month’s billing) (This will work if all invoice numbers are sequentially used for the firm’s billing). The resultant report will show up any Expense Recovery entries that were made in error in the VAT month in question (incorrect date) but billed in subsequent months. This is useful if you are doing the exercise past the VAT period and the billings for the subsequent period has commenced.

o   Received From/Paid To is Equal To “Expense Recovery” Or Received From/Paid To Has “Taxes on Disbursements”. The resultant report will give a list of all ledgers that have Expense Recovery and Taxes on Disbursements. The report can be exported to Excel and analyzed by sorting out. On Excel a) ANT Disbursement entries can be alienated, b) Zero VAT Rated Expense Recovery entries can be identified and alienated and c) any Vatable Expense Recovery entries that have no corresponding Taxes billed identified and alienated. The balance of entries can be checked for VAT calculation to ensure that the total VAT billed on Disbursements is correct.

From my experience a lot of the VAT differences arise from the Disbursement elements.  Unfortunately there is no way users can be able to tell the Vatable Disbursements from the Non-Vatable Disbursements billed using built in reports in PCLaw . One therefore has to rely on mining the information from the above reports. The above if looked at properly should enable one to identify the errant entries or incorrect VAT amounts.

As a rule I recomend Accounts staff at all offices to reconcile the VAT on the last day of the month by close of business.

About Accurate Billing and Collection Reports

Billing and Collection information plays a vital part in law firms' remuneration structure. This information is constantly needed to make objective/subjective assessments of lawyers or partners productivity.

There are numerous reports in PCLaw to get this information. However for information to be consistent and accurate the system must be configured and data entry rules set to properly reflect the company’s policies on fee credits and collections.

There are a number of areas in PCLaw that need to be configured properly.

To begin under System Settings>Billing tab the option “Allocate fee changes to Resp Lawyer” determines if any Bill Up/Down of Fee amounts during billing should be allocated to the responsible lawyer or not. This is important and most firms leave it disabled if they wish the changes to be pro-rated amongst the working lawyers.

The next important area is under System Systems>Data Entry tab. Here you define how the collections are applied. Most firms leave it to Taxes, Disbursements and Fees in that order. It is important that this setting reflects the company’s policies and once set this setting is not changed. However this can be overridden on a case by case basis. For instance if the client is only paying the Disbursements and Taxes portion of the bill and not the fees, this can be allocated at data entry under Receive Payment>invoices tab>Payment details button.

The other area to look carefully at is at Matter levels Bill Settings. Here under Options “Auto Allocate Time/Fees to Working Lawyers” is checked by default and that is what most firms wish to allow PCLaw to automatically determine and allocate the fee credits to the lawyers working on the case at billing time in proportion to the value of their time spent. If it is the company policy for all fee credits to go the file holder then this option must be left unchecked (fee credits in such a case will go the Responsible Lawyer).

Within these parameters PCLaw still allows you to override who gets the fee credits at billing stage. If fee credit overrides is to be done, this can be accomplished at the “Prompt for changes to Billed Amount” stage during billing or at Matter Manger>Billing tab under “Split Lawyer Charges”. Firms must set clear rules embedded at Prebill levels for partners in charge of the matter to indicate desired fee credit allocations on Prebills.

Properly setting up the system and defining rules for partners, billing/accounts staff to follow for fee credits and collections is therefore vital to get accurate and consistent billings and collections reports.

Journal Entry on Bank Journal Issue

The G/L Adjustment feature in PCLaw can be used to pass non-matter related entries (receipts/payments) via the General Bank journal. After performing the entry “Show Entry on Bank Journal” must be checked. The entry will appear on the General Bank Journal with a blank “Received From/Paid to” field while the "Rec/Chq #" will indicate the Journal Adjustment Ref #.

Tip: Faster way perhaps for accountants to record transactions for several office banks (e.g. monthly bank charges) in one go.

Bug: Once the entry is done NEVER drill down from the General Bank Journal or General Journal reports to delete the entry because although it will appear as deleted on the General Journal report,  the bank journal and G/L statements will still show the entry.  To get round this one would have to pass a reversal entry or call Technical Support to help fix the issue by removing the entry from the back-end. The only uneventful way to delete the entry would be via G/L>General Journal>Correct G/L Adjustment. This issue is true as of V10 SP5 HF3.

 

Tiered Rate Structures

I had a situation for a law firm who took up a matter which would proceed in stages/phases and the rate matrix for each phase and lawyer was different.  The firm entered into a billing arrangement where it would bill fees based on the phase the matter fell under. So, lawyers working on the first phase had to record time at particular rates. If the matter proceeded to phase 2,3, etc the rates changed accordingly.

Below is a write-up I did on how to accomplish this.

General Overview of Rates in PCLaw

·         Rate Categories Assigned: Rates assigned for a matter are predefined in the system at firm level or matter level. Rates can also fall in categories and the individual rates for each timekeeper may vary for each category as defined in the system. For example: If a matter has the “A” rate assigned, lawyer ABC who has an A rate of 1000.00 will bill at 1000.00 per hour; lawyer DEF who has an A rate of 750.00 will bill at 750.00 per hour, and so on. It is also possible to change the rates of the timekeepers for any category effective from user defined dates.

·         Monetary Rate Assigned: A matter can also have a fixed monetary rate assigned as opposed to the A, B, C, etc rate category. For example: If a matter has a charge out rate of 1400.00, any timekeeper who records time on the matter will bill at a rate of 1400.00 regardless of his/her rate under each rate category.

·         Task Based Billing: A matter can also be billed by Tasks (known as task based billing). This is where time is categorised by task with each task carrying their unique rates.

Whether a rate assigned to a matter is category (A, B, C, etc) based, task based or a monetary value defined, further rate exceptions are possible with a number of permutations including the ability to bill by stage/phase.

Time Recording for Matters to be billed in Stages/Phases

Whilst an array and flexibility of billing rates & styles is possible, how to assign rates based on stages or phases of the matter needs some thoughtful planning. Some matters progress in stages and the lawyer may agree with the client a tiered rate structure based on the lawyer working and stage the scope of work falls under. It is possible to bill time based on the stage /phase and the timekeeper working on the matter.

Prerequisites for achieving stage based billing on a matter

·         Use specially created Task Code (e.g. “ST” for Specials Tasks) instead of ”BW” (Billable Work) at matter manager level under Main tab as the Default Task Code. Use this Task Code as default for all matters that will be billed in Stages/Phases.

·         Define the Stages/Phases at firm level – Useful to have as many stages as possible e.g. Phase 1, Phase 2, etc at Task Code level (Options>Lists>Task Codes).

·         Use Rate exception at matter manger level under Billing tab to set up as many timekeepers as possible.

·         Users have to use appropriate Task Codes (Phase 1, Phase 2, etc) when docketing time to have that matter billed at the correct rate based on the phase for which time is being recorded.

·         If user does not define the task code at time entry level, the matter’s default rate will be used.

·         When new timekeepers join in, apart from setting their default category A, B, etc rates, all matters with rate exception should be updated. To aid in doing this, the list of client matters report layout can be designed, and printed specifically for this purpose. The report can be filtered under Advance Search to show only matters with particular Task Code – “ST” in this case.

Client Account Surplus

Trust Bank Account transactions in PCLaw can only be associated to matters and not to any G/L accounts. This is probably due to the Trust Bank Account regulations in US/Canada for which PCLaw was primarily designed for. This means that any receipt or payment from Trust Account cannot be associated directly with any G/L account – the system will only accept matter allocations.

The above may present problems for new firms wishing to adopt the PCLaw system in jurisdictions in which banks have no Law Society rules they need to comply as far as Law Firm Trust Accounts go. Such firms may ponder with Trust accounting issues such as how to maintain minimum balances, how to account for non-matter related interest income and bank charges arising from Trust accounts.

The following is involved to get round this:

o   Opening of a Client Surplus Asset account (this can be a General Bank account or simply a current asset G/L account)

o   Opening of a client called “Client A/c Surplus”

o   Opening of “Client A/c Surplus” matters for each Trust Bank Account

Every time a non-matter entry is to be made from Trust you would use the Client Surplus matter for that Trust account. Then and opposite entry is done via the Client A/c Surplus Asset account.

Example:  Interest from Trust A/c needs to be accounted from Trust A/c 1 for 1,000.00. First one would do a Trust receipt against client surplus matter 0000-001 for 1.000.00. Then one would debit Client A/c  Surplus Asset A/c and credit Interest Income G/l account with 1,000.00. This is achieved via Firm Receipt (if the Client Surplus Asset account is opened as a General Bank account) or a journal entry (if the Client Surplus Account is opened as a G/L account).

At all times the balance in the Client A/c  Surplus Asset account must be equal and opposite to the total of Trust balances in all the Client A/c  Surplus matters. One would have to keep a tab on this manually.

I know in the past for different reasons PCLaw had a version for firms in Scotland to enable the association and tracking of the Client Surplus Asset and matter accounts. Firms in Scotland can have a substantial Client A/c Surplus and they can then overdraw the Trust Account for normal client matters provided the amounts overdrawn do not exceed the  balance in the Client A/c  Surplus. PCLaw had a feature to automatically track this. I am not sure if the Trust regulations for law firms in Scotland have changed or if Lexis still supports this version of PCLaw.

About Reasons for Trust Overdraws and How to monitor progress of correction of Negative Trust Balances

Most firms operate Trust accounts and the operation of these Trust funds are governed by strict local bar and law society rules. However there are also firms that operate in jurisdictions with non existent or lax Trust Accounting rules and do not pay attention to trust account management. Worse things have happened to firms that also operate without due diligence and controls exercised by firm partners and management. The result is inadvertent or fraudulent Trust overdraws. Reasons for inadvertent Trust overdraws include:

 Accounting for trust transactions on incorrect matters: Writing trust cheques against wrong matters when there are sufficient funds at the time only comes to light towards the end of the matter .When time comes to dispose balance of the trust funds it is determined that the balance should be more that what is available. This anomaly can be caused by a) pure mis-postings by accounts staff or b) incorrect/distorted matter details availed to accounts staff by lawyers/legal staff. At the time of disbursing balance of trust funds, some lawyers, because of time constraints, will approve payments with instructions “to sort out later”. This is very true for instance, for real estate (property) transactions when there are completion dates that need to be adhered to and the firm is acting for the buyer and vendor and somewhere along the line disbursements for the buyer were incurred utilizing trust funds belonging to the vendor.

Multi Currency trust transactions: Firms who choose to handle foreign currency trust transactions do so by processing trust entries in their local currency whilst keeping a manual record for the value of foreign currency trust balance for each matter. This is a primitive method because one can track the foreign currency value as well as the reporting currency value of the trust balances in PCLaw (this is a topic for another day). Because of fluctuating exchange rates negative trust balances can occur although in reality the forex value of the trust funds is not negative.

Disbursing uncleared trust funds: Again some firms flaunt rules because ‘urgent’ payments need to be made and accounts staffs are instructed to process payments on matters before funds for that purpose have cleared the bank. When Trust receipts result in NSF trust balances go into negative.

Allowing Negative Trust Balances: Most firms restrict the occurrence of negative trust balances. This can be set under: Options>System Settings>Data Entry tab. However, this is sometimes left unchecked and subsequent trust overdraws warnings during data entry go unheeded.

PCLaw has a great tool to identify and manage the correction of negative trust balances. The Trust Listing report can be run for negative balances only. The report gives last trust entry date for every matter that has a negative trust balance. The Last Trust entry date signifies:

·         The last date of the Trust cheque that resulted in the matter going into Trust Overdraft

·         The last date of the Trust receipt on a matter that already had a Trust Overdraft but the receipt was not enough to move the matter out of Trust Overdraft

Once negative trust balances have been identified and are determined as genuine, the resultant Trust Listing report can be exported to Excel and sorted by Last Trust date. This Excel report will give the earliest and latest date and can act as a starting or ‘base’ report. Once the latest date is known on this base report, subsequent trust listing reports can be run for negative balances with advance search filter (that can be saved in the system) for dates greater than the latest date as per the original report. For example if the last Trust date per base report was 18/Jul/2012, under Advance filter of the Negative Trust Listing report, the date can be set as greater than 18/Jul/2012. By so doing you can identify if any subsequent dated trust entries have taken place on matters that have negative balances and is useful for tracking progress on correction and more importantly help prevent matters from aggravating further!

Of course on every report the total of negative Trust balances need to be checked and compared to prior reports to determine overall improvements/replenishment efforts!

How to identify Unbilled Expense Recoveries to be removed

A lot of firms have a policy to bill soft costs (i.e. expense recoveries – courier, printing, photocopies, stationery, phones, etc) at point of billing. The following are some of the reasons for doing this:

·    Indiscriminately tracking expense recoveries on matters hypes up the income statement before even the matters have been billed and this distorts the financials of the company.

·    Tax implications – expense recoveries increase the overall revenue and this may increase the tax liability in some jurisdictions before even clients have been billed for the recoveries.

·    VAT Implications – there could be breach of VAT regulations. Some VAT jurisdictions state that VAT is payable at the earlier of a) delivery of goods/services, b) receipt of money or c) issuance of an invoice. VAT only crystallizes at time of billing in PCLaw. By recording recoveries before billing a firm has met the criteria of “delivery of goods/services” even though there is no Output VAT and the client may be billed many months later.

One of the challenges as part of the monthly exercise is to determine if there have been any Expense Recovery items charged on matters that have not yet been billed in the same month. The following reports are not worth running for this purpose:

·    Client Costs Journal: It will give all the expense recoveries entered in the month but it is not possible to determine the billing status.

·    Client Disbursement Analysis and Interest: This can give a list of unbilled soft costs but it is very clogged up and cannot be analyzed.

I have discovered the efficient way to do this is by running the client ledger for the month with the following advanced filters:

Ø  Field: Received From/Paid To

Ø  Comparison: Is Equal To

Ø  Compare To: “Expense Recovery”

The resultant report will give all expense recoveries for the month with the invoice numbers tagged to it for all relevant matters. This report can be exported to Excel for analysis and sorting by invoice number, etc. One can quickly identify whether there have been expense recoveries that should be removed. Once this task is complete, other month end tasks can be completed and accounting restriction date re-set.

Another quicker way is through the Register>Expense tab which can be filtered for all unbilled entries. However for this the user needs access to the Register under Security.

My wish would be for PCLaw to have an option of having a Memorandum account for recording soft recoveries while recognizing the income/expense recoveries (G/L) at point of billing.

Direct Disbursements – Vatable vs. Non-Vatable

For firms operating in a VAT/GST environment, there are no ready reports in PCLaw through which one can tell the vatable disbursements from the non-vatable disbursements. Disbursements on client ledgers stem from the following source journals:

• General Bank
• Accounts Payable
• Expense Recovery

Most firms experience problems at billing time when they realize that direct disbursements that should have been non-vatable (“n”) have inadvertently been treated as vatable (“y”) at data entry. It may be difficult/impossible to go back to the entry and change the VAT allocation because either the payable has been settled, banks having been reconciled or dates are restricted.

To avoid such problems and as part of the monthly workflows, Accountants can run various reports to ensure that the direct disbursements charged on client matters have been properly classified as Vatable/Non-vatable according to local tax rules.

To aid in doing this the custom field “VAT Category for Allocation” on GB and PJ report layouts is very useful. The reports can then be exported to Excel and sorted out by the column for the VAT category to identify any mistakes.

Locating and correcting the VAT status of Unbilled Disbursement entries this way is easier than having to face problems at billing time several months later in the future.

Exporting to Excel - General Bank Journals

Every so often Accounts staff have to prepare periodic reports on Excel for receipts & collections and payments and analyse them. To achieve this they would typically export the General Bank Journal using a layout they have designed.

The resultant report opened in Excel may not have multiple allocation line items if a receipt or cheque entry has two allocations. (all entries having either one or more than two allocations will appear correctly). To get round this, use PCLaw's “Default” report layout to open the General Bank Journal in PCLaw before saving it to Excel. Do not use any custom designed layouts. This issue is true as of Version 10.