01 — Market Claims Workflow Redesign

Workflow Redesign · Fixed Income & Equities

Reconstructing a fragmented manual exception process around a structured reconciliation template and a four-team production line, moving the team from partial visibility to a fully reportable pipeline.

• 182→97 — Avg. claim age, days
• 40→95% — Reconciliation rate
• ~2,000 — Live claims at any time
• 4 — Specialist teams (production line)

The market claims team reconciled exceptions arising from entitlement to coupon interest payments on fixed income securities and dividend payments on equity securities. The core issue is one of entitlement: when a bond changes hands after record date but before pay date, the seller receives the coupon from the registrar but is not entitled to keep it; the buyer paid for it as part of the purchase price via accrued interest. Because BNY operated at institutional scale, BNY typically sat in the middle of a chain, claiming from the counterparty who sold to them whilst forwarding to the counterparty they sold to.

The workflow was entirely manual and fragmented. A daily macro-run Excel file pulled all exceptions and assigned them by stock ticker letter to individual analysts, each managing 300–400 exceptions. Analysts worked largest credits and oldest claims first, leaving a significant middle band unworked and ageing. Tracking depended on freehand comments, so if an analyst was absent, their reconciliations were uninterpretable to colleagues.

Stage One: The Reconciliation Template

A structured Excel workbook was designed and made mandatory across the team, using tabs as stages of the claim. Tab 1 accepted event and exception data once. Tab 2 auto-cross-checked trade dates against record and pay dates, validated quantities, identified counterparty codes, and calculated the claimable amount. Tab 3 generated a pre-populated email, with body, addresses and SSI details auto-populated from counterparty codes and client. Tab 4 was a replica of the formal claim letter, exportable as PDF and uploadable directly to the digital signature system, removing print-sign-scan entirely.

Stage Two: The Production Line

Each analyst owned their claims end-to-end, reconciling, chasing, negotiating, escalating and settling whilst handling inbound claims from counterparties. The constant context-switching made prioritisation impossible. A production line approach was implemented: Reconcile handled new exceptions and sent the first claim. Contact managed correspondence and negotiation. Payment handled fund movements and settlement. Escalate handled complex claims and disputes.

Handover was automated via email subject codes, with CT routed to Contact, PT to Payment and ET to Escalate. Server rules categorising replies automatically. A structured status column (“Pending tax documents”, “Awaiting counterparty confirmation”) made the pipeline reportable for the first time and fed directly into the divisional KPI dashboard.

02 — Division-Wide KPI Dashboard

Power BI · Divisional Reporting

Replacing a constellation of legacy aged packs with a single Power BI dashboard, refreshed every 15 minutes, with row-level security across six tiers and metric-level data freshness indicators.

• 15 min — Refresh cadence
• 6 — RLS tiers
• 1 — Consolidated divisional view
• ∞→1 — Reports → single source

Divisional performance reporting was handled by an internal MI team producing aged packs in legacy .xls format. A separate report per metric, each stored in its own location, each updated daily or weekly through a combination of old macros and manually-maintained lookup tables. The lookup tables themselves were inconsistent: some records keyed by employee name, some by ID, some by initials, some by email.

Reference Layer First

The first stage was mapping the existing MI reports, identifying what each measured, how it was keyed, and what its lineage was, before building a central reference layer that connected all the inconsistent identifiers. This reference layer became the foundation that made cross-report consistency possible for the first time.

Multi-Source Model, Live Status

The data model pulled from Excel documents, CSV exports, start-of-day source files, and live API connections. Every metric card carried a dynamic status: “Pending update” where data was at least a day stale, “Updating” during refresh, “Updated at [timestamp]” once a same-day refresh completed.

Before the dashboard, performance data had been held at senior level only. Individual contributors were accountable for outcomes against numbers they could not see. The dashboard closed that gap entirely, and by hosting it inside the Asset Service Hub it became the natural starting point for every user's day.

Power BI · Power Query · DAX · RLS · 6 tiers · API + CSV + XLSX sources

03 — SharePoint Hub & Asset Service Hub

SharePoint · Asset Services Division

A single platform replacing fragmented network drives and personal notes, expanded across all five Asset Services teams under one divisional home, the natural starting point for every user's day.

• 5 — Team sites under one hub
• 10 — One-click platforms in header
• One source — Of truth, propagating automatically

