# Excelquro synthetic analytics project kit

All records are fictional, educational and denominated in INR. No customer identity information is included. Version 1, 18 October 2026. Dataset source: Excelquro-authored synthetic examples.

Use this kit for Excel MIS Automation, SQL Business Performance and the Power BI Collections Dashboard. The three projects deliberately share the same controls so results can be reconciled across tools.

## Files and table grain

| File/table | Grain/key | Important fields |
|---|---|---|
| payments.csv / Payments | One event per payment_id (9) | loan_id FK, payment_date ISO date, amount numeric INR |
| loans.csv / Loans | One loan at 31 Jan 2026 per loan_id (4) | branch_id, agent_id, POS INR, start_dpd and end dpd, monthly_target |
| branches.csv / Branches | One branch per branch_id (2) | Fictional branch labels |
| agents.csv / Agents | One assigned analyst per agent_id (2) | Fictional labels; assignment on Loans |
| targets.csv / Targets | One branch-month per branch_id+month_end (4) | Monthly amount; not a per-payment target |
| calendar.csv / Calendar | One date in 2026 (365) | date, year, month, month_name |

Loans start_dpd is the beginning-of-January state and dpd is the end-of-January state. This single snapshot cannot provide a February POS or DPD trend. Agents represent assignment, not independent evidence of who personally collected each payment. Monthly targets on Loans are descriptive January controls; use the separate Targets table for multi-month reporting.

## Import and reproduce

Excel: use Data → From Text/CSV; type identifiers as text, date fields as Date and amount/POS/target fields as numeric. The practice workbook contains real source tables, a derived branch lookup and live January summary formulas. Yellow cells accept learner commentary. Build your own PivotTable; no native PivotTable has been fabricated by the workbook generator. Use compatible desktop Excel for native acceptance and recalculate.

SQLite: open collections.sqlite in a compatible SQLite tool, or execute setup.sql in a **new empty** SQLite database. Run answers.sql statement by statement. These are read-only learning queries; do not connect them to an employer database. SQLite LIMIT, substr and date syntax may need adapting for SQL Server/T-SQL. Window examples need SQLite 3.25+.

Power Query: use payments-january.csv and payments-february.csv as same-schema inputs in a dedicated folder. Do not mix combined payments.csv, output files or the other CSV tables into that folder. Import separately and append if your edition lacks a folder connector. Correct result: 9 events, not 18.

Power BI: read POWER_BI_GUIDE.md. A native PBIX is not supplied or verified. Windows Power BI Desktop is needed to author one; organizational sharing can require licensing. A model specification is a useful alternative exercise, but label it as a specification rather than completed native work.

## Control ledger

- All events: 9; January: 5; February: 4. Unique payment and loan keys; no orphan payments.
- All-period payment amount: 98,000. January: 48,000. February: 50,000.
- January Pune: 27,000 / 30,000 = 90%; Nashik: 21,000 / 30,000 = 70%; combined 80%.
- February Pune: 30,000 / 30,000 = 100%; Nashik: 20,000 / 30,000 = 66.6667%; combined 83.3333%.
- Monthly total target: 60,000 each month. Two-month target: 120,000.
- January total shortfall: 12,000; Nashik gap: 9,000.
- Snapshot POS: 1,000,000; 90+ DPD POS: 500,000 (50%). Nashik snapshot POS: 500,000.
- Educational cure rate: start_dpd>0 and end dpd=0 divided by start_dpd>0: 1/4 =25%. This is not a regulatory or credit-policy definition.
- February collection change: +2,000, +4.1667% relative. Achievement change: +3.3333 percentage points.
- L004 has two January events totaling 14,000. A raw January payment-to-loan join wrongly sums POS to 1,400,000. Aggregate events before adding balances; SUM(DISTINCT pos) is not a general solution.

## Portfolio checkpoints and self-review rubric

1. Preserve raw source and document types/grain.
2. Build transformations/model with deliberate cardinality.
3. Reproduce the controls and test a missing ID, duplicate and zero target in disposable copies.
4. Deliver an accessible summary, a factual management note, reproduction steps and limitations.

Each criterion is 25 points for **self-review**, with reasons and evidence. In-site numeric checks and MCQs do not certify an entire workbook, native report, identity or job readiness. Keep your own exported queries and native work files. Never publish real borrower data.

## Recovery and refresh

Preserve the last accepted output. Validate a new refresh in a candidate copy; stop if keys, counts or totals differ unexpectedly. Record files, as-of date, controls and exceptions. Restore the last accepted output if the refresh cannot be reconciled. Do not change expected totals simply to pass a check.