8. Investigate Exceptions and Recommend Controls

The controller requests a focused controls review. Investigate alternative explanations for each flag before deciding what the evidence establishes. You will examine repeated supplier references and overtime approval support, then recommend controls with a clear owner, procedure, and assessment method.

Advanced · 4 sessions of 60–90 minutes

Prepare

Complete one earlier process case
BPMN Process and Internal Control Workshop (recommended)

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.
Define an explicit criterion and denominator, distinguishing time entries from people and an exception flag from a proven control failure.

Tools

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

Produce

An exception register, two source-supported workpapers, and prioritized control recommendations.

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

Worked scope. Duplicate supplier invoice and check references across the entire recorded history, plus approved overtime entries with positive overtime dated October 1–December 31, 2026. These populations have different denominators. The review uses source records, not instructor anomaly labels or the support workbook.

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

Preparation: Complete one earlier process case, BPMN Process and Internal Control Workshop (recommended). 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.
Define an explicit criterion and denominator, distinguishing time entries from people and an exception flag from a proven control failure.

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
PurchaseInvoice	T37_PurchaseInvoice	PurchaseInvoiceData
PurchaseInvoiceLine	T38_PurchaseInvoiceLine	PurchaseInvoiceLineData
DisbursementPayment	T39_DisbursementPayment	DisbursementPaymentData
TimeClockEntry	T66_TimeClockEntry	TimeClockEntryData
OvertimeApproval	T65_OvertimeApproval	OvertimeApprovalData
PayrollRegister	T69_PayrollRegister	PayrollRegisterData
PayrollPayment	T71_PayrollPayment	PayrollPaymentData

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.

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.
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.
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.
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.
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.
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 risks, criteria, and evidence

Create Case08.xlsx or Case08.sql and a Review note. Use four conclusion categories: supported exception, explained timing or valid circumstance, unresolved evidence, and no exception under the stated test. A query flag is the starting point.

What turns a risk into an investigation?

Reading guide: Arrows show steps in the investigation. Confirm record keys, dates, and scope before interpreting a result.

Define the risk and the criterion to test. Select a population with a stated denominator and date boundary. Collect source evidence before classifying a finding. — Expand the diagram for a larger view with zoom and pan controls.

1.1. Specify two review criteria

Predict before you run: Could the same number of flags mean different things in different populations?

Excel
SQL

Named inputs: PurchaseInvoiceData ← PurchaseInvoice and DisbursementPaymentData ← DisbursementPayment (import here; full populations retained), TimeClockEntryData ← TimeClockEntry (import here; raw query retained).

Produced outputs: CheckPayments: nonblank check references only, Overtime: Q4 2026 approved entries with positive overtime, Review: separate population counts and criteria.

Import PurchaseInvoice as PurchaseInvoiceData without a date filter.
Import DisbursementPayment as DisbursementPaymentData without removing rows; retain both complete raw queries.
Create a Reference named CheckPayments, filter CheckNumber not blank, and load that reference for the check-reference test.
Keep the original query available for invoice payment investigations.
Import TimeClockEntry as TimeClockEntryData; preserve the raw query and reference it as Overtime, filtered WorkDate October 1–December 31, 2026, ClockStatus Approved, and OvertimeHours greater than zero.
Load the last selection as Overtime.
On Review label B2 Invoice documents, B3 Payments with a check reference, and B4 Approved overtime records.

Enter the counts:

Excel — Review!B2
=ROWS(PurchaseInvoiceData[PurchaseInvoiceID])

Excel — Review!B3
=ROWS(CheckPayments[DisbursementID])

Excel — Review!B4
=ROWS(Overtime[TimeClockEntryID])

For the invoice test define a repeated reference as the same SupplierID and exact InvoiceNumber occurring more than once; for checks use SupplierID and nonblank CheckNumber.
For overtime, first test missing linked approval or hours above linked ApprovedHours.
Treat identity, status, and timing as further evidence checks.

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.

c08-populations.sql
SELECT 'Supplier invoices: released history' AS Population,
       COUNT(*) AS Documents
FROM PurchaseInvoice
UNION ALL SELECT 'Payments with nonblank check reference: released history',
       COUNT(*)
