5. Explain Production Cost and Delivery Pressure

The operations planner wants to change a schedule after a difficult production period. The controller asks whether late completion, material usage, labor support, and cost variance point to the same cause. You will examine the January release cohort, trace two work orders, and identify the operational evidence needed before recommending a change.

Intermediate · 3 sessions of 60–90 minutes

Prepare

• 1. Meet the Records
• 2. Read an Order
• 3. Connect the Records
• 9. Follow Manufacturing Costs
• Manufacturing process chapter

Readiness

• Complete your chosen Excel or SQL setup. Preserve source row keys and check row counts, unique keys, and amounts after descriptive joins.
• Before starting, state the cohort, cutoff, row key, independently aggregated measures, and one evidence limitation.
• Aggregate issue, labor, completion, and close independently by work order, and compare capacity at one row per center/day.

Tools

Complete either Excel or SQL. Both routes include worked checks.

Produce

A work-order cost bridge, completion/close comparison, capacity evidence, and a production review memo.

The worked core teaches the method. The independent challenge asks you to apply it and defend your own conclusion.

Worked scope. 336 work orders released January 1–31, 2024, with events assessed through February 29. Begin with work order 1 and compare work order 67. Capacity comparisons cover all scheduled work in the selected work centers and dates, not only the January cohort.

Choose Excel or SQL. The site retains your selection between lessons and cases.

Preparation: 1. Meet the Records, 2. Read an Order, 3. Connect the Records, 9. Follow Manufacturing Costs, Manufacturing process chapter. Use the Excel setup or SQL setup for your chosen tool.

Ready to begin:

• Complete your chosen Excel or SQL setup. Preserve source row keys and check row counts, unique keys, and amounts after descriptive joins.
• Before starting, state the cohort, cutoff, row key, independently aggregated measures, and one evidence limitation.
• Aggregate issue, labor, completion, and close independently by work order, and compare capacity at one row per center/day.

Key Data Sources

Each source below has a distinct row meaning. Import only the fields and populations requested in each stage. Working tables default to the source name plus Data unless a stage assigns a more specific name.

Source	Published Excel Table	First working name
WorkOrder	T51_WorkOrder	WorkOrderData
WorkOrderOperation	T52_WorkOrderOperation	WorkOrderOperationData
WorkOrderOperationSchedule	T53_WorkOrderOperationSchedule	WorkOrderOperationScheduleData
MaterialIssue	T54_MaterialIssue	MaterialIssueData
MaterialIssueLine	T55_MaterialIssueLine	MaterialIssueLineData
LaborTimeEntry	T60_LaborTimeEntry	LaborTimeEntryData
ProductionCompletion	T56_ProductionCompletion	ProductionCompletionData
ProductionCompletionLine	T57_ProductionCompletionLine	ProductionCompletionLineData
WorkOrderClose	T58_WorkOrderClose	WorkOrderCloseData
WorkCenter	T47_WorkCenter	WorkCenterData
WorkCenterCalendar	T48_WorkCenterCalendar	WorkCenterCalendarData
SupplyPlanRecommendation	T80_SupplyPlanRecommendation	SupplyPlanRecommendationData
DemandForecast	T78_DemandForecast	DemandForecastData

Import, merge, group, and retain evidence

Use Microsoft 365 desktop and a separate analysis workbook. Keep the released workbook unchanged. The table above names the exported Excel Tables, which differ from worksheet names. For first-time setup, follow Set Up Excel.

