Finance expense cleanup
Halden & Pike Consulting (fictional)
Turn a messy UK expense export with resubmitted claims and three currencies into one clean claim per row in pounds, then report approved spend by department.
- Level
- intermediate
- Time
- ~60 min
- Deliverables
- 2
- Rubric
- v1
Access
Pro. This brief is public. The data pack, workspace, hints, grading and solution explanations need Pro, Lifetime Learning or a seat in an organization. See plans.
Each deliverable's solution explanation unlocks when it passes. You can also unlock it early once you have used every hint for that deliverable (this needs an account so the help is recorded, and the deliverable then counts as completed with help).
Business goal
Give the finance controller a refreshable list of current claims converted to GBP and a monthly view of approved spend by department and category.
Halden & Pike's expense tool exports every submission, including corrections. When a consultant fixes a claim, the tool writes a new row with the same ClaimID and a later SubmittedAt; only the latest submission counts. Claims can be in GBP, EUR or USD; finance keeps a monthly table of conversion rates to GBP. The controller asked for current claims in pounds and approved spend by department.
Graded deliverables
1. Current Claims
60 pointsOne row per claim (its latest submission), rejected claims removed, amounts converted to pounds.
- Columns: ClaimID, EmployeeID, ExpenseDate, Category, Currency, Amount, Status, AmountGBP
- Exactly one row per ClaimID, taken from its latest submission
- No Rejected claims; Pending claims stay
- AmountGBP rounded to 2 decimals; GBP claims keep their amount
Expected output schema for Current Claims Column Type ClaimID text EmployeeID text ExpenseDate date Category text Currency text Amount decimal Status text (Approved or Pending) AmountGBP decimal (2 dp) - Grain:
- One row per ClaimID that is not currently rejected.
- Order:
- Not graded.
- Nulls:
- No nulls: every non-GBP claim has a rate for its month.
2. Approved Spend
40 pointsApproved claims only, totalled in GBP by department and category.
Builds on: Current Claims
- Columns: Department, Category, Claims, TotalGBP
- Approved claims only (Pending excluded)
- Unknown employees grouped as Unlisted
- Sorted by Department, then Category
Expected output schema for Approved Spend Column Type Department text Category text Claims whole number TotalGBP decimal - Grain:
- One row per department and category with at least one approved claim.
- Order:
- Graded: Department ascending, then Category ascending.
- Nulls:
- No nulls. Unknown employees are reported as Department "Unlisted".