The project addressed two problems. First, the division operated on a shared network drive, well-organised for compliance but reliant on the legacy Office file-sharing model, producing a chaotic daily cycle of overwritten changes, locked files and lost work. Second, each contributor maintained their own personal notes containing links to the platforms the role depended on; when a URL changed, the update had to cascade through every person's notes individually, and frequently did not.

Site Architecture: Four Zones

The market claims site was structured across three pages. The homepage carried four distinct zones: a row of ten large-format buttons (EASYWAY, TLM VIEW, NetX360+, NEXUS, EDI, Document Manager, SWIFT AMH, SWIFT EMX, NEXEN ERR, DYNAMICS); the migrated team file structure; a Quick Links column for regular tools (Counterparty Search, Markets Volumes, Ticksheet, IBAN Convertor); and internal escalation contacts with name, title and photo.

From One Team to a Division

The model was expanded to all five Asset Services teams. Each received its own site built to the same structure, with role-based access ensuring members could only access their own site. These were linked under a parent platform, the Asset Service Hub, which served as the divisional home page. The KPI dashboard was hosted directly within it.

04 — Power Platform Upskilling Programme

Capability Transfer · Five Teams

Building Power BI capability inside each operational team rather than centralising it, leaving every Asset Services team with someone who understood their BAU deeply enough to report on it usefully.

• 5 — Teams, one volunteer each
• 3+ — Independent reports produced
• 600 hrs — Saved annually, one workflow
• 0 — Stalled projects on departure

The division-wide KPI dashboard was the first time Power Platform had been visible at scale across BNY Pershing, used daily by individual contributors, team leaders, VPs and senior management simultaneously. That visibility created demand. Each department wanted its own custom reporting, built closer to the detail of their BAU workflows than the divisional summary.

Building Capability In Place

Rather than route requests through the continuous improvement team, capability was built directly within each team. Volunteers were recruited from across the five Asset Services teams, one per team, and a structured upskilling programme was designed and delivered.

Replicate, Don't Improve

The first task for each volunteer was deliberate in its constraint: replicate an existing report in their team, not improve it. Replication allowed them to practise the technical mechanics without the additional cognitive load of designing structure from scratch.

One volunteer built a Power Query workflow that calculated daily dividend rounds on unit trust funds by shareholder, saving 600 hours annually. The monthly markets statistics and the team goal report were both automated. When I left, none of my Power Platform projects stalled.

05 — End-to-End Operational Data Pipeline

Operational Data · Weighbridge to Reporting

From paper weighslips to live Power BI dashboards: a complete chain (weighbridge, GPS, schedule, CRM, accounting, debt collection) automated end to end, with no manual data entry remaining.

• 0 — Manual entries by go-live
• 5 — Systems joined on timestamp + key
• Intraday — Operational visibility
• £0 — Capex on hardware replacement

Senior management were venture capitalists based in London with virtually no visibility into day-to-day operations or finances at the Cheshire site. There was no reporting infrastructure. Drivers attended scheduled collections, returned to site, used the weighbridge, which printed a paper weighslip recording the registration (typed manually by the driver) and the two weights. Slips accumulated and were entered the following day by the bookkeeper into a blank Excel workbook.

Step 01: Digitising the Weighbridge

Replacing the physical weighbridge would have required significant capex. Instead I made a business case for OpenWeigh: software sitting between the sensor and the printer, intercepting the output and converting it to an exportable CSV. A dedicated always-on PC was approved, and scheduled CSV exports meant weighbridge data was available intraday with no manual intervention.

Step 02: Cleaning Registrations via GPS

Drivers typed their own registration at the weighbridge, so the data contained consistent typos. Tracking system exports recorded GPS-verified registrations and locations at every timestamp. By matching tracking to weighbridge on timestamp and location, accurate registrations replaced the manually-typed ones.

Step 03: Joining Schedule, CRM, Accounts

Weighbridge data, now carrying correct registrations, was joined to schedule data, which held the job, postcode and assigned vehicle. Customer numbers were pulled from the CRM using job postcode as the join key. The dataset was now complete at transaction level: vehicle, customer, location, tonnage, value. This connected to QuickBooks, generating weekly receivable invoices automatically.

Step 04: Automated Debt Collection

ChaserHQ connected to QuickBooks and the CRM and automatically sent templated chasers based on age of debt, escalating through a sequence until balance cleared.

06 — Cash Discrepancy Investigation Process

Cashier-Level Accountability · Two-Store Operation

Building cashier-level accountability into a shared-till environment by reconstructing nine weeks of envelopes into a transaction log, and using absolute-difference methodology to expose offsetting errors.

