1. Resolve a Customer Account Review

The sales coordinator is preparing a response to a design-trade customer. The warehouse has delivery information, treasury can see money received, and the controller wants the response to explain which invoices that money settles. You have been asked to assemble one account review that all three teams can use. Begin by predicting whether an undelivered order, an unpaid invoice, and unapplied cash would require the same follow-up.

Basic · 2 sessions of 60–90 minutes

Prepare

• 1. Meet the Records
• 2. Read an Order
• 3. Connect the Records
• 4. Follow a Goods Sale
• 5. Follow Customer Settlement
• 6. Find the Accounting Entries

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.
• Keep the order cohort fixed while changing event cutoffs. Distinguish receipt from application and balance each complete voucher.

Tools

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

Produce

A document timeline, two-date invoice settlement schedule, and a customer response with an action owner.

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

Worked scope. Customer 120, Hubbard Inc: the six orders placed from January 1 through March 31, 2024. Compare March 31 and April 30. This selection excludes other orders and is not the customer’s complete account history.

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, 4. Follow a Goods Sale, 5. Follow Customer Settlement, 6. Find the Accounting Entries. 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.
• Keep the order cohort fixed while changing event cutoffs. Distinguish receipt from application and balance each complete voucher.

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
Customer	T4_Customer	CustomerData
SalesOrder	T9_SalesOrder	SalesOrderData
SalesOrderLine	T10_SalesOrderLine	SalesOrderLineData
Shipment	T14_Shipment	ShipmentData
ShipmentLine	T15_ShipmentLine	ShipmentLineData
SalesInvoice	T16_SalesInvoice	SalesInvoiceData
SalesInvoiceLine	T17_SalesInvoiceLine	SalesInvoiceLineData
CashReceipt	T19_CashReceipt	CashReceiptData
CashReceiptApplication	T20_CashReceiptApplication	CashReceiptApplicationData
CreditMemo	T23_CreditMemo	CreditMemoData
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 — Establish what was delivered and billed

Save a separate Case01.xlsx or Case01.sql. Keep a Review sheet or text note for the scope, decisions, and evidence. The order date selects the cohort, while shipment and invoice dates determine what had happened by the review date.

Who must contribute to the customer response?

Reading guide: Rows show proposed teaching responsibilities. They do not establish employee access independence or control operation.

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

Download editable Draw.io

Read the figure in words

• Sales coordinator defines the six-order scope.
• Operations explains shipments and remaining quantities.
• Controller and treasury explain invoices, cash, and allocation.

Conceptual teaching diagram. Rows show proposed teaching responsibilities. They do not establish employee access independence or control operation.

1.1. Define the order population

Predict before you run: Predict the number of rows when six orders have several lines.

Excel

Named inputs: SalesOrderData ← SalesOrder (import here), SalesOrderLineData ← SalesOrderLine (import here).

Produced outputs: SelectedOrders: six order headers, OrderLines: 22 selected order lines, OrderSummary: six-row PivotTable.

1. Import T9_SalesOrder → SalesOrderData, T10_SalesOrderLine → SalesOrderLineData.

2. In Power Query keep both imported queries unchanged.

3. Reference SalesOrderData as SelectedOrders; filter SelectedOrders to CustomerID 120 and OrderDate on or after January 1 and before April 1, 2024.

4. Retain the six selected rows, including SalesOrderID and OrderDate.

5. Reference SalesOrderLineData and merge with SelectedOrders on SalesOrderID using an Inner join; expand OrderDate only.

6. Name the result OrderLines and load it.

7. Insert a PivotTable: Rows = SalesOrderID and OrderDate; Values = Count of SalesOrderLineID and Sum of LineTotal.

8. Set Count explicitly; the default sum of an identifier is meaningless.

SQL

GROUP BY produces one result per order and date. COUNT counts line identifiers; SUM totals their amounts. The inner SELECT defines the fixed order population. ORDER BY makes the result repeatable.

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

c01-orders.sql

SELECT o.SalesOrderID,
       o.OrderDate,
       COUNT(l.SalesOrderLineID) AS Lines,
       ROUND(SUM(l.LineTotal),2) AS OrderedLines
