Financial Reporting Dashboard

Financial Reporting Dashboard

Overview

A financial reporting dashboard consolidates your three core financial statements — P&L (Income Statement), Balance Sheet, and Cash Flow — into an interactive view that management, investors, and board members can navigate without requesting custom reports from finance.

For ecommerce businesses, the most valuable feature is drill-down: the ability to see total gross margin and then click through to gross margin by product category, channel, or geography. This transforms static financials into an investigation tool.

This skill guides you through connecting your accounting system to a dashboard layer, structuring the ecommerce-specific P&L, and building drill-down reports using your platform data.

When to Use This Skill

• When your CFO or investors request monthly P&L and cash position reports
• When replacing manual spreadsheet-based financials with an automated dashboard
• When you need drill-down from consolidated totals to channel, product category, or SKU level
• When preparing for a board meeting, fundraise, or M&A process
• When your accounting system does not produce ecommerce-specific breakdowns
• When you need to reconcile revenue in your ecommerce platform against your GL

Core Instructions

Step 1: Connect your accounting system to your platform

A financial reporting dashboard must be built on your accounting system (QuickBooks, Xero, NetSuite), not on your ecommerce platform data alone. Platform revenue data diverges from GAAP financials due to recognition timing, payout lags, and adjustments.

Ecommerce platform → accounting system integrations:

A2X setup for Shopify (most common workflow): 1. Install A2X from the Shopify App Store 2. Connect A2X to your QuickBooks Online or Xero account 3. Map Shopify transaction types (sales, refunds, shipping, discounts, fees) to your chart of accounts 4. A2X creates one summary journal entry per Shopify payout period — matches what hits your bank account with the accounting entries

Step 2: Structure the ecommerce P&L

The ecommerce P&L has a specific structure that differs from a generic income statement:

INCOME STATEMENT
─────────────────────────────────────────
Gross Revenue (total selling price × units)
  - Returns & Refunds
  - Discounts & Promotions
= Net Revenue

  - Cost of Goods Sold
    Product cost (weighted average or FIFO)
    Inbound freight & duties
= Gross Profit
  Gross Margin % = Gross Profit / Net Revenue × 100

Operating Expenses
  - Fulfillment & Shipping (outbound, 3PL)
  - Marketing & Advertising (Meta, Google, TikTok, email)
  - Technology & Platform fees (Shopify, apps, SaaS)
  - Customer Service payroll
  - G&A (salaries, rent, legal, accounting)
  - Depreciation & Amortization
= Total Operating Expenses

= EBITDA (Net Revenue - COGS - OpEx + D&A)
= EBIT  (Net Revenue - COGS - OpEx)

  Interest income / expense
  FX gains/losses
= EBT
  Income tax
= Net Income

Set up chart of accounts in QuickBooks / Xero with separate accounts for each ecommerce-specific line item. This is what enables channel and category drill-down later.

Step 3: Build the financial reporting dashboard

---

Shopify

Option A: QuickBooks Online Reporting (recommended starting point) 1. Connect Shopify to QuickBooks via A2X (see Step 1) 2. In QuickBooks, go to Reports → Profit and Loss — generate monthly P&L with comparison to prior period or budget 3. Go to Reports → Profit and Loss Detail for transaction-level drill-down 4. For board reporting: use QuickBooks Advanced which provides customizable dashboards and scheduled report emails

Option B: Xero Reporting 1. In Xero, go to Accounting → Reports → Profit and Loss — configure date range, comparison period, and layout 2. Enable Tracking Categories in Xero (Settings → Advanced → Tracking Categories) — create categories for "Channel" (DTC, Amazon, Wholesale) and "Department" to get drill-down in P&L 3. Tag transactions by channel as they are entered; Xero P&L then shows margin by channel automatically

Option C: Google Looker Studio (free, for visual dashboards) 1. Connect QuickBooks or Xero to Google Sheets using Coupler.io or G-Accon (exports accounting data to Google Sheets on a schedule) 2. Build a Looker Studio report on top of the Google Sheet data: add scorecards for Net Revenue, Gross Margin %, and EBITDA; add a time-series chart for monthly P&L trend; add a bar chart for expense category breakdown 3. Share the Looker Studio URL with board members — auto-refreshes when the Google Sheet updates

Option D: Finaloop (fully automated Shopify bookkeeping + reporting) 1. Install Finaloop from the Shopify App Store 2. Finaloop handles all bookkeeping automatically: categorizes Shopify transactions, tracks COGS, and generates GAAP-ready P&L, balance sheet, and cash flow statements 3. Financial statements are available in the Finaloop dashboard and exportable to PDF; integrates with QuickBooks and Xero

