Commercial Electrical Project Analysis

Project Summary:

This project analyzes the performance of three commercial projects: Avid, Clewiston, and The Manor, using raw financial data from QuickBooks. The goal is to identify why one of these jobs performed poorly by using expense and margin analysis to draw comparisons and provide actionable recommendations. 

  • Project files available on GitHub

 

Key Skills Demonstrated: 

  • Data extraction from QuickBooks (CSV export)
  • Data cleaning and transformation (Python/Jupyter, Excel)
  • Data visualization and dashboard design (Tableau)
  • Advanced calculations (subcategory cost normalization, performance metrics)
  • Exploratory data analysis and storytelling with data
  • Business problem-solving (performance analysis, financial insights)

Data Extraction:

I accessed QuickBooks Online with credentials provided by the business owner and exported relevant project data as CSV files. To ensure the analysis captured all relevant expenses, I adjusted the columns in QuickBooks and downloaded two CSV files per job.

Data Cleaning:

I used Jupyter Lab, Python and pandas to clean and prepare the data for analysis.

 

Concatenation: For each project (Avid, Clewiston, and The Manor), I  loaded two CSV files, skipped irrelevant header rows, and concatenated them into a single dataset.

This step consolidates the job data from multiple sources into one dataset per project for streamlined analysis.
Handling Missing Values: I removed rows with missing values to ensure data quality.
 
Data Type Adjustment: Numeric fields were converted appropriately, and dates were formatted as datetime to support time-based calculations.
Improve Readability and Data Consistency: Upon reviewing the account names, I noticed numeric prefixes (e.g., '51400 Job Materials Purchased') that were irrelevant to the analysis. I removed these prefixes:

Data Transformation:

Combining Labor-Related Expenses:

I grouped several accounts such as     'Wages', 'Taxes', and 'PT Casual Labor', under a single 'Labor' category to streamline the analysis:

 

Adjusting Dates for Labor Entries:                                                                   

Since paychecks are issued one week after the associated work period, I shifted all labor entry dates back by one week to reflect the actual work periods.

 

Grouping Minor Expense Accounts:                                                                         

I identified smaller, inconsistent expenses, such as 'Repairs and Maintenance' and 'Office Supplies', which did not significantly affect job performance. These accounts were grouped under 'Other' to reduce noise and allow clearer comparisons:

 

Removing Irrelevant Expenses:

For example, I removed 'Legal Fees' from the Avid project dataset, as these were incurred after the project ended and were not relevant to its performance. Similarly, I excluded employee bonuses from the Manor dataset.

Excel Workbook Creation

After cleaning and transforming the data, I converted the datasets to Excel files and combined them into a single Excel Workbook.

  • I included income information from invoices provided by the business owner and added a sheet to the workbook that breaks down income and expenses by billing period.
  • Normalized Billing Periods: Instead of using specific dates, I grouped transactions by billing periods in Excel to make comparisons across projects more meaningful.

The following is an example of code used to calculate the total expenses by category and billing period: 

The printed information was then manually entered into Excel.

 

All Jupyter Notebook files used in this project are available in the Notebooks folder on GitHub.

Incorporating Geographical Data

While working in Tableau, I realized that job location—specifically the distance from the main office—might provide additional insights into job performance. To account for this, I created an Excel spreadsheet containing the latitude and longitude of each job site along with the driving distance from the main office.

  • I also added a breakdown of subcontractor labor to explore its impact on performance.
  • All processed data files are available in the Processed Data folder on GitHub.

Analysis and Visualizations in Tableau

In the next section, I’ll present interactive Tableau visualizations that explore the relationships between expenses, job location, and performance across the three projects. These visualizations will provide a clearer picture of the factors that contributed to the success—or underperformance—of each job.

 The are three Tableau Visualizations for this analysis:

    1. Report: Summarizes key metrics across projects to provide quick insights.
    2. Exploration: Allows users to interactively explore data, identify trends, or uncover insights.
    3. Data Story: Presents a narrative-driven visualization, guiding viewers through the most significant findings.

Tableau Visualizations can also be found here at Tableau Public.

Calculations Used in Tableau Analysis

Income Calculation:  

{ FIXED [Job Name (JobsBalance)], [Billing Period]: MAX([Income]) }

Purpose: In the JobsBalance spreadsheet, income for each billing period appeared across multiple rows (one for each subcategory). This calculation ensures accurate income totals by taking the maximum income per billing period.

Expense Calculation:                                                                           

{ FIXED [Job Name (JobsBalance)], [Billing Period]: MAX([Expense]) } :

Purpose: Like the income calculation, this ensures accurate expense tracking by using the maximum expense value per billing period, avoiding duplicate calculations.

Profit (Accounting for Overhead):                                           

[Income Calculation] - [Expense Calculation] - (0.15 * [Income Calculation]) 

Purpose: This calculation subtracts a 15% overhead cost from the income, giving a more accurate view of profit after overhead expenses.

Total Job Materials Purchased:                                              

  • [Subcategory Cost] - [Vendor Credits]                                         
  • Purpose: Subtracts vendor credits (refunds for returned materials) from subcategory costs to calculate the total material expenses before any returns.

Job Margin Calculation:                                                                      

  • { FIXED [Job Name]: MAX([Margin]) }                                         
  • Purpose: Ensures that each job’s margin is accurate, preventing margin values from being repeated across rows for different expense categories.

Normalized Cost Calculation:                                                      

  • [Cost] / [Total Income] * 100                                                     
  • Purpose: Allows for comparison of expenses across jobs by normalizing them to a percentage of income. Although using total cost was considered, income-based normalization aligned better with the goal of comparing margins relative to income.

Explanation of Pivot Use:

In addition to these calculations, I used pivot tables to create new columns and restructure the data for easier analysis and visualization building.

Conclusion

This project demonstrates my ability to extract, clean, transform, and analyze financial data to solve real-world business problems. Using Python, Excel, and Tableau, I uncovered meaningful insights and provided actionable recommendations for improving project performance.

I chose this project because I enjoy empowering businesses with data-driven insights that create real impact. Helping a small business owner make informed decisions through thoughtful analysis was particularly rewarding and reaffirmed my passion for data storytelling and problem-solving.

Back to blog