1. Import: On Windows, choose Data → Get Data → From File → From Workbook. On Mac, choose Data → Get Data (Power Query) → Excel workbook. Select the published workbook, choose the named table in Navigator, then Transform Data. Rename the query in Query Settings. Set identifiers to Whole Number, dates to Date, and amounts/hours to Decimal Number. Keep reference numbers as text. Apply the stage’s filters before loading large tables.
2. Merge. In the Power Query editor choose Home → Merge Queries → Merge Queries as New. Select the two queries and matching key columns. For multiple keys, select them in the same order with Ctrl on Windows or Command on Mac. Use the join type specified in the stage. Expand only the required fields using the button on the merged column. Clear Use original column name as prefix when a stage uses bare column names, and rename summary columns exactly as specified. Compare row counts before and after expansion. A lookup requires a unique key, while a one-to-many relationship needs a merge or a separate aggregate.
3. Group: Choose Home → Group By. Use Advanced for multiple keys or totals. Enter each output name, operation (Sum or Count Rows), and source column explicitly. Group each detail source before joining independent totals. Right-click a query and choose Reference when building a new summary from its existing filters.
4. Load. Load the final query as a table on a new worksheet. On Windows, Close & Load To can leave staging queries as connection-only. On Mac, use the editor’s Close & Load for the final result. Select the loaded table and set its Table Name to the name used in the formulas. Keep source queries in the editor so filters and relationships remain inspectable. Microsoft documents the Mac editor and loading workflow and matching multiple merge keys.
5. Summarize. Select a working table, choose Insert → PivotTable, and place the specified fields in Rows and Values. In Value Field Settings, count identifiers and sum amounts. Ordinary SUM includes filtered-out rows. Use SUBTOTAL(109,table[column]) for a visible sum or SUMIFS for an explicit population. Refresh queries and PivotTables after changing a filter.
6. Retain. Save each session’s workbook and review notes. Record the source file, cutoff, key fields, row meaning, and checks. SQL learners save each supplied statement and its result from DB Browser’s Execute SQL tab. Run one statement at a time using the cases' read-only queries. Follow Save the Result Not Just the Query to retain and verify the exported rows.

Copy one formula at a time. For a block labeled Excel, create the named column and paste into its first data cell, or use the named worksheet cell. If a table formula does not fill down automatically, copy it through the remaining data rows. Paste a block labeled Power Query into Add Column → Custom Column in the query editor without a leading equals sign. Resolve No match results before using a lookup in another calculation.

The examples use English function names and comma separators. Regional Excel installations may use translated names or semicolons with the same fields and criteria. Investigate a blank relationship or date before converting it to zero.

Session 1 — Define the cohort and read the plan

Create Case05.xlsx or Case05.sql. A release-date cohort stays fixed even when completion or close occurs in a later month. Keep actual event dates visible.

How does a plan become production evidence?

Reading guide: Planning and schedules describe intended activity; dated issue, completion, and close records establish execution.

Expand the diagram for a larger view with zoom and pan controls.

Download editable Draw.io

Read the figure in words

• A recommendation and work order describe intended supply.
• Operations and daily schedules describe planned work-center activity.
• Actual events establish issue, completion, and financial close.

Conceptual teaching diagram. Planning and schedules describe intended activity; dated issue, completion, and close records establish execution.

1.1. Keep the release cohort fixed

Predict before you run: Would filtering by completion month answer the same question as filtering by release month?

Excel

Named inputs: WorkOrderData ← WorkOrder (import here).

Produced outputs: Orders: fixed January 2024 release cohort; 336 rows, missing dates retained.

1. Import WorkOrder as WorkOrderData.

2. Reference it as Orders and filter ReleasedDate January 1–31, 2024, preserving the raw query.

3. Load as Orders, keeping IDs and all date fields.

4. Label Review!B2 Release cohort and enter:

   Excel — Review!B2

   =ROWS(Orders[WorkOrderID])

5. Add CompletedByCutoff to Orders:

   Excel — Orders[CompletedByCutoff]

   =IF(AND(ISNUMBER([@CompletedDate]),[@CompletedDate]<=DATE(2024,2,29)),1,0)

6. Add ClosedByCutoff:

   Excel — Orders[ClosedByCutoff]

   =IF(AND(ISNUMBER([@ClosedDate]),[@ClosedDate]<=DATE(2024,2,29)),1,0)

7. Add MissingPlanLink:

   Excel — Orders[MissingPlanLink]

   =IF([@SupplyPlanRecommendationID]="",1,0)

8. Do not filter away missing dates or links.

SQL

Run each statement separately in the SQL editor. Save its text and result with the stage title. Read the result at the grain described in the question and compare the checkpoint before continuing.

c05-cohort.sql

SELECT WorkOrderID,
       ItemID,
       PlannedQuantity,
       ReleasedDate,
       DueDate,
       CompletedDate,
       ClosedDate,
       SupplyPlanRecommendationID
FROM WorkOrder
WHERE ReleasedDate>='2024-01-01' AND ReleasedDate<'2024-02-01'
ORDER BY WorkOrderID;

c05-timing.sql

