Microsoft Excel contains law firm templates that can be used by law firms, attorneys, lawyers, and anyone who practices law. In this post, we will cover the usage of the Law Firm Financial Analysis Worksheet.
Using the Law Firm Financial Analysis Worksheet
Let’s examine the structure and usage of the “Law Firm Financial Analysis Worksheet.” This workbook allows you to enter detailed financial information about your law firm, so that you can analyze the results in order to make better business decisions. You should first note that there are seven color-coded worksheets shown on the tabs at the bottom of the workbook. Information that is recorded within the “Actuals,” “Project Parameters,” “Actual Totals,” and “Monthly Billings” worksheets is then used by the charts shown within the “Billing Timeline,” “Account Billing,” and “Skill Type Billing” worksheets. Note that some sample data has been included within the data entry worksheets so that you can more easily understand how the worksheets function together.
Let’s begin by examining the “Project Parameters” worksheet. Within this worksheet you will list each project by general project type that your firm handles. The sample data is shown in cells A7 through A12. For each project type listed, you then input what percentage of the total hours of the project is performed by each general type of skilled worker, which are listed in cells B6 through G6. The percentages are then entered into the intersecting cells. For each skilled worker listed, you can then enter the billing rate into the “Blended Rate” cells in cell range B13 through G13. This rate is the average billing rate for someone with the selected skill set. Once this information has been entered, it will then be referenced by the other worksheets to determine project billing information. Note that you can also add columns and rows as needed in order to accommodate your law firm’s business needs.
The next sheet we will examine is the “Actuals” worksheet. Within this worksheet, you enter the names of the individual projects that your firm handles into the cells shown in column A within the table. Note that you should also enter the name of each project into the corresponding cells within column A on the “Actual Totals” and “Monthly Billing” worksheets, as well.
Then, back on the “Actuals” worksheet, you enter the project type for the project into the adjacent cell in column B. Note that each project type must correspond to one of the project types that you created in column A of the “Project Parameters” worksheet. You can then input the “Actual Start” and “Actual Finish” dates for the project. When you input the values into these columns, Excel will then calculate the “Duration” shown in column F. After that, enter the total number of work hours on the project into the “Actual Work” column. As you enter the total work for each project, you will see the grand total of work hours displayed at the bottom of column E.
As you complete the data entry listed, the values shown within all of the other spreadsheets will calculate themselves. This is the data that is then used for the financial analysis of your projects. If you view the “Actual Totals” worksheet, you will see the total amounts billed for each general type of skilled worker for each project. The data shown here is derived from the values entered on the “Actuals” and “Project Parameters” worksheets. You will also see the “Month of Completion” for each project, as well as grand totals for all values listed.
You can then click to the “Monthly Billings” worksheet to view the amounts billed for each project broken down by month of completion. If you click the “Billing Timeline” worksheet, you can view a line chart that displays the monthly billings for your firm. This is a chart that represents the data shown within the “Monthly Billings” worksheet.
You can click the “Account Billing” worksheet to view a chart that shows billing amounts by project. This chart is derived from the data shown within the “Actual Totals” worksheet. You can also click the “Skill Type Billing” worksheet to view a pie chart that displays total billing by general skill type. This information is taken from the “Actual Totals” worksheet. Note that you can enter your company’s name in cell A1 within the “Actuals” through “Monthly Billings” worksheets.
You can also right-click the “Your logo here” picture box at the top of those worksheets and then select the “Change picture…” command from the pop-up menu that appears in order to open a dialog box that allows you to select a picture of your company’s logo to add to these sheets, if desired.
