Managerial Queries

Use this page as the managerial and cost-accounting query library. The groups below organize reusable SQL around cost management, portfolio performance, sales commissions, inventory and supplier behavior, manufacturing operations, workforce coverage, planning, replenishment, and design-service utilization.

Managerial Query Groups

Budget, cost center, and cost management

Use these queries to compare cost-center activity, budget expectations, and price-volume-cost budget bridges.

Open a query when you want to inspect the SQL, copy it into SQLite, or trace the source-table logic.

Budget vs Actual by Cost Center

Compare monthly budget to posted operating expense by cost center and account.

Output

One row per fiscal year, month, cost center, and budgeted account.

Main tables

Budget, CostCenter, Account, GLEntry, JournalEntry.

Cost Center Activity Summary

Summarize operational volume and posted operating expense by cost center and month.

Output

One row per activity month and cost center.

Main tables

SalesOrder, PurchaseRequisition, GLEntry, Account, JournalEntry, CostCenter.

Budget vs Actual Revenue Price Volume Cost Bridge

Compare budgeted and actual quantity, revenue, price realization, and standard-cost consumption by collection and style family.

Output

One row per fiscal month, collection, and style family.

Main tables

BudgetLine, Item, SalesInvoice, SalesInvoiceLine.

Portfolio, profitability, service, returns, and pricing

Use these queries to compare sales mix, product economics, contribution margin, service outcomes, returns, and commercial pricing pressure.

Open a query when you want to inspect the SQL, copy it into SQLite, or trace the source-table logic.

Sales Mix by Customer Region Item Group

Analyze billed sales mix by customer geography, customer segment, and product family.

Output

One row per invoice month, region, customer segment, item group, and item.

Main tables

SalesInvoice, SalesInvoiceLine, Customer, Item.

Basic Product Profitability

Estimate product-level profitability from billed revenue and shipped cost.

Output

One row per item.

Main tables

SalesOrderLine, SalesInvoiceLine, ShipmentLine, Item.

Unit Cost Bridge Dm Dl Varoh Fixedoh

Show the standard unit-cost bridge for manufactured finished goods.

Output

One row per manufactured item.

Main tables

Item.

Absorption vs Contribution Margin

Compare absorption margin and contribution margin by item and supply mode.

Output

One row per item.

Main tables

SalesInvoiceLine, SalesInvoice, Item.

Manufactured vs Purchased Margin Comparison

Compare margin structure between manufactured and purchased finished goods.

Output

One row per supply mode.

Main tables

SalesInvoiceLine, Item.

Backorder Fill Rate and Shipment Lag

Measure fill rate, remaining backorder quantity, and shipment lag from order entry.

Output

One row per order month and item group.

Main tables

SalesOrder, SalesOrderLine, Shipment, ShipmentLine, Item.

Returns and Refund Impact by Customer and Item

Review how returned quantity, credit activity, and refunds affect customer and item-group performance.

Output

One row per credit-memo month, customer region, and item grouping.

Main tables

CreditMemo, CreditMemoLine, SalesReturnLine, CustomerRefund, Customer, Item.

Sales Margin by Collection Style Material

Compare billed sales, credits, net sales, cost, and gross margin by product collection and lifecycle attributes.

Output

One row per item group, collection, style family, material, and lifecycle status.

Main tables

SalesInvoiceLine, ShipmentLine, CreditMemoLine, SalesReturnLine, Item.

Product Portfolio Mix by Collection Style Lifecycle Supply Mode

Review the product portfolio by collection, style family, lifecycle, and supply mode with both SKU counts and billed activity.

Output

One row per sellable portfolio grouping.

Main tables

Item, SalesInvoiceLine.

Contribution Margin by Collection Material Lifecycle Supply Mode

Compare contribution margin by collection, material, lifecycle, and supply mode.

Output

One row per item group and portfolio attribute grouping.

Main tables

SalesInvoiceLine, CreditMemoLine, Item.

Customer Service Impact by Collection Style

Review customer-service performance by collection and style family using shipment lag, fill rate, and backorder pressure.

Output

One row per item group, collection, and style family.

Main tables

SalesOrder, SalesOrderLine, Shipment, ShipmentLine, Item.

Portfolio Return Refund Impact by Collection Lifecycle

Compare return, credit, and refund impact by collection and lifecycle status.

Output

One row per item group, collection, and lifecycle grouping.

Main tables

SalesInvoiceLine, CreditMemo, CreditMemoLine, CustomerRefund, Item.

Sales Rep Override Rate and Discount Dispersion

Review override concentration and promotion discount dispersion by sales rep and customer segment.

