Inprotech: Gross margins, cost rates and the Budget tab

One area I had a large amount of input into when I was at Maxim/CPASS was the addition of cost rates to the recording of work in progress and the addition of the Budget tab as an optional screen in the Cases program. The goal was to provide a first cut at facilities to assist with putting more financial rigour into the management of clients and their matters.

The traditional financial measurement of the value of client to the firm is nett billings (ie. after recovering disbursements). While this is important information my interest was in understanding what level of gross margin or profit that the client was delivering.

As an example, a large corporate client with a high level of billings may also require a lot of (non-chargeable) attention to meet their particular needs and demand a level of discounting, just because they can! The net result of this could be that the profit contribution from that client could be significantly lower then expected and the effort doing the work for that client may earn a higher return elsewhere. Equally, small local clients are can seen as lucrative by the person that has built a strong relationship with them but what is the actual profit position? If it is not adequate then should we be servicing them? Then, who are the clients that are contributing the most profit to our business because these are the ones that we want to protect and nurture.

The two important pieces of financial information to measure this are gross margin and gross margin %. Gross Margin is the raw profit contributed by the client. Let's say fees are $1,000 and the cost of delivery is $600 then the Gross Margin is $400. The Gross Margin % is $400 over $1,000 or 40%. While gross margin is a direct amount and therefore can be easily understood the Gross Margin % is a ratio that is a measure of return on investment and, in some ways, an assessment of risk with the customer.

Now I am not saying that total billings isn't useful information as the volume of work provided is an important factor in understanding the totality of a client relationship. Equally, dominating market share in a particular segment may require retaining some less profitable customers.

In my view, however, the key driver for marketing and customer service strategy should be client profitability. We should be investing our money in the areas of the business that have the greatest likelihood of return. Without assessing this, obviously alongside other relevant information, we don't ensure that we have fully understood our offering to the market place.

So how does the system help? Firstly, there is the ability to set up cost rates for work in progress items in a similar way as you can set up charge out rates for time. When this is done the cost of the work or expense is calculated and recorded on the WIP item along with the charge for the item. If the charge is written off or down the cost remains regardless of how it is billed to the customer.

For time, costs rates are an hourly rate, similar to charge out rates. Say, the cost rate for a particular staff classification is $100 per hour and the charge out rate applicable for the work is $400 per hour. When 30 minutes of time is recorded in the timesheet program, a cost of $50 and a charge of $200 will be written to the work history table allowing the calculation of the gross margin for the item at $150 ($200 minus $50).

For disbursements, cost rates are a percentage of the disbursement amount. For examples, associates invoices, courier charges and the like would be defined as having a cost of 100% of the disbursement (because that is the true direct cost). Recoverables such as photocopying and fax charges, on the other hard, may be defined as having a cost of 50% of the disbursement because there is no directly related invoice and the firm has decided that this is the notional cost that is to be assigned to these type of expenses.

Following on the above example, an associates invoice of $1,000 would be recorded with a cost of $1,000 and hence have no contribution to gross margin and a sundries charge of $20 would have an associated cost of $10 giving a $10 contribution to the gross margin.

The system also provides for 2 cost rates, surprisingly enough called Cost Rate 1 and Cost Rate 2. The intention here was to allow calculation of gross margin under two different scenarios. For example cost rate 1 could be used for the direct cost of the time, a rate directly related to the staff salary or a blended salary for a staff classification. Cost rate 2 could then be be used to add on an some percentage of on costs to cover any taxes, insurance or superannuation/pension that are directly related to employment. Alternatively this rate could include all of that plus some notional portion of overhead (accommodation, admin support) as per the firm's accounting policies.

A little thought needs to be put into this as the raw information will read differently depending upon the approach taken but in the end the key thing is the relativity between the figures for different clients. Therefore it is not something you want to change once you have started the process of recording information.

In terms of the standard system, the only place the information is aggregated is on the Budget tab for the case. This calculates the net fees and the total cost to provide a gross margin for the case. The next step was to provide reports aggregating costs & fees and calculating margins for clients overall but we never got around to doing it because we got so little feedback on what was provided. I suspect no user has ever implemented this aspect of the system.

The other work that was done at the same was the ability to record a budget (and a revised budget) against a case and there is a simplistic little tool that helps you do this eg. enter a staff member or staff class and a number of hours plus disbursements and it calculates the total budget. When the budget is created it also records budgeted costs, if set up, and budgeted gross margin are compared against actual for the case as well as showing budget used.

The tab also summarises billing for the case by fees, disbursements etc.. and provides a list of all bills raised for case. These can then be displayed our re-printed. From memory there is also a site option to allow a warning to be displayed if the fees for the case go over budget and we (naively) provided a web part that displays the actual versus budget on Client WorkBench. I suspect no one will ever implement that bit!!