FROM DisbursementPayment
WHERE CheckNumber IS NOT NULL AND TRIM(CheckNumber)<>''
UNION ALL SELECT 'Approved overtime entries: Q4 2026',
       COUNT(*)
FROM TimeClockEntry
WHERE WorkDate>='2026-10-01' AND WorkDate<'2027-01-01' AND ClockStatus='Approved' AND OvertimeHours>0
ORDER BY Population;

Checkpoint. 40,204 supplier invoices; 12,130 payments with a nonblank check reference; 1,895 approved positive-overtime entries in Q4 2026.

The tests do not cover every AP or payroll risk. Note that exact reference matching misses differently formatted duplicates before interpreting a zero or low rate.

If your result differs

Do not filter AP to Q4 simply to match overtime dates. The worked duplicate-reference evidence is across recorded history.

Keep for the next stage: Save a scope sheet with risks, criteria, exclusions, denominators, and expected evidence.

Session stopping point

Save the scope and criteria before viewing flags. This prevents the results from silently redefining the question.

Session 2 — Investigate repeated AP references

A repeated reference may indicate a duplicate document, a reused reference, or a recording problem. Inspect the linked commercial evidence and payments before alleging duplicate payment.

What evidence distinguishes a repeated reference from duplicate payment?

Reading guide: Arrows show steps in the investigation. Confirm record keys, dates, and scope before interpreting a result.

Group invoice or check references within the same supplier. Read each flagged document, its receipt links, and payment trail. Classify what repeats and what additional evidence is required. — Expand the diagram for a larger view with zoom and pan controls.

2.1. Find groups without multiplying documents

Predict before you run: Should the denominator count duplicate groups or document rows?

Excel
SQL

Named inputs: PurchaseInvoiceData, DisbursementPaymentData, and CheckPayments (Session 1.1).

Produced outputs: InvoiceGroups and CheckGroups: repeated document references, PaymentsByInvoice: independently summed payments, DuplicateInvoices and DuplicateChecks: document-level exception evidence.

In Power Query Reference PurchaseInvoiceData as InvoiceGroups.
Group by SupplierID and InvoiceNumber; Count Rows as Occurrences; filter Occurrences greater than 1.
Reference InvoiceGroups as DuplicateInvoices and Inner merge to PurchaseInvoiceData on both keys; expand the document columns.
Separately reference CheckPayments as CheckGroups, group by SupplierID and CheckNumber, Count Rows as Occurrences, and retain counts greater than 1.
Reference CheckGroups as DuplicateChecks and Inner merge to CheckPayments on both keys; expand the document columns.
Reference DisbursementPaymentData as PaymentsByInvoice, group by PurchaseInvoiceID and Sum Amount as PaidAmount, then Left merge that unique-key summary to DuplicateInvoices.
Keep the grouped-key queries and full source queries available; load DuplicateInvoices and DuplicateChecks for inspection.

groups identifies exact supplier/reference pairs occurring more than once. Joining the group back retrieves each flagged document. The payment SUM is independent, so multiple payments cannot duplicate invoice values.

Blank references are excluded before grouping. The same supplier and CheckNumber must match. Retain both group counts and payment-row counts because they answer different questions.

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

c08-duplicates.sql
WITH groups AS (SELECT SupplierID,
       InvoiceNumber,
       COUNT(*) AS Occurrences
FROM PurchaseInvoice
GROUP BY SupplierID,InvoiceNumber
HAVING COUNT(*)>1) SELECT i.SupplierID,
       i.InvoiceNumber,
       i.PurchaseInvoiceID,
       i.InvoiceDate,
       i.GrandTotal,
       g.Occurrences,
       ROUND(COALESCE((SELECT SUM(p.Amount)
FROM DisbursementPayment p
WHERE p.PurchaseInvoiceID=i.PurchaseInvoiceID),0),2) AS Paid
FROM PurchaseInvoice i
JOIN groups g ON g.SupplierID=i.SupplierID AND g.InvoiceNumber=i.InvoiceNumber
ORDER BY i.SupplierID,i.InvoiceNumber,i.PurchaseInvoiceID;