SELECT COUNT(*) AS Orders,
       SUM(CASE WHEN CompletedDate<='2024-02-29' THEN 1 ELSE 0 END) AS CompletedByCutoff,
       SUM(CASE WHEN ClosedDate<='2024-02-29' THEN 1 ELSE 0 END) AS ClosedByCutoff,
       SUM(CASE WHEN CompletedDate<='2024-02-29' AND CompletedDate>DueDate THEN 1 ELSE 0 END) AS CompletedAfterDueDate,
       SUM(CASE WHEN SupplyPlanRecommendationID IS NULL THEN 1 ELSE 0 END) AS MissingPlanLink,
       SUM(CASE WHEN CompletedDate<ReleasedDate THEN 1 ELSE 0 END) AS CompletionBeforeRelease
FROM WorkOrder
WHERE ReleasedDate>='2024-01-01' AND ReleasedDate<'2024-02-01';

Checkpoint. 336 released orders; 336 completed by February 29 but only 290 closed. 136 completed after their due date. One lacks a planning link; one has completion before release.

Completion and close measure different responsibilities. A chronology anomaly also means these dates require source follow-up before a scheduling recommendation relies on them.

If your result differs

Current Status is not an as-of measure. Test the actual dates against the cutoff and retain missing links.

Keep for the next stage: Save Orders and the timing counts, including the exceptions.

1.2. Trace the two operation paths

Predict before you run: Can a work order be completed in the file even when its operation support is missing?

Excel

Named inputs: WorkOrderOperationData ← WorkOrderOperation and WorkOrderOperationScheduleData ← WorkOrderOperationSchedule (import here).

Produced outputs: SelectedOperations: operations for WO1/WO67, ScheduleByOperation: dated schedule summary, OperationTrace: selected operations with schedule support.

1. Import WorkOrderOperation as WorkOrderOperationData and WorkOrderOperationSchedule as WorkOrderOperationScheduleData; preserve the raw queries.

2. Reference WorkOrderOperationData as SelectedOperations and filter WorkOrderID 1 or 67.

3. Reference WorkOrderOperationScheduleData as ScheduleByOperation, filter ScheduleDate through February 29, 2024, and group by WorkOrderOperationID, Sum ScheduledHours as ScheduledHours.

4. Reference SelectedOperations as OperationTrace, Left merge ScheduleByOperation on WorkOrderOperationID, and sort by WorkOrderID then OperationSequence.

5. Keep PlannedLoadHours and actual start/end dates beside the schedule total.

SQL

Run each statement separately in the SQL editor. Save its text and result with the stage title. Read the result at the grain described in the question and compare the checkpoint before continuing.

c05-operations.sql

SELECT o.WorkOrderID,
       o.WorkOrderOperationID,
       o.OperationSequence,
       o.WorkCenterID,
       o.PlannedLoadHours,
       o.PlannedStartDate,
       o.PlannedEndDate,
       o.ActualStartDate,
       o.ActualEndDate,
       ROUND(COALESCE(SUM(CASE WHEN s.ScheduleDate<='2024-02-29' THEN s.ScheduledHours ELSE 0 END),0),2) AS ScheduledThroughCutoff
FROM WorkOrderOperation o
LEFT JOIN WorkOrderOperationSchedule s ON s.WorkOrderOperationID=o.WorkOrderOperationID
WHERE o.WorkOrderID IN (1,67)
GROUP BY o.WorkOrderOperationID
ORDER BY o.WorkOrderID,o.OperationSequence,o.WorkOrderOperationID;

Checkpoint. Four operation rows for work order 67, progressing through work centers 1–4. No operation rows are returned for work order 1. WO67’s scheduled hours are 9.47, 19.28, 11.45, and 4.44 by operation sequence.

The absence of operation detail for WO1 is an evidence limitation, not proof that no work happened. Other documents still record its material issue and completion. Compare support completeness before inferring efficiency.

If your result differs

An Inner join from Orders to Operations would hide WO1 from the cohort summary, so retain the prescribed Left join.

Keep for the next stage: Save the operation trace and a note about WO1’s missing support.

Session stopping point

Retain the 336-order cohort and the four-row comparison trace. State what the planning records can establish and what actual-event evidence is still needed.

Session 2 — Build costs at one row per work order

Materials, labor support, completions, and close records each have their own grain. Summarize them separately before merging to the cohort.

What changes when materials become finished goods?

Reading guide: Material issue, standard-cost completion, and variance close have distinct account effects and evidence populations.

Expand the diagram for a larger view with zoom and pan controls.

Download editable Draw.io

Read the figure in words

• Material issue moves component cost into work in process.
• Completion records finished goods at standard material and conversion cost.
• Close records material and conversion variances after completion.

Conceptual teaching diagram. Material issue, standard-cost completion, and variance close have distinct account effects and evidence populations.

