Usage Rule Example for Legal Firm
When creating and uploading a usage rule for a legal firm to process billable hours, you would create a Usage Lookup Table with the following name and requirements:
Lawyers — A two column table that lists the names of each lawyer in the first column and the corresponding level of each lawyer (either "Junior" or "Senior") in the second column. The names of the lawyers must be in the same format as would be specified when entering the usage information (for example, either "John Jones" or "Jones, John"). To explore this example, you need to create a Lookup Table with minimal entries as shown below. You can use lawyer names of your own choosing and add additional lawyers:
| Lookup | Value |
|---|---|
|
Howard Young |
SR |
|
Mike Jones |
JR |
| Cell | Explanation | Contents |
|---|---|---|
| B8 | Amount (User Input). The number of chargeable minutes. Used to determine final cost of billable hours. Notice that the Units of Measure (B7) is not specified because that piece of information is not used in any of the calculations. | 73 |
| B10 | Text 1 (User Input). Name of lawyer performing the services. Used to calculate "Junior" or "Senior" and the appropriate cost per hour. | Mike Jones or Howard Young |
| D2 | Rule Result. Determines if the lawyer was located in the Lookup table. The rule cannot work unless there is a lawyer is associated with the event. | =IF(ISERROR(G2)=0; 1; 0) |
| E2 | Rule Rate. The final charge for this event (legal service). Gathered from G4. | =G4 |
| G2 | Formula to determine the level of the lawyer associated with the chargeable minutes. Used for calculating the rate. | =VLOOKUP(B10; $Lawyers.A2:B10; 2; 0) |
| G4 | Formula for determining the rate (junior lawyer or senior lawyer). | =IF(G2="SR"; G7; IF(G2="JR"; G8; 0)) |
| G7 | Formula to calculate the charge for the minutes assuming a Senior lawyer. | =((G13*G11)/60) |
| G8 | Formula to calculate the charge for the minutes assuming a Junior lawyer. | =((G14*G11)/60) |
| G10 | Formula that rounds up minutes to the closest half hour. If a half hour has begun, the formula rounds up to include the entire half hour. | =ROUNDUP((B8/30); 0) |
| G11 | Formula to determine the total number of chargeable minutes. | =(G10*30) |
| G13 | The hourly rate for a Senior lawyer. Optionally, this cell could be formatted to show the amount as dollars. | 200 |
| G14 | The hourly rate for a Junior lawyer. Optionally, this cell could be formatted to show the amount as dollars. | 100 |
Column B is reserved for input, column C is reserved for output, column D is reserved for the Rule Result, and column E is reserved for the Rate generated by the rule. Cells in columns G through Z are reserved for rule-related information, formulas, and calculations.
When performing calculations, Gotransverse recognizes only certain functions. For a list of these functions, refer to Global Usage Rule SpreadsheetFunctions.