c08-checks.sql
WITH groups AS (SELECT SupplierID,
       CheckNumber,
       COUNT(*) AS Occurrences
FROM DisbursementPayment
WHERE CheckNumber IS NOT NULL AND TRIM(CheckNumber)<>''
GROUP BY SupplierID,CheckNumber
HAVING COUNT(*)>1) SELECT p.SupplierID,
       p.CheckNumber,
       p.DisbursementID,
       p.PurchaseInvoiceID,
       p.PaymentDate,
       p.Amount,
       p.ClearedDate,
       g.Occurrences
FROM DisbursementPayment p
JOIN groups g ON g.SupplierID=p.SupplierID AND g.CheckNumber=p.CheckNumber
ORDER BY p.SupplierID,p.CheckNumber,p.DisbursementID;

Checkpoint. Six invoice-reference groups contain 12 invoice rows. Two check-reference groups contain four payment rows. The first invoice pair is supplier 1, reference V0001-2024-000028, invoice IDs 28 and 44.

Twelve flagged invoice rows and six groups are different counts. A percentage based on groups divided by document rows would mix units. Keep the invoice and payment flag rates separate.

If your result differs

Group within supplier. A reference repeated by two different suppliers is not the same test. Exclude blank check references explicitly.

Keep for the next stage: Save both group lists and all flagged document rows.

2.2. Follow the first invoice pair to receipts and payment

Predict before you run: Do invoices 28 and 44 represent the same goods and amount?

Excel
SQL

Named inputs: PurchaseInvoiceLineData ← PurchaseInvoiceLine (import here), DisbursementPaymentData (complete raw query from Session 1.1).

Produced outputs: InvoicePairLines: invoices 28/44 line evidence, InvoicePairPayments: all-method payments for invoices 28/44, Review: separate invoice register rows.

Import PurchaseInvoiceLine as PurchaseInvoiceLineData; reference it as InvoicePairLines and filter PurchaseInvoiceID 28 or 44.
Keep GoodsReceiptLineID, POLineID, ItemID, Quantity, and LineTotal.
Reference the original DisbursementPaymentData as InvoicePairPayments and filter to those same invoice IDs without the nonblank-check filter; these payments may use another method.
Compare document dates and amounts, receipt links, and payment IDs.
Retain a separate register row for each invoice.

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.

c08-invoice-detail.sql
SELECT l.PurchaseInvoiceID,
       l.PILineID,
       l.GoodsReceiptLineID,
       l.POLineID,
       l.ItemID,
       l.Quantity,
       l.LineTotal
FROM PurchaseInvoiceLine l
WHERE l.PurchaseInvoiceID IN(28,44)
ORDER BY l.PurchaseInvoiceID,l.PILineID;

c08-payment-detail.sql
SELECT DisbursementID,
       PurchaseInvoiceID,
       PaymentDate,
       Amount,
       CheckNumber,
       ClearedDate
FROM DisbursementPayment
WHERE PurchaseInvoiceID IN(28,44)
ORDER BY PaymentDate,DisbursementID;

Checkpoint. Invoice 28 is 629.94 for item 220, receipt line 48; invoice 44 is 1,504.07 for item 193, receipt line 41. Payments 127 and 51 settle the different amounts on April 1 and March 23.

The repeated invoice reference is supported. The different amounts, items, and receipt links weaken the claim that these are duplicate economic transactions. The source data alone does not show the supplier’s original invoice images or a bank confirmation.

If your result differs

A paid status or matching reference cannot establish duplicate payment. Do not sum the pair and label it a proven loss.

Keep for the next stage: Write Workpaper A: criterion, flagged keys, source trail, alternative explanation, conclusion, missing evidence, and next action.

2.3. Investigate the repeated check example

Predict before you run: Does one repeated check reference establish that the bank paid twice?

Excel
SQL

Named inputs: DuplicateChecks and PurchaseInvoiceData (earlier stages).

Produced outputs: DuplicateChecks: invoice lookup columns, Review: SupplierID 1 / CHK0006076 evidence, separate from repeated-invoice criterion.

In DuplicateChecks filter SupplierID 1 and CheckNumber CHK0006076.
Inspect DisbursementIDs 6076 and 6107, their PurchaseInvoiceIDs, PaymentDate, Amount, and ClearedDate.

Add InvoiceNumber to DuplicateChecks:

Excel — DuplicateChecks[InvoiceNumber]
=XLOOKUP([@PurchaseInvoiceID],PurchaseInvoiceData[PurchaseInvoiceID],PurchaseInvoiceData[InvoiceNumber],"No match",0)