2.1. Prepare the four event summaries

Predict before you run: How would joining raw issue lines to raw labor entries change the totals?

Excel

Named inputs: Orders (Session 1.1), MaterialIssueLineData ← MaterialIssueLine and MaterialIssueData ← MaterialIssue (import here), LaborTimeEntryData ← LaborTimeEntry (import here), ProductionCompletionLineData ← ProductionCompletionLine and ProductionCompletionData ← ProductionCompletion (import here), WorkOrderCloseData ← WorkOrderClose (import here).

Produced outputs: IssuesByWorkOrder, LaborByWorkOrder, CompletionsByWorkOrder, ClosesByWorkOrder: separate one-row-per-work-order summaries, CostBridge: all 336 cohort orders; unclosed variance remains null.

1. Import MaterialIssueLine as MaterialIssueLineData, MaterialIssue as MaterialIssueData, LaborTimeEntry as LaborTimeEntryData, ProductionCompletionLine as ProductionCompletionLineData, ProductionCompletion as ProductionCompletionData, and WorkOrderClose as WorkOrderCloseData.

2. Keep those raw queries unchanged.

3. Create four references and summarize before merging.

4. IssuesByWorkOrder: merge MaterialIssueLineData to MaterialIssueData on MaterialIssueID, filter IssueDate through February 29, group by WorkOrderID and sum ExtendedStandardCost as Issued.

5. LaborByWorkOrder: filter LaborTimeEntryData WorkDate through February 29, group by WorkOrderID and sum ExtendedLaborCost as LaborSupport.

6. CompletionsByWorkOrder: merge ProductionCompletionLineData to ProductionCompletionData on ProductionCompletionID, filter CompletionDate through February 29, group by WorkOrderID and sum QuantityCompleted as CompletedQty, ExtendedStandardMaterialCost as CompletedMaterials, ExtendedStandardConversionCost as CompletedConversion, and ExtendedStandardTotalCost as CompletedTotal.

7. ClosesByWorkOrder: filter WorkOrderCloseData CloseDate through February 29, group by WorkOrderID and sum MaterialVarianceAmount as MaterialVariance, ConversionVarianceAmount as ConversionVariance, and TotalVarianceAmount as TotalVariance.

8. Reference Orders as CostBridge and Left merge these four unique-key summaries on WorkOrderID; load as CostBridge.

9. Replace missing issue/labor/completion totals with zero only after checking event absence.

10. Leave missing close variances blank, meaning no close by cutoff.

SQL

Four named results summarize issues, labor, completions, and closes separately by WorkOrderID. LEFT JOIN adds these totals to the fixed release cohort. Missing close totals stay null, distinguishing an unclosed order from a zero variance.

Run each complete statement separately and retain its result before continuing.

c05-costs.sql

WITH issues AS (SELECT h.WorkOrderID,
       SUM(l.ExtendedStandardCost) AS Issued
FROM MaterialIssue h
JOIN MaterialIssueLine l ON l.MaterialIssueID=h.MaterialIssueID
WHERE h.IssueDate<='2024-02-29'
GROUP BY h.WorkOrderID), labor AS (SELECT WorkOrderID,
       SUM(ExtendedLaborCost) AS LaborSupport
FROM LaborTimeEntry
WHERE WorkDate<='2024-02-29'
GROUP BY WorkOrderID), completions AS (SELECT h.WorkOrderID,
       SUM(l.QuantityCompleted) AS CompletedQty,
       SUM(l.ExtendedStandardMaterialCost) AS CompletedMaterials,
       SUM(l.ExtendedStandardConversionCost) AS CompletedConversion,
       SUM(l.ExtendedStandardTotalCost) AS CompletedTotal
FROM ProductionCompletion h
JOIN ProductionCompletionLine l ON l.ProductionCompletionID=h.ProductionCompletionID
WHERE h.CompletionDate<='2024-02-29'
GROUP BY h.WorkOrderID), closes AS (SELECT WorkOrderID,
       SUM(MaterialVarianceAmount) AS MaterialVariance,
       SUM(ConversionVarianceAmount) AS ConversionVariance,
       SUM(TotalVarianceAmount) AS TotalVariance
FROM WorkOrderClose
WHERE CloseDate<='2024-02-29'
GROUP BY WorkOrderID) SELECT w.WorkOrderID,
       ROUND(COALESCE(i.Issued,0),2) AS Issued,
       ROUND(COALESCE(l.LaborSupport,0),2) AS LaborSupport,
       ROUND(COALESCE(c.CompletedQty,0),2) AS CompletedQty,
       ROUND(COALESCE(c.CompletedMaterials,0),2) AS CompletedMaterials,
       ROUND(COALESCE(c.CompletedConversion,0),2) AS CompletedConversion,
       ROUND(COALESCE(c.CompletedTotal,0),2) AS CompletedTotal,
       x.MaterialVariance,
       x.ConversionVariance,
       x.TotalVariance