FROM SalesOrder o
JOIN SalesOrderLine l ON l.SalesOrderID=o.SalesOrderID
WHERE o.SalesOrderID IN (SELECT SalesOrderID
FROM SalesOrder
WHERE CustomerID=120 AND OrderDate >= '2024-01-01' AND OrderDate < '2024-04-01')
GROUP BY o.SalesOrderID,o.OrderDate
ORDER BY o.OrderDate,o.SalesOrderID;

Checkpoint. Six orders, 22 lines. Order 29 has five lines totaling 20,969.46. The other orders are 75, 236, 185, 434, and 388.

An order header identifies the customer and date, while its lines describe the separate goods. Keep the order total out of a line-level sum.

If your result differs

If the count is larger, check the customer and order-date filter before changing any join.

Keep for the next stage: Save OrderLines and the six-row order summary.

1.2. Compare ordered, shipped, and billed quantities

Predict before you run: Which difference would require operations to act, and which would require billing to act?

Excel

Named inputs: OrderLines (Session 1.1), ShipmentData ← Shipment and ShipmentLineData ← ShipmentLine (import here), SalesInvoiceData ← SalesInvoice and SalesInvoiceLineData ← SalesInvoiceLine (import here).

Produced outputs: ShippedByOrderLine and BilledByOrderLine: one row per order line with a dated event, Fulfillment: all 22 selected order lines.

1. Import Shipment as ShipmentData, ShipmentLine as ShipmentLineData, SalesInvoice as SalesInvoiceData, and SalesInvoiceLine as SalesInvoiceLineData before merging.

2. Keep these raw queries unchanged.

3. Reference ShipmentLineData as ShippedByOrderLine and merge to ShipmentData on ShipmentID; expand ShipmentDate.

4. Filter ShipmentDate through March 31, 2024.

5. Group by SalesOrderLineID, Sum QuantityShipped into a column named Shipped.

6. Separately reference SalesInvoiceLineData as BilledByOrderLine; merge to SalesInvoiceData on SalesInvoiceID, expand InvoiceDate, filter through March 31, 2024, then group by SalesOrderLineID, Sum Quantity into a column named Billed.

7. Reference OrderLines as Fulfillment and merge ShippedByOrderLine and BilledByOrderLine to it using Left outer joins on SalesOrderLineID.

8. Expand the two totals and replace unmatched numeric nulls with 0 after checking that they mean no event.

9. Load as Fulfillment.

10. Add OrderedLessShipped to Fulfillment and enter:

    Excel — Fulfillment[OrderedLessShipped]

    =ROUND([@Quantity]-[@Shipped],2)

11. Add ShippedLessBilled and enter:

    Excel — Fulfillment[ShippedLessBilled]

    =ROUND([@Shipped]-[@Billed],2)

SQL

WITH names two temporary results inside this statement: shipped and billed. Each is first summed by SalesOrderLineID. The final LEFT JOIN retains every selected order line, and COALESCE shows zero when no dated event exists. This avoids multiplying shipment and invoice detail against each other.

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

c01-fulfillment.sql

WITH shipped AS (SELECT sl.SalesOrderLineID,
       SUM(sl.QuantityShipped) AS Shipped
FROM ShipmentLine sl
JOIN Shipment s ON s.ShipmentID=sl.ShipmentID
WHERE s.ShipmentDate<='2024-03-31'
GROUP BY sl.SalesOrderLineID), billed AS (SELECT il.SalesOrderLineID,
       SUM(il.Quantity) AS Billed
FROM SalesInvoiceLine il
JOIN SalesInvoice i ON i.SalesInvoiceID=il.SalesInvoiceID
WHERE i.InvoiceDate<='2024-03-31'
GROUP BY il.SalesOrderLineID) SELECT l.SalesOrderID,
       l.SalesOrderLineID,
       l.Quantity AS Ordered,
       COALESCE(s.Shipped,0) AS Shipped,
       COALESCE(b.Billed,0) AS Billed
