The task and original case
Reconcile invoice value with cash received without treating billing as profit. Your artifact is a small invoice-to-cash ledger and an unmatched-receipt check. The invented inputs only establish a receivable balance under their stated scope.
| Case element | Value or requirement |
|---|---|
| Inputs | Invoices total 100; recorded receipts total 40; assume receipts apply to these invoices only. |
| Build | An invoice-to-cash reconciliation with an unmatched-receipt check. |
This is an original invented exercise. It is independent practice, with no employer assessment or open-vacancy claim. Download the inputs, attempt your own result, then open the separate answer key.
Data and worked inputs
Build a result another person can follow
Create invoice and receipt tables with stable IDs and an invoice reference on every receipt. Aggregate receipts by invoice before joining, so several payments do not duplicate billed amounts. Use invoices as the retained population and keep an exception register for receipts referencing unknown invoices.
For the base case, invoices total 100 and matched receipts total 40. Assume all supplied receipts apply to these invoices, with no credits, taxes or other adjustments in the model. pandas documents join types and validation; check invoice uniqueness and the summarized join's cardinality. Calculate outstanding balance as invoice value minus applied receipts, then reconcile the total to the source tables.
Sources[1]
Check the worked result
The matched outstanding balance is 100 −40 =60. Cash collected is 40. Billed value remains 100. Display these as three separately named measures: invoice value, matched cash received and outstanding invoice balance.
No expense inputs are supplied, so the exercise cannot establish profit. Likewise, a due date or overdue classification would require additional information. The strongest portfolio explanation says exactly which balances the provided ledger can support.
Make the failure visible
Add a receipt linked to an unknown invoice and verify that it appears in the exception register. It must not silently reduce a known invoice's balance. Test receipts exceeding one invoice's value too; flag the overpayment or model a declared credit policy. Avoid letting a duplicate receipt ID increase cash applied without a duplication check.
Revise it and ask someone to reproduce it
Have a peer add a second valid receipt of 20 against a known invoice. Matched receipts become 60 and outstanding becomes 40, while original invoice value stays 100. Preserve the first reconciliation and the new receipt's ID. Return to the exception register and explain what information is needed to apply any unresolved cash.
- Invoice value, cash received and outstanding balance reconcile separately.
- Unknown invoice references remain unresolved and visible.
- Duplicate receipts and overpayments have a declared review path.
Describe the work accurately
After completing your own version, you could adapt this project bullet: “Reconciled an invented invoice-to-cash ledger with aggregated receipts, retained invoice values and an unmatched-payment exception register.”
Use Projects or Independent practice as the heading. Keep the input files, result and revision together. Describe only your own completed work; the invented numbers are model outputs, not employer results or measured business impact.