3. Explain Supplier Obligations at Month End

Accounts payable and the controller are reviewing quarter-end obligations. Receiving knows what arrived, AP knows which bills were received, and the controller has expense estimates awaiting later evidence. You will prepare two schedules and explain how each changes through June. Predict whether a later payment changes the liability that existed on March 31.

Basic · 2 sessions of 60–90 minutes

Prepare

• 1. Meet the Records
• 2. Read an Order
• 3. Connect the Records
• 8. Follow a Purchase
• 11. Follow an Estimate into Settlement

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 receipt, invoice, payment, and estimate evidence separately before explaining GRNI, AP, and accrued-service obligations at each cutoff.

Tools

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

Produce

Separate goods and expense-obligation schedules, selected posting checks, and a quarter-end review note.

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

Worked scope. Supplier 27’s five first-quarter 2024 goods receipts, plus the separate repairs-and-maintenance accrual journals 9, 26, and 43. Review March 31 and subsequent evidence through June 30. The accruals are a separate population, not purchases from supplier 27.

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, 8. Follow a Purchase, 11. Follow an Estimate into Settlement. 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 receipt, invoice, payment, and estimate evidence separately before explaining GRNI, AP, and accrued-service obligations at each cutoff.

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
PurchaseOrder	T33_PurchaseOrder	PurchaseOrderData
PurchaseOrderLine	T34_PurchaseOrderLine	PurchaseOrderLineData
GoodsReceipt	T35_GoodsReceipt	GoodsReceiptData
GoodsReceiptLine	T36_GoodsReceiptLine	GoodsReceiptLineData
PurchaseInvoice	T37_PurchaseInvoice	PurchaseInvoiceData
PurchaseInvoiceLine	T38_PurchaseInvoiceLine	PurchaseInvoiceLineData
DisbursementPayment	T39_DisbursementPayment	DisbursementPaymentData
JournalEntry	T2_JournalEntry	JournalEntryData
GLEntry	T3_GLEntry	GLEntryData
Account	T1_Account	AccountData

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 — Follow received goods into supplier liabilities

Create Case03.xlsx or Case03.sql. Label the goods schedule and accrual schedule separately. A purchase order records a commitment, while a receipt establishes arrival.

Why do purchasing obligations follow two paths?

Reading guide: Goods and accrued services follow separate evidence paths. Compare their obligations without joining their populations together.

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

Download editable Draw.io

Read the figure in words

• Goods: order, receipt, supplier invoice, payment.
• Services: estimate, linked invoice, residual adjustment, payment.
• Keep the two selected populations separate before interpreting obligations.

Conceptual teaching diagram. Goods and accrued services follow separate evidence paths. Compare their obligations without joining their populations together.

1.1. Select the receipt population

Predict before you run: How many receipt lines might one supplier order produce?

Excel

Named inputs: PurchaseOrderData ← PurchaseOrder (import here), GoodsReceiptData ← GoodsReceipt and GoodsReceiptLineData ← GoodsReceiptLine (import here).

Produced outputs: SupplierOrders: supplier 27 order headers, SelectedGoodsReceipts: Q1 2024 receipt headers, Receipts: selected receipt-line detail.

1. Import PurchaseOrder as PurchaseOrderData, GoodsReceipt as GoodsReceiptData, and GoodsReceiptLine as GoodsReceiptLineData.

2. Preserve all three raw queries.

3. Reference PurchaseOrderData as SupplierOrders and filter SupplierID 27.

4. Reference GoodsReceiptData as SelectedGoodsReceipts and filter ReceiptDate January 1 through March 31, 2024; Inner merge to SupplierOrders on PurchaseOrderID.

5. Reference GoodsReceiptLineData, merge to SelectedGoodsReceipts on GoodsReceiptID, and expand ReceiptDate.

6. Load as Receipts.

7. Preserve GoodsReceiptLineID, POLineID, QuantityReceived, and ExtendedStandardCost.

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.

c03-receipts.sql

SELECT r.GoodsReceiptID,
       r.ReceiptDate,
       l.GoodsReceiptLineID,
       l.POLineID,
       l.QuantityReceived,
       l.ExtendedStandardCost
