project deep dive
Automated Daily Operations
Dashboard — Excel & SQL
A self-refreshing reporting system that replaced sporadic manual reports with daily automated visibility
across production, WIP, financials and operational efficiency — reducing the monthly closing cycle
from 12 days to 4. Still in active production use.
2022 – Present
BI Analyst & Automation Architect
Quadgraphics Colombia
67% faster monthly close
Live in production
impact at a glance
12→4
days to close (days)
daily
auto-refresh cadence
before & after
before
- No daily visibility — management saw numbers only when someone prepared them
- Data scattered across SQL, Excel files from each area, machine exports and web feeds
- Monthly close required 12 days of manual report and presentation production
- Errors in manual consolidation caused rework and delayed decisions
- Every report was a one-off effort — no reusable infrastructure
→
after
- Automatic daily refresh — management opens the file and data is current
- All sources consolidated into one structured reporting layer automatically
- Monthly close reports and presentations generated instantly from accumulated daily data
- No manual consolidation, no reconciliation errors
- System evolved over years — still in active production use
data sources consolidated
🗄️
ERP / SQL Database
Direct ODBC connection to the company's ERP — orders, costs, production records pulled live.
📊
Area Excel Files
Spreadsheets maintained by other departments — linked and pulled automatically via Power Query.
🏭
Machine Exports
Production equipment output files — press, folding and binding machine reports ingested daily.
🌐
Web Feeds
External data sources — rates, indices or reference data fetched automatically on refresh.
↓ all sources → single consolidated reporting layer
WIP calculation engine
production stages tracked per client order
Paper Conversion
→
Printing (20% today)
→
Folding
→
Binding
→
Packaging
→
Delivered
what gets calculated for each stage completion
% stage completion → value
Daily % × order total = WIP value accrued
raw material assignment
Materials consumed per stage allocated to order cost
third-party costs
Outsourced process costs (e.g. binding) logged and attributed
freight & logistics
Inbound and outbound freight costs tracked per order
KPIs tracked daily
production
Output vs. Monthly Plan
Daily production units measured against monthly targets with real-time % completion and projected end-of-month — allowing management to identify shortfalls days ahead.
WIP
Work-in-Progress Valuation
Every active client order valued daily based on production stage completion. Materials, third-party costs and freight attributed automatically as each stage advances.
financials
Costs, Margins & Daily P&L
Cost of goods, gross margin and daily financial position updated automatically — replacing weekly or monthly spreadsheet-based financial reviews.
efficiency
Operational Performance
Machine uptime, waste rates and production stops tracked against benchmarks. Deviations surfaced immediately rather than discovered at month-end.
how it was built
01 / connectivity
ODBC + Power Query
Direct ODBC connection to SQL for ERP data. Power Query handles all other sources — Excel files, machine exports and web feeds — with a single click refresh.
02 / automation
VBA Macros
Macros automate the full refresh sequence: source connections, data cleaning, KPI recalculation and report formatting — triggered on file open or on demand.
03 / calculation layer
Array Formulas (no pivot tables)
All aggregations and cross-source calculations built with matrix/array formulas — chosen deliberately over pivot tables for stability, auditability and performance at scale.
04 / linking
Cross-Book Formula Links
Advanced formula links pull live data from other workbooks — enabling area-specific files to feed the central dashboard without manual copy-paste.
05 / closing logic
Month-End Auto-Aggregation
Built-in end-of-month logic automatically aggregates all daily entries, sorts and structures the data for the closing report — the output that used to take 8+ days to produce manually.
06 / visualization
Power BI Layer
Executive-facing Power BI dashboards built on the same data infrastructure using DAX and Power Query — providing interactive visualizations on top of the same source of truth.
monthly closing: 12 days → 4 days
time spent on closing reports & presentations
before
12 days of manual report production
12 days
The 8 days saved came specifically from report and presentation production for the monthly close —
the most time-intensive manual task, now fully generated from accumulated daily data
that the system had been building automatically throughout the month.
results
67% reduction in monthly closing cycle — 12 days to 4, with closing reports generated automatically from daily accumulated data.
Daily operational visibility where none previously existed — management went from sporadic reports to opening a file every morning with current numbers.
WIP valued accurately every day — each client order's financial position tracked in real time across all production stages, materials and third-party costs.
Four live data sources consolidated automatically — SQL/ERP, area Excel files, machine exports and web feeds, with no manual intervention.
Still in active production use — the system has evolved over years and continues to support Quadgraphics' operations and reporting cycles.
key takeaway
This wasn't a dashboard project — it was an operational intelligence infrastructure built incrementally over years.
The deliberate choice to use array formulas instead of pivot tables, to connect directly to SQL via ODBC,
and to encode the month-end closing logic into the daily data structure
reflects a deeper understanding of how reporting systems fail in practice.
The result is a tool management trusts enough to use every day — which is the only metric that matters.