FROM WorkOrder w
LEFT JOIN issues i ON i.WorkOrderID=w.WorkOrderID
LEFT JOIN labor l ON l.WorkOrderID=w.WorkOrderID
LEFT JOIN completions c ON c.WorkOrderID=w.WorkOrderID
LEFT JOIN closes x ON x.WorkOrderID=w.WorkOrderID
WHERE w.ReleasedDate>='2024-01-01' AND w.ReleasedDate<'2024-02-01'
ORDER BY w.WorkOrderID;

Checkpoint. 336 rows remain. WO1: issue 754.29, labor support 191.89, completion materials 729.36, conversion 290.49, completed total 1,019.85. Its material variance is 24.93 and conversion variance 718.23, totaling 743.16.

Material issue less standard material completion equals the material variance for this closed example. Labor support is one input into actual conversion cost rather than its complete measure. Do not force it to equal completion conversion plus close variance.

If your result differs

ConversionVarianceAmount already combines direct labor and overhead variance, so adding those components again would double-count them.

Keep for the next stage: Save CostBridge and a WO1 bridge with units and cost basis labeled.

2.2. Compare a contrasting cost result

Predict before you run: Is the largest absolute variance also the largest variance relative to output?

Excel

Named inputs: CostBridge (Session 2.1).

Produced outputs: CostBridge: AbsVariance and VariancePerCompletedUnit columns, Review: separate WO1/WO67 cost and timing comparison.

1. In CostBridge add AbsVariance; leave unclosed orders blank:

   Excel — CostBridge[AbsVariance]

   =IF([@TotalVariance]="","",ABS([@TotalVariance]))

2. Filter TotalVariance nonblank and sort AbsVariance descending.

3. Inspect WO67.

4. Retain the ReleasedDate, DueDate, CompletedDate, and ClosedDate columns carried from Orders in the previous merges.

5. Compare WO1 and WO67 on completed quantity, total completion cost, material variance, and conversion variance.

6. Add VariancePerCompletedUnit only where a close exists and completed quantity is positive:

   Excel — CostBridge[VariancePerCompletedUnit]

   =IF(OR([@TotalVariance]="",[@CompletedQty]<=0),"",[@TotalVariance]/[@CompletedQty])

7. Keep LaborSupport in a separate column.

SQL

This repeats the same independently aggregated cost method for WO1 and WO67. It runs independently without a temporary table from an earlier step.

Run each complete statement separately and retain its result before continuing.

c05-contrast.sql

WITH issues AS (SELECT h.WorkOrderID,
       SUM(l.ExtendedStandardCost) AS Issued
FROM MaterialIssue h
JOIN MaterialIssueLine l ON l.MaterialIssueID=h.MaterialIssueID
WHERE h.IssueDate<='2024-02-29'
GROUP BY h.WorkOrderID), labor AS (SELECT WorkOrderID,
       SUM(ExtendedLaborCost) AS LaborSupport
FROM LaborTimeEntry
WHERE WorkDate<='2024-02-29'
GROUP BY WorkOrderID), completions AS (SELECT h.WorkOrderID,
       SUM(l.QuantityCompleted) AS CompletedQty,
       SUM(l.ExtendedStandardMaterialCost) AS CompletedMaterials,
       SUM(l.ExtendedStandardConversionCost) AS CompletedConversion,
       SUM(l.ExtendedStandardTotalCost) AS CompletedTotal
FROM ProductionCompletion h
JOIN ProductionCompletionLine l ON l.ProductionCompletionID=h.ProductionCompletionID
WHERE h.CompletionDate<='2024-02-29'
GROUP BY h.WorkOrderID), closes AS (SELECT WorkOrderID,
       SUM(MaterialVarianceAmount) AS MaterialVariance,
       SUM(ConversionVarianceAmount) AS ConversionVariance,
       SUM(TotalVarianceAmount) AS TotalVariance
FROM WorkOrderClose
WHERE CloseDate<='2024-02-29'
GROUP BY WorkOrderID) SELECT w.WorkOrderID,
       ROUND(COALESCE(i.Issued,0),2) AS Issued,
       ROUND(COALESCE(l.LaborSupport,0),2) AS LaborSupport,
       ROUND(COALESCE(c.CompletedQty,0),2) AS CompletedQty,
       ROUND(COALESCE(c.CompletedMaterials,0),2) AS CompletedMaterials,
       ROUND(COALESCE(c.CompletedConversion,0),2) AS CompletedConversion,
       ROUND(COALESCE(c.CompletedTotal,0),2) AS CompletedTotal,
       x.MaterialVariance,
       x.ConversionVariance,
       x.TotalVariance