Add InvoiceGrandTotal:

Excel — DuplicateChecks[InvoiceGrandTotal]
=XLOOKUP([@PurchaseInvoiceID],PurchaseInvoiceData[PurchaseInvoiceID],PurchaseInvoiceData[GrandTotal],"No match",0)

Compare with the invoice-reference workpaper, keeping the two criteria separate.

Checkpoint. The two payment rows refer to invoices 3101 and 4070, amounts 1,468.04 and 1,104.48, both dated January 1, 2025, with different cleared dates.

The repeated check reference warrants treasury/AP follow-up. Distinct payment rows and clearing fields still do not replace external banking evidence. Classify the reference exception as supported and the duplicate-cash-loss claim as unresolved.

If your result differs

Do not confuse CheckNumber with the unique DisbursementID.

Keep for the next stage: Retain the payment pair and specify the bank or supplier evidence needed.

Session stopping point

Save Workpaper A and both AP exception registers. Your conclusion must separate a confirmed reference problem from an unproven loss.

Session 3 — Investigate overtime approval support

Approved payroll time and a linked overtime approval are different records. A positive overtime amount can be approved for pay while its separate authorization link is missing.

Which approval supports the overtime hours?

Reading guide: Arrows show steps in the investigation. Confirm record keys, dates, and scope before interpreting a result.

Select approved time entries with positive overtime in the defined quarter. Left join the linked overtime approval so missing links remain visible. Inspect hours, identity, period, dates, and status before concluding. — Expand the diagram for a larger view with zoom and pan controls.

3.1. Classify the hours test

Predict before you run: Why would an Inner join hide the very exception you want to inspect?

Excel
SQL

Named inputs: Overtime (Session 1.1), Approvals ← OvertimeApproval (import here).

Produced outputs: OvertimeReview: time-entry detail and approval match; HoursTest classification, HoursTestCounts: entry-count PivotTable.

Import OvertimeApproval as Approvals.
In Power Query Left outer merge Overtime to Approvals on OvertimeApprovalID.
Expand the approval ID as MatchedApprovalID, ApprovedHours, ApprovedDate as OvertimeApprovedDate, and Status.
Keep the time record’s original ApprovedDate separately.
Load as OvertimeReview.

Add HoursTest:

Excel — OvertimeReview[HoursTest]
=IF([@MatchedApprovalID]="","Missing linked approval",IF([@ApprovedHours]<[@OvertimeHours],"Hours exceed approval","Hours supported"))

Create a PivotTable with HoursTest in Rows and Count of TimeClockEntryID in Values.
Preserve zero approved hours as a real value.

LEFT JOIN retains missing approvals. CASE applies the hours-support criteria in order: no linked record, insufficient approved hours, then hours supported. The WHERE clause fixes the eligible quarter, approved clock status, and positive overtime denominator.

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

c08-overtime.sql
SELECT t.TimeClockEntryID,
       t.EmployeeID,
       t.PayrollPeriodID,
       t.WorkDate,
       t.OvertimeHours,
       t.OvertimeApprovalID,
       a.ApprovedHours,
       a.ApprovedDate,
       a.Status,
       CASE WHEN a.OvertimeApprovalID IS NULL THEN 'Missing linked approval' WHEN a.ApprovedHours<t.OvertimeHours THEN 'Hours exceed approval' ELSE 'Hours supported' END AS HoursTest
FROM TimeClockEntry t
LEFT JOIN OvertimeApproval a ON a.OvertimeApprovalID=t.OvertimeApprovalID
WHERE t.WorkDate>='2026-10-01' AND t.WorkDate<'2027-01-01' AND t.ClockStatus='Approved' AND t.OvertimeHours>0
ORDER BY t.WorkDate,t.TimeClockEntryID;

Checkpoint. 1,895 eligible time entries: 336 missing linked approval, zero with hours exceeding a linked approval, and 1,559 with hours supported. The missing-link rate is 17.73% of eligible entries.

These 336 time entries lack a linked approval record under this test; this does not establish that the associated employees lacked authorization outside the file. The 1,559 hours-supported rows still need identity, status, and date checks for a fuller authorization conclusion.

If your result differs

Keep null matches. A zero-row exception list after an Inner join is not evidence of effective control.