• 17 days — Retrospective data entry
• 1% — McDonald's Canada standard
• 7.47→0.67 — Best individual % improvement
• 12 — Shift managers & team leaders

Tills were not assigned to individual cashiers but shared across the shift. When a cash-up was performed, the resulting discrepancy was attributed to the shift as a whole. If one cashier was $50 short and another was $50 over, the shift reconciled to zero and nothing was flagged. Individual patterns of error, whether from poor counting, float mismanagement, or deliberate short-changing, were invisible.

The Workbook: Six Sheets

A cash tracking workbook was built from scratch and data entered retroactively from physical envelopes. The Entry sheet provided a clean daily input form, associating each cashier's count with the supervising manager's initials. The Data sheet computed cash over/short, and derived an absolute difference, stripping the sign to give a direction-neutral measure of inaccuracy. This was deliberate: a net of zero could mask a $5 short on one till and $5 over on another.

The Scorecards sheet produced automated rankings updated in real time. For each cashier: total absolute discrepancy, number of tills counted, average per till, total tendered, and discrepancy as % of total tender, the metric used to apply the standard.

The first report was distributed transparently after 17 days of collection, including my own figures, ranked among the managers. Only one manager met the 1% standard at 0.45%; one was at 14.58%. Across nine weeks, the effect was measurable: of cashiers present in sufficient volume in both early and later periods, the majority improved. Several high-variance cashiers reduced their rates substantially.

07 — Cash Management & Banking Overhaul

Deposit Control · Two-Store Operation

A structured cash-control audit, two store-floor process documents, a two-store deposit tracker with RAG thresholds, and per-day investigation workbooks generating plain-English narratives for shift managers.

• 36 — Controls audited, 5 categories
• 67% — Initial audit score
• −$11,768 — Consolidated 2018 shortfall
• RAG — Weekly thresholds, both stores

Whilst the cashier-level project addressed individual accountability, a parallel problem existed at deposit level. Discrepancies there reflected errors introduced during counting and deposit preparation by shift managers, separate from cashier-level shortfalls. January 2018 alone totalled −$782.04 against expected, annualising to a projected $9,207.89 loss.

Cash Control Audit: 10 Feb 2018

Thirty-six controls assessed across five categories with full, partial or zero credit. Overall 67%. Weakest areas: CCTV coverage 57% and cash audit envelope completion 50%. Cash controls 63%, with POS codes not changed weekly, skims not made four times daily, panic alarms not worn or tested.

Two Process Documents

Displayed in-store. The cashier document, “Your Till, Your Responsibility”, communicated four behavioural expectations: count your float, use only your till, lock your till, check your change. The manager document set out four mandatory deposit steps: till count, float count, backup count, deposit count.

08 — Transaction-Level Reconciliation Tool

Three-Layer Architecture · Power Query + VBA

Replacing day-total reconciliation with transaction-level matching across statement, ledger and PSP records, with a stateless classification layer feeding an immutable, append-only state machine.

• 3 — Architectural layers, no overlap
• 5 — State tables of record
• 09:15 — Daily completion time
• < 3 mo — Solo build, end to end

The existing reconciliation approach compared aggregate daily totals (statement versus ledger at day level), then worked backwards through transactions to identify the discrepancy. This is the inverse of industry practice. Rather than surfacing exceptions directly, the process began with a known difference and required manual investigation to locate it within potentially hundreds of transactions. Reconciliation typically completed around 1pm.

Layer 01: Presentation

Six tabs reflecting transaction state rather than data source: Reconciliation, Exceptions, Pending, Matched, Off Platform, Audit. A dashboard reconciles cash balances using ECB FX rates, with EUR as control currency, off-platform balances isolated and netted separately.

Layer 02: Classification (Power Query)

On every refresh, classifies each item deterministically into AutoMatch, AutoMatchInternal, or AutoException. Stateless and idempotent, with no knowledge of yesterday, manual resolutions, or pending items. Rebuilds from scratch on every run.

Layer 03: State Machine (VBA)

Five tables hold the truth: Matched_History, Exceptions_Outstanding, Pending_Outstanding, OffPlatform_History, Audit_Log. Any ItemID that has ever appeared in the Audit_Log will never be auto-inserted again, regardless of refresh count. Nothing duplicates across runs.

09 — Monthly Fee Management Tool

Intent · Execution · Reporting