Output

One row per sales rep and customer segment.

Main tables

SalesOrder, SalesOrderLine, Employee, Customer.

Collection Revenue Margin Before After Promotions

Compare collection-level revenue before promotions to net revenue after promotions.

Output

One row per month and collection.

Main tables

SalesInvoiceLine, ShipmentLine, SalesInvoice, Item.

Customer Specific Pricing Concentration and Dependency

Show which customers depend most on customer-specific pricing instead of segment pricing.

Output

One row per customer.

Main tables

SalesOrder, SalesOrderLine, Customer.

Monthly Price Floor Pressure and Override Concentration

Review how often sales lines sit at or below the price floor and how often override approvals are used.

Output

One row per month.

Main tables

SalesOrder, SalesOrderLine, PriceListLine.

Sales Commission Expense by Rep Segment

Review sales commission expense by sales rep, revenue type, customer segment, and invoice month.

Output

One row per sales rep, revenue type, customer segment, and fiscal month.

Main tables

SalesCommissionAccrual, SalesCommissionAdjustment, Employee, Customer.

Inventory, purchasing, and supplier performance

Use these queries to inspect inventory movement, purchasing activity, and supplier receipt reliability.

Open a query when you want to inspect the SQL, copy it into SQLite, or trace the source-table logic.

Inventory Movement by Item and Warehouse

Compare inbound and outbound inventory movement by month, warehouse, and item.

Output

One row per activity month, warehouse, and item.

Main tables

GoodsReceipt, GoodsReceiptLine, Shipment, ShipmentLine, Warehouse, Item.

Purchasing Activity by Supplier Category

Review purchasing volume by supplier, supplier category, and item group.

Output

One row per order month, supplier, and item group.

Main tables

PurchaseOrder, PurchaseOrderLine, Supplier, Item.

Supplier Lead Time and Receipt Reliability

Review supplier lead time and receipt reliability from purchase order to first and final receipt.

Output

One row per order month and supplier.

Main tables

PurchaseOrder, PurchaseOrderLine, GoodsReceipt, GoodsReceiptLine, Supplier.

Manufacturing, routing, throughput, labor, and capacity

Use these queries to connect BOM, routing, work-center load, work-order activity, labor support, and manufacturing variance.

Open a query when you want to inspect the SQL, copy it into SQLite, or trace the source-table logic.

BOM Standard Cost Rollup

Compare manufactured-item standard cost to BOM material plus standard conversion cost.

Output

One row per manufactured finished good.

Main tables

Item, BillOfMaterial, BillOfMaterialLine.

Work Order Throughput by Month

Summarize released, completed, and closed work-order activity by month.

Output

One row per release month.

Main tables

WorkOrder, ProductionCompletionLine, WorkOrderClose.

Material Usage and Scrap Review

Compare BOM-based expected component usage to actual issued material by work order.

Output

One row per work order component.

Main tables

WorkOrder, BillOfMaterialLine, MaterialIssue, MaterialIssueLine, Item.

Production Completion and FG Availability

Review manufactured finished-goods activity by month, warehouse, and item group.

Output

One row per fiscal period, warehouse, and item group.

Main tables

ProductionCompletion, ProductionCompletionLine, Shipment, ShipmentLine, SalesReturn, SalesReturnLine, WorkOrder, Warehouse, Item.

Manufacturing Variance by Month Item Group

Review manufacturing variance by month, item group, and warehouse.

Output

One row per close month, warehouse, and item group.

Main tables

WorkOrderClose, WorkOrder, Item, Warehouse.

Direct Labor by Work Order and Employee Class

Review direct labor hours and cost by work order, employee title, and pay class.

Output

One row per work order and employee class grouping.

Main tables

LaborTimeEntry, Employee, WorkOrder, Item, PayrollPeriod.

Labor Efficiency and Rate Variance

Review direct labor rate and efficiency variance by work order.

Output

One row per work order with direct labor activity.

Main tables

LaborTimeEntry, WorkOrder, Item, ProductionCompletionLine.

Routing Master Review

Review the routing design assigned to manufactured items.

Output

One row per manufactured item and routing operation.

Main tables

Item, Routing, RoutingOperation, WorkCenter.

Work Center Activity and Operation Hours

Compare operation-level direct labor and work-center activity by month.

Output

One row per month, work center, and operation code.

Main tables

WorkOrderOperation, RoutingOperation, WorkCenter, LaborTimeEntry, WorkOrder, Item.

Daily Load vs Capacity

Compare daily scheduled load against available work-center capacity.

Output

One row per work center and calendar date.

Main tables