FROM GoodsReceipt r
JOIN GoodsReceiptLine l ON l.GoodsReceiptID=r.GoodsReceiptID
JOIN PurchaseOrder o ON o.PurchaseOrderID=r.PurchaseOrderID
WHERE o.SupplierID=27 AND r.ReceiptDate>='2024-01-01' AND r.ReceiptDate<'2024-04-01'
ORDER BY r.ReceiptDate,r.GoodsReceiptID,l.GoodsReceiptLineID;

Checkpoint. Five receipts and five lines; standard receipt cost totals 7,234.30. Receipt IDs are 174, 175, 253, 396, and 340.

The receipt amount is inventory at standard cost. The supplier invoice may contain a different purchase cost or tax, so receipt cost cannot simply be subtracted from invoice GrandTotal to calculate GRNI.

If your result differs

Filter by receipt date, not purchase-order date. Receipt line IDs 175, 176, 254, 397, and 341 identify a different grain from the headers.

Keep for the next stage: Save Receipts and a receipt-date timeline.

1.2. Trace partial invoice matches

Predict before you run: Will each receipt have exactly one invoice by June 30?

Excel

Named inputs: Receipts (Session 1.1), PurchaseInvoiceLineData ← PurchaseInvoiceLine and PurchaseInvoiceData ← PurchaseInvoice (import here), DisbursementPaymentData ← DisbursementPayment (import here).

Produced outputs: GoodsInvoices: matched goods-invoice lines through June 30, 2024, Payments: selected goods-invoice payments; raw payment query preserved.

1. Import PurchaseInvoiceLine as PurchaseInvoiceLineData, PurchaseInvoice as PurchaseInvoiceData, and DisbursementPayment as DisbursementPaymentData.

2. Preserve the raw queries.

3. Reference PurchaseInvoiceLineData as GoodsInvoices; Inner merge to Receipts on GoodsReceiptLineID.

4. Merge PurchaseInvoiceData on PurchaseInvoiceID and expand InvoiceDate and GrandTotal.

5. Filter InvoiceDate through June 30, 2024; load as GoodsInvoices.

6. Reference DisbursementPaymentData, filter to the selected PurchaseInvoiceIDs, and load as Payments.

7. Add PaidMarch to GoodsInvoices:

   Excel — GoodsInvoices[PaidMarch]

   =SUMIFS(Payments[Amount],Payments[PurchaseInvoiceID],[@PurchaseInvoiceID],Payments[PaymentDate],"<="&DATE(2024,3,31))

8. Add PaidJune:

   Excel — GoodsInvoices[PaidJune]

   =SUMIFS(Payments[Amount],Payments[PurchaseInvoiceID],[@PurchaseInvoiceID],Payments[PaymentDate],"<="&DATE(2024,6,30))

9. Keep LineTotal beside GrandTotal.

10. This worked selection has one selected line per invoice; verify that before summing headers.

SQL

One result row is one matched supplier invoice line. The two correlated payment totals are calculated independently. InvoiceDate limits subsequent invoice evidence to June 30 without establishing payment by that date.

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

c03-matches.sql

SELECT l.GoodsReceiptLineID,
       l.PILineID,
       i.PurchaseInvoiceID,
       i.InvoiceDate,
       l.LineTotal,
       i.GrandTotal,
       ROUND(COALESCE((SELECT SUM(p.Amount)
FROM DisbursementPayment p
WHERE p.PurchaseInvoiceID=i.PurchaseInvoiceID AND p.PaymentDate<='2024-03-31'),0),2) AS PaidMarch,
       ROUND(COALESCE((SELECT SUM(p.Amount)
FROM DisbursementPayment p
WHERE p.PurchaseInvoiceID=i.PurchaseInvoiceID AND p.PaymentDate<='2024-06-30'),0),2) AS PaidJune
FROM PurchaseInvoiceLine l
JOIN PurchaseInvoice i ON i.PurchaseInvoiceID=l.PurchaseInvoiceID
WHERE l.GoodsReceiptLineID IN (SELECT gl.GoodsReceiptLineID
FROM GoodsReceiptLine gl
JOIN GoodsReceipt r ON r.GoodsReceiptID=gl.GoodsReceiptID
JOIN PurchaseOrder o ON o.PurchaseOrderID=r.PurchaseOrderID
WHERE o.SupplierID=27 AND r.ReceiptDate>='2024-01-01' AND r.ReceiptDate<'2024-04-01') AND i.InvoiceDate<='2024-06-30'
ORDER BY l.GoodsReceiptLineID,i.PurchaseInvoiceID;