FROM SalesOrderLine l
LEFT JOIN shipped s ON s.SalesOrderLineID=l.SalesOrderLineID
LEFT JOIN billed b ON b.SalesOrderLineID=l.SalesOrderLineID
WHERE l.SalesOrderID IN (SELECT SalesOrderID
FROM SalesOrder
WHERE CustomerID=120 AND OrderDate >= '2024-01-01' AND OrderDate < '2024-04-01')
ORDER BY l.SalesOrderID,l.LineNumber;

Checkpoint. 22 rows remain. Lines 924, 1515, and 1694 have ordered quantities exceeding shipments by 0.63, 3.67, and 1.63 respectively. Every line’s shipped quantity equals its billed quantity at this cutoff.

The selected evidence points to incomplete fulfillment on three lines. It does not establish missing billing for goods already shipped. Those are different conclusions.

If your result differs

More than 22 rows means a detail table was joined before summarizing. A tiny floating-point difference is not an unbilled quantity.

Keep for the next stage: Save Fulfillment and identify the three lines for an operations follow-up.

1.3. Keep invoices at their own grain

Predict before you run: Will the number of invoices equal the number of orders?

Excel

Named inputs: SalesInvoiceData (raw query from Session 1.2), SelectedOrders (Session 1.1).

Produced outputs: Invoices: 20 selected invoice headers, including later dates.

1. Reference the raw SalesInvoiceData imported in Session 1.2 as Invoices and Inner merge to SelectedOrders on SalesOrderID.

2. Preserve SalesInvoiceData for other references.

3. Keep SalesInvoiceID, SalesOrderID, InvoiceDate, and GrandTotal.

4. Sort by InvoiceDate and SalesInvoiceID; load as Invoices.

5. Keep all related invoice dates for now so the second session can reconstruct both cutoffs.

6. On Review, label B4 Visible invoices and enter:

   Excel — Review!B4

   =SUBTOTAL(103,Invoices[SalesInvoiceID])

7. Label B5 Visible invoice amount and enter:

   Excel — Review!B5

   =SUBTOTAL(109,Invoices[GrandTotal])

SQL

This result has one invoice header per row. The order subquery selects the cohort, but invoice dates remain unrestricted so both reporting cutoffs can be reconstructed later.

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

c01-invoices.sql

SELECT SalesInvoiceID,
       SalesOrderID,
       InvoiceDate,
       GrandTotal
FROM SalesInvoice
WHERE SalesOrderID IN (SELECT SalesOrderID
FROM SalesOrder
WHERE CustomerID=120 AND OrderDate >= '2024-01-01' AND OrderDate < '2024-04-01')
ORDER BY InvoiceDate,SalesInvoiceID;

Checkpoint. 20 related invoices in recorded history; 17 are dated through March 31. Do not add freight and tax again: GrandTotal already includes them.

An order can be invoiced in parts. The April invoices belong in the later review, even though the orders were placed in the first quarter.

If your result differs

Ordinary SUM includes hidden rows. Use SUBTOTAL for filtered totals or a date-conditioned SUMIFS.

Keep for the next stage: Save Invoices and pause. Write one sentence distinguishing cohort selection from event cutoff.

Session stopping point

You should have six orders, 22 line comparisons, and 20 invoice headers. Keep the three incomplete-fulfillment lines separate from the receivables question.

Session 2 — Reconstruct settlement and prepare the response

Receiving money and applying it to an invoice are separate events. Start from the invoice population, then summarize applications and credits independently.

Why must settlement records be summarized separately?

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

• One order can have several shipments and invoices.
• One receipt can support several invoice applications.
• Aggregate applications and credits by invoice before comparing balances.

Conceptual teaching diagram. Summarize each many-side independently to the comparison grain before joining; keep other analytical grains separate.

2.1. Read cash and credit evidence

Predict before you run: Predict whether adding receipt amounts down the application table would overstate this population’s settlement.

Excel

Named inputs: Invoices and SelectedOrders (Session 1), CashReceiptApplicationData ← CashReceiptApplication and CashReceiptData ← CashReceipt (import here), CreditMemoData ← CreditMemo (import here).