Keep for the next stage: Save the complete classification and the flagged subset with stable IDs.

3.2. Compare a flag with an hours-supported record

Predict before you run: What does the comparison reveal about the limits of the first test?

Excel
SQL

Named inputs: OvertimeReview and Approvals (Session 3.1).

Produced outputs: OvertimeReview: additional approval-identity/date fields, Review: selected missing-link and hours-supported evidence.

Filter OvertimeReview to TimeClockEntryIDs 49436 and 49581.
Return to the merge expansion and add EmployeeID as ApprovalEmployeeID, PayrollPeriodID as ApprovalPeriodID, and WorkDate as ApprovalWorkDate from Approvals, keeping time-record values beside them.
Compare employee, period, work date, status, and approval date.
For the flagged record, retain blank approval fields and request supporting evidence rather than filling them from a nearby employee or day.

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.

c08-compare.sql
SELECT t.TimeClockEntryID,
       t.EmployeeID,
       t.PayrollPeriodID,
       t.WorkDate,
       t.OvertimeHours,
       t.ApprovedDate AS TimeApprovedDate,
       t.OvertimeApprovalID,
       a.EmployeeID AS ApprovalEmployeeID,
       a.PayrollPeriodID AS ApprovalPeriodID,
       a.WorkDate AS ApprovalWorkDate,
       a.ApprovedHours,
       a.ApprovedDate AS OvertimeApprovedDate,
       a.Status
FROM TimeClockEntry t
LEFT JOIN OvertimeApproval a ON a.OvertimeApprovalID=t.OvertimeApprovalID
WHERE t.TimeClockEntryID IN(49436,49581)
ORDER BY t.TimeClockEntryID;

Checkpoint. Entry 49436 is employee 17’s 4.50 overtime hours on October 1, linked to approval 16587 for 4.50 hours. Entry 49581 is employee 18’s 0.34 overtime hours on October 5 with no linked overtime approval.

The comparison supports a missing-link finding for 49581 under this review’s criterion. It does not prove the work was not performed, the employee was overpaid, or anyone acted dishonestly.

If your result differs

Do not use an anomaly label as evidence. Compare source fields and distinguish a missing record from a contradictory record.

Keep for the next stage: Write Workpaper B with the tested criterion, both records, the evidence gap, and a proposed follow-up.

Session stopping point

Save the overtime register and Workpaper B. State the denominator and the boundaries of the test in its conclusion.

Session 4 — Prioritize findings and design assessable controls

The final task is a reasoned recommendation. Count and amount can help prioritize follow-up, but frequency alone is not a measure of proven financial loss.

How should an exception be classified?

Reading guide: An investigation can reach several defensible classifications. A query flag alone does not determine the conclusion.

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

Download editable Draw.io

Read the figure in words

Flag: the query criterion identifies a record.
Investigation: compare supporting documents and alternative explanations.
Conclusion: supported exception, explained circumstance, unresolved evidence, or no exception under the test.

Conceptual teaching diagram. An investigation can reach several defensible classifications. A query flag alone does not determine the conclusion.

4.1. Build a prioritized exception register

Predict before you run: Which issue warrants immediate evidence collection, and which warrants a process improvement?

Excel
SQL

Named inputs: Earlier AP/check/overtime workpapers with their separate populations.

Produced outputs: ExceptionRegister: bounded findings and follow-up priorities.

Create an ExceptionRegister table with FindingID, Test, Population, SourceKeys, ObservedCondition, Evidence, AlternativeExplanation, ConclusionCategory, PotentialConsequence, Owner, and NextAction.
Enter the worked AP pair, repeated-check pair, and overtime comparison.
Keep potential consequences as hypotheses unless amounts are supported.
Rank follow-ups using urgency, plausible impact, and ease of obtaining decisive evidence; explain the ranking in prose rather than inventing a numeric risk score.

Checkpoint. The worked evidence supports repeated references and missing approval links. It leaves duplicate economic loss and off-system authorization unresolved.

A reasonable priority is to obtain supplier/bank evidence for repeated payment references and restore verifiable overtime authorization links before claiming loss or misconduct. Other priorities are acceptable if they explain impact and uncertainty.

If your result differs

Do not combine the 336 overtime flags and 16 AP document flags into one overall exception rate.

