Paul W. Carlson, CPA (00:00):
Hi, this is Paul Carlson, CPA with Law Firm Velocity. In this video, we're talking about how to get weekly billable dollar amounts by timekeeper from Clio.
In working with hourly and flat fee based firms for the last several years that our advanced forecast, we can nail down all firm expenses. The thing that's been a puzzle until we found this was getting a better control over revenue. A lot of firms like to report revenue by practice area, which is murky. That what we've started to do is we report revenue on the face of the income statement as revenue by timekeeper, which is tremendous because you can look at the income statement, and you can see who generated all collected fees for the month. And from that you can see the history over time to see who's producing within the firm and who's struggling. That clarity, when we do this on turnarounds, we'll find where a paralegal has gone on strike, and just stopped billing. Or we're in a billing process that people are putting in their billable goals, but when it comes down to collections, that all the times getting written off. And so, the collected numbers are low.
So once we have that put together on the income statement, then we want a measure where we can see every week are we making our progress towards our collections goals? And so, what we've developed is this weekly billable dollars scorecard where we help the firm set up goals for their weekly collections and then, we track billable dollars by timekeeper. So billable dollars is the amount of time entered into the system that there's a lot that can happen between entering the time and then, actually collecting the fees. But this gives us a measure if we're on track or not.
So the trick is to update this every Monday, and give the managing partner feedback on if anyone needs extra attention. The trouble with doing this every week is there's always a couple timekeepers who put in their time late. So this turns out that you're often having to update this week and the prior week every week as the numbers will move a bit. So we needed a way to get these numbers quickly out of Clio, grow for a couple weeks, and then if we're going to have a dozen timekeepers that we needed a good process.
So where we start, the simple way to do this is you do time, you put in your date range. So we like to use a Saturday to Friday time period because that way when you run the reports on Monday morning, everyone should be in here. So, this is sample data, I don't have anything current, so I have to use this weird date range.
So in this window we can see down here that there's $41,000 of billable time for my name. So you could do this, and then you go up to the filter and then, you could pick the next person, and run the number for that person and then do it again for another person. That if the firm only has a couple timekeepers and the numbers don't move around too much, this isn't so bad. But once we get to a dozen people and we're getting hundreds of rows, we needed a better process. So what we've found is we set our date range to the last week. In the filters, we clear the filter so I hit the X to take a name out of here. So it's not going to filter and we should see all timekeepers. So here, we see time entries for everyone. And this works especially well when the firm has co-counsel within Clio, because you can't pick co-counsels from this list. They're hidden, that the only way to find co-counsel is to do this trick and show everyone.
So what we do is we get the report of everyone for our time window and we export this to Excel as a CSV. And let's just do the visible columns, we want to see everything. Okay, so this is going to make a Excel file for me to down...
All right, so I've opened the downloaded Excel CSV file. And so now, let's do a couple steps within this data so we can get subtotals for individual timekeepers because right now this is a note. It's just a flat file with the detailed time entries that there's no totals in here. So the first step we want to do is... So we want User, we want Billable Dollars and we want Hours. So I'm going to hide these columns and then highlight what's left. Do data. First step is to sort this buy user. And so now, we have the usernames in order and the magic happens with subtotal. We're going to tell it that each change in user, we want Excel to add a sum two hours, and billable dollars and uncheck that box and click OK.
And then if we go over here and we click this second box, and now we have billable dollars by timekeeper. So this column gives us total hours. And this column gives us billable dollars. And we can use these values to go fill out our Google sheet. And so, if you want to see the detail behind a person, you can hit the plus sign, and we see the one entry in Associate A. We can see all the time entries behind my name. All right, we click that and it collapses everything back up again.
All right with that, that's how we can calculate weekly billable dollars based on Clio data. When you do this, you probably want to run this for the prior week and then two weeks ago, and then update the detail back in this weekly billable dollar scorecard and we can track our progress towards monthly revenue goals. Thanks. Bye-Bye.