Paul W. Carlson, CPA (00:01):
In our prior Clio matter budget video, we discussed how the Clio matter budgets only appear within the matter summary screen, and that there's not a way to create a management report for matter budgets. So, in this video, we're going to talk about how we made this report where we can see a quick summary of open matters, their budgets, and remaining budget.
(00:30):
So the first, I don't know, half dozen columns are all from a matter export. So what that looks like is we go to Clio, we go to Open Matters. And we use the columns filter to change the columns that are available on the screen. So we turn off a couple fields we do not need, and then we add in that custom budget field that we created. And from here, we just push this to Excel, and we can copy and paste this into Excel. And then, we will move Responsible Attorney to the left. We will slide Client Name to the left, and then we will sort Responsible Attorney, Client Name, and Client, and Matter Number. So that picks up the majority of the report.
(01:25):
Next is we need to know the amount that's been billed and the unbilled amount or the WIP amount for each matter. So where that comes from is we go to the revenue report, and we push the revenue report for all dates to Excel. And so, what that looks like is this report and it's just a big mess. So one thing we need to do is we need to slide the Matter Number all the way to the left, and that's to facilitate the VLOOKUP process.
(02:03):
The next step we need to do is we want to add an Unbilled column, which is a sum of the unbilled time and unbilled expenses. And we want to create a billed column, which is a sum of the billed time and the billed expenses. So then what we do is we use a VLOOKUP, which is a fascinating Excel formula. So what that says is we want to look up, we want to find this value from column C2 over in the other spreadsheet. And when we find an exact match, give us whatever result is in the 22nd column to the right and report that number here. So what that does is it tells Excel to find Matter 34 in this other sheet, and then return the billed value into this cell.
(02:56):
We do the same for the unbilled. And then for the remaining budget is just a formula that it's budget minus billed minus unbilled, and then percentage of budget is just remaining divided by total budget. And then, we can add the conditional formatting. And so, now that we have all this detail put together in one place, we stitch it together. And then, we can use this as a management report. For some firms, we will create this. Other firms have Excel gurus on staff who can watch this video and put this together.
(03:30):
So let us know if there's anything we can help with. Thanks. Bye.
