Create a Billing Sheet in Excel for Legal Needs Effortlessly

Billing sheet in excel for Legal

hi everyone this is Nick pooja I'm the director for standards at the Leeds Oversight Committee and I wanted to explain the Leeds 98b file in some detail and show you how you could create one manually using Excel so the best place to start is to go to the Leeds home page which is Leeds org and on the homepage under lead standards there's data exchange formats billing formats and the first format is the Leeds 98b format when you go there you'll see that you can download a sample file and also the specifications for the file so let's take both files we'll take the specification and we'll get the sample file sample file comes as a the link is a text view of the text and so you can either just copy and paste this to a notepad or you can you might be able to right click on the link and download it as a file I'm just gonna copy and paste it and then go to my Leeds file I'll do it from scratch here we'll just say this is a sample Leeds 98b file but as you can see it's a little hard to read because the field delimiter is this pipe symbol and then it quickly gets out of orientation here so I'm going to show you how you can import this into Excel and really edit the file a lot easier so we're just gonna save that file I'm also going to open up this excel document which we downloaded which is the specification for leads 98 because we'll be using this to understand the fields and then we can just say file open and navigate to this leads file that I've just downloaded our sample so it's in my leads folder and inside here and what you need to do is a select all files and then you can double click on the text file and a wizard will show up which allows you to import the file and it is clearly a field delimited file my data has headers which is the top row and then you want to put a vertical bar or pipe symbol for the delimiter and then you'll see it quickly aligns the data nicely and then you can really just say finish you don't need to format like everything to be general so that you can edit it more easily and presto change-o we have the leads file in spreadsheet so let's go through what a leads 9db file has the first line of the leads 98b file must contain this string leads 98b and then every line of text must end with the two square brackets so you see that on the first line and then the next line is the names of the columns and what I like to do is if I click here and then double click here widen everything to be able to see the full field names the second line has the field names those names you're gonna see line up with the specification document which I opened up so invoice date invoice number client ID law firm matter ID and as we scroll across you'll see all the fields that make up the leads file ending with client matter ID and then again every line must end with those two square brackets very important so this is a sample leads file and take a look at this first and then what we can do is simply edit this file to make our own leads file and so let's go field by field the first field is invoice date and that must be in the year-month-day format with no spaces or dashes or slashes next and that's the overall invoice date then we have the invoice number it can be a string up to 20 characters document it over here then we have the client ID and that is the law firm assign client code that the law firm has assigned to that client okay so if you're a law firm that's going to be your ID for the client now that might come from the e billing system so you want to check with them to understand what the client ID is next we have the law firm's matter ID that would be the client matter number or the matter at the law firm okay so this is sort of the client matter number separated two fields and notice that in the sample invoice we actually have two different matters being billed here most leads files are for a single matter but you can enter multiple matters on one leads file and actually notice here the invoice number changed this second this last line here is defining a second invoice so the first set here of six lines or five lines mix up one invoice and then this is a second invoice here starting with this last line but again most leads file all the invoice numbers are the same it's a single many many a billing systems requiring you to submit one invoice per file so this is just this is going to be the total invoice number invoice amount that's this field here it's required again in this example we have two invoices changes but notice that many of these fields just keep repeating over and over and you're gonna see in a minute as we get further along with the fields that the data changes from line to line but a lot of these early lines like the invoice number the billing start date and end date which again is year month day the the leading zeros for days and months so this should always be the characters for dates then this is the invoice description this can be upwards fifteen hundred fifteen thousand characters but most people don't have to lengthy of an invoice description here then we get to lying numbers so this is the first field that is actually changing for each line and every line in an invoice and again this makes up one invoice and this makes up another invoice every line should have a unique lying number whether it's a fee or an expense and the number can be any number it doesn't have to start with one but it's pretty common to start with one sequentially but they just have to be unique numbers for each line in the file then we get into these let's just scroll down here we have whether the entry is a fee or an expense so f and E and then there's also an I have an ie that's all defined right here the invoice level fees or invoice level expense versus a line item a fee or a line item expense so the e and F this is a these are three line-item fee records and two expense line items this last invoice here is single entry for a invoice level fee next we have the number of units for that line item so with fees this would be the number of hours expenses it would be the number of units of that item so this is defined right here number of units next we have a any adjustment amount which would be like a discount that could be applied by the law firm for the line item so if you're discounting in this example a fee entry it can be discounted by a certain amount and then we have the line item total it's a little odd but I believe further over we're going to have the unit amount here so you don't want to rearrange these columns but it is a little odd that the unit amount so these are the hourly rates for the three fee items and then these are the unit amounts for the different expense items here okay so that's the unit amounts we'll come back to that so the line item total here is if you see over here it is the line item unit cost times the number of units plus any adjustment a negative number would be a discount and that is applied after the multiplication of the units next we have the line item date which would be the date of the fee or services and then we have for fees we have the task codes and the activity codes here and here and then for expenses we have the expense codes these are typically the UT BMS codes which can be found on the liens website also be building or the client will define what codes should be used or allowed to be used here for tasks based billing then for fees we need to have the timekeeper ID this is the unique identifier for the time keeper and we're gonna see in a minute we have the time keepers name over here okay but the time keeper ID is typically a number that identifies the time keeper every law firm must produce a set of time keepers and their IDs that they will be using and these has to be the ID name this is the classification I'll show you in a minute so here is the timekeeper ID here's the timekeeper name and then the classification it can be the list of valid timekeeper classifications can be found on the leads website under lead standards data elements and then the document is called LOC revise time keep time keeper classification codes next we have the line item description which for fees this would be the services work performed and then for expenses this would be a description of the expense then we have the law firm ID so this is typically the tax ID of the law firm here but it could be some other identifier locations this was the unit cost that we came over and noticed time keeper name the classification of the time keeper last but not least is the client matter ID and this is the identifier from the corporation so it's the client assigned matter code which you must get from the client ID over here it's the ID from the law firms perspective and then the last field at the end is the ID of that matter from the corporation's perspective okay so those numbers are usually different and this is one of the most important fields for the billing to allow the corporation to understand what matter is being billed and it must be the matter ID assigned by the corporation so those are the fields that make up a leads file and if we start editing these we'll just get rid of this last one because we simply want a single invoice matter and we'll put in 2018 let's do today which is oh nine nineteen and that's gonna be for every entry here in Boise number we'll just make up a number one two three four five six is our invoice number again I'm just going to paste down this would be the client and matter IDs at the law firm so this could be one space why don't I go 101 and then the matter [Music] would be matter 102 again I'm just making up client matter numbers for some hypothetical matter the invoice will be let's say $1,000 need to find one without dollar signs because we can't have the dollar signs in there I know what we can do if I just put in the dollar amount I think I can go two decimal places like this okay so we need to have that format for amounts with two decimal places no dollar signs billing start date end date so let's say it was for August 2018 ou812 28 31 there's 31 days in August for services rendered let's do contract a little bit of a description again you repeat so all these first set of fields here get repeated line numbers one through five will work fine and we'll just stay with three feed items and two expense I hate on this but clearly depending upon what type of line items you want to send you put F or a there you'll say we have four hours three hours and five hours of work this would be one expense of each for the expense items and let's say this one is a hundred dollars again if we want the decimal places we just used this icon to get two decimal places over let's say this is three hundred dollars so we have a $100 and a three hundred dollar expense in the invoice I've got I'm not gonna have any discounts on this invoice but this is where we could put in discounts if we wanted to apply discounts they would go in as negative and the the line item total is going to be the units times the unit cost so let's say we have the first attorney is gonna be $400 an hour and the second attorney is $300 an hour and then our unit costs here one hundred and three hundred with total two there because we just have one unit of these expenses and these the units of mass unit amounts and will make all these have those extra decimal places so we have four hundred dollars an hour for this attorney 300 for this attorney a $100 expense and a $300 spends we'll leave these expense codes I don't know exactly what they are these actually may be the expense codes descriptions for the 111 110 but we could expand here the description these do not have to be the expense code descriptions this is a free-form description area with a pretty decent length I believe for that description let's look that up just to understand it line item description can be up to 15,000 characters and lengths there can have pretty lengthy descriptions here we'll stay with these tasks and activity codes don't know exactly what they are but activity codes here the timekeeper IDs these seemed a little odd so let's say this is time keeper 1,001 1,002 oh no these are first two entries are for the same time keeper 1001 and we'll say 1002 for this time keeper and let's change their names this is going to be Smith Jack Jack Smith and this is gonna be Jones Jane Jane Jones and Jack Smith and partner associate that sounds fine these are classification IDs I'll document what your choices are for there this is clearly not gonna have to be edited so this would be like a client matter number a lot of law firms go like 2018 which is the year - oh four seven four three seven so that might be something that the corporation reviews as their matter number and that's what you would put there make sure you leave the brackets at the end there so line item dates this is the dates of service so we skipped over that let's say this was all in August so longest tenth because that's a different attorney and expenses other expense was from August 12 now we have to make sure our totals come out right so the way this total works we can actually use a formula here so if we say the total equals the line number of units times the line item cost and we could actually put in the discount if we wanted their loss line item adjustment and then if I paste that should work and then we can add again the two decimal places so that should be the line item totals and then if we add those up here we could see what our invoice total should be 4,700 which would go here is the invoice total 4700 okay so what we're doing here is showing that now this would just be like if I had a $10 let's say $100 discount that would go to 1500 this would be 46 we'd have to change this for the invoice total to 46 but we're not gonna have you know what I'll leave that there we have a hundred dollar discount on this one line item for this one attorney which is not per unit it is the total discount of a line item so notice it's not reducing his rate from 400 to 300 it's reducing the total for that line item dollars so now our totals for me 600 which we would want to adjust here we could probably put a formula in for that there we go so we don't want this piece of information but that should be a legitimate leads file now that could be submitted if the information matches up invoice date invoice number is important these numbers this might have to match a client identifier that the corporation but the most important thing to match up is their client their matter number that they want you to use for billing that matter now how do we we can't submit the spreadsheet so we have to see this as a text file and this is a little tricky but watch what we can do so I'm going to first save the file I believe since it was opened as a text file let's see put it in this directory well we'll save it once is a text file let's put these 98b for invoice one two three four five six I think don't think that it's going to look too good because it's gonna be tabbed and limited so do you want to keep this using this format yes but so we look at what was created I can open it with notepad I believe it's tab delimited and that's the problem because we need to have pipe symbols in there so one option that's available to you is to simply highlight one of the tabs you have to like drag across there copy that and then go in probably click at the top then go in to replace pace with control V that tab and then put in the vertical bar pipe symbol replace all and replace all the tabs with pipes you need to get rid of those additional pipes or vertical bars making sure each line ends with a bracket no blank lines at the bottom so make sure you have no blank lines everything ends right there and there's no spaces after and you might want to just double check that there's no spaces at the end of each line that it ends with those two brackets another alternate is I'll show you so what you can do is if you go to your control panel and go to region and language and go under advanced settings you can change the list separator from a comma to just type in a pipe symbol or vertical bar and now when we save the file save as we can save it as a CSV okay and the CSV will have the pipe symbol in it so watch when we go to look at this file now open it up with notepad it has the pipe symbols so we have some extra lines here which we can just simply get rid of and we don't want all the pipe symbols on that first line so the first line must be like that pipe symbols going across on all the lines at every line ending in the brackets and including the descriptive line and this is now a legitimate leads 98b file that can be submitted so hopefully it was a good little video on how to create a leads file thanks everyone bye-bye