---

WooCommerce

1. Connect WooCommerce to your accounting system (QuickBooks via the WooCommerce QuickBooks plugin or Xero via the Xero extension) 2. Use your accounting system's reporting (QuickBooks Reports → P&L or Xero → Profit and Loss) as the primary financial reporting layer 3. For WooCommerce-specific drill-down by product/category, use Metorik alongside your accounting system — Metorik provides product-level and category-level revenue and margin, while your accounting system provides the GAAP-accurate totals 4. For a unified view: export monthly P&L from QuickBooks/Xero to Google Sheets and export Metorik channel/product breakdown to a second sheet; build a Looker Studio dashboard that combines both

---

BigCommerce

1. Connect BigCommerce to accounting via Webgility (QuickBooks) or Amaka (Xero) 2. Use your accounting system for financial statements 3. Glew.io (BigCommerce App Marketplace) provides ecommerce-specific financial analytics including gross margin by product, channel, and customer segment — complement your accounting system's P&L with Glew's operational margin view

---

Step 4: Add drill-down capabilities

The value of a financial reporting dashboard over static statements is drill-down. Set up these dimensions in your reporting:

By channel (DTC vs. Amazon vs. Wholesale):

• In QuickBooks: Use Classes (QuickBooks Advanced) to tag transactions by channel; run P&L by class
• In Xero: Use Tracking Categories as described above
• In Looker Studio: Add a channel filter that refreshes all charts based on the selected channel

By product category:

• Map your product categories to your accounting system's chart of accounts
• Alternatively, use your ecommerce platform's analytics (Shopify Analytics → Sales by product, Metorik → Products) for product-level margin, and your accounting system for company-level totals

By time period:

• All accounting systems support P&L comparison: current month vs. prior month, current month vs. prior year same month, YTD vs. prior YTD
• For trailing-12-month views and rolling period analysis, use Google Looker Studio or a BI tool connected to your data warehouse

Step 5: Automate report distribution

Replace email attachments with shared dashboard links:

Scheduled reports in QuickBooks: 1. Go to Reports → [Report Name] → Save and Schedule 2. Set schedule: monthly, on the 5th business day after month close 3. Add recipients (CFO, CEO, board members) — they receive the report by email with the latest numbers

Scheduled reports in Xero: 1. Xero does not natively schedule report emails, but you can use G-Accon for Xero (Google Sheets add-on) to automatically refresh Xero data in Sheets and trigger email distribution via Apps Script

Board reporting package: For board meetings, produce a standard 1-page financial summary with:

• Net Revenue vs. budget (current month and YTD)
• Gross Margin % vs. prior year
• EBITDA vs. budget
• Cash balance and runway
• Top 3 variance explanations

Most accounting systems can produce this as a PDF report; automate generation with QuickBooks Advanced or Xero's scheduled reporting.

Best Practices

• Build on your accounting system, not platform data — Shopify gross sales and accounting net revenue are not the same number; always report from your GL, not the ecommerce platform API
• Automate period closes — set up a monthly job that locks financial facts as of period close; do not allow historical periods to change; post adjusting entries in the current period
• Show percentage metrics alongside absolute values — gross margin % is more comparable across periods than gross margin dollars; always show both
• Define currency and rounding conventions — document whether numbers are in whole dollars or thousands; handle multi-currency consolidation explicitly
• Build a data freshness indicator — show the last-updated timestamp prominently on dashboards so users know whether they are looking at yesterday's close or real-time data
• Annotate unusual variances — allow the finance team to add text annotations to period variances explaining one-time items (inventory write-down, marketing launch surge)

Common Pitfalls

Related Skills

• @financial-analytics-dashboard
• @cash-flow-forecasting
• @ecommerce-budgeting-forecasting
• @revenue-recognition-accounting
• @profit-margin-analysis

Financial Data Integrity and Board Reporting Pipeline

Problem/Feature Description

Volta Supply Co. is a consumer goods ecommerce company preparing for a Series B fundraise. Their investors have requested audited-quality monthly financials going back 18 months. The current dashboard was built by a junior engineer who piped Shopify revenue figures and Stripe payout data directly into the reporting tables. Before a recent board meeting, the CFO discovered that the revenue numbers in the dashboard were ~$40,000 higher than the QuickBooks P&L for the same month, and that $18,000 in customer returns processed in April were incorrectly showing as an April expense rather than a Q1 reduction. The board also complained that the February P&L they reviewed last week had been silently updated, making it impossible to know what numbers they had actually approved.

