Overview
Executive summary
EC's three service-stream Google Sheets are the de-facto production database. The current structure (rows = staff, columns = months, cells = colour-coded job status) works for manual scanning but breaks under filter, pivot, export, and audit operations. This revamp normalises the schema into a relational structure while maintaining all existing data fidelity and workflow.
Key outcomes
- Colour coding becomes explicit status columns (job status and payment status separated)
- Three monolithic sheets become four domain-specific tabs + one dashboard
- Manual reconciliation gets a structured ledger with unique transaction IDs
- Grant-unit visibility through a linked performance dashboard with monthly KPIs
At a glance
Complexity: Medium (schema design + Google Sheets formulas + team retraining)
Duration: 2–3 weeks (design, build, UAT, cutover)
Value: Approximately $15–$20K/year in reconciliation admin savings; an auditable record for DEX compliance.
Part 1
Current state analysis
Existing structure
Home Maintenance sheet
- Rows: grouped by contractor name (gardening firms, gutter contractors, handyman), sub-grouped by worker/team
- Columns: month, sub-columns for each week or job date
- Cell content: client name, job type, hours worked, job status (colour: green = complete, yellow = incomplete, red = issue), payment status (colour: green = paid, yellow = pending, grey = unpaid)
- Evidence tracking: hours often reported on the contractor's own sheet; before/after photos stored separately in email/Drive
DA & Transport sheets
- Similar structure but simpler (fewer contractors)
- Same colour coding (job status + payment method indicator)
- DA: primary cleaning contractor; 2-hour jobs are atomic units
- Transport: trip-level granularity; often 7–10% free trips (no charge)
Data quality issues observed
- 82% of gutter jobs missing a client charge
- Before/after photos often delayed (days or weeks after the job)
- Manual QC happens after the fact; no invoice reference linked to the payment record
- Colour-only status encoding breaks in exports, filters, and charts
- Manual transcription from evidence to sheet (photos, hours, receipts) has a 2–5 day delay
- No unique transaction IDs — reconciliation against bank/Xero requires manual name/date/amount matching
- No separation of concerns: client master mixed with job ledger mixed with reconciliation notes
- No data validation: free text for contractor, region, and job type leads to inconsistency
- Grant-unit tracking requires manual counting (no built-in formulas)
Part 2
Target state schema
Four primary tabs — Client Master, Job Ledger, Reconciliation Ledger, Performance Dashboard — plus one Contractor Register tab for reference data. Each tab has a single, clearly-defined purpose. Validation rules and formulas live inside the sheet so the team's day-to-day workflow is the source of truth.
Tab 1
Client Master
Single source of truth for client demographic + grant eligibility + contact + regional allocation.
Refresh: Monthly — updated by Intake when new clients onboard.
Tab 2
Job Ledger
Atomic transaction log. One row = one completed job. The sole source of truth for what was delivered, who delivered it, when, and what the client owes.
Refresh: Real-time — updated by service teams as jobs complete.
Tab 3
Reconciliation Ledger
Transaction-level matching of client payments to invoices to bank deposits. Bridges Job Ledger ↔ Bank Feed ↔ Xero.
Refresh: Weekly bank reconciliation.
Tab 4
Performance Dashboard
Executive summary — grant commitment vs actual delivery, regional breakdowns, monthly trends, compliance status.
Refresh: Auto-calculated from Job Ledger (daily).
Tab 1 — Client Master schema
| Column | Type | Notes |
|---|---|---|
| ClientID | Text (auto-increment) | Format: CL-001, CL-002. Unique identifier. |
| ClientName | Text | Full name. |
| Age | Number | Reporting only; not used in calculations. |
| Region | Dropdown | [North Melbourne, Ascot Vale, Moonee Ponds, Essendon, Brunswick, …]. |
| DAEligible | Checkbox | True if client has DA allocation. |
| DAHoursAlloc | Number | Annual DA hours from MyAgedCare allocation. |
| HMEligible | Checkbox | True if client has HM allocation. |
| HMHoursAlloc | Number | Annual HM hours from grant. |
| TransportEligible | Checkbox | True if client has Transport allocation. |
| TransportTripsAlloc | Number | Quarterly trips from grant. |
| PreferredPaymentMethod | Dropdown | [Card via SMS, Bank Transfer, Cash, BPAY, Cheque]. |
| PhoneNumber | Text | For booking confirmations. |
| EmailAddress | Text | If available. |
| PreferredCntcWindow | Text | e.g. “mornings 9–12” or “anytime”. |
| EvidencePreference | Dropdown | [Photos emailed, WhatsApp, GPS tracked, Paper signed]. |
| OnboardDate | Date | When client first entered the system. |
| Notes | Text | Dietary restrictions, access issues, preferred worker gender, etc. |
Validation: Region and PreferredPaymentMethod use linked dropdowns; eligibility checkboxes gate which ledger rows are possible for the client; OnboardDate format enforced.
Tab 2 — Job Ledger schema
This is the operational heart of the system. Every completed job — whether DA, HM, or Transport — lands as one row.
| Column | Type | Notes |
|---|---|---|
| JobID | Text | Format: JOB-2026-04-00001. Unique, never reused. |
| DateCompleted | Date | When job was finished, not when entered into sheet. |
| ClientID | Dropdown | Link to Client Master. Governs downstream eligibility. |
| ServiceType | Dropdown | [DA, HM-Garden, HM-Gutter, HM-Handyman, Transport]. |
| ContractorID | Dropdown | Link to Contractor Register. Links to payment authority. |
| WorkerName | Text | First name of actual worker (for client receipt). |
| LocationRegion | Dropdown | Filled from ClientID; overridable if work done elsewhere. |
| StartTime / EndTime | Time | For DA/HM jobs; optional for Transport. Duration calculated. |
| HoursWorked | Number | Manual entry or auto-calculated from StartTime/EndTime. |
| TripsCompleted | Number | For Transport only. |
| BeforePhotos / AfterPhotos | URL | Link to Drive folder or image URL (auto-populated if GPS-tracked). |
| JobNotes | Text | Free text: “Client not home”, “Gutter blocked with leaves”, etc. |
| JobStatus | Dropdown | [Complete, Incomplete, Issue]. Replaces colour coding. |
| IssueType | Conditional dropdown | Shows only if JobStatus = Issue. [Client absent, Safety concern, Contractor no-show, Incomplete scope, Other]. |
| ClientChargeRate | Number | Hourly rate or flat fee from service agreement. |
| ClientChargeTotal | Number | = HoursWorked × ClientChargeRate (or flat fee). |
| GrantUnitsUsed | Number | Calculated: DA = 1 per job; HM = HoursWorked; Transport = TripsCompleted. |
| PaymentStatus | Dropdown | [Unpaid, Partial, Paid]. Replaces colour coding. |
| PaymentMethod | Dropdown | [Card via SMS, Bank Transfer, Cash, BPAY, Cheque, Not Requested]. |
| AmountPaid | Number | Actual cash received (may differ from charge if partial). |
| PaymentDate | Date | When cash was banked (not promised date). |
| ReceiptRef | Text | Invoice number or Square receipt ID. For reconciliation against the bank feed. |
| XeroInvoiceID | Text | Link to Xero invoice (auto-filled if Square-synced; manual if Xero-only). |
| ContractorCost | Number | What EC paid the contractor for this job. |
| ContractorPaymentStatus | Dropdown | [Unpaid, Paid]. |
| ContractorPaymentDate | Date | When EC paid the contractor. |
| DEXSubmittedYN | Checkbox | True if this job was included in a DEX monthly report. |
| DEXSubmissionDate | Date | Which DEX submission (month/year). |
| QCApprovedBy | Dropdown | [Alexis, Nawal, Alisya, blank if not yet QC'd]. |
| QCApprovedDate | Date | When QC sign-off occurred. |
| CreatedDate / LastModified | Date | System timestamps (auto-filled). |
| ModifiedBy | Dropdown | Who last edited (audit trail). |
Key formulas
- HoursWorked: if StartTime and EndTime provided,
= (EndTime − StartTime) × 24; else manual. - ClientChargeTotal:
= IF(ServiceType = "Transport", TripsCompleted × ClientChargeRate, HoursWorked × ClientChargeRate). - GrantUnitsUsed:
= IF(ServiceType = "DA", 1, IF(ServiceType = "Transport", TripsCompleted, HoursWorked)). - ContractorPaidStatus: auto-populated when ContractorPaymentDate is filled.
Tab 3 — Reconciliation Ledger schema
| Column | Type | Notes |
|---|---|---|
| TransactionID | Text | Format: TXN-2026-04-00001. Unique. |
| DateReconciling | Date | When this reconciliation entry was created/updated. |
| JobID | Dropdown | Link to Job Ledger. May be blank for non-job payments (e.g. family-member advance). |
| ClientID | Dropdown | Which client the payment relates to. |
| InvoiceID | Text | Xero or Square invoice number. |
| InvoiceAmount | Number | What client was invoiced. |
| BankDepositDate | Date | When money arrived in EC bank account. |
| BankDepositAmount | Number | Amount cleared. May differ from InvoiceAmount (partial payment). |
| BankTransactionRef | Text | Bank statement reference or BSB/Account hint. |
| MatchStatus | Dropdown | [Matched, Partial Match, Unmatched, Disputed]. |
| MatchMethod | Dropdown | [Invoice ref in bank memo, Fuzzy match (name+amount+date), Manual review]. |
| MatchConfidence | Dropdown | [High, Medium, Low]. |
| MatchingNotes | Text | e.g. “Family member Jane Smith transferred $50 from her account on behalf of John Smith (client). Matched via phone-call confirmation.” |
| OutstandingAmount | Number | = InvoiceAmount − BankDepositAmount. |
| WriteOffYN | Checkbox | True if debt is deemed uncollectable and removed from AR. |
| WriteOffReason | Conditional dropdown | Shows if WriteOffYN = True. [Client deceased, Uncontactable, Agreed discount, Other]. |
| WriteOffApprovedBy | Conditional dropdown | CEO or CFO sign-off required. |
| XeroPostedDate | Date | When this transaction was posted to Xero. |
| ReconciliationApprovedBy | Dropdown | [Nawal, Elle, Mohamed]. |
| ReconciliationApprovedDate | Date | Sign-off. |
Fuzzy matcher logic (embedded as hidden helper columns)
Input: Job Ledger (JobID, ClientChargeTotal), Bank CSV feed (Amount, Date, Memo).
Algorithm: IF (ABS(Amount − ClientChargeTotal) < 5) AND (DaysApart(BankDate, JobCompletedDate) ≤ 14) AND (TextSimilarity(Memo, ClientName) > 0.6) → “Fuzzy Match (High)”, else continue.
Output: MatchStatus column populated; rows with unmatched bank amounts highlighted in yellow.
Tab 4 — Performance Dashboard
One page. Six summary cards plus three charts. All values driven by SUMIF/COUNTIF formulas against the Job Ledger.
Card 1 — DA Performance
- Grant commitment (from CHSP agreement)
- YTD hours delivered
- YTD jobs completed
- Completion rate %
- Red flag if completion < 85%
Card 2 — HM Performance
- Grant commitment (hours)
- YTD hours delivered (sum across HM-Garden, HM-Gutter, HM-Handyman)
- YTD jobs completed
- Completion rate %
- Red flag if < 85%
Card 3 — Transport Performance
- Grant commitment (trips)
- YTD trips delivered
- YTD jobs completed
- Completion rate %
- Red flag if < 85%
Card 4 — Invoice Reconciliation
- Outstanding AR (sum of unpaid)
- Average days outstanding
- Reconciliation backlog (count unmatched)
- % reconciled
Card 5 — Contractor Compliance
- Active contractors
- With current police check
- With current WWVP
- Compliance % (red if < 100%)
Card 6 — Monthly Finance Snapshot
- Client invoices issued (this month)
- Client payments received (this month)
- Collection rate %
- Contractor costs (this month)
- Net margin (this month)
Charts
- Chart 1 — Monthly grant units trend (stacked bar): X-axis month, Y-axis grant units delivered, stacks by service (DA, HM-Garden, HM-Gutter, HM-Handyman, Transport), overlay line showing the monthly target (commitment ÷ 12).
- Chart 2 — Regional breakdown (pie): hours/trips per region. Identifies regional under/over-serving.
- Chart 3 — Payment status waterfall (area): Invoiced (cumulative) → Paid → Outstanding → Write-off. Shows month-by-month aging of client receivables.
Reform-readiness caveat. Where this schema references per-claim mechanics, billable units, classification levels, or price caps that would feed the Support at Home (SAH) program, the equivalents are based on HCP-program rules published October 2025. Equivalent CHSP-to-SAH transition mechanics have not yet been published by the Department; we are using HCP as the best available proxy. The transition is currently flagged for no earlier than 1 July 2027.
Part 3
Contractor register (linked reference data)
One row per contractor firm or individual. Linked from Job Ledger via ContractorID. This tab gives EC its first auditable compliance register.
| Column | Type | Notes |
|---|---|---|
| ContractorID | Text | Format: CONT-001, CONT-002. |
| ContractorName | Text | Business or individual name. |
| ServiceType | Dropdown | [DA, HM-Garden, HM-Gutter, HM-Handyman, Transport]. |
| BusinessStatus | Dropdown | [Active, Paused, Inactive]. |
| PoliceCheckRequired | Checkbox | True per CHSP working-with-vulnerable-persons requirement. |
| PoliceCheckSighted | Date | When police check was physically sighted and recorded. |
| PoliceCheckExpiry | Date | Expiry date of police check. |
| PoliceCheckStatus | Formula | = IF(TODAY() < PoliceCheckExpiry, "Current", "EXPIRED"). Red flag if expired. |
| WWVPRequired | Checkbox | True per CHSP. |
| WWVPSighted / Expiry / Status | Date / Date / Formula | Same pattern as Police Check. |
| InsuranceRequired / Sighted / Expiry / Status | Checkbox / Date / Date / Formula | Public liability insurance. Same pattern. |
| ServiceAgreementYN | Checkbox | Written service agreement on file. |
| ServiceAgreementDate | Date | When signed. |
| PricingBasis | Dropdown | [Hourly rate, Flat fee per job, Retainer, Commission]. |
| DefaultHourlyRate | Number | Used for DA/HM jobs if no override. |
| ContactName / PhoneNumber / EmailAddress | Text | Primary contact, for dispatch and invoicing. |
| BankAccount | Text | BSB/Account (masked for security). For payments. |
| RelatedPartyYN | Checkbox | True if owner is related to EC leadership. |
| RelatedPartyNotes | Text | Brief description of the relationship. |
| RelatedPartyRegisterDate | Date | When declared to funder. |
| Notes | Text | Any issues, preferences, escalation. |
Validation: Status formulas auto-update (no manual entry). Expiry columns have conditional formatting — yellow if < 30 days, red if expired.
Part 4
Migration strategy + data mapping
Phase 1
Parallel Build (weeks 1–2)
- Build all five new tabs in a separate sheet (“EC_Master_v2”) alongside existing sheets
- Populate Client Master from existing client names + MyAgedCare data
- Populate Contractor Register from historical contractor names; extract police-check / WWVP dates from Compliance files
- Migrate 3 months of historical Job Ledger data (Feb–Apr 2026) manually — highest-value recent data
- Set up Reconciliation Ledger with bank feed for Apr–May 2026
- Validate formulas; spot-check 20 random jobs
Phase 2
Team UAT (weeks 2–3)
- Alexis (HM) tests with the new Job Ledger; QC workflow unchanged, but data-entry format changes
- Alisya (Transport) tests booking-to-ledger flow
- Nawal tests Reconciliation Ledger + dashboard refresh
- Elle / Mohamed test bank-reconciliation workflow
- Kaltun spot-checks dashboard vs historical P&L
- Feedback loop; fix any data-entry UX issues
Phase 3
Cutover (Fri EOD week 3)
- Rename old sheets to “v1_Archive” (read-only)
- Rename new sheet to live names
- All new entries from Monday (week 4) go to new sheets only
- 1-week overlap period: any new entries on old sheets are manually migrated to new
- Kaltun sends team announcement with new workflow + quick-reference guide
Historical data retention
- Old sheets archived but remain accessible (read-only) for audit trail
- 2-year lookback available from new sheets (Reconciliation Ledger)
- Dashboard will backfill with historical formula calculations once data is migrated
Part 5
Team training + workflows
How each role uses the new schema day-to-day.
Intake (Umar)
- On MyAgedCare referral: add row to Client Master (auto-increment ClientID), fill eligibility + region + contact info
- Link to Job Ledger when job scheduled
Domestic Assistance (Nawal + DA contractor)
- Job completed: create Job Ledger row, fill ClientID, ServiceType, ContractorID, DateCompleted, StartTime, EndTime (or manual HoursWorked), photos URL, JobStatus
- Payment collected: fill PaymentStatus, AmountPaid, PaymentDate, ReceiptRef
- QC approval: Nawal marks QCApprovedBy and QCApprovedDate
Home Maintenance (Alexis + contractors)
- Pre-job: create Job Ledger row with ClientID, ServiceType (HM-[type]), ContractorID, scheduled dates, notes
- Post-job: contractor provides photos + hours; Alexis enters HoursWorked, BeforePhotos, AfterPhotos, JobNotes
- QC: review, mark JobStatus = Complete or Issue; if Issue, fill IssueType
- Approval: QCApprovedBy = Alexis, QCApprovedDate
Transport (Alisya + Nawal)
- Booking: create Job Ledger row, ClientID, ServiceType = Transport, ContractorID, TripsCompleted = 1, DateCompleted
- Receipt: Nawal logs PaymentMethod, AmountPaid, ReceiptRef when receipt arrives
- QC: mark QCApprovedBy = Nawal
Accounts reconciliation (Elle / Mohamed)
- Bank feed arrives: load into Reconciliation Ledger helper columns
- Fuzzy matcher highlights likely matches; manual review of unmatched
- For each matched transaction: fill TransactionID, InvoiceID, MatchStatus, MatchMethod, BankDepositDate, BankDepositAmount
- Weekly sign-off: ReconciliationApprovedBy, ReconciliationApprovedDate
DEX reporting (Nawal / Kaltun)
- Monthly: pull Job Ledger filtered by DateCompleted, ServiceType, GrantUnitsUsed
- Dashboard automatically shows grant-units delivered; compare to commitment
- If < 85%, alert Kaltun (conditional formatting)
- Prepare DEX submission — now auditable, not cell-colour-based
Part 6
Implementation checklist
| When | What |
|---|---|
| Week 1, Day 1 | Share this blueprint with EC team; get feedback |
| Week 1, Day 2–3 | Design Google Sheets formulas in a separate test sheet |
| Week 1, Day 4 | Populate Client Master + Contractor Register |
| Week 1, Day 5 | Migrate Feb–Apr 2026 Job Ledger data |
| Week 2, Day 1–2 | Build Reconciliation Ledger + bank feed formulas |
| Week 2, Day 3 | Build Performance Dashboard; validate formulas |
| Week 2, Day 4 | Alexis, Nawal, Alisya UAT; collect feedback |
| Week 2, Day 5 | Fix bugs from UAT; finalise workflow docs |
| Week 3, Day 1–2 | Team training sessions (30 min per role) |
| Week 3, Day 3–5 | Overlap period (dual entry if needed); support calls |
| Friday EOD Week 3 | Cutover; archive old sheets; go live |
Part 7
Success criteria + metrics
| Goal | Metric | Target | Measurement |
|---|---|---|---|
| Data consistency | % of Job Ledger rows with complete required fields | > 95% | Weekly audit of new entries |
| Reconciliation time | Days from payment received to Reconciliation Ledger posted | < 3 days (was ~7–14) | Sample 10 jobs/week |
| Grant-unit visibility | Monthly dashboard refresh accuracy vs DEX submission | 100% match | Compare dashboard to DEX PDF each month |
| Contractor compliance | % of active contractors with current police check + WWVP | 100% (was 0% tracked) | Dashboard card, red flag if < 100% |
| Team adoption | % of staff correctly filling Job Ledger (no reminders needed) | > 90% by week 4 | Spot-check entry quality |
| AR aging reduction | Outstanding invoices > 90 days old | < 10% of AR (was 40%+) | Reconciliation Ledger report |
Part 8
Future enhancements (post-revamp)
- GPS-tracked evidence intake: if a contractor app is built, auto-populate BeforePhotos, AfterPhotos, StartTime, EndTime, HoursWorked from app telemetry.
- Automated fuzzy matcher: currently manual. Could be a daily script pulling bank CSV, running the match algorithm, and auto-populating the Reconciliation Ledger with high-confidence matches.
- DEX API integration: pull grant commitment directly from DEX instead of manual entry; auto-flag if trajectory misses.
- Xero sync: post-cutover, one-way sync from Reconciliation Ledger → Xero invoices (for unpaid) and bank transactions (for received).
- Contractor management app: supplement or replace the manual contractor register with app-based compliance tracking + photo evidence.
Part 9
Risks + mitigations
| Risk | Impact | Mitigation |
|---|---|---|
| Data entry errors during migration | Bad historical data cascades to dashboard | Spot-check 20 random rows manually; second pair of eyes on Feb–Apr 2026 |
| Team resistance to new workflow | Adoption fails; revert to old sheets | Involve Alexis, Nawal, Alisya in blueprint feedback; 30-min training per role; first-week high support |
| Formula errors (esp. date calculations) | Dashboard shows wrong KPIs | Test all formulas with 10 sample rows before UAT; conditional formatting to catch blanks |
| Google Sheets permission issues | Collaboration breaks mid-revamp | Ensure all editors have full access to v2 sheet before starting; no view-only editors |
| Contractor data incomplete | Compliance register missing key expiry dates | Proactive email to all contractors asking police check + WWVP info; cross-reference email + Drive |
Part 10
Sign-off + next steps
- Kaltun reviews blueprint (1–2 day feedback window)
- Alexis spot-checks HM schema (especially IssueType dropdown options)
- Nawal spot-checks Reconciliation Ledger workflow
- MR Labs builds the test sheet with formulas + sample data (week 1)
- Team UAT (week 2)
- Cutover (Friday week 3)
Handover: once live, the team owns day-to-day data entry. MR Labs remains on-call for formula questions and issues during the first two weeks.