Produced outputs: Applications: selected application detail, Receipts: unique receipt headers, CreditsByOrder and CreditsByInvoice: separate selections, Credits: deduplicated union by CreditMemoID.

1. Import CashReceiptApplication as CashReceiptApplicationData and CashReceipt as CashReceiptData.

2. Reference CashReceiptApplicationData and Inner merge with Invoices on SalesInvoiceID; load as Applications.

3. Reference CashReceiptData and load as Receipts.

4. In Applications add ReceiptDate:

   Excel — Applications[ReceiptDate]

   =XLOOKUP([@CashReceiptID],Receipts[CashReceiptID],Receipts[ReceiptDate],"No match",0)

5. Format ReceiptDate as a date and investigate any No match result.

6. Add ReceiptAmount:

   Excel — Applications[ReceiptAmount]

   =XLOOKUP([@CashReceiptID],Receipts[CashReceiptID],Receipts[Amount],"No match",0)

7. Do not total that repeated receipt amount.

8. Import CreditMemo as CreditMemoData.

9. Create two references, CreditsByOrder filtered to the six SalesOrderIDs and CreditsByInvoice filtered to the 20 OriginalSalesInvoiceIDs; Append as new, then remove duplicates by CreditMemoID in Power Query.

10. Load as Credits.

11. Inspect OriginalSalesInvoiceID before allocating a credit.

SQL

Each result is one application. ReceiptAmount repeats when one receipt funds several applications. Sum AppliedAmount for invoice settlement and retain distinct receipt IDs when describing received cash.

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

c01-applications.sql

SELECT a.SalesInvoiceID,
       a.CashReceiptApplicationID,
       a.ApplicationDate,
       a.AppliedAmount,
       r.CashReceiptID,
       r.ReceiptDate,
       r.Amount AS ReceiptAmount
FROM CashReceiptApplication a
JOIN CashReceipt r ON r.CashReceiptID=a.CashReceiptID
WHERE a.SalesInvoiceID IN (SELECT SalesInvoiceID
FROM SalesInvoice
WHERE SalesOrderID IN (SELECT SalesOrderID
FROM SalesOrder
WHERE CustomerID=120 AND OrderDate >= '2024-01-01' AND OrderDate < '2024-04-01'))
ORDER BY a.ApplicationDate,a.CashReceiptApplicationID;

c01-credits.sql

SELECT CreditMemoID,
       CreditMemoDate,
       SalesOrderID,
       OriginalSalesInvoiceID,
       GrandTotal
FROM CreditMemo
WHERE SalesOrderID IN (SELECT SalesOrderID
FROM SalesOrder
WHERE CustomerID=120 AND OrderDate >= '2024-01-01' AND OrderDate < '2024-04-01') OR OriginalSalesInvoiceID IN (SELECT SalesInvoiceID
FROM SalesInvoice
WHERE SalesOrderID IN (SELECT SalesOrderID
FROM SalesOrder
WHERE CustomerID=120 AND OrderDate >= '2024-01-01' AND OrderDate < '2024-04-01'))
ORDER BY CreditMemoDate,CreditMemoID;

Checkpoint. 24 applications across recorded history. Credit memo 30 is dated April 26 and credits invoice 1086 by 612.38. Receipt 165 appears on several application rows; its 18,938.14 must be counted only once when describing the receipt.

This credit changes the April explanation. It is subsequent evidence, not a reduction that was already recorded on March 31.

If your result differs

A credit found by order only needs further allocation evidence. Do not force it onto the first invoice. This worked credit has an explicit invoice link.

Keep for the next stage: Save Applications, Credits, and the dates of the receipt/application handoffs.

2.2. Build the two-date settlement schedule

Predict before you run: Will the later balance fall only because of cash, or can new invoices and credits change it too?

Excel

Named inputs: Invoices, Applications, and Credits (earlier stages), Review!B2: reporting date.

Produced outputs: Invoices: cutoff-dependent Applied, Credited, and Remaining columns, Two dated values-only settlement snapshots.