For the upcoming fundraise, the finance team needs a redesigned data pipeline and reporting layer with proper data governance: clean sourcing from the accounting system, correct return attribution, immutable closed periods with an adjusting-entry workflow, a full audit trail so any number can be traced back to a source transaction, and a variance commentary feature so the finance team can annotate month-end variances before the dashboard is shared with the investment committee.

Output Specification

Produce the following:

1. pipeline_design.md — A design document describing:

• The correct data sourcing strategy (which systems should be the authoritative source for each type of financial data and why)
• How returns and refunds will be attributed to the correct accounting period
• The period close workflow (how periods are locked and how corrections are handled afterward)
• How multi-entity intercompany transactions will be handled at the consolidated level

2. schema_updates.sql — SQL statements that implement:

• Any schema changes needed to enforce the data governance rules identified in your design document
• The database structures needed to support the variance annotation feature
• Any linkage or reference tables needed for the audit and traceability requirements

3. etl_notes.py — A Python script or pseudo-code that demonstrates:

• The period close snapshot process (including how it could be automated)
• How adjusting entries for closed periods are handled
• The commentary workflow (creating and associating notes with specific line item variances)

Management Dashboard: Metrics and Drill-Down Module

Problem/Feature Description

Apex Brands operates an ecommerce business across three channels: a direct-to-consumer website, Amazon, and a wholesale B2B portal. Their CFO uses a static Google Sheet to track monthly KPIs and has asked for a real-time dashboard replacing it. The executive team wants a single "scorecard" view at the top of the dashboard showing the most important financial health metrics, with the ability to click through to channel-level or category-level breakdowns.

The engineering team has already built the financial_facts database table (with columns: fact_id, fiscal_period, statement_type, line_item, channel, product_category, geography, amount, version). They now need to build the Python module that defines the KPI queries and the drill-down query builder. The head of FP&A has also asked the team to document which chart type should be used for each financial view, as the frontend team will implement these in a separate sprint. Finally, the CFO noted that she sometimes opens the dashboard and can't tell if the numbers are from this morning or last week, so there should be a clear indication of data currency.

Output Specification

Produce a Python file dashboard_metrics.py that includes:

• A FINANCIAL_KPIS list/structure defining the headline KPI cards with their SQL queries, formatting, and comparison configuration
• A build_pnl_query(filters: dict) -> str function that dynamically constructs a filtered P&L SQL query
• A VISUALIZATION_SPEC dictionary or equivalent structure mapping each financial statement view to the recommended chart type and any relevant notes

Also produce a dashboard_spec.md that describes the data freshness strategy (how and where the last-updated timestamp will be displayed) and explains how percentage metrics will be presented alongside absolute values in the UI.

The implementation should work against a PostgreSQL database using the financial_facts schema described above.

Financial Reporting Database Design

Problem/Feature Description

Meridian Commerce is a Series A ecommerce company selling across four channels: its own website (Shopify), Amazon, a B2B wholesale portal, and a retail distributor network. The finance team currently produces monthly P&L reports by manually assembling CSV exports from Shopify, Amazon Seller Central, their 3PL (for fulfillment costs), Google/Meta ad platforms, and QuickBooks Online (their accounting GL). The process takes three days each month-end and is error-prone.

The engineering team has been asked to build a database-backed financial reporting layer that will power a management dashboard. The new system must support comparing any month against the prior month (and prior year same month), must allow the CFO to filter the P&L by channel or product category, and must eventually support budget vs. actuals reporting once the FP&A team completes their annual planning model. The company uses a standard calendar year but their largest retail customer recently asked about 4-4-5 fiscal calendar support for a joint business plan.

Output Specification

Produce a SQL schema file (schema.sql) containing:

• The core fact table(s) and any dimension or mapping tables needed for the reporting layer
• All indexes required for efficient dashboard queries
• A query (pnl_comparison.sql) that returns a period-over-period P&L comparison for two fiscal periods passed as parameters, including both absolute and percentage variance columns
• A brief design_notes.md explaining the key design decisions, including how the schema handles budget vs. actuals data and how it accommodates non-standard fiscal calendars

The schema should cover all three financial statements (P&L, balance sheet, cash flow) but the comparison query only needs to cover the P&L.