Financial Queries
Use this page as the financial-accounting query library. The groups below organize reusable SQL by the question students are trying to answer: statement tie-out, settlement timing, working capital, sales-commission payable, payroll liabilities, manufacturing cost, CAPEX, budget, and commercial performance.
Financial Query Groups
Revenue, margin, and commercial pricing
Use these queries to connect billed revenue, gross margin, promotions, price realization, and customer-facing commercial activity.
Open a query when you want to inspect the SQL, copy it into SQLite, or trace the source-table logic.
AR, AP, customer cash, and working capital
Use these queries to separate invoice aging, cash application, supplier settlement, and month-end working-capital pressure.
Open a query when you want to inspect the SQL, copy it into SQLite, or trace the source-table logic.
Payroll, accruals, and manufacturing cost
Use these queries to explain payroll liabilities, gross-to-net pay, accrued expenses, and manufacturing-accounting movement.
Open a query when you want to inspect the SQL, copy it into SQLite, or trace the source-table logic.
Trial balance, statements, reconciliation, and close
Use these queries to move from posted GL activity into trial balance, financial statements, control-account support, and close validation.
Open a query when you want to inspect the SQL, copy it into SQLite, or trace the source-table logic.
Budget, pro forma, CAPEX, fixed assets, and design services
Use these queries to connect explicit budget assumptions, pro forma statements, fixed-asset lifecycle events, debt timing, and design-service revenue.
Open a query when you want to inspect the SQL, copy it into SQLite, or trace the source-table logic.
Main Table Families
| Topic | Main tables |
|---|---|
| Balance sheet reporting | GLEntry, Account, JournalEntry |
| Cash flow reporting | GLEntry, Account, JournalEntry |
| Fixed assets and CAPEX | FixedAsset, FixedAssetEvent, DebtAgreement, DebtScheduleLine, PurchaseInvoice, DisbursementPayment, JournalEntry, GLEntry |
| Income statement reporting | GLEntry, Account, JournalEntry |
| Revenue and gross margin | SalesInvoice, SalesInvoiceLine, Shipment, ShipmentLine, CreditMemo, CreditMemoLine, Item, PriceList, PriceListLine, PromotionProgram, GLEntry, Account |
| AR and customer cash | SalesInvoice, CashReceipt, CashReceiptApplication, CreditMemo, CustomerRefund, Customer |
| AP and supplier cash | PurchaseInvoice, PurchaseInvoiceLine, DisbursementPayment, Supplier, GoodsReceipt |
| Working capital | GLEntry, Account, SalesInvoice, CashReceiptApplication, SalesCommissionAccrual, SalesCommissionAdjustment, SalesCommissionPayment, PurchaseInvoice, DisbursementPayment, PayrollRegister, PayrollLiabilityRemittance |
| Sales commissions | SalesCommissionRate, SalesCommissionAccrual, SalesCommissionAdjustment, SalesCommissionPayment, SalesCommissionPaymentLine, SalesInvoiceLine, CreditMemoLine, GLEntry, Account |
| Accrued expenses | JournalEntry, PurchaseInvoice, PurchaseInvoiceLine, Shipment, GLEntry, Account, Supplier, Item |
| Payroll liabilities and support | PayrollPeriod, TimeClockEntry, LaborTimeEntry, PayrollRegister, PayrollRegisterLine, PayrollPayment, PayrollLiabilityRemittance, Employee |
| Manufacturing balances | WorkOrderClose, ProductionCompletionLine, MaterialIssueLine, JournalEntry, GLEntry, Account |
| Trial balance and close cycle | GLEntry, JournalEntry, Account |
Financial Statement Reconciliation Path
This sequence follows the statement tie from annual net income into retained earnings, then narrows into revenue reconciliation when the statement view and the operational view stop matching. Work through the queries in order so the bridge stays connected from statement to ledger to source document.
Open each query from the guide and work through the sequence from statement tie-out into revenue cutoff detail.
- Annual Income to Equity Bridge
Start with the annual statement tie between net income, retained earnings, and the balance-sheet residual.
- Post Close Profit and Loss Leakage Review
Then confirm that no revenue, expense, or `8010` balances remain open after the close.
- Round Dollar Manual Journal Close Sensitivity Review
Use this sensitivity review only when a custom anomaly profile or manual edit leaves unexplained residuals after close.
- Annual Net Revenue Bridge
Then move account by account into annual net revenue.
- Invoice Revenue Cutoff Exception Summary
Open the cutoff summary when annual net revenue does not tie from source documents into the GL.
- Invoice Revenue Cutoff Exception Trace
Then inspect the affected invoice lines, shipment lines, and revenue GL rows in detail.
CAPEX and Fixed Asset Path
This sequence starts in the fixed-asset subledger, then ties the asset lifecycle back into manufacturing cost, debt cash flows, and the budget roll-forward. Work through it in order so students keep the operational document chain separate from depreciation, note financing, and disposal accounting.
This is the fastest path for teaching the asset register, depreciation routing, financing cash, and disposal effects as one connected story.
- Fixed Asset Rollforward by Behavior Group
Start with the monthly rollforward by behavior group so manufacturing, warehouse, and office assets stay separated.
- Capex Acquisitions Financing and Disposals
Then trace the lifecycle events behind each addition, financing choice, and disposal.
- Debt Amortization and Cash Impact
Use the debt schedule next so principal, interest, and cash timing are explicit.
- Manufacturing Cost Component Bridge
Then connect manufacturing-equipment depreciation back into manufacturing clearing and conversion cost.
- Cash Flow Statement Indirect Monthly
Use the indirect cash flow statement to show how CAPEX moves into investing cash while note activity moves into financing cash.
- Pro Forma Cash Flow Indirect Monthly
Finish with the pro forma cash flow to compare the explicit CAPEX and note schedule with the budget roll-forward.
- Keep the broader timing scan in view through Audit Queries, especially
Cutoff and timing analysisandPotential anomaly review. Those queries show the wider timing population, while the reconciliation sequence narrows the investigation to the invoices that actually affect annual net-revenue tie-out. - If the cutoff summary shows invoices with
InvoiceBeforeShipmentFlag = 1andInvoiceYearVsGlYearFlag = 1, treat that pattern as seeded anomaly behavior inside the published teaching dataset rather than a defect in the statement logic. - After net revenue, repeat the same source-to-GL-to-statement-to-close pattern for COGS, manufacturing variance, labor, overhead, operating expenses, other income and expense, and retained earnings.
Recommended Case Pairings
- Use Working Capital and Cash Conversion Case when you want a balance-sheet and settlement-timing exercise.
- Use Financial Statement Bridge Case when you want to move from operations into
GLEntry, control accounts, and close entries. - Use CAPEX and Fixed Asset Lifecycle Case when you want to teach the asset register, depreciation routing, debt financing, and disposal accounting together.
- Use Product Portfolio Profitability Case when you want a financial view of collection, lifecycle, and supply-mode performance.
- Use Demand Planning and Replenishment Case when you want forecast, replenishment, and planning-pressure analysis to sit beside working-capital and inventory timing.
- Use Pricing and Margin Governance Case when you want list-price realization, promotions, override pressure, and net-margin dilution in one commercial analysis path.
Interpretation Notes
- Revenue posts at invoicing. COGS posts at shipment.
CashReceiptdoes not equal settled AR by itself. UseCashReceiptApplication.- Payroll register activity records liabilities first; cash leaves through payroll payments and remittances later.
- Working-capital analysis gets stronger when students separate balances from timing.
- Forecast, policy, and replenishment tables support planning-informed working-capital and inventory-timing analysis.
- Explicit price-list, promotion, and override lineage lets students analyze revenue and margin against commercial rules.
- The richer item master now supports collection, style family, lifecycle, and supply-mode financial analysis without changing the underlying posting model.
- The richer employee master now supports job-family, job-level, and people-cost review without requiring a separate HR-history subledger.
- Customer deposits and unapplied cash analysis should start from
CashReceiptandCashReceiptApplication, alongside AR. - Sales commission analysis should start from
SalesInvoiceLine.LineTotal, then traceSalesCommissionAccrual,SalesCommissionAdjustment,SalesCommissionPaymentLine, andSalesCommissionPaymentinto2034and6290. - Accrued-expense analysis should focus on
2040,PurchaseInvoiceLine.AccrualJournalEntryID, the service-item lines that settle finance-managed estimates, and the outbound-freight accrual and settlement pattern created fromShipment.FreightCost. - Year-end close entries are real posted journals and should be filtered when you want raw multi-year P&L activity.
- The income-statement starter queries use pre-close P&L activity, so they exclude the year-end close journals that zero out revenue and expense accounts.
- The balance-sheet starter queries are point-in-time ending-balance statements, not monthly activity reports.
- The balance-sheet starter queries derive
Current Year Earningsfor interim periods so assets continue to tie to liabilities plus equity before the annual close moves earnings into retained earnings. - The indirect-method cash flow starter queries reconcile from pre-close net income into operating cash, then combine that with investing and financing cash movements.
- The direct-method cash flow starter queries classify cash-ledger activity into teaching buckets such as customer receipts, supplier payments, payroll, and other operating cash.
- The cash flow starter queries treat opening journals as the
Beginning Cashseed for the first reporting period instead of showing them as operating, investing, or financing flows. - Manufacturing-equipment depreciation debits
1090Manufacturing Cost Clearing, so it belongs in manufacturing-cost interpretation rather than warehouse or office operating expense review. - Warehouse and office fixed-asset depreciation debits
6130, which keeps those assets in operating expense even when they share the same broad fixed-asset lifecycle controls as plant equipment. - CAPEX additions, note-financing reclasses, debt payments, and disposals now sit in the same financial-analysis path, so students can reconcile the asset register to investing cash, financing cash, and gain-or-loss activity.
- The published default SQLite is anomaly-enriched when
anomaly_modeisstandard, so it is useful for teaching comparisons but not as the clean baseline for database-integrity reconciliation.