WorkCenterCalendar, WorkOrderOperationSchedule, WorkCenter.

Monthly Work Center Utilization

Summarize monthly work-center utilization and identify recurring bottlenecks.

Output

One row per month and work center.

Main tables

WorkCenterCalendar, WorkOrderOperationSchedule, WorkCenter.

Operation Delay and Bottleneck Review

Review planned versus actual operation timing and highlight bottleneck-prone work centers.

Output

One row per work-order operation.

Main tables

WorkOrderOperation, RoutingOperation, WorkCenter, WorkOrder, Item.

Backlog Aging by Work Center

Show open operation backlog and how far those operations sit past their planned end dates.

Output

One row per open work-order operation.

Main tables

WorkOrderOperation, WorkOrder, RoutingOperation, WorkCenter.

Shift Adherence and Overtime by Work Center

Review shift adherence and overtime concentration by work center and month.

Output

One row per month, shift, and work-center grouping.

Main tables

TimeClockEntry, ShiftDefinition, EmployeeShiftAssignment, WorkCenter, Employee.

Approved Clock Hours vs Labor Allocation

Compare approved direct-labor time-clock hours to the labor allocation recorded against work-order operations.

Output

One row per month, work center, and routing operation.

Main tables

TimeClockEntry, LaborTimeEntry, WorkOrderOperation, RoutingOperation, WorkCenter, WorkOrder.

Paid Hours vs Productive Labor by Work Center

Compare approved paid hours to productive labor allocation by work center and month.

Output

One row per month and work center.

Main tables

TimeClockEntry, LaborTimeEntry, WorkOrderOperation, WorkCenter, ShiftDefinition.

Workforce coverage, planning, replenishment, and design-service utilization

Use these queries to review workforce structure, roster coverage, attendance pressure, forecast quality, replenishment recommendations, and design-service utilization.

Open a query when you want to inspect the SQL, copy it into SQLite, or trace the source-table logic.

Headcount by Cost Center Job Family Status

Review workforce composition by cost center, job family, level, and employment status.

Output

One row per cost center, job family, job level, and employment status.

Main tables

Employee, CostCenter.

Labor and Headcount by Work Location Job Family Cost Center

Compare headcount, payroll cost, approved time, and direct labor by work location, job family, and cost center.

Output

One row per work location, cost center, and job family grouping.

Main tables

Employee, CostCenter, PayrollRegister, TimeClockEntry, LaborTimeEntry.

Staffing Coverage vs Work Center Planned Load

Compare rostered staffing hours to scheduled work-center load at the daily grain with monthly rollup context.

Output

One row per work center and calendar day with rostered hours, scheduled load hours, and the coverage gap.

Main tables

EmployeeShiftRoster, WorkOrderOperationSchedule, WorkCenter.

Rostered vs Worked Hours by Work Center Shift

Compare planned rostered hours to approved worked hours by work center, shift, and month.

Output

One row per month, work center, and shift with planned hours, worked hours, and variance.

Main tables

EmployeeShiftRoster, TimeClockEntry, ShiftDefinition, WorkCenter.

Absence Rate by Work Location Job Family Month

Measure absence rates by month, work location, and job family.

Output

One row per month, work location, and job family with absence hours, rostered hours, and absence rate.

Main tables

EmployeeAbsence, EmployeeShiftRoster, Employee.

Overtime Approval Coverage and Concentration

Review how overtime hours concentrate by work center and how consistently those hours carry overtime approvals.

Output

One row per month and work center with overtime totals and approval coverage metrics.

Main tables

TimeClockEntry, OvertimeApproval, WorkCenter.

Punch to Pay Bridge for Hourly Workers

Trace hourly payroll from raw punch counts through approved time, labor allocation, and gross pay.

Output

One row per hourly employee and payroll period.

Main tables

TimeClockPunch, TimeClockEntry, LaborTimeEntry, PayrollRegister, Employee.

Late Arrival Early Departure by Shift Department

Measure late-arrival and early-departure patterns by shift and department.

Output

One row per month, department, and shift.

Main tables

EmployeeShiftRoster, TimeClockEntry, ShiftDefinition.

Inventory Coverage and Projected Stockout Risk

Estimate inventory coverage and stockout risk from the latest planning recommendation state.

Output

One row per item and warehouse at the latest planned week.

Main tables

SupplyPlanRecommendation, DemandForecast, Item, Warehouse.

Rough Cut Capacity Load vs Available Hours

Compare rough-cut planned load to available hours by work center and week.

Output

One row per work center and week.

Main tables

RoughCutCapacityPlan, WorkCenter.

Expedite Pressure by Item Family and Month