Of course, what is really needed to make the feature useful is some reporting. The ability to measure profitability by client, type of client, industry segment, technology specialisation, etc... allow key information to be available for strategic decision making. While there are no reports provided with the system they could easily be constructed. The beauty of the database is that all the information is collected in an elegant, normalised and generally easily understood manner.

The key is unlocking this rich information source.

Inprotech: Correspondence Instructions

An interesting piece of functionality in the system is Correspondence Instructions. I have often wondered how widely used it is.

The idea is that you can record notes against a name record that pop up during the case take on process, billing and letter writing so that the operator sees them to knows any special instructions that have been provided about corresponding with the client. It might be all correspondence should be emailed to this address, invoice copies are required to be sent on yellow paper or they don't like staples in their letters!! The fields are available on the Client Details tab for the name record in the Names program.

There are 4 types of instructions, a default, one for cases, one for billing and one for PassThru (letters). If the same notes are to be displayed across all areas then just complete the default text. If a different set of instructions is required for billing then select the billing type and put the notes against this. This will override the default notes when the pop up occurs in the billing program.

The delivery of the notes is controlled by the Correspondence Instructions site option and this defines where pop ups are enabled.

The delivery of information to the user is via the "tip" functionality, the smaller yellow box that you see sometimes if help text has been set up against case tabs and detail entries. The instructions will pop up when a new case is added when the name is the instructor and when a bill is created when the name is the debtor.

With cases, my preference would be to display the instructions every time that you go into a case so that it is always in the operators face, but that's not how it works. I would also prefer it to be a bigger dialog right in the middle of the screen rather than the little yellow one on the left hand side. This way more instructions could be read without scrolling and you can't really miss them. The ability to display in Timesheet would also be useful.

Implementation of displaying the instructions in letters requires a bit more effort. Docitems need to be set up and templates changed. I am afraid I am not fully aware of the specifics here.

Lastly, this functionality was meant to supplement, not replace, the ability to automate instructions and processes with clients. Things like:

• copies of correspondence to a different address,
• billing in a different format for a client,
• different billing address,
• different statement address,
• numbers of renewal reminders to be received,
• auto renew,
• number of bill copies,
• billing currency, multiple cases on a bill or not,
• different letters, language of correspondence,

etc... are all things that can be recorded the system in other places so that the system can handle these without operator intervention.

Inprotech: AR Item Import

This little program is quite useful for loading up open items in the debtors ledger, most typically used when transferring information from an old system to Inprotech. It accepts an CSV file of items and loads them up as debit and credit journals.

First issue is that the layout of the import file isn't provided but if you ask the help desk they will give you the functional specification for the program which includes the file layout.

Next issue is that the program requires the NameNo of the client record in Inprotech, which is the internal unique identifier of the record in the Names database. The only place that this is visible is by going into the details of each name record and using the Systems Details menu item to visually read the number, clearly a time consuming task when you are looking to load items for hundreds, if not thousands, of customers.

The way to get around this is to use Excel:

1. Load the old system's account code for the customer against the name record using the account code field (or Search Key 2 as per a previous article).
2. Use SQL to extract a table that has the account code and the name number as the two columns.
3. In Excel, use the VLOOKUP function against the two columns (they will have to be sorted in account code sequence) to get the name number for the account code so that it can be used to import the item.

One word of warning on this program. For debit items, it converts the transaction date as the date of the transaction provided. For credit items, it seems to change that date to the date of the current period for some reason. This means that while the total balance of the load is correct the actual totals by aging period my not be and, if not, will have to be corrected manually to ensure that customer statements are consistent with the previous system. The problem has been reported to CPA but I wouldn't expect it to be corrected at any stage in the foreseeable future.

Inprotech: Keywords and Stop Words

I have often wondered how many clients make use of the Keyword functionality in the Cases program. It was a bit of carry through from the previous system we worked on and has probably been superseded by the ability to text search and to a certain extent clashes with the case attribute functionality.

The intention was to provide a facility to tag cases with any number of keywords that could be searched upon, hence the keyword field on the case enquiry screen.

To make it easier for entry, keywords are extracted from the title and attached to the case at the time the case is added. I am pretty sure that the there isn't a site control to stop this happening so they are always created and you probably have a big keyword table - could be wrong here though.

What you can also do is define certain keywords as Stop Words. Stop words are pieces of text that won't be created as keywords. For instance, "the", "and", "a", etc... are words that you don't want cluttering up your keyword list, presuming you care!

Stop words can be defined as Case Stop Words or Name Stop Words and this is where it is potentially interesting. If the keyword is a Name Stop Word then the system uses this information when automatically creating the default Search Key 1 when a name is added. The entered name is used but any Name Stop Words are removed.

For example, making "the" and "company" Name Stop Words will mean that the Search Key 1 for the name "The Proctor & Gamble Company" will default to "Proctor & Gamble". This facilitates makes easy searching for the end user and removes the need to change the search key manually.

