Automated Monthly Report System — DataFirst
Beginner
20 min
38 views
0 solutions
Overview
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.
Case Details
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.
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.
Data Sources
https://docs.google.com/spreadsheets/d/1XYZ_example_hw05_datafirst_monthly/edit?usp=sharing
Solution Frameworks
Report Automation Framework: Data Sources → Consolidation → Calculations → Summary → Visualization → Distribution.
Error Handling Framework: Use IFERROR() in all formulas to prevent #N/A from breaking the report.
Documentation Framework: Every automated report needs a 'How to Use' section for handover.
Error Handling Framework: Use IFERROR() in all formulas to prevent #N/A from breaking the report.
Documentation Framework: Every automated report needs a 'How to Use' section for handover.
Solver Guidance & Tutorials
Organize your file with clear tab names: Consolidated, Summary, Charts, Settings.
IMPORTRANGE best practice: Store source URLs in a 'Settings' tab and reference them with cell references.
QUERY tip: Use LABEL clause to rename columns for cleaner output: LABEL SUM(A) 'Total Revenue'.
Macro recording: Do all formatting steps manually first, then record. Edit the script to add timestamp.
Apps Script bonus: Use MailApp.sendEmail() with pdf export URL parameter.
Tutorial Link: https://aplly.xyz/case-studies?search=&type=2&difficulty=Beginner&status=
IMPORTRANGE best practice: Store source URLs in a 'Settings' tab and reference them with cell references.
QUERY tip: Use LABEL clause to rename columns for cleaner output: LABEL SUM(A) 'Total Revenue'.
Macro recording: Do all formatting steps manually first, then record. Edit the script to add timestamp.
Apps Script bonus: Use MailApp.sendEmail() with pdf export URL parameter.
Tutorial Link: https://aplly.xyz/case-studies?search=&type=2&difficulty=Beginner&status=
What You'll Learn
- Problem-solving and analytical thinking
- Data-driven decision making
- Business strategy development
- Professional report writing
0
Solutions Submitted
Difficulty
Beginner
Estimated Time
20 minutes
Relevance
Fresh
Source
Google Sheets Module - Puneet Arora