Automated Monthly Report System — DataFirst

DataFirst Consulting needs a fully automated monthly reporting system that consolidates data from multiple sheets, applies calculations, generates charts, and creates a PDF-ready summary. Students combine all learned skills: IMPORTRANGE, ARRAYFORMULA, QUERY, Macros, and Apps Script to build an end-to-end automation.

CONTEXT: DataFirst Consulting's analysts spend 2-3 hours every month compiling reports from different project sheets. Build an automated system that does this in one click.

TASK 1 — DATA CONSOLIDATION: Use IMPORTRANGE to pull data from 3 project sheets (provided) into a master 'Consolidated' tab.

TASK 2 — AUTOMATED CALCULATIONS: Use ARRAYFORMULA to calculate: Total Hours, Billable Amount, Utilization %, Project Margin for all rows.

TASK 3 — EXECUTIVE SUMMARY: Create a 'Summary' tab with: Total Revenue, Total Hours, Average Utilization, Top Project by Revenue, Bottom Project by Margin — all using QUERY.

TASK 4 — VISUAL DASHBOARD: Create 3 charts: Revenue by Project (bar), Utilization Trend (line), Margin Distribution (pie).

TASK 5 — MACRO: Record a macro 'FormatReport' that: freezes header, applies formatting, adjusts column widths, updates timestamp.

BONUS: Write an Apps Script function to email the report as PDF to a specified address.