Data visualization of legal fees, billing, and amounts left in trust

I created this interactive report using Excel and Power BI to show one possible solution for attorneys who want to see information about their clients, the legal fees they incur, and other information about their case. Although this data is hypothetical, a report like this could be beneficial to the attorney and law firm.

When viewing this report on this website, you can click on the “Fit to page” button located on the bottom right side of the report, zoom in and out of it, and click anywhere in the report to see how the data changes.

In most lawsuits, a client pays an attorney a large dollar amount, called a retainer, at the start of the case, and this money is put into a trust bank account. Attorneys then bill the client for the work done on their case. But attorneys that handle client cases are often one-step removed from the amount a client has left in the trust account. They may not handle the billing and collection of finances from the client, someone else in the firm may do this.

But an attorney may want to know how much is left in the trust account to answer questions of what next steps to take in the case. The report I’ve created can quickly answer many questions the attorney has relating to this.

This report is interactive, and an attorney can click through it to see what legal activities are being performed for the client each month, how much they total, when a trust account has a balance less than a certain amount and should be replenished, when a retainer is paid and more.

I like giving short directions with my Power BI reports because I want the user to understand how to use the visual in order to get the information they want quickly. I also make my reports clean in terms of design; although I appreciate art, data visualizations should be useful and easy to use. Additionally, I like reports that have contact information showing how the user can get help or ask questions about the report. As an attorney myself, I know that getting the information I need quickly is very important.

Power BI is such a great software to use for creating interactive reports, however, the default design elements are not wonderful. Having taught design when I was a professor, I know that color choices can communicate certain information.

For this report, I wanted the colors to mean certain things. So in the table that shows amounts left in trust, blue numbers mean a client has more than $2000 in their trust account. Red numbers mean the trust account balance is getting low, and the attorney may need to ask for another retainer, change how the case is being handled, etc.

For the bar and pie charts, I wanted to group certain legal work together. Since I’ve practiced law, I know that Court Appearances and Travel are billed on the same day. And things like Discovery, Research, and Depositions are similar types of legal work. Whenever I’ve communicated with a client or opposing counsel, I needed to review the file and often did drafting work to motions after our discussions.

So I went to Adobe’s Color Wheel (available here: https://color.adobe.com/create/color-wheel) and selected a double split complementary color scheme. Then I opened the pallete in Adobe Photoshop and and added additional colors there. The result was this color palette:

color pallette

I really enjoyed building this report. If you have any questions about it, or the process, feel free to email me at sarakubikphd@gmail.com

U.S. Law Firms’ Business Patterns

I analyzed data collected from the U.S. Census for the years 2017 through 2020 that measures business patterns of U.S. law firms.

Areas of practice include corporate law, family law, estate planning, patent attorneys, legal aid services, real estate law, tax law and others. The data does not include public defenders, prosecutors, US attorneys, or attorney generals.

I used Power BI to clean, transform, and analyze the data. After reviewing the data, I created charts, plots, tables and other visualizations and built an interactive report. Click on the different parts in the report below to see how the data changes.

What can you conclude about this data?

  1. Most law firms in the U.S. have between one and four employees and are S-corporations. This reflects something unique in terms of how most law firms are really tiny in terms of the number of people working for the firm. In fact, the Small Business Association defines “small businesses” as having 250-1500 employees, depending upon the industry! Most small business law firms in the U.S. have very few people who shoulder all of the business and legal responsibilities. They are truly multi-taskers.
  2. The total number of law firms has decreased every year. By clicking on each of the years, we can see that the total number of law firms went from 173,019 in 2017 to 169,582 in 2020. And this data does not include the effect the pandemic had on the existence of U.S. law firms.

However, if we look at the changes in the total number of firms by employee size, our calculations show that the total number of firms with one to 19 employees decreased from 2017 to 2020, while total number of firms with 20 to over a thousand employees increased in that same time period.

20172020Changes
All firms173,019169,5822.0% decrease
1 – 4 employees127,001123,8322.5% decrease
5 – 9 employees25,29824,8411.8% decrease
10 – 1911,74211,6460.8% decrease
20 – 496,1556,3112.5% increase
50 – 991,7591,8535.3% increase
100 – 2498258543.5% increase
250 – 4991851871.1% increase
500 – 99945462.2% increase
1000 +91233.3% increase

We can see the change in number of law firms visually as well, although it is slight. The charts below is interactive and shows changes in the number of law firms from 2017 through 2020. You can click on it to see more information and also hover over each vertical year area to see tool-tips that show the exact number of law firms with various employee sizes.

Since I broke apart the number of employees, I decided to do the same for type of organization. What organizational type increased in number from 2017 to 2020? What decreased? Below are the answers.

Right off the bat we can see that S-corporations increased in numbers from 2017-2020, while it appears the other business organization decreased or remained about the same. Just to make sure, I ran the following calculations…

20172020Changes
S-corporation 83,40388,6276.3% increase
Sole proprietorships39,99034,07914.8% decrease
Partnerships28,14226,8534.6% decrease
C-corporation19,24017,7397.8% decrease
Non-profit2,2262,2350.4% increase
Other noncorporate2352126.1% increase

Although the large percentage increase in “Other noncorporate” is unique, remember there are very few law firms that are categorized as this versus the other traditional business types. We can add more information to the what we concluded earlier: that overall, the number of law firms has decreased from 2017 to 2020, with the largest employee size being between one to four employees. Although the number of law firms decreased, the change did not happen at the same rate across all business types. Sole proprietorships saw the biggest decrease while S-corporations actually increased in numbers.

Finally, the Census data I pulled was only available from 2017 through 2020. A LOT happened in the last 2 years, of course. How did the pandemic affect law firms? What about the forgiveable loan program called the COVID-19EIDL, which many solos and small law firms received? Did that have an effect on the business type change? All interesting things to ponder.

Re-focusing

It’s been a while since my last post. A LOT has happened since then. Most noteably, the pandemic. Like the rest of the world, my life was radically changed because of this. It was a period of triage for many of us, and that resulted in my radio silence on this blog.

But, I’m back.

What has not changed is that I’m still an Illinois-licensed attorney mainly focusing on family law. I own my own law firm and that has allowed me the incredible flexibility to be with my children. I can always work more; I can never get back the time I have with my children as they grow up.

So this has re-focused me on my career path. I will always have my law license and actually aim to get licensed in more states when I can. I love learning. The more I learn, the more I want to learn more. (LOL at my grammar but you get my point!)

One common area that I’ve always been interested in is technology. Technology changes so rapidly and that is exciting to me. Throughout my time as a lawyer, I’ve been learning about new technologies and new applications of the technologies. New development. I’ve even taken online courses to keep my skillset current and understand how I can use these tools (used by data analytics and data scientists) in combination with what I already know (stastical analysis, research, content creation, the law).

So you’ll see a change in the posts I’m going to make. They’re going to be more data-focused because that is what I’m into now. I’m exploring data analytics, artificial intelligence, machine learning, and big data. They are technical and can be complex, but I aim to be able to explain my thoughts as clearly and efficiently as possible.