Checkpoint. Seven invoice lines through June, versus five receipts. Receipt line 176 has two invoices by June and another later in recorded history. None of the selected goods invoices is paid by March 31.

A receipt can be invoiced in parts, and an invoice can remain unpaid. A one-result lookup would lose the later match. Keep a merge for this one-to-many relationship.

If your result differs

An eighth match dated July 14 lies beyond this follow-up window. Preserve it as later evidence without including it in June totals.

Keep for the next stage: Save GoodsInvoices and date-conditioned payment columns.

1.3. Reconstruct the selected liability accounts

Predict before you run: Should unbilled receipt cost and unpaid supplier bills share one label?

Excel

Named inputs: AccountData ← Account and GLEntryData ← GLEntry (import here), Payments (Session 1.2).

Produced outputs: GoodsLiability: selected 2010/2020 ledger detail, Obligations: four account/cutoff rows.

1. Import Account as AccountData and GLEntry as GLEntryData; retain the raw ledger in Power Query.

2. Create three filtered GLEntryData references, keeping SourceDocumentType with SourceDocumentID: GoodsReceipt IDs 174,175,253,396,340; PurchaseInvoice IDs 143,278,1499,179,422,614,947; and DisbursementPayment IDs obtained from Payments.

3. Append the references, merge AccountData on AccountID, then retain AccountNumber 2010 or 2020.

4. In Power Query choose Add Column → Custom Column, name it LiabilityChange, and paste the expression below without a leading equals sign:

   Power Query — GoodsLiability / LiabilityChange

   [Credit]-[Debit]

5. Load as GoodsLiability.

6. On Review create an Excel Table named Obligations with columns AccountNumber, AsOf, and LiabilityBalance.

7. Enter four rows pairing accounts 2010 and 2020 with March 31 and June 30, 2024; ensure AsOf contains real Excel dates.

8. In LiabilityBalance enter:

   Excel — Obligations[LiabilityBalance]

   =SUMIFS(GoodsLiability[LiabilityChange],GoodsLiability[AccountNumber],[@AccountNumber],GoodsLiability[PostingDate],"<="&[@AsOf])

SQL

events selects liability postings for the known receipt, invoice, and payment population. Credit minus debit gives a positive liability. Each cutoff sums only postings on or before its date. This measures posted GRNI rather than subtracting supplier price from standard receipt cost.

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

c03-goods-balances.sql

WITH cutoffs(AsOf) AS (VALUES('2024-03-31'),('2024-06-30')), events AS (SELECT g.PostingDate,
       a.AccountNumber,
       g.Credit-g.Debit AS LiabilityChange
FROM GLEntry g
JOIN Account a ON a.AccountID=g.AccountID
WHERE a.AccountNumber IN(2010,2020) AND ((g.SourceDocumentType='GoodsReceipt' AND g.SourceDocumentID IN(174,175,253,396,340)) OR (g.SourceDocumentType='PurchaseInvoice' AND g.SourceDocumentID IN(143,278,1499,179,422,614,947)) OR (g.SourceDocumentType='DisbursementPayment' AND g.SourceDocumentID IN(SELECT DisbursementID
FROM DisbursementPayment
WHERE PurchaseInvoiceID IN(143,278,1499,179,422,614,947))))) SELECT d.AsOf,
       e.AccountNumber,
       ROUND(SUM(e.LiabilityChange),2) AS SelectedLiability
