Paul W. Carlson, CPA (00:00):
Hey, this is Paul Carlson, CPA, with Law Firm Velocity. A lot of our firms use a report that summarizes time by timekeeper by matter, where we can see the total hours that went into each matter and the value of the time that went into that matter each week. It's just a good tool to understand what everyone did last week and just to get a gauge on production. A lot of times we'll set billable time goals for each timekeeper, so the associate attorney is going to have a goal where they need to have $8,000 of billable time every week, and this report allows us to measure their goal versus what they actually produced. These numbers will usually be reported into a scorecard so management can quickly see how everyone's doing. Surprisingly, a lot of case management tools don't have a report that's in this format, or they don't have the ability where you can click in to see what exactly happened on this matter where you can just click the plus sign and see the detailed time entries.
(01:05):
This is nice you can click in and see the detail that otherwise, this turns into like a 300-page report. So to make this report from Cosmolex, first you have to be logged in to Cosmolex. I'm on the activities and time and expense. So we want to undo the filters at the top because we want to see all time entries for the week. Then in this filter box, we're going to want to set this to show time entries that were created last week. So you can use the range and we'll typically use a Saturday to Friday week. The expectation is everyone has their time in by close of business on Friday and we're running this report the following week. And so that would filter the time for the period in question. And then what we need to do is push this data into Excel so we can format the data. So do the export.
(02:11):
We need to change the export format to CSV and it's a custom report. Because if you leave it export current page, you're just going to get one page of data. If you do custom export and you leave it page one to page one, it's only going to export the first page of data. You need to go into this option and select the last option, so if the report to six pages long, you want it to push page one through page six into that CSV. So I have my sample data over here. So when it comes out, it looks something like this. So we need to move a few things around and do some formatting. So we want this to be sorted or arranged by timekeeper. So to move columns in Excel, you click it, you highlight the cell, get your arrows here, drag it over. So we want timekeeper, we want matter, we want date, and we want time or quantity, and then we want the value of that entry.
(03:22):
Do some cleaning here. Timekeeper is good, matter we don't want to make too big. So the date includes the times. We're going to change the date format to short date, and we're good there. I usually hide these columns because we don't need to see the tax details. We have this row here that's too tall, so I will in the corner select all the rows, and then I'll just pick one row and make it a little taller, and that forces everything to be the same height. Next step is to sort this. So go data, sort, and we want to sort this by timekeeper and by matter and then by date. All right, so now we have everything sorted. So why we need to sort this is we're going to use the subtotal tool that's driven based on changes in these columns. So we go to subtotal. It's best if you un-select the whole sheet and just highlight what you want to subtotal. Go subtotal. So what this says is at each change in timekeeper, we want to sum, we don't want status. We want a sum of the time and the value.
(04:44):
Okay. So at this point we have subtotals by each timekeeper, and now we need to add a nested sum so we can get subtotals by matter. We just click subtotal again. This time, we say at change in matter. We want to use sum of time and value. And here's the big trick to nested subtotals is you have to uncheck that box because if this box is checked, it's going to erase the subtotals we just made by attorney, and you're only going to receive subtotals by matter. If we uncheck this, the per timekeeper subtotals remain in place and we're just adding more subtotals. Click okay, and we have a giant detail mess. So here we click the number three, and what that does is number four is all the detail. Click the number three, and what that does is it says, hey, only show the totals by each matter. And so here we have a report that's the hours and the value of time by timekeeper.
(05:57):
At this point, you want to do a little bit of a check that any internal or overhead matters should not have a value by their time. So here we have an internal marketing matter that we have $4,400 of time. That should not be here because we only want to collect time or report value of time on client-facing work. So this is not great, so there's a problem within our case management tool setup that we need to go and set the billing rates for all timekeepers on this matter to zero. I'm just working with sample data, so things are always a little strange. And with that, we walked through how to create this time summary out of Cosmolex, and it looks like I've successfully finished this before the vacuum cleaners turned on. Thanks. Bye-bye.
