6. Find the Accounting Entries
The controller asks you to explain the accounting behind the sale. You already know what happened in the documents. Now identify the ledger entries for four events and check each complete voucher before interpreting individual accounts.
Your task. Connect selected business events to complete ledger vouchers and explain their debit and credit effects.
How do separate events change separate accounts?
Reading guide: Read document dates and accounting effects separately. The timeline does not create additional database relationships.
Read the figure in words
- Invoice 202: 31 January 2024; Receivable increases; Billing is recognized
- Receipt 173: 22 February 2024; Debit cash 952.07; Credit deposits 952.07
- Application 225: 23 February 2024; Debit deposits 952.07; Credit receivables 952.07
Read document dates and accounting effects separately. The timeline does not create additional database relationships.
Before You Start
Finish Follow Customer Settlement. Use shipment 87, sales invoice 202, cash receipt 173, and cash application 225. Review basic debits and credits.
Before running a query, use the four posting routes to classify order 11 and the four events. Predict the direction of cash, receivables, inventory, revenue, expense, and liabilities on your evidence template. Then retrieve the complete vouchers and compare your prediction with the accounts actually used.
- Excel
- SQL
Start Lesson06.xlsx in Excel and import T1_Account as AccountData. The ledger import is large, so apply the filters below in Power Query before loading.
Open the SQLite database in your SQL tool. Save this lesson's statements as Lesson06.sql. Run each supplied query separately, using its filters to identify the records and dates for that step.
1. Locate the Complete Event Vouchers
A source identifier is meaningful together with its source type. Shipment 87 and invoice 87 would be different documents.
- Excel
- SQL
Import T3_GLEntry four times, keeping all its columns but applying these pairs of filters before loading:
| Working table | SourceDocumentType | SourceDocumentID |
|---|---|---|
| ShipmentGL | Shipment | 87 |
| InvoiceGL | SalesInvoice | 202 |
| ReceiptGL | CashReceipt | 173 |
| ApplicationGL | CashReceiptApplication | 225 |
In each table, add an AccountName column:
=XLOOKUP([@AccountID],AccountData[AccountID],AccountData[AccountName],"No match",0)
Keep GLEntryID, VoucherNumber, PostingDate, Debit, and Credit visible. Do not filter the voucher down to one account before checking its balance.
The parentheses keep each type paired with its identifier. The account join adds names without changing the ledger-row grain.
SELECT g.GLEntryID, g.PostingDate, g.SourceDocumentType,
g.SourceDocumentID, g.VoucherNumber, a.AccountNumber,
a.AccountName, g.Debit, g.Credit
FROM GLEntry AS g
JOIN Account AS a ON a.AccountID = g.AccountID
WHERE (g.SourceDocumentType = 'Shipment' AND g.SourceDocumentID = 87)
OR (g.SourceDocumentType = 'SalesInvoice' AND g.SourceDocumentID = 202)
OR (g.SourceDocumentType = 'CashReceipt' AND g.SourceDocumentID = 173)
OR (g.SourceDocumentType = 'CashReceiptApplication' AND g.SourceDocumentID = 225)
ORDER BY g.PostingDate, g.GLEntryID;
Expect 15 ledger rows. Shipment records inventory, COGS, and freight cost, while the invoice records the customer receivable, sales, freight revenue, and tax. Receipt 173 increases cash and customer deposits. Application 225 reduces deposits and the receivable.
2. Check and Explain Each Voucher
Balance is a necessary accounting check. It does not establish that the event was authorized or that every amount is economically correct.
- Excel
- SQL
On Notes, keep four rows for Shipment, Invoice, Receipt, and Application. Label columns B Debits, C Credits, and D Difference. Before entering each formula in the named cell, confirm that its source table contains only the selected event.
=SUM(ShipmentGL[Debit])
=SUM(ShipmentGL[Credit])
=ROUND(B2-C2,2)
=SUM(InvoiceGL[Debit])
=SUM(InvoiceGL[Credit])
=ROUND(B3-C3,2)
=SUM(ReceiptGL[Debit])
=SUM(ReceiptGL[Credit])
=ROUND(B4-C4,2)
=SUM(ApplicationGL[Debit])
=SUM(ApplicationGL[Credit])
=ROUND(B5-C5,2)
Inspect VoucherNumber in each source table and confirm each complete voucher balances.
Group the complete selected postings by event and voucher. GROUP BY puts rows with the same listed values into a group, so SUM and COUNT produce one check per voucher. Read the numbers together with the accounts in the first result.
SELECT SourceDocumentType, SourceDocumentID, VoucherNumber,
COUNT(*) AS LedgerRows,
ROUND(SUM(Debit), 2) AS Debits,
ROUND(SUM(Credit), 2) AS Credits,
ROUND(SUM(Debit - Credit), 2) AS Difference
FROM GLEntry
WHERE (SourceDocumentType = 'Shipment' AND SourceDocumentID = 87)
OR (SourceDocumentType = 'SalesInvoice' AND SourceDocumentID = 202)
OR (SourceDocumentType = 'CashReceipt' AND SourceDocumentID = 173)
OR (SourceDocumentType = 'CashReceiptApplication' AND SourceDocumentID = 225)
GROUP BY SourceDocumentType, SourceDocumentID, VoucherNumber
ORDER BY SourceDocumentType, SourceDocumentID, VoucherNumber;
| Event | Ledger rows | Total debit = total credit |
|---|---|---|
| Shipment 87 | 6 | 6,089.34 |
| Invoice 202 | 5 | 12,248.33 |
| Receipt 173 | 2 | 952.07 |
| Application 225 | 2 | 952.07 |
Each difference is 0.00. The two 952.07 vouchers describe different events, so adding them does not mean the customer sent 1,904.14.
3. Tell the Event-to-Entry Story
Predict first: Why can January 31 goods revenue and February 22 cash both be correct? Would recording application 225 on February 23 mean receiving more cash?
Reveal the complete-voucher bridge
| Event and posting date | Complete debit side | Complete credit side | Evidence scope |
|---|---|---|---|
| Shipment 87, January 31, 2024 | 5010 COGS 1,715.19 + 4,270.14; 5050 freight-out expense 104.01 | 1040 finished goods 1,715.19 + 4,270.14; 2040 accrued expenses 104.01 | All 6 ledger rows; each side 6,089.34 |
| Invoice 202, January 31, 2024 | 1020 AR 12,248.33 | 4010 goods revenue 3,552.21 + 7,845.57; 4050 freight revenue 103.00; 2050 sales tax payable 747.55 | All 5 ledger rows; each side 12,248.33 |
| Receipt 173, February 22, 2024 | 1010 cash 952.07 | 2060 customer deposits and unapplied cash 952.07 | All 2 ledger rows; each side 952.07 |
| Application 225, February 23, 2024 | 2060 customer deposits and unapplied cash 952.07 | 1020 AR 952.07 | All 2 ledger rows; each side 952.07 |
These are all 15 rows returned in Step 1, grouped for explanation rather than reduced to a selected sales line. Lesson 4 followed order line 43, while its practice variation followed line 42. The complete shipment and invoice vouchers here include both goods lines. The shipment’s total goods cost is 5,985.33, while the invoice’s total goods revenue is 11,397.78. Freight-out expense 104.01 and freight revenue 103.00 are different accounts and amounts. Tax payable is a liability, not revenue.
The order established demand without a posting. Shipment records cost and inventory movement, while the invoice records goods revenue and the receivable. Receipt records cash arrival, and application allocates it to AR without another inflow. The January events do not wait for February’s cash.
At January 31, exclude both later cash events. At February 22, exclude application 225 because receipt 173 itself has not reduced invoice 202’s receivable. These observations describe the selected events, not the invoice’s entire open balance. One 952.07 application cannot establish full settlement, so retrieve all dated applications and relevant credits before calculating that balance.
Readiness Check: After Lesson 6
Before widening the investigation, show that you can:
- Pair a source type with its identifier, retrieve all 15 rows, and balance each complete voucher before selecting accounts.
- Explain why a selected order-line amount differs from a complete shipment or invoice voucher.
- Separate receipt from application and state which events fall inside a specified cutoff.
- Write a conclusion limited to the selected evidence, naming the other settlement records needed for an invoice balance.
If a total differs, check the paired filters, retained source lines, date types, and join multiplicity before adjusting your interpretation. If the number matches but the grain or cutoff explanation fails, revisit Lesson 3, Lesson 4, or Lesson 5. A matching answer alone does not pass this checkpoint.
Keep Your Evidence
Save the four voucher checks and an event-to-accounting explanation. Identify which entries recognize inventory movement, billing, cash receipt, and invoice settlement. Record the source file, selected keys, filters, and what one result row represents. Keep your workbook or saved SQL with your explanation.
Complete the evidence template for January 31, February 22, and February 23, 2024. Explain why the order creates no posting, why cash arrival is not new revenue, and what each selected application establishes. Before claiming full settlement, retrieve the invoice's remaining dated applications and credits. Retain the result grid using the SQL export procedure, or save the filtered Excel tables with their checks.
A source-to-ledger match supports this selected trace. A balanced voucher does not prove authorization or population completeness. Keep the Posting Reference available for fuller definitions.
Try One Change
Find the ledger rows for cash receipt 528, then for application 1194. Predict whether their voucher totals should match.
Compare your reasoning
The totals differ because receipt 528 records 7,092.79, while application 1194 assigns 3,130.89 to invoice 202. Change both the source type and its paired identifier when adapting a filter.
Next Steps
You can now follow goods from demand to accounting. The next investigation applies the same method to staff time. Continue to Follow Design Time into Billing.