Make Bill in Excel for Legal Use Effortlessly

Make bill in excel for Legal

hi everyone this is Nick koushik I'm the director of standards for the Leeds organization and I wanted to explain the Leeds ninety-eight beat ifile and if you show you how you can create one using Excel let's go to the Leeds website first and on the Leeds website you can download under lead standards data exchange formats e-billing formats the Leeds 98 B format bi format did a previous video of the Leeds 90 B format so we can first get the format specification which is an excel file which will open up and we can also take a look at a sample file which displays on your screen what I probably recommend is just copying here the file so I'll do a ctrl C copy and we will save the file to notepad and then paste it in so we have the Leeds 98b file and as you can see it's a little hard to read so what we're gonna do is import it into Excel and so we will save this first and we'll save this as Leeds 98b I - V - sample it's the second version of the be high format specification and will also open up the specification document which has all the field definitions so move this over to this side of the screen and then what we can do is we can do a file open here and we can locate that file we need to choose all files here and here's the file that we just imported an loaded or created and what it's going to give us is a import wizard we want to choose delimited leave that choice there our data does have headers so we want to click that and then we want to check this and put in a vertical bar or pipe symbol here which will make all of the data arranged properly then leave everything as general strings and finish our import and now what we have is we have our specification our our leads 90 be sample file here which we'll review first so the first line must contain this string it's leads 90 bi version 2 and each line in a leads 90 file must end with two square brackets including the first line next line has all the field names and what I like to do is if I click here and the top left and then double click on one of the dividers the column dividers you can widen all of the columns automatically to match the widest data in that column so we see the column names on the second line and then we have some data now let's go over a field I filled the different fields and the leads that I need be I file first we have the invoice date all dates must be a year four-digit year two-digit month and then two-digit day with leading zeros on the month and day and then we have the invoice number now notice a leads file leads 98 file can have multiple invoices so this the first five lines here make up one invoice and then this this last line here makes up a second invoice now most a billing systems and corporations ask that you only submit one leads fought one invoice per leads file so you might not see this too often but in the sample that we have on a leads organization website we show an example where you can actually have multiple invoices in one leads file but that's not too common so we'll just focus on this set of fields here primarily so this is the invoice number matching over here then we have the client ID and the matter ID for the law firm so this is law firm's client number for the client the corporation typically and the matter number at the law firm sometimes this client number has to match something within the e billing system so sometimes that's the tax ID of the coorporate of the corporation or some other number that must be used for the e billing system but a lot of times it can just be the law firms client and then matter number for the matter the invoice is in regards to then and the law firm assigned matter number multiple matter IDs can be billed in the same invoice so that's an important note there when you just move that over a little bit so we can see the full description there and that's a again different billing systems and different clients might have a different opinion on whether you can bill against multiple matters in one invoice but the specification does allow it next we have the invoice amount again notice this separate line here which is a separate invoice has a different invoice amount but this is repeated the invoice amount which is the in noise total here or the total invoice and there should be no commas or dollar signs in this data with up to two decimal places for your invoice total next we have the billing start date and billing end date for the invoice again that's your month day then we come to the invoice and scroll over a little bit the invoice description again it's repeated notice many of these fields or data here gets simply are repeated for each line so this first set of fields here is repeated then we come across the invoice line item number and each line item in this file must have a unique line item number doesn't have to start at one but the convention is it usually does start at one it just has to be a unique number in a Jew number for each line number then we get let's move our spreadsheet here we have line number and then we have the whether it is a fee whether the line is a fee item or an expense item so these are the fees first three are fee items and then these two lines are expense items you can also have invoice level fees or invoice level expenses with I II and I F this is all described here in the expense fee I feel it's two character field then we come to the item unit number or a number of units so for fees this would be the number of hours billed with support for decimal support for expenses it would be the number of units of that expense type so if there was copies then that would be the number of these things like that this is typically in powers that's defined here okay for the units then we have now it's a little confusing but you typically would have units and then you have the unit amount but the unit amount is way over to the right all the way over here so again for the first three items are fee items and that's the hourly rate for the fees and then these is the unit amount of the two expense items on the invoice so I'm just jumping over there to sort of try to understand the use of units the unit amount and then this is the adjustment amount if there's any line item adjustment on the entry a negative value is a discount for alignment adjustments and then we have the line item total which is simply the units times the unit amount plus any line item adjustment should equal the total again these should not have the dollar signs or commas but can be up to two decimal places actually up to four decimal places in the specification but obviously rounded to when calculating a payment this is the line item date so it'd be the date of the line item and then for fees the task is required for fee items so we have the tasks and the activity code the activity is also required so the you to be best billing tasks and activities are required for fee items and then the expense code is required for the expense items now those can be located our leads website you can find the activity codes here the expense codes here so here you can see the examples of some activity codes and if we come back we can look at the expense codes here and then this jumps you over to the YouTube EMS comm website which has a broader collection of codes so again the task and activity codes for fee items and the expense codes here for expense items moving on we then for the fee items we want to identify the time keeper ID and this is a unique number that the law firm assigns to each time keeper that number needs to be transmitted to the corporation of the course with the time keepers name and time papers classification now with leads 98 be I really all the fields we've seen so far are matching the youths 90 B file but going over in regards to the time keeper data we have two fields here we have the time keeper last name and first name separated as two separate fields in the BI file whereas in the in the original B file we just had the time keepers name which would typically be first space last or last comma first but coming back this is the time keeper ID which the again the law firm is assigning this is your line item description can be up to 15,000 characters a lot of times it's a lot shorter than that but this is your line item description for each line item again these are the fee line items and these are the two expense line items this is the law firm ID which will typically be the law firm's tax ID but it could be some other ID you just have to understand what the client or the corporation wants as he wants to be you wants you to use as your law firm ID a that's usually transmitted and agreed upon with the corporation we talked about the unit number comes comes next then we have the timekeeper ID which we saw already and the time cares timekeeper classification if we look at the leads org website we can see the timekeepers or timekeeper classifications listed here so if we select that and then open up this link you can see an example of the current timekeeper classifications and the codes you'd be using this first column code and you know you have clearly Associate Partner I'm sure is in here paralegal various different time keeper classifications that would only be appropriate for the feed line items then we have one of the most important fields in the leads file which is the clients matter ID so this is the corporation or the not the law firms matter I D it's the clients matter I D a lot of times this would be in a format of like year - some type of sequential number but whatever they've identified however that the corporation identifies the matter must be recorded here and this is pretty important because the corporation is going to understand what matter your billing against by this ID now starting with column Y here these are the new fields that the liens ninety beat I for international information have added so first we have a let's scroll down here so here's the PIO number it's a purchase order number if a purchase turner happens to be used clearly optional clients tax ID that would be the we had the law firms tax ID and this would be the clients tax ID and then we have the matter name which should be let's see what we state here the name of the matter to which the invoice relates this field is is provided for the information only and must be not be used by the receiving application as a primary key for the matter so a lot of times that could be the law firms matter name which may not match the corporations matter name that's what's most important for the corporation understand what matter it is is the client matter ID okay but this is a the name of the matter the invoice tax total would be the total tax on the invoice completely and a big part of the additional fields which we'll go over now for bi4 international invoices are tax fields which we'll get into more and more so this is the net total which the net total basically is the total of the invoice plus the tax so it's the line-item total value of this - the invoice total tax so that is the net total the invoice currency is the iso currency code for the currency being used on the invoice we then have both the timekeepers lastname and timekeepers first-name as two separate fields in the leads original leads 98b file we just had one field for the time keepers name and now this separates the first and last name we have the account type which is listed here and it's either o for owner account or t for third party account the law firm name and law firm address should be repeated here for each line two lines of address and then city state and zip or postal code for the law firm and also in the law firm country we then have the clients name typically the corporation's name two lines of address city state and zip and country and then we have the tax rate for that line item so notice that the fees are being taxed at 15% and this would be the percent as a decimal let's just scroll down to show you that and it can be this actually is being changed as we speak to support many more digits for tax rates it's currently four decimal places and I think we're gonna go to ten decimal places for the line item tax rate that's not officially out yet but that's coming then we have the line item tax toll which would be typically the line item total times the tax so let's see to confirm that it is the line item total with wide item total is the line out of unit cost times the number of units plus any adjustments and then we which is the line item total and then we multiply it by the tax rate to get the line item tax total the tax type a very important field which identifies the type of tax that this is and the options are that GST PST or other we have the invoice reported tax total which is the total tax on the invoice right here and then last but not least we have the currency of the tax so both the currency of the tax and the currency of the invoice which we saw back here can be different but we have two separate fields so this is the invoice currency and the tax currency so that makes up all of the fields in a leads 98 file also notice in the very last field the data must end with a bracket because every line even including the line of field names must have the two square brackets to finish so now that we've reviewed an invoice let's create a new sample invoice so when we get rid of this last line and then we can use the fields that already exist here to make up our own invoice so we'll say like 20 [Music] 18:09 28 which is today will be the date of the invoice copy that down our invoice number will be 1 2 3 4 5 gee let's say and our client number let's just make up client number and it'll be 101 and our matter number for that client will be 1,001 so these again are the law firm identifications for the client and the matter or total invoice will just say is a thousand dollars and you don't need the decimal places if it's a zero our start date will be the month of August 2018 so 2018 August is o 8 of 1 for a start date and the end date will be 2018 Oh 831 and again notice all of these fields are here work done back in the matter will be our description for the invoice will use sequential numbers let's say we have five lines of line items for this invoice and we'll stay with three feet items and two expense items but again you could add additional lines here if you had multiple fees and/or multiple expense items let's say we did four hours of work on the first line item five hours on the second and a half an hour on the third and then we had one unit of expense for the first item and maybe two units for the second expense item we will have let's say $100 discount on the first fee item no other discounts on the other items now our total should really be an equation so and say it equals the unit amount times the unit cost and then plus any adjustment which is the column here so that's how we get to 1300 the line item unit costs and these are the hourly rates for the three fee items so let's say we charge $400 for the first attorney and 250 for the second attorney and our attorneys are Bill Jones and attorneys billing the phone the first two feet items and the third fee item will be Jane Smith and their who can keep these classifications and we'll come back to that but Bill Jones you know the timekeepers first and last names can be repeated over here so bill Jones for the first two line items and James Smith and by the way this adjustment is on the unit amount but on the on the total it's not an adjustment on the power he reaped so the hourly rate I think was four hundred dollars now we're not billing all four hours at $300 an hour we were billing for hours at $400 an hour and then giving $100 adjustment for that one line item now the total of the invoice could be calculated here for the invoice total so that is now our invoice table which we would want to place the value of that over here because we had the invoice total right here and I like to using this paste special where we can paste the value there that should be our invoice total now actually that may change if we change our expense items so our expenses you know what we'll leave our expense items alone this is the total that the two line items for expenses were these two amounts we had I think two units of this and one unit of that that's why the total actually yeah yeah so one unit of that two units of the last item which was that out-of-town traveling as they travel twice for example you know we'll change that so the first one was one unit for the meals and this would be FedEx charges or something and we had two FedEx items each at that same amount now the expense codes I don't know what are some valid expense codes but these should be your expense codes and activity codes for those fee and line items again let's maybe use 1001 and 1002 for time keepers bill Jones and James Smith these are the these would be the law firm identified IDs next we want to put in the line item dates of service so the first three items are for the fee items so let's say this is 2018 August 4th for that line item 2018 Oh 8:05 the next line item and this last fee item would be 2018 oh these are all August and then these two items are for the fee expenses so these expenses let's say happened on 20 1808 August 20th and 2018 Oh 821 21st of August so then we have the UTS task codes here are the activity codes for the fee items and then the expense codes for the expense items those you just need to look up I showed you where they can be found we went over the time keeper IDs already now the line item descriptions sometimes they match the tasks and activity codes sometimes they're just free form proofy items they would be the line item detail about the description of the work done and then these would be the types of expenses here I'll just leave those there that's fine then we come to the law firm tax ID this should be the law firm's tax ID so let's say it's one two one two three the same this was that unit cost which we went over these are the attorneys you can put in an attorney and/or timekeeper for expense items if you'd like you don't have to but you can tell and whenever you fill in that time people name here you want to make sure you fill in your ID on that alone had to match so if you're gonna use timekeepers here for your expense items make sure you put your timekeeper IDs here this is the corporations matter number so a lot of corporations would do something like maybe that could be their matter number you'd have to ask the law of the corporation your client for what matter number they use for the matter that's a this is a very important field then we have a purchase order I'm pretty sure purchase orders are optional and they are so I'm gonna just clear this out because I don't think we would have a purchase order clients tax ID this is the law firm but no the corporation's tax ID not the law firms tax ID so let's say that's 77 - number this is the matter name and let's say it's the Acme matter this is the invoice tax total for the invoice and we need to do some calculations on that so if we look at the different fields the some of the line item tax totals so we first have to get the line extols so and the line-item net total is the sum of all line-item totals - any total tax so let's move over and we'll say our currency is a u.s. dollar again the you can search for the iso country codes currency codes and probably find on google the various currency codes these were the time people names that we put in already and our count type is all gonna be for the owner own account it's not a third party around billing the third party work this is the law firm law offices Janaki Jones let's say copy that down a lot of lines just have repeated data and it seems like a waste but one Main Street but you basically want to fill in the address city state and postal code this is the clients name so we'll make that I can be incorporated and the address I'm not going to update that but this would simply be the address and then we come to the tax rate so let's say for London this London law firm we have a 10% tax which would be representative is that the items and the line item total which is going to be right here is simply the unit cost times unit amount plus adjustments which we did an equation which is the line item total then times that tax so we can simply say this column equals the tax rate times and if we scroll all the way back to the line item total right here that should be the amount of tax for these different line items okay and then we want to sum this up and that's going to be our total tax on the invoice so copy that and go back to the invoice tax total which is here and again I like I like using this paste value okay so a lot of these lines aren't in a lot of these fields aren't in reference to the specific fee or expense item it's really a overall value for the invoice and then the net invoice net total to make sure we get this right which is back here the net total is the sum of the all the line item totals in the invoice minus the invoice tax total okay and the invoice tax total was the sum of the line item tax tunnels which we just summed up so our net invoice is [Music] the sum sum of the line item totals which is way back here and then they said - whoops the talks actually I think it's just this value so just it's so copy that okay so again the invoice net total is was the sum of the line item totals in the invoice minus the invoice tax total so I believe that is our leads 98b by file we have a invoice totaled here which is the sum of all the line items right here then we had which was 42 28 and then we had at the very end here we had these additional tax items we need an additional tax of that amount that was then added we didn't get to this last one which is the this is the type of tax we can leave that there and then we have the invoice reported tax total the amount of the text in the national currency is reported to the tax authority so this is the amount reported to the tax authority I've got to believe that's going to be the same as our total invoice tax which was right here it's just the amount that ported which in most cases which is probably the same and then this is the invoice tax currency probably want to make our currencies all the same so this would be that great Birdman pound and leave GP GP GBP so we'll leave this currency GBP for the invoice and that's the currency for the invoice and for some reason we also have a currency for the tax which is there so that is a leads 98 bi file and I'm gonna get rid of these little some fields at the bottom we have to get this file this data out into a text file and there are a variety of ways if we simply just save this file as a CSV or as a text file okay and we'll typically labels this like invoice one two three four we will see that the file has tabs and we have to replace all of these tabs with vertical bars so the way you can do that is simply highlight one of the tabs do control C or edit copy and then put your cursor on the very first character first line and go to replace and then you can do control V here to paste that tab in because you can't just put a tab in there and then put in a vertical bar pipe symbol and say replace all and you'll get some extra pipes on the first line you also may get some extra lines with pipes make sure you don't have an extra blank line so there you see how my cursors blinking there so hit backspace and then just double check that each line ends with a bracket including that long line with labels and that is a legitimate leads no idea B files should be a text file and again make sure this is on the first line and we can save this we have a legitimate leads not a B file thanks everyone take care of bummer