Keep for the next stage: Save the exception register and a brief prioritization rationale.

4.2. Propose controls that can be tested

Predict before you run: What would a reviewer observe to determine whether your proposed control operated?

Excel
SQL

Named inputs: ExceptionRegister (Session 4.1).

Produced outputs: ControlReview: proposed controls, test populations, procedures, and failure criteria.

Create a ControlReview table with Risk, ProposedControl, Owner, Frequency, RetainedEvidence, PopulationForTesting, TestProcedure, and FailureCriterion.
For AP, propose checking supplier/reference uniqueness before posting, with documented review of legitimate repeated references.
For overtime, propose checking employee, period, date, approved hours, and approval status before payroll release, with an explicit late-approval escalation.
State these as proposed controls; the case has not established an existing company policy.
Include a retrospective sample test and a repeatable population query as complementary procedures.

Checkpoint. An assessable recommendation identifies who performs the check, when it occurs, what evidence is retained, and what constitutes failure. A lower flag count alone is not sufficient: deleted or disconnected records could also reduce it.

For AP, inspect recorded review/override evidence for a sample of repeated references and verify all invoices remain in the population. For overtime, trace selected paid hours to valid approvals and inspect escalation evidence. Test both flagged and unflagged records.

If your result differs

A recommendation to “improve controls” is incomplete without an owner, evidence, and a criterion a reviewer can assess.

Keep for the next stage: Write a 600–800 word controls memo with two workpapers, priorities, proposed controls, and test procedures.

Session stopping point

Save both workpapers, the register, and controls memo. Your independent challenge begins with new evidence and no public model conclusion.

How can the proposed control be assessed?

Reading guide: This is a proposed classroom assessment cycle. Preserve its population and denominator when repeating the assessment.

Define the control owner, timing, and retained evidence. Test a stated population and trace both flagged and unflagged examples. Evaluate failures, investigate causes, and repeat the assessment without changing the denominator silently. — Expand the diagram for a larger view with zoom and pan controls.

Prepare the Decision

The worked review demonstrates why a flag is not a finding about fraud or financial loss. Repeated references and missing approval links are supported conditions whose economic consequences require additional evidence. Controls become useful recommendations when the owner, timing, evidence, test population, and failure criteria are explicit.

Deliverable: An exception register, two source-supported workpapers, and prioritized control recommendations. 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

Investigate Q3 2026 overtime using the same eligibility and hours-support criteria. For AP, define a new review of supplier invoice references within 2025 and investigate any flagged groups, or document a clean result. Keep denominators distinct and justify your follow-up priorities.

Planning hint

A new population can produce fewer flags, different explanations, or no exceptions. Evaluate evidence completeness before treating a zero result as assurance. Preserve all eligible records when joining support.

Assessment criteria: Assess explicit criteria and denominators, source tracing, alternative explanations, proportionate conclusions, and controls that can be assessed without relying on anomaly labels. Your instructor evaluates the method, evidence, and reasoning. Model answers for this challenge remain in private course materials.

Optional specialist investigations

Extend the same risk-to-evidence method to pricing, planning support, or employee authority. These are separate scopes, not extra flags to append to the core denominator.

Excel
SQL

Named inputs: Choose only one optional investigation and import its named source tables before use, Read the selected canonical query for exact source keys, dates, and population.

Produced outputs: SpecialistWorkpaper: chosen criterion, separate source detail, valid joins, and bounded conclusion.

For pricing, relate SalesOrderLine to PriceOverrideApproval and effective price/promotion records.
For planning, relate WorkOrder or PurchaseRequisition to SupplyPlanRecommendation while retaining missing links.
For authority, compare the actor and approval date with the relevant employee-role evidence, distinguishing current assignments from historical authority.
Define a criterion and a population before loading detail; use the optional canonical queries below as maps of the available source fields.

Worked check. Retain an evidence workpaper and state where the records cannot establish historical authorization. These extensions do not supply assessment model answers.

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.