FROM WorkOrder w
LEFT JOIN issues i ON i.WorkOrderID=w.WorkOrderID
LEFT JOIN labor l ON l.WorkOrderID=w.WorkOrderID
LEFT JOIN completions c ON c.WorkOrderID=w.WorkOrderID
LEFT JOIN closes x ON x.WorkOrderID=w.WorkOrderID
WHERE w.ReleasedDate>='2024-01-01' AND w.ReleasedDate<'2024-02-01' AND w.WorkOrderID IN(1,67)
ORDER BY w.WorkOrderID;

Checkpoint. WO67 has the largest absolute closed variance in this cohort: 5,463.38, including 647.23 material and 4,816.15 conversion. It completed 32.99 units at 20,387.82 standard total cost. It completed February 7 against a February 4 due date and closed February 13.

The larger absolute variance partly reflects scale. It also has operation support that WO1 lacks. Inspect the dates and cost components before attributing the difference to worker productivity or a particular work center.

If your result differs

A blank variance is not a zero variance. Rank closed orders only and show the 46 orders not yet closed separately.

Keep for the next stage: Save the two-order comparison and two competing explanations.

Session stopping point

Your cost bridge should preserve 336 orders and separate missing close evidence from zero variances. Save the two-order comparison before examining capacity.

Session 3 — Relate timing to capacity and recommend a follow-up

A cohort’s late completion can coincide with capacity pressure without proving causation. Compare dates and work centers, then identify the evidence needed to test the explanation.

Which totals can be joined without multiplication?

Reading guide: Summarize each many-side independently to the comparison grain before joining; keep other analytical grains separate.

Expand the diagram for a larger view with zoom and pan controls.

Download editable Draw.io

Read the figure in words

• Summarize issue, labor, completion, and close independently by work order.
• Join each one-row summary to the fixed 336-order cohort.
• Analyze daily work-center capacity separately from order cost.

Selected released evidence and teaching explanation. Summarize each many-side independently to the comparison grain before joining; keep other analytical grains separate.

3.1. Look beneath the period capacity total

Predict before you run: Could total scheduled hours be below capacity while individual days are over capacity?

Excel

Named inputs: WorkOrderOperationScheduleData (raw query from Session 1.2), WorkCenterCalendarData ← WorkCenterCalendar (import here).

Produced outputs: LoadByCenterDay: one schedule total per center/day, DailyCapacity: one calendar capacity row per center/day; retained for inspection, UnmatchedScheduleDates: reverse Left anti merge, CapacityByCenter: independently summed daily capacity/load.

1. Reference the raw WorkOrderOperationScheduleData as LoadByCenterDay, filter ScheduleDate January 1–February 29, 2024, group by WorkCenterID and ScheduleDate, and sum ScheduledHours.

2. Import WorkCenterCalendar as WorkCenterCalendarData.

3. Reference it as DailyCapacity and filter CalendarDate to the same dates.

4. Left merge DailyCapacity to LoadByCenterDay on WorkCenterID and CalendarDate = ScheduleDate, selecting keys in that order.

5. Replace unmatched scheduled totals with zero after checking for schedule dates absent from the calendar: reference LoadByCenterDay as UnmatchedScheduleDates and Left anti merge to DailyCapacity on the same two keys, in the same order.

6. In Power Query add a Custom Column named AboveCapacity:

   Power Query — Daily capacity / AboveCapacity

   if [ScheduledHours] > [AvailableHours] then 1 else 0

7. Preserve DailyCapacity; reference it as CapacityByCenter and group by WorkCenterID to sum available hours, scheduled hours, and AboveCapacity.

8. For WO67’s centers inspect daily rows around January 30–February 5.

SQL

Daily schedule totals are joined to the calendar before work-center aggregation. DaysAboveCapacity preserves daily peaks that a comparison of period totals would hide.