FROM cutoffs d
JOIN events e ON e.PostingDate<=d.AsOf
GROUP BY d.AsOf,e.AccountNumber
ORDER BY d.AsOf,e.AccountNumber;

Checkpoint. March 31: selected AP 5,132.04 and GRNI 2,208.58. June 30: selected AP 2,096.16 and GRNI 142.79.

These balances describe the selected receipt population’s posted transitions. The GRNI balance falls when matched invoices clear receipt cost, while AP falls through payment. Neither schedule represents the company-wide accounts.

If your result differs

Retain document type with source ID. Account numbers are not AccountID values. Do not add all supplier 27 transactions to this selected-receipt schedule.

Keep for the next stage: Save the four-row liability schedule and stop after writing the meaning of each account.

Session stopping point

Save the receipt-to-invoice matches and two-date liability schedule. Explain the remaining June GRNI before moving to expense estimates.

Session 2 — Replace estimates with later evidence

The controller’s three repairs-and-maintenance estimates follow a different path. An invoice consumes the estimate, and the actual postings explain any difference.

How does the obligation change its accounting form?

Reading guide: Distinguish pre-invoice obligations, accounts payable, payment, and any independent residual-accrual adjustment.

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

Download editable Draw.io

Read the figure in words

• Receipt or expense estimate creates a liability before a supplier bill.
• The supplier invoice transfers the supported amount into accounts payable.
• Payment settles AP; an estimate difference may need a separate adjustment.

Conceptual teaching diagram. Distinguish pre-invoice obligations, accounts payable, payment, and any independent residual-accrual adjustment.

2.1. Read the linked estimates and invoices

Predict before you run: What would you expect if the invoice is below the estimate? What if it is above?

Excel

Named inputs: JournalEntryData ← JournalEntry (import here), PurchaseInvoiceLineData and PurchaseInvoiceData (raw queries from Session 1.2).

Produced outputs: Estimates: three journal headers, AccrualInvoices: linked invoice-line detail, EstimateReview: estimate/invoice comparison.

1. Import JournalEntry as JournalEntryData and preserve the raw query.

2. Reference JournalEntryData, filter IDs 9,26,43, and load as Estimates.

3. Reference the raw PurchaseInvoiceLineData from Session 1.2 as AccrualInvoices, filter AccrualJournalEntryID 9,26,43, and merge to the raw PurchaseInvoiceData on PurchaseInvoiceID.

4. Load as AccrualInvoices.

5. Reference Estimates as EstimateReview, merge to AccrualInvoices on JournalEntryID = AccrualJournalEntryID, and load as EstimateReview.

6. Add EstimateLessInvoice:

   Excel — EstimateReview[EstimateLessInvoice]

   =ROUND([@TotalAmount]-[@LineTotal],2)

7. Keep InvoiceDate visible; do not use the final invoice amount to erase the earlier estimate.

SQL

LEFT JOIN retains an estimate even if its invoice link is missing. AccrualJournalEntryID supplies the relationship. The later invoice amount is displayed beside the original estimate rather than replacing it.

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

c03-accruals.sql

SELECT j.JournalEntryID,
       j.PostingDate,
       j.TotalAmount AS Estimate,
       l.PILineID,
       l.LineTotal AS Consumed,
       i.PurchaseInvoiceID,
       i.InvoiceDate,
       i.GrandTotal,
       ROUND(COALESCE((SELECT SUM(p.Amount)
FROM DisbursementPayment p
WHERE p.PurchaseInvoiceID=i.PurchaseInvoiceID AND p.PaymentDate<='2024-06-30'),0),2) AS PaidJune
FROM JournalEntry j
LEFT JOIN PurchaseInvoiceLine l ON l.AccrualJournalEntryID=j.JournalEntryID
LEFT JOIN PurchaseInvoice i ON i.PurchaseInvoiceID=l.PurchaseInvoiceID
WHERE j.JournalEntryID IN (9,26,43)
ORDER BY j.JournalEntryID,l.PILineID;

Checkpoint. Journal 9 estimated 10,039.33; the March 9 invoice was 10,019.59, leaving 19.74. Journal 26 estimated 9,551.39 versus an April invoice of 9,889.91; journal 43 estimated 9,721.85 versus 9,914.02.

