Tax Firm Data Analysis

This is a summary of a project. The full details can be found on my GitHub page.

Project Goal

This project analyzes 873 open tax projects representing 4,600 hours of preparer work to optimize task assignment at a mid-sized tax firm. Power BI dashboards were developed to help the Project Manager assign more work efficiently based on employee skill, availability, and project history.

This project streamlined the reporting process, removing all manual data entry. Reduced reporting time from 2 hrs/week to 30 seconds. This allows for running the report repeatedly throughout the day for near-real-time information.

Overview and Constraints

Overview

• Previously there was no efficient way to determine who was low on work. Employees would seek out more projects on their own from the firm partners.
• This process could result in 30-120 min in lost productivity.
• The data is from roughly 10 days before the 4/15 deadline. There are 873 open tax projects, totalling ~4,600 hours to prepare.

Constraints

• Almost all tax projects have to go through the PM, who determines the "best" employee(s) to prepare each project, based on the criteria above (skill level, current workload, available hours, and work on related projects).
• The 'checked-in' date is a project constraint which determines the projects to assign first. Projects which have an older checked-in date have been waiting the longest, and need to be assigned first. (e.g. projects with a January check-in date need to be assigned before any with February or March check-in dates.)
• Some projects need to be prepared by the same employee, either because the projects belong to the same client, or are part of the same group (a subset of related clients).

The data below was taken about ten days before the busy season deadline (4/15).

Approach

With staff availability, workloads, and projects waiting to be assigned primarily determined by word of mouoth, being able to determine how to assign and balance projects was a very slow process. But, the project tracking software tool that was being used included all sorts of metrics (like project budget, project status, client groups, and assigned staff), so the first task was to surface that information in an easy-to-digest way.

After pulling the information from Excel, and later SQL, and seeing charts of how impacted each staff member was, it was easy to see at a glance who was most available for additional work. Step two then invovled comparing the client groups assigned to each project, and then look at which staff person has already done work for those groups. Again, groups are a collection of related clients. So if one of our clients owns five businesses, they may all have different names, but would all share the same group. Thus, if an employee has already done work for four of the businesses, they should probably also be assigned work for the fifth, even if they have more work assigned, in case the finances of all the businesses are interdependent. This was done with a simple COUNTIF function, to see how many times a client group shows up on each employee's list, and sorting out any conflicts where work had been split among multiple employees.

Step three involved looking at the check-in date of each project that a staff person was working on, and comparing it to the check-in date of projects that are waiting to be assigned. This mitigated assigning work to one person when another person might get to it sooner (even if that second person had more work currently assigned to them). As an example, if a project is checked-in at the start of March, and one employee is working on a project from mid-February, another employee who is working on items in March may be a more attractive recipient, even if they have more overall work; The employee working in February will need to clear all February work before they can begin anything from March, so any new projects may be delayed.

Data Model

The data model below comes from the various tax software used within the office.

• Employees - A unique list of all employees
• Projects - All details about the client tax work (referred to as projects) within the firm, including the client name, client number, and whether the client is in a group. Also contains project details like the due date, the type of project, the roles within each project (a preparer and one or more reviewers), the hours assigned to each role, the project status, and when the project was "checked-in" and ready to be assigned (the date_received).

Results and Recommendations

• Prioritize extensions to get through the 873 projects and 4,600 hours of work over the next ten days.
• Direct the associates to prioritize preparing both intern projects and intern project extensions.
• Direct the associates to focus on extensions. They can get through projects quickly, and most of the work needs to be extended.
• Prioritize assigning work to Lucca Leon and Lucca Rush until they reach their target hours (25 and 50 hours, respectively).
• Reallocate projects from London Moran once large projects are in progress to prevent hidden bottlenecks.

Gallery

This is a summary of a project. The full details can be found on my GitHub page.