Run each complete statement separately and retain its result before continuing.

c05-capacity.sql

WITH load AS (SELECT WorkCenterID,
       ScheduleDate,
       SUM(ScheduledHours) AS Hours
FROM WorkOrderOperationSchedule
WHERE ScheduleDate>='2024-01-01' AND ScheduleDate<='2024-02-29'
GROUP BY WorkCenterID,ScheduleDate) SELECT c.WorkCenterID,
       ROUND(SUM(c.AvailableHours),2) AS AvailableHours,
       ROUND(SUM(COALESCE(l.Hours,0)),2) AS ScheduledHours,
       SUM(CASE WHEN COALESCE(l.Hours,0)>c.AvailableHours THEN 1 ELSE 0 END) AS DaysAboveCapacity
FROM WorkCenterCalendar c
LEFT JOIN load l ON l.WorkCenterID=c.WorkCenterID AND l.ScheduleDate=c.CalendarDate
WHERE c.CalendarDate>='2024-01-01' AND c.CalendarDate<='2024-02-29'
GROUP BY c.WorkCenterID
ORDER BY c.WorkCenterID;

Checkpoint. Assembly: 9,230.89 available hours, 5,316.59 scheduled, but two days above capacity. Finishing and Test has seven days above capacity. Period totals alone conceal daily peaks.

This capacity comparison covers all scheduled work, so it provides context for the cohort rather than allocating all capacity pressure to it. Available work-center hours are not interchangeable with employee paid hours.

If your result differs

Keep calendar dates with no load. Check calendar coverage before concluding there are no overloaded days.

Keep for the next stage: Save the center summary and one daily detail view supporting or weakening your explanation.

3.2. Prepare the production review

Predict before you run: What can you recommend now, and what still needs an operational explanation?

Excel

Named inputs: Orders, CostBridge, DailyCapacity, and CapacityByCenter (earlier stages).

Produced outputs: Review: timing, cost, and capacity sections; recommendation and chart.

1. Build Review with three sections: cohort timing, two-order cost comparison, and capacity context.

2. Link their verified results.

3. Create a column chart for the five centers’ counts of days above capacity.

4. Write a recommendation that names the planner or production role, identifies a specific date/center or missing record to inspect, and explains how that inspection would affect the proposed schedule change.

SQL

Use the saved result tables from the preceding stages to construct this comparison and written interpretation. Retain the query names, filters, source keys, and amounts in your case notes. An ordered result table can communicate the comparison without making a chart or switching tools.

Checkpoint. The worked evidence shows 46 completed but unclosed orders, missing operation support for WO1, and daily capacity peaks despite spare period totals. These are distinct follow-ups.

Recommend checking daily scheduling and missing support before changing staffing or treating variance as inefficiency. A cost variance, a late completion, and a documentation gap need not share one cause.

If your result differs

Do not claim that spare aggregate hours prove enough capacity on every day, or that standard-cost variance measures cash overspending.

Keep for the next stage: Save a 400–500 word memo with one prioritized action and a stated alternative explanation.

Session stopping point

Save a reproducible cost bridge and a bounded recommendation. Keep unresolved evidence visible.

When are goods available, and when is accounting complete?

Reading guide: Compare work orders on parallel dated tracks. Finished-goods availability and accounting close are different milestones.

Expand the diagram for a larger view with zoom and pan controls.

Download editable Draw.io

Read the figure in words

• WO1 completes January 26 and closes February 1.
• WO67 completes February 7 and closes February 13.
• Across the cohort, 336 are complete but only 290 closed by February 29.

Selected released evidence and teaching explanation. Compare work orders on parallel dated tracks. Finished-goods availability and accounting close are different milestones.

Prepare the Decision

The production review should separate cost accounting from operational timing. The data supports specific investigations into documentation, daily capacity peaks, and the 46 orders awaiting close at the cutoff. It does not support a blanket conclusion that employees were inefficient or that every late order arose from inadequate capacity. For an AIS submission, complete the short selected-work-order WIP checkpoint in Lesson 9. It uses work order 1 as a separate trace. The wider pooled-clearing reconciliation remains optional, and its costs cannot all be assigned to that order.

Deliverable: A work-order cost bridge, completion/close comparison, capacity evidence, and a production review memo. Include the source file, selection dates, source IDs, measure definitions, checks, evidence limitations, and the next action. A clearly ordered SQL result table can support the conclusion, with a chart added if useful.

Independent Challenge

