Tax Firm Data Analysis

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, totaling ~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).

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).

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