Duplicate AP Reference Detail Review
Detailed invoice and payment reference groups across released history.
Scheduled without Punch and Punch without Schedule Review
Optional extension; compare this query’s scope with the guided case.
Overlapping or Incomplete Punch Review
Optional extension; compare this query’s scope with the guided case.
Absence with Worked Time Review
Optional extension; compare this query’s scope with the guided case.
Overtime without Approval Review
Optional extension; compare this query’s scope with the guided case.
Roster After Termination Review
Optional extension; compare this query’s scope with the guided case.
Duplicate Payment Reference Review
Optional extension; compare this query’s scope with the guided case.
Payroll Control Review
Optional extension; compare this query’s scope with the guided case.
Missing Routing or Operation Link Review
Optional extension; compare this query’s scope with the guided case.
Operation Sequence and Final Completion Review
Optional extension; compare this query’s scope with the guided case.
Released Work Orders Due without Actual Start Summary
Optional extension; compare this query’s scope with the guided case.
Released Work Orders Due without Actual Start Review
Optional extension; compare this query’s scope with the guided case.
Executive Role Uniqueness and Control Assignment Review
Optional extension; compare this query’s scope with the guided case.
Current State Employee Assignment Review
Optional extension; compare this query’s scope with the guided case.
Approval Authority Limit Review
Optional extension; compare this query’s scope with the guided case.
Item Master Completeness Review
Optional extension; compare this query’s scope with the guided case.
Item Status Alignment Review
Optional extension; compare this query’s scope with the guided case.
Discontinued or Prelaunch Item Activity Review
Optional extension; compare this query’s scope with the guided case.
Approval Authority Review by Expected Role Family
Optional extension; compare this query’s scope with the guided case.
Terminated Employee Activity Rollup by Process Area
Optional extension; compare this query’s scope with the guided case.
Time Clock Exceptions by Employee Supervisor Work Center
Optional extension; compare this query’s scope with the guided case.
Forecast Approval and Override Review
Optional extension; compare this query’s scope with the guided case.
Inactive or Stale Inventory Policy Review
Optional extension; compare this query’s scope with the guided case.
Requisitions and Work Orders without Planning Support
Optional extension; compare this query’s scope with the guided case.
Recommendation Converted After Need by Date Review
Optional extension; compare this query’s scope with the guided case.
Discontinued or Prelaunch Planning Activity Review
Optional extension; compare this query’s scope with the guided case.
Headcount by Cost Center Job Family Status
Optional extension; compare this query’s scope with the guided case.
Terminated Employee Activity Review
Optional extension; compare this query’s scope with the guided case.
Approval Role Review by Org Position
Optional extension; compare this query’s scope with the guided case.
Expired or Overlapping Price List Review
Optional extension; compare this query’s scope with the guided case.
Promotion Scope and Date Mismatch Review
Optional extension; compare this query’s scope with the guided case.
Customer Specific Price List Bypass Review
Optional extension; compare this query’s scope with the guided case.
Sales Below Floor without Approval
Optional extension; compare this query’s scope with the guided case.
Override Approval Completeness Review
Optional extension; compare this query’s scope with the guided case.

Next Steps

Continue to Reports and Analytics to compare broader evidence. Keep your completed evidence and use the Query Library for a separate extension.

Notice a mistake?Report an error on this page

Key Data Sources​

Session 1 — Define risks, criteria, and evidence​

1.1. Specify two review criteria​

Session 2 — Investigate repeated AP references​

2.1. Find groups without multiplying documents​

2.2. Follow the first invoice pair to receipts and payment​

2.3. Investigate the repeated check example​

Session 3 — Investigate overtime approval support​

3.1. Classify the hours test​

3.2. Compare a flag with an hours-supported record​

Session 4 — Prioritize findings and design assessable controls​

4.1. Build a prioritized exception register​

4.2. Propose controls that can be tested​

Prepare the Decision​

Independent Challenge​

Optional specialist investigations​

Recommended Query Sequence​

Next Steps​

Key Data Sources

Session 1 — Define risks, criteria, and evidence

1.1. Specify two review criteria

Session 2 — Investigate repeated AP references

2.1. Find groups without multiplying documents

2.2. Follow the first invoice pair to receipts and payment

2.3. Investigate the repeated check example

Session 3 — Investigate overtime approval support

3.1. Classify the hours test

3.2. Compare a flag with an hours-supported record

Session 4 — Prioritize findings and design assessable controls

4.1. Build a prioritized exception register

4.2. Propose controls that can be tested

Prepare the Decision

Independent Challenge

Optional specialist investigations

Recommended Query Sequence

Next Steps