Use work orders released in February 2024 and assess events through March 31. Rebuild all four cost summaries, select a contrasting order by an explicit rule, and defend one operational follow-up.

Planning hint

Keep the release cohort fixed while changing event cutoffs. Retain orders without a close because their unfinished position is part of the population to explain.

Assessment criteria: Assess cohort integrity, separate aggregation, standard versus actual support, completion versus close, capacity scope, and a recommendation that acknowledges uncertainty. Your instructor evaluates the method, evidence, and reasoning. Model answers for this challenge remain in private course materials.

Optional worked extension — Read the replenishment support

Follow WO67’s recommendation 777 into its item, warehouse, and planning week. This is one planning trace, not a reconstruction of the whole planning system.

Excel

Named inputs: SupplyPlanRecommendationData ← SupplyPlanRecommendation and DemandForecastData ← DemandForecast (import here).

Produced outputs: PlanningSupport: recommendation 777 matched to current forecast by item/warehouse/week.

1. Import SupplyPlanRecommendation as SupplyPlanRecommendationData and reference it to filter ID 777.

2. Import DemandForecast as DemandForecastData and filter IsCurrent = 1.

3. Reference the selected recommendation as PlanningSupport and Left merge on ItemID, WarehouseID, and BucketWeekStartDate = ForecastWeekStartDate in that order.

4. Keep forecast approval and recommended quantity separate because a gross requirement can include more than forecast demand.

SQL

Use the following source trace for this optional extension.

c05-planning.sql

SELECT s.SupplyPlanRecommendationID,
       s.ItemID,
       s.WarehouseID,
       s.BucketWeekStartDate,
       s.BucketWeekEndDate,
       s.DriverType,
       s.GrossRequirementQuantity,
       s.ProjectedAvailableQuantity,
       s.RecommendedOrderQuantity,
       s.NeedByDate,
       s.ReleaseByDate,
       f.DemandForecastID,
       f.ForecastQuantity,
       f.ApprovedDate
FROM SupplyPlanRecommendation s
LEFT JOIN DemandForecast f ON f.ItemID=s.ItemID AND f.WarehouseID=s.WarehouseID AND f.ForecastWeekStartDate=s.BucketWeekStartDate AND f.IsCurrent=1
WHERE s.SupplyPlanRecommendationID=777
ORDER BY f.DemandForecastID;

Worked check. Recommendation 777 has Sales Backlog as its driver, gross requirement 36.25, recommended quantity 32.99, and need-by date February 4. The matching current forecast is 10.96, approved January 19. Keep backlog, availability, and the recommendation calculation distinct because forecast alone does not explain the recommended quantity.

Further queries and earlier case topics

Recommended Query Sequence

These canonical queries extend the investigation. Their populations, dates, and measures may differ from the worked case, so read their definitions before comparing results. These optional references extend the complete guided routes above. Earlier specialist topics consolidated into this case remain accessible through this reference sequence.

Choose a reference after completing the guided investigation and verify its scope before comparing totals.

1. Forecast vs Actual Demand by Week Item Group Collection Lifecycle

   Optional extension; compare this query’s scope with the guided case.

2. Forecast Error and Bias by Collection Style Family

   Optional extension; compare this query’s scope with the guided case.

3. Supply Plan Driver Mix by Collection and Supply Mode

   Optional extension; compare this query’s scope with the guided case.

4. Inventory Coverage and Projected Stockout Risk

   Optional extension; compare this query’s scope with the guided case.

5. Expedite Pressure by Item Family and Month

   Optional extension; compare this query’s scope with the guided case.

6. Recommendation Conversion by Type Priority Planner

   Optional extension; compare this query’s scope with the guided case.

7. Rough Cut Capacity Load vs Available Hours

   Optional extension; compare this query’s scope with the guided case.

8. BOM Standard Cost Rollup

   Optional extension; compare this query’s scope with the guided case.

9. Unit Cost Bridge Dm Dl Varoh Fixedoh

   Optional extension; compare this query’s scope with the guided case.

10. Work Center Activity and Operation Hours

    Optional extension; compare this query’s scope with the guided case.

11. Paid Hours vs Productive Labor by Work Center

    Optional extension; compare this query’s scope with the guided case.

12. Manufacturing Cost Component Bridge

    Optional extension; compare this query’s scope with the guided case.

Next Steps

Continue to Explain Staffing, Overtime, and Payroll Cost. Keep your completed evidence and use the Query Library for a separate extension.