(833) 272-6221

Overview

Client: Division Controller at a large public company overseeing hundreds of millions in annual revenue across numerous contracts. Needed a faster, more reliable way to monitor close progress, spot anomalies, answer ad-hoc questions, and deep-dive into segments and projects.

Situation

Traditional close reviews were slowed by fragmented ERP reports and static spreadsheets. Leaders needed trend analysis, variance explanations (Actual vs Budget/Forecast), and instant drill-down into the drivers of each number without waiting on IT or re-running multiple reports.

Objectives
  • Provide a single workbook with month-by-month, account-by-account trends.
  • Enable double-click drill-through from summary balances to detailed transactions.
  • Support ad-hoc slicing by business segment, project, cost center, and attribute filters.
  • Compare Actual vs Budget/Forecast at detailed account/project levels.
Approach
  • Excel Front-End: Power Pivot/DAX measures for trends & variances; VBA for one-click refresh and event-driven “double-click to pivot.”
  • Power Query + SQL: On-demand extracts from the ERP; standardized chart of accounts, entity/project mappings, and date/calendar tables.
  • Drill-Down Architecture: Summary sheet feeds a dynamic Pivot Table that expands to transaction-level detail with full attribute filters.
  • Quality Controls: Reconciliation checks, exception flags (e.g., missing COA mappings), and audit trails for each refresh.
Close Faster

Hours saved each month

| Accuracy

Anomaly & mapping checks

Self-Service

Rapid ad-hoc analysis

Tech stack: Excel (Power Query, Power Pivot/DAX, VBA), SQL (views/procs), ERP connectors, calendar/COA mapping tables.

Process Flow

From ERP to transaction-level insight in a few clicks.

Source System

Transform & Model

Business Logic

Outputs

  • ERP Extracts) → GL, Projects, COA, Budget/Forecast
  • Power Query → SQL Normalize schemas & mappings
  • Modeling (DAX) → Trends, variances, period comps
  • Excel UX (VBA) → Double-click to Pivot (detail)
  • Analysis Views → Segment/project & account drill-downs
  • Controls → Supports subledger to GL and Financials recon
Highlights
  • One workbook becomes the Controller’s “single pane of glass.”
  • Double-click any balance to generate a targeted Pivot with all detail.
  • Actual vs Budget/Forecast at the granularity leaders expect.

Implementation Notes

  • Supports multiple entities and fiscal years.
  • Handles very large GL tables with query folding & parameterized pulls.
Contact