The task and original case
Reconcile refunds while preserving an unknown payment reference as an exception. The finished report should show what can be matched and what remains unresolved. Forcing every refund onto a known payment creates a tidy-looking but unsupported balance.
| Case element | Value or requirement |
|---|---|
| Inputs | Payments P 1=80,P 2=50; refunds R 1 againstP 1=20 and R 2 against an unknown payment=10. |
| Build | A refund exception register and a reconciled matched balance. |
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
The payment table contains P1 for 80 and P2 for 50. Refund R1 references P1 for 20; R2 references an unknown payment for 10. Check payment and refund identities, summarize known refunds by payment reference, and retain a separate unmatched-refund register.
Use a left join to keep both known payments, and a match indicator or equivalent audit to identify refund references missing from the payment table. pandas documents join and validation behavior. Do not choose the nearest amount or first payment as a substitute for an actual reference. The exception needs missing source evidence, not a more convenient join.
Sources[1]
Check the worked result
Known payment total is 130. The matched refund total is 20, so matched net payments are 110: P1 contributes 60 and P2 contributes 50. The unmatched refund amount 10 is reported separately and remains unapplied to the known-payment calculation.
A global arithmetic subtraction of all supplied refund amounts yields 100, but does not establish the net of the two known payment records. The report should name its scope instead of presenting both totals as if they describe the same reconciled population.
Make the failure visible
Duplicate R1 and verify that identity validation prevents counting its 20 twice. Add a refund exceeding P1's original amount and flag it for review under your declared policy. If a later source file reveals the unknown payment, match by identity and show the source revision that resolved the exception; do not rewrite the original register as if it had always matched.
Revise it and ask someone to reproduce it
Ask a peer to supply a new known payment P3 for 30 and repair R2's reference to P3. The matched ledger now has payment total 160, refunds 30 and net 130. Preserve the original 110 matched net and unresolved 10 beside the new result so the change is explained by added evidence.
- Matched net 110 is traceable to the two known payments.
- The unknown refund 10 remains a visible exception.
- Duplicate and excessive refunds are reviewed before balance calculation.
Describe the work accurately
After completing your own version, you could adapt this project bullet: “Reconciled an invented payment-and-refund ledger with identity checks, matched net balances and a separately retained unmatched-refund 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.