Measure how often planning pressure escalates to expedite recommendations.

Output

One row per month and item-family slice.

Main tables

SupplyPlanRecommendation, Item.

Forecast Error and Bias by Collection Style Family

Summarize forecast error and bias by collection and style family.

Output

One row per collection and style family.

Main tables

DemandForecast, SalesOrder, SalesOrderLine, Item.

Supply Plan Driver Mix by Collection and Supply Mode

Review recommendation mix by planning driver, collection, and supply mode.

Output

One row per collection, supply mode, and driver.

Main tables

SupplyPlanRecommendation, Item.

Design Service Engagement Utilization and Labor Margin

Compare engagement staffing mix, utilization, billed revenue, and labor margin for design services.

Output

One row per service engagement.

Main tables

ServiceEngagement, ServiceEngagementAssignment, ServiceTimeEntry, ServiceBillingLine, Customer, Employee.

Main Table Families

Topic	Main tables
Budget vs actual	Budget, BudgetLine, CostCenter, Account, GLEntry, JournalEntry
Product portfolio and sales mix	Item, SalesInvoiceLine, CreditMemoLine, ShipmentLine, SalesOrderLine, Customer, PriceList, PriceListLine, PromotionProgram, PriceOverrideApproval
Sales commissions	SalesCommissionAccrual, SalesCommissionAdjustment, SalesCommissionRate, SalesCommissionPaymentLine, SalesInvoiceLine, CreditMemoLine, Employee, Customer
Inventory and purchasing	GoodsReceiptLine, ShipmentLine, SalesReturnLine, ProductionCompletionLine, PurchaseOrderLine, Supplier, Warehouse, Item
BOM, routing, and work-center planning	BillOfMaterial, BillOfMaterialLine, Routing, RoutingOperation, WorkCenter, WorkCenterCalendar, Item
Work-order throughput and variance	WorkOrder, WorkOrderOperation, WorkOrderOperationSchedule, MaterialIssueLine, ProductionCompletionLine, WorkOrderClose
Labor, headcount, payroll mix, and workforce planning	Employee, EmployeeShiftRoster, EmployeeAbsence, OvertimeApproval, TimeClockPunch, TimeClockEntry, LaborTimeEntry, PayrollRegister, WorkCenter, CostCenter

Recommended Case Pairings

• Use Product Portfolio Profitability Case when you want a portfolio, lifecycle, and contribution-margin sequence.
• Use Workforce Cost and Org-Control Case when you want workforce structure, labor mix, and approval concentration in one lab.
• Use Workforce Coverage and Attendance Case when you want planned staffing, approved time, absences, and overtime in one operational lab.
• Use Demand Planning and Replenishment Case when you want forecast, replenishment, component-demand, and rough-cut capacity analysis in one sequence.
• Use Pricing and Margin Governance Case when you want commercial policy, promotion effect, override concentration, and collection-level margin analysis in one case.
• Use Product Portfolio Case when you want a lighter introductory entry point before moving into the broader profitability pack.

Interpretation Notes

• The item master now supports collection, style family, material, finish, color, lifecycle, and supply-mode analysis inside the existing Item table.
• The employee master now supports work location, job family, job level, employment status, and current-state active analysis without adding a separate HR-history model.
• Supply mode changes the meaning of cost analysis. Manufactured items can support both absorption and contribution-margin work because fixed overhead is stored separately.
• Portfolio mix and profitability should be read together with service-level measures such as fill rate, shipment lag, and return pressure.
• Work location and cost center answer different questions. Use both when students compare workforce structure to payroll or labor usage.
• Workforce-planning analysis is stronger now that rostered hours, approved worked hours, absences, raw punches, and overtime approvals can be compared directly.
• The dataset includes a weekly planning layer, so students can compare forecast, policy, recommendation, and rough-cut capacity pressure before execution starts.
• The dataset includes commercial-pricing coverage, so students can compare list price, resolved price-list pricing, promotions, overrides, and net realized margin without introducing a separate quote system.
• Sales commissions are modeled as a separate selling-cost subledger, so students can compare commission expense by rep, segment, and revenue type without mixing it into payroll.
• The budget model now starts from BudgetLine, so students can move from forecast, planned price, standard cost, payroll, and timing assumptions into the summary Budget table and the pro forma statements.
• The current manufacturing model is still a foundation. It supports operations, labor, and contribution-margin analysis without switching inventory valuation to actual cost.
• Demand-planning analysis is most useful when students connect DemandForecast, InventoryPolicy, SupplyPlanRecommendation, MaterialRequirementPlan, and RoughCutCapacityPlan in one analytical path.