1. Set the table names to Invoices, Applications, and Credits exactly.

2. On Review label B2 Reporting date and enter:

   Excel — Review!B2 — first cutoff

   =DATE(2024,3,31)

3. In Invoices add Applied:

   Excel — Invoices[Applied]

   =SUMIFS(Applications[AppliedAmount],Applications[SalesInvoiceID],[@SalesInvoiceID],Applications[ApplicationDate],"<="&Review!$B$2)

4. Add Credited:

   Excel — Invoices[Credited]

   =SUMIFS(Credits[GrandTotal],Credits[OriginalSalesInvoiceID],[@SalesInvoiceID],Credits[CreditMemoDate],"<="&Review!$B$2)

5. Add Remaining:

   Excel — Invoices[Remaining]

   =ROUND([@GrandTotal]-[@Applied]-[@Credited],2)

6. Filter InvoiceDate through the date in Review!B2.

7. Label Review!B6 Visible remaining balance and enter:

   Excel — Review!B6

   =SUBTOTAL(109,Invoices[Remaining])

8. Save a values-only snapshot of the visible rows with its cutoff label.

9. Replace Review!B2 with the second date, update the InvoiceDate filter, and repeat:

   Excel — Review!B2 — second cutoff

   =DATE(2024,4,30)

10. Keep the formulas in the working table for refresh.

11. Copy only visible cells when retaining a filtered snapshot; alternatively filter the query before loading each dated view.

SQL

The cutoffs result contains the two review dates. CROSS JOIN pairs each date with each selected invoice, and the invoice-date condition excludes bills not yet issued. Each correlated SUM reads only applications or credits for that invoice through that date. Summarize the resulting rows separately by AsOf.

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

c01-balances.sql

WITH cutoffs(AsOf) AS (VALUES('2024-03-31'),('2024-04-30')) SELECT d.AsOf,
       i.SalesInvoiceID,
       i.GrandTotal,
       ROUND(COALESCE((SELECT SUM(a.AppliedAmount)
FROM CashReceiptApplication a
WHERE a.SalesInvoiceID=i.SalesInvoiceID AND a.ApplicationDate<=d.AsOf),0),2) AS Applied,
       ROUND(COALESCE((SELECT SUM(c.GrandTotal)
FROM CreditMemo c
WHERE c.OriginalSalesInvoiceID=i.SalesInvoiceID AND c.CreditMemoDate<=d.AsOf),0),2) AS Credited,
       ROUND(i.GrandTotal-COALESCE((SELECT SUM(a.AppliedAmount)
FROM CashReceiptApplication a
WHERE a.SalesInvoiceID=i.SalesInvoiceID AND a.ApplicationDate<=d.AsOf),0)-COALESCE((SELECT SUM(c.GrandTotal)
FROM CreditMemo c
WHERE c.OriginalSalesInvoiceID=i.SalesInvoiceID AND c.CreditMemoDate<=d.AsOf),0),2) AS Remaining
FROM cutoffs d CROSS
JOIN SalesInvoice i
WHERE i.SalesOrderID IN (SELECT SalesOrderID
FROM SalesOrder
WHERE CustomerID=120 AND OrderDate >= '2024-01-01' AND OrderDate < '2024-04-01') AND i.InvoiceDate<=d.AsOf
ORDER BY d.AsOf,i.SalesInvoiceID;

Checkpoint. March 31: 17 invoices, 72,676.21 billed, 44,067.35 applied, zero credits, and 28,608.86 remaining. April 30: 20 invoices, 75,742.57 billed, 67,836.87 applied, 612.38 credited, and 7,293.32 remaining.

The balance fell by 21,315.54 while new invoices added 3,066.36. A statement that “cash collected equals the fall in receivables” would miss both new billing and the credit.

If your result differs

Check application dates, not just receipt dates. A final Paid status cannot reconstruct an earlier balance.

Keep for the next stage: Save the two schedules and a change bridge: new invoices less additional applications less new credits.

2.3. Check accounting and assign the response

Predict before you run: Which event reduces receivables: cash receipt or cash application?

