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.

Should you move PCLaw from C-TREE to SQL?

In my prior blog post I compared effects of two offices of a firm running PCLaw C-TREE vs PCLaw SQL. The question is then should a firm implement / upgrade to the SQL or C-TREE version of PCLaw.

Whilst I am not a techy I have some experience working with a few CICs on PCLaw SQL set of issues and can give my view based on my own experience and requirements I have seen firms need.

CICs will give you conflicting answers and reasons for moving (or not moving) to SQL. One might then wonder: who is right and who is not? The short answer as one CIC put it is “there is no right or wrong answer..”. What works for one firm may not work for another.

Here is my take on it: As stated in my previous post, PCLaw just is not designed to work on SQL and can have devastating effects. There are however success stories of mid size firms using it well but my take is that if a firm feels it needs to move to SQL to get the benefits of SQL then it probably has reached a size it needs to take a holistic approach and assess its requirements. It may be that it has outgrown PCLaw. However if merely integrating with another software is the sole reason for upgrading to SQL version then it needs to explore other avenues.

My experience is that the C-TREE is better than SQL multiple times over because with C-TREE you have the following advantages (that are disadvantages of SQL):

·         Speed: C-TREE reports and routines are much faster than SQL.

·         Quick to implement and cost friendly: No techy /CIC needed and no additional cost outlay in terms of hardware/software.

·         Portability: PCLaw C-TREE database is easy to back up into a pla file and hence carry whenever and wherever one needs to and set-up. This has a number of benefits for a busy Accountant as stated below. SQL database on the other hand requires your Network Administrator or contracted / in-house IT staff to perform backups.

·         Creating a local set of books on a laptop or PC: For someone like me I carry a number of historic sets of books for different firms on my local drive. This allows access to historic or standalone set of books when needed. This historic data-sets has the following advantages: run and analyze historic reports via Excel, etc, trouble shoot problems, design new/edit existing reports/templates for later installation on a live system, test Security groups and restrictions before implementing on a live environment, test new functions & features using real firm data, review work performed by Accounts staff, etc.

·         Run VDI on a standalone: Sometimes running the Verify Data Integrity (VDI) routine on the live server may present problems. Quickly making a copy of the live dataset, locking it and installing on a standalone enables one to quickly run the VDI, perform a backup and restore the backup on to the live set.

I have always carried the data sets of firms that are on C-TREE whenever I have had to travel away. I ensure that the datasets I carry are most recent (a day old). One case I vividly recall was when I had to travel to a far away place where I could only get intermittent internet and email access. I was still able at my free time to generate all the Year End reports properly analyzed and email back to the auditors. All this without the need to remote in to the firm’s database (which would have been the case had the firm been on SQL). All I had to ensure was that I had a copy of the dataset with all the period and Year End adjustments complete. It was like carrying a brief case of accounts. The pla file was all I ever needed!.

I personally therefore would never touch a SQL database for the above reasons and also because of the inherent issues that the PCLaw SQL system is know for - data corruption, slowness etc. My mantra then; stick to the strengths of PCLaw and better the Devil you know.

A PCLaw SQL Horror Story

In the past I have seen two incidences when a firm decided to migrate the PCLaw database to SQL. Both related to the same firm, with the first being for one branch and the second for the other. Different databases for different offices; different reasons to migrate to SQL; and all for the same firm.

The reason for the first office was to enable better data mining, extraction of reports for business intelligence, ensure data integrity and minimize data corruption issues. The upgrade was a disaster, crippling the Accounts office and within two days the database was reverted back to C-TREE.  All happy going from then on.

The database at the second office was faced with a forced upgrade situation imposed upon by the consultants who were engaged in implementing a DMS (Interwoven Worksite). Without upgrade of PCLaw to SQL they were not going to touch anything. And that was after the firm had signed off the purchase (of Worksite)! In a nutshell there was a complete disregard of the Accounting system for the sake of PCLaw integration with Worksite. Despite the risks presented in a “crystal ball” fashion and despite earlier warnings, the firm upgraded to SQL.

Fast forward two years later: The Accounts office on the C-TREE database continued with greater speed and efficiency whilst the other office on SQL began getting issues that got from bad to worse. To put things into perspective the branch on C-TREE database was consistent in finalizing its year end accounts & audit by  February while the other office had the worst year as it took 9-10 months in finalizing its Audit/Accounts.

So, what went wrong? The cause is “Interwoven” – yes as the DMS name suggests all issues interweaved from slowness in report generation, data corruption, errors prone balances which the Accounts team relied, to finalizing periodic and Year-End accounts and audit. The domino effect had disastrous consequences.

A short horror story and simply because unlike more mature programs (Worksite in this case)  PCLaw was not designed to operate on SQL. So, should a firm upgrade to SQL or stick to C-TREE? That is probably a topic for next discussion.