Replacing a matrix spreadsheet that destroyed audit trails with a three-layer model: a deduplicated client master, a per-period transactional ledger, and a read-only dashboard derived from operational truth.

• 3 — Layers, clean separation
• 1 row — Per client per due date
• Auto — New-client onboarding

As an EMI, Remittance 360's client accounts did not carry overdraft facilities, so standing orders could not be used to collect monthly fees automatically. Fees were taken manually on or after the 15th. The legacy process used a matrix-style spreadsheet, with clients on Y, months on X, fee in each cell. When collected, the cell was zeroed.

Three failures: (i) zeroing destroyed the audit record entirely; (ii) a single shared comments column covered every period for each client; (iii) new clients were added manually from memory, with omissions inevitable.

Layer 01: CleanClientData

The operational master list. Power Query pulls from all business entities within BLK Group, cleans and standardises, and deduplicates clients who have moved between entities. New clients appear automatically on next refresh.

Layer 02: MonthlyFeeData

The transactional ledger. One row per client per due date. Populated exclusively by the AppendMonthlyFees macro; users cannot write directly. Each row carries its own Comments field: a per-period record rather than a single shared note.

10 — Fuel Efficiency & Theft Detection

Telematics × Fuel Card Reconciliation

A tank-capacity reconciliation methodology turning telematics consumption against fuel-card purchases into an arithmetic test for fraud, formalised into a fleet-level reporting tool covering 47 contracts.

• −10% — Fuel bill, no mileage drop
• 1→47 — Contract growth
• 9 — Rolling weeks per analysis
• Targeted — CCTV requested per outlier

Fuel was among the highest cost lines on any contract involving field gangs. On the United Utilities contract, a deliberate programme reduced fuel cost across three fronts: renegotiating fuel card deals, promoting efficient driving to reduce idling and harsh handling, and deterring fuel card fraud. The fraud pattern was specific: employees entrusted with fuel cards were filling jerry cans or personal vehicles at the pump in addition to their work vehicles.

The Telematics Insight

Unlike MPG-based estimates derived from mileage, the tracking system used a sensor in the fuel injector valve to report actual fuel consumption. The telematics reading was a direct measure of what went into the engine, not a calculation. Combined with fuel card transaction data showing what was purchased at the pump, a mathematical discrepancy could be calculated at vehicle level.

Methodology: Tank Capacity as Threshold

Fleet records held the precise tank size for each vehicle type: a Sprinter van carried a 90-litre tank. Starting from an assumed empty tank, each fuel card purchase was added and each telematics consumption reading subtracted, producing a running tank balance across a rolling nine-week window. If the balance exceeded the vehicle's physical tank capacity, it was arithmetically impossible for all the purchased fuel to have entered that vehicle.

The methodology was formalised into a fleet-level reporting tool, initially covering Yorkshire Water's 118-vehicle fleet, then expanded across an additional 46 contracts in the CGU division, including United Utilities, Severn Trent, Welsh Water, Northern Powergrid, Scottish Water, Northumbrian Water, Affinity Water, and several central government accounts.

11 — Contract P&L Reporting Suite

Weekly P&L · Pre-Power-Platform

A structural fix to a manual three-day weekly P&L process, isolating raw exports from transformation logic so the workbook could be rerun weekly without rebuilding.

• 3 days→1 — Production time
• Mon — Distribution day, 10am
• Fri→Tue — Weekly P&L call
• 11 — Source export tabs

Each Amey contract had a finance department producing the official monthly P&L, a legally accurate account of the prior month's costs, delivered at the start of the following month. By the time it landed, costs were weeks old. The Business Efficiency team's scope included producing a parallel weekly P&L from available cost exports, giving contract managers current-week visibility they could act on rather than waiting for the official pack to confirm what had already happened.

The Inherited Process

Owned by a senior manager who would begin each week on Monday, spend three full days collating and reconciling exports, and distribute by end of day Wednesday. Managers reviewed Thursday; the weekly call took place Friday. Each intervention was applied directly to the pasted source data, so formulas could not be left in place and reused; the manual had to be followed from the beginning each week.

The Structural Fix

On every export tab (Vehicle Hire, Plant Hire, GR, PO, Store Movements, Weekly Timesheet, Mobile Phones, Subcontract, Revenue, Overheads, Fuel), a set of yellow-highlighted columns was added to the right of the raw data. These contained formulas performing all required transformations and lookups without touching the source data itself. The raw export was pasted in and left intact.