Excel

Named inputs: GLEntryData ← GLEntry and AccountData ← Account (import here), Earlier settlement and fulfillment checks.

Produced outputs: SelectedLedger: 10 rows, four complete vouchers, VoucherChecks: debit/credit PivotTable, Review: customer response.

1. Import GLEntry as GLEntryData and Account as AccountData.

2. Keep GLEntryData in Power Query and filter references before loading: SourceDocumentType/SourceDocumentID pairs SalesInvoice/68, CashReceipt/43, CashReceiptApplication/47, CreditMemo/30.

3. Use four filtered references and Append as new; do not filter all four IDs without their document types.

4. Merge AccountData on AccountID.

5. Load the 10 rows as SelectedLedger.

6. Insert a PivotTable with VoucherNumber in Rows and sums of Debit and Credit in Values.

7. Check each complete voucher balances.

SQL

Keep each SourceDocumentType/SourceDocumentID pair together. The same numeric ID can occur in unrelated source tables. Joining Account adds names without changing the posting grain.

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

c01-ledger.sql

SELECT g.PostingDate,
       g.SourceDocumentType,
       g.SourceDocumentID,
       g.VoucherNumber,
       a.AccountNumber,
       a.AccountName,
       g.Debit,
       g.Credit
FROM GLEntry g
JOIN Account a ON a.AccountID=g.AccountID
WHERE (g.SourceDocumentType='SalesInvoice' AND g.SourceDocumentID=68) OR (g.SourceDocumentType='CashReceipt' AND g.SourceDocumentID=43) OR (g.SourceDocumentType='CashReceiptApplication' AND g.SourceDocumentID=47) OR (g.SourceDocumentType='CreditMemo' AND g.SourceDocumentID=30)
ORDER BY g.PostingDate,g.VoucherNumber,g.GLEntryID;

Checkpoint. 10 posting rows in four balanced vouchers. Receipt 43 debits cash and credits customer deposits by 1,413.08. Application 47 moves that amount from deposits to receivables two days later.

A supported response distinguishes operations follow-up from accounting follow-up. Explain the 7,293.32 selected balance at April 30, acknowledge credit 30, and ask operations to confirm the three earlier fulfillment gaps against later shipments before promising delivery.

If your result differs

Filtering to the AR account alone makes a voucher appear unbalanced. Restore all lines of each selected event.

Keep for the next stage: Retain the voucher check and write a 200–300 word customer response with one named role responsible for each next action.

Session stopping point

Save the two cutoff schedules, the event-to-accounting check, and your response. Another reader should be able to reproduce every amount from the six-order scope.

What changed between the two reviews?

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: selected invoices less applications leave 28,608.86.
• April adds invoices, applications, and credit memo 30.
• April 30: the same order cohort leaves 7,293.32.

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 result supports a focused customer-account response, not an assertion about every balance Hubbard Inc has ever held. Operations addresses quantities, customer accounting confirms allocations and credits, and treasury supplies receipt evidence. A plausible next action is to confirm the later fulfillment position and contact the customer about the remaining selected invoices with the two-date schedule attached.

Deliverable: A document timeline, two-date invoice settlement schedule, and a customer response with an action owner. 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 customer 120’s orders dated April 1–June 30, 2024. Reconstruct June 30 and July 31. Retain the cohort, invoice schedule, applications, credits, and one complete voucher. Explain whether the same action priorities still make sense.

Planning hint

Change the order-cohort dates separately from the event cutoffs. Rebuild the invoice list rather than reusing the worked case’s 20 IDs. A valid result can have no credits or no remaining balance.

Assessment criteria: Assess correct population, separate event dates, no repeated receipt totals, complete voucher checks, and an action justified by the observed evidence. 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. O2C Document Chain Completeness

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

2. Monthly Revenue and Gross Margin

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

3. AR Aging Open Invoices

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

4. Sales Commission Payable Rollforward

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

5. Backorder and Return Review

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

Next Steps

Continue to Review Design Work and Billing. Keep your completed evidence and use the Query Library for a separate extension.