The first estimate exceeds its invoice. The other two fall short by 338.52 and 192.17. Describe the direction explicitly before deciding how the accounting resolves it.

If your result differs

Use AccrualJournalEntryID, not a description search, to identify the invoice link. A missing link is not evidence that no expense occurred.

Keep for the next stage: Save the three-row estimate comparison.

2.2. Inspect adjustment and recognition entries

Predict before you run: Will every difference require a separate adjustment journal?

Excel

Named inputs: JournalEntryData (raw query from Session 2.1), GLEntryData and AccountData (raw queries from Session 1.3).

Produced outputs: Adjustments: selected estimate/reversal headers, AccrualLedger: complete selected journal/invoice vouchers, VoucherChecks: debit/credit PivotTable; two accrued-liability totals.

1. Reference the raw JournalEntryData as Adjustments; filter JournalEntryID 9,26,43 or ReversesJournalEntryID 9,26,43.

2. Keep distinct IDs; load as Adjustments.

3. Create filtered references of the raw GLEntryData and Append as new using document pairs JournalEntry/9,26,43,698 and PurchaseInvoice/39851,39861,39872.

4. Merge AccountData, retain all account lines, and load as AccrualLedger.

5. Create a PivotTable by VoucherNumber with Debit and Credit totals.

6. On Review label B8 Accrued liability at March 31 and enter:

   Excel — Review!B8

   =SUMIFS(AccrualLedger[Credit],AccrualLedger[AccountNumber],2040,AccrualLedger[PostingDate],"<="&DATE(2024,3,31))-SUMIFS(AccrualLedger[Debit],AccrualLedger[AccountNumber],2040,AccrualLedger[PostingDate],"<="&DATE(2024,3,31))

7. Label B9 Accrued liability at June 30 and enter:

   Excel — Review!B9

   =SUMIFS(AccrualLedger[Credit],AccrualLedger[AccountNumber],2040,AccrualLedger[PostingDate],"<="&DATE(2024,6,30))-SUMIFS(AccrualLedger[Debit],AccrualLedger[AccountNumber],2040,AccrualLedger[PostingDate],"<="&DATE(2024,6,30))

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.

c03-journals.sql

SELECT JournalEntryID,
       PostingDate,
       EntryType,
       TotalAmount,
       ReversesJournalEntryID,
       Description
FROM JournalEntry
WHERE JournalEntryID IN (9,26,43) OR ReversesJournalEntryID IN (9,26,43)
ORDER BY PostingDate,JournalEntryID;

c03-accrual-postings.sql

SELECT g.PostingDate,
       g.SourceDocumentType,
       g.SourceDocumentID,
       g.VoucherNumber,
       a.AccountNumber,
       g.Debit,
       g.Credit
FROM GLEntry g
JOIN Account a ON a.AccountID=g.AccountID
WHERE ((g.SourceDocumentType='JournalEntry' AND g.SourceDocumentID IN(9,26,43,698)) OR (g.SourceDocumentType='PurchaseInvoice' AND g.SourceDocumentID IN(39851,39861,39872))) AND g.PostingDate<='2024-06-30'
ORDER BY g.PostingDate,g.VoucherNumber,g.GLEntryID;

Checkpoint. Journal 698 on March 11 clears the 19.74 overestimate by debiting accrued expenses and crediting expense. The two larger invoices consume their full existing estimates and recognize the additional expense in the invoice postings. Their excesses do not require invented residual journals. March 31 selected accrued expenses are 19,273.24; by June 30 they are zero.

The later evidence changes the liability’s form and, where necessary, the expense amount. Reading only journal totals would hide the underestimates recognized on the invoices.

If your result differs

Keep all voucher lines for a balance check. Use only account 2040 when calculating the selected accrued-expense liability.

Keep for the next stage: Save complete voucher checks and identify the expense effect for each estimate.

2.3. Follow payment and prepare the review

Predict before you run: Does a June payment prove that the obligation was absent in March?

Excel