Now, the catch. The only place that I am aware of where you can define a Stop Word is from the Keyword picklist but not the keyword picklist on the case enquiry screen. You have to go to the keyword picklist that is available from the Keywords tab in Cases, so you will need a program that has screen control to give you access to that tab.

Inprotech: Refunding Money to Clients

The Accounts Receivable module is a little clunky when it comes to giving money back to clients. A little clunky is a bit of an understatement, it doesn't actually handle it at all so there is a workaround. Luckily, giving money actually back to customers is not something that happens all that frequently (rather than leaving the credit on account).

Assume that the client's account is in credit. Maybe this is because they paid a deposit in the first instance and it wasn't all expended or you over estimated the associates charge (or the exchange rate moved!) or there was a refund of fees from the IP office because of overpayment of fees (might have to write another little story as to how to handle that as well!).

Whatever the reason, the client is in credit, you have sent them a statement so they know and they want their money back rather than keeping it on account for future work. Somehow you have to get the funds off the debtors ledger and back into their hands.

Here's what to do:

1. Set up a reason of Client Refund, applicable only to AR.
2. In your GL Interface mapping (you brave enough to go in there?) go to the Debtors Revenue accounting and set up a mapping for the Client Refund reason to go to Suspense in your General Ledger (I am assuming everyone has one of these accounts).
3. In Accounts Receivable raise a debit journal against the client account that matches the amount in credit. Use the new Client Refund reason. The mapping that you have just set up will cause the Suspense account to be creditted.
4. Apply the outstanding credit against the debit item just raised so that it is cleared off the client's debtor account.
5. You now need to get the money to the customer. Go to the Accounts Payable and if there is no tax applicable use Manual Payment function to raise the payment. Use the Account only facility. Enter Suspense account as the applicable expense account. The debit created here will nett off against the credit raised in AR.
6. If however there is some tax applicable as a part of the refund you will need to raise an invoice to sort the tax out. The expense account in this instance still must be Suspense so that the item raised in AR is cleared and Suspense can reconcile. You then pay the invoice through manual payment selecting the entered invoice as the one to be paid.

The issue, of course, is that you need a supplier to be able to raise the cheque and/or payment. One way to do this is to make the client a supplier as well so the payment process can be run. I'm not a big fan of this because it clutters up your supplier list with clients for transactions that probably only occur once or twice for the customer.
My preferred method is to have a Miscellaneous Supplier for one off payments rather then set the specific supplier up. This is not just refunds but any spurious invoice that comes along rather as it saves setting up the supplier each time.
This way you can just enter the details of the client and the refund into the description field and this can be printed on the remittance advice and by doing a manual payment you can type in the Drawer Name to print on a cheque, if that's how you are paying them. If its by EFT you have to raise that manually but at least you have a remittance advice!
Phew!! That was easy. Happy to take questions at this stage. Yes, you in the front row....

Inprotech: Finding Criteria

While I am thinking about it there was another time recently when a piece of, what I thought was, well known functionality was seen for the first time and commented upon how useful it was. This was a little gem under the Navigator menu in the Control program.

The normal way to find a criteria is to enter in as few attributes as possible, typically, case type, country, property type and maybe action, to get as short a list as possible to find the criteria that you are after. This will take three or 4 clicks and tabs and a hit of the Refresh button and then a short search visually to find the criteria you want.

Not too painful but you generally work on one criteria at a time and usually know the criteria number from the record that has created an issue. If this is the case, select the Navigator menu and the Find Criteria item, a dialog will be displayed. Type in the criteria number, eg. -6277, 788, 14, and hit enter: the criteria will be found immediately and displayed in the criteria table!!

Inprotech: Moving Columns

I was showing someone the Rates Maintenance program the other day, funnily enough to have a look at the charge out rates that had been set up!!

Of course looking at all the rates as an overview is typically difficult as there is no report. It is also not helped by the fact that the sequence of the columns displayed, while ordered like the fields on the screen, is not really consistent with how rates are typically set up eg. staff classification, staff member, local/foreign, etc...

To alleviate this I dragged the columns into the display sequence I wanted so that the relevant columns were at the start of the table rather than having to scroll horizontally to see them. I then moved the empty and hence meaningless columns to the right (and off the screen) so they didn't get in the way.

To my surprise the person watching said "I didn't know you could do that". It is in fact a standard piece of functionality for all tables displays in the system. You simply click on the column header and drag it to the position you want. You can also re-size the columns to the width required so that the display or the data is easier to read.

Unfortunately, the program doesn't remember these settings, which would be fantastic, but it is really easy to do and can provide value for the session you are working on in some instances.

I presume everyone knows about right clicking on tables so that an Excel CSV file can be created of the contents of the table (in the adjusted column sequence!) so that a report can be quickly created without having to resort to SQL.