Named inputs: DisbursementPaymentData (raw query from Session 1.2), Payments, AccrualInvoices, and Obligations (earlier stages).

Produced outputs: Payments: expanded goods/accrual selection through June 30, 2024, Review: separate goods and accrual settlement schedules.

1. Edit the Payments reference selection on the raw DisbursementPaymentData to include goods invoice IDs 143, 278, 1499, 179, 422, 614, 947 and accrual invoice IDs 39851, 39861, 39872, filter PaymentDate through June 30, and sort by PurchaseInvoiceID and PaymentDate.

2. Keep goods and accrual IDs in separate categories on Review.

3. On Review label B10 Accrual invoice payments through June 30 and enter the three-invoice sum:

   Excel — Review!B10

   =SUMIFS(Payments[Amount],Payments[PurchaseInvoiceID],39851)+SUMIFS(Payments[Amount],Payments[PurchaseInvoiceID],39861)+SUMIFS(Payments[Amount],Payments[PurchaseInvoiceID],39872)

4. Compare the result with the three AccrualInvoices GrandTotal values; Payments is already filtered through June 30.

5. For goods, reconcile the four paid invoices to the June AP schedule.

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.

c03-payments.sql

SELECT PurchaseInvoiceID,
       DisbursementID,
       PaymentDate,
       Amount
FROM DisbursementPayment
WHERE PurchaseInvoiceID IN(143,278,1499,179,422,614,947,39851,39861,39872) AND PaymentDate<='2024-06-30'
ORDER BY PurchaseInvoiceID,PaymentDate,DisbursementID;

Checkpoint. Seven payments appear. Goods payments total 5,132.04. The three accrual-related invoices are paid May 20, June 13, and June 22, totaling 29,823.52.

At March 31 the selected expenses include the two open estimates and the first invoice’s AP. Later settlement explains subsequent events without removing the obligation at March 31.

If your result differs

Keep the separate populations labeled. Do not offset a supplier receipt liability with an unrelated maintenance accrual.

Keep for the next stage: Write a 250–350 word review note: distinguish GRNI, accrued expenses, and AP; quantify the selected positions; name one June follow-up.

Session stopping point

Retain both schedules, their source links, and the review note. The note should explain recognition dates and settlement dates separately.

What does the later evidence change?

Reading guide: Read document dates and accounting effects separately. The timeline does not create additional database relationships.

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

Download editable Draw.io

Read the figure in words

• March 31 captures selected receipts, invoices, and remaining estimates.
• April–June adds invoices, expense differences, and payments.
• June 30 still has selected goods AP and GRNI, while the three expense estimates are settled.

Selected released evidence and teaching explanation. Read document dates and accounting effects separately. The timeline does not create additional database relationships.

Prepare the Decision

The worked schedules support a bounded quarter-end explanation. Some goods remain partly uninvoiced and some bills unpaid in June. The maintenance estimates are fully consumed and their invoices paid by June. A useful next action is to ask AP and receiving to resolve the residual goods matches, while retaining the estimate comparison for improving the next close.

Deliverable: Separate goods and expense-obligation schedules, selected posting checks, and a quarter-end review note. 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

Select supplier 27’s April–June receipts and the second-quarter repairs-and-maintenance accruals. Review June 30 and follow subsequent evidence through September 30. Use explicit accrual links and keep the two populations separate.

Planning hint

Find the second-quarter accrual journals by EntryType, PostingDate, and the repairs-and-maintenance description, then use their IDs. A new population may require several invoice lines per estimate.

Assessment criteria: Assess correct receipt and estimate populations, partial matches, date boundaries, liability signs, complete postings, and separation of recognition from payment. Your instructor evaluates the method, evidence, and reasoning. Model answers for this challenge remain in private course materials.

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. P2P Document Chain Completeness

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

2. AP Aging Open Invoices

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

3. Accrued Expense Rollforward

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

4. Accrued vs Invoiced vs Paid Timing

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

5. Accrued Service Settlement Exception Review

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

Next Steps

Continue to Choose a Product and Pricing Priority. Keep your completed evidence and use the Query Library for a separate extension.