Client
JPMorgan Chase Bank, N.A.
Role
Process analysis & automation
Tooling
Alteryx
Window
One week

Three reports reconciled by hand, every cycle

The benefit

What was achieved

Process improved
65%
Less handling time per cycle
Process improved
83%
Fewer errors in the finished report
The problem

Three reports were reconciled by hand every cycle. It took 48 minutes of an analyst and produced about six errors.

My contribution

What I personally did on this

Owned
The time study · root-cause analysis · the scope decision
Built
The 13-tool Alteryx workflow and its email trigger
Led
Validation across the eight build stages
What was delivered

Nobody runs it. The email runs it.

Before
  • Three reports opened and reformatted by hand every cycle
  • Comparison done line by line, visually
  • 8 manual touchpoints between arrival and final report
  • Around 6 formatting or copy-paste issues per cycle
  • Whoever owned it had to remember to start it
After
  • Fires on an inbound email whose subject names the report
  • Standardise, join, calculate variance, flag exceptions
  • 3 touchpoints, all of them judgement
  • Reconciled pack and exception list emailed straight back out
  • Same inputs, same outputs, every run
The Alteryx workflow drawn tool by tool: three Input Data tools read the trade, finance and rates reports; Select and Data Cleansing standardise them; two Formula tools normalise keys and prepare rates; Join Multiple matches the three sources; a Formula calculates variance and match status; Filter separates matches from exceptions; Summarize produces control totals; Output Data writes the reconciled pack and exception list
Thirteen tools across four Alteryx categories. The numbered circles are the eight stages it was built and validated against
Workflow analysis

Where the bottleneck lived

Before
Three reports arriveTrade, finance, rates. All on time
→
⚠ Bottleneck Clean, format, compare by hand Deterministic work, repeated identically every cycle, with no judgement in it
→
Investigate the breaksThe only part that needed a person
After
Email landsThe subject names the report. No one has to notice it arrived
→
✓ Automated Standardise, join, compare, flag Same logic every time, and it shows its control totals
→
✓ Unchanged A person investigates the exceptions Deliberately. Automating this step would have been the mistake
Notes

What shaped the decisions

On the numbers

The cycle was timed before and after, activity by activity, which is why the saving breaks down into cleaning, comparison and report preparation rather than arriving as one round figure.

The scope decision that made it a week

Root cause named six categories. System integration was genuinely one of them, and it was deliberately left alone. A one-week sprint that reaches for the hard cause delivers nothing at all.

Requirements

What it had to do, and why it mattered

Five, and the last one is the reason it still runs.

  1. 01
    Read three sources without anyone reformatting them first
    Align names and types, trim whitespace, handle nulls, strip formatting noise.
    The sources were never going to standardise themselves. Absorbing that mess is the automation’s job, not a precondition for it.
    Standardise
  2. 02
    Join on agreed reconciliation keys
    Dates, identifiers and numeric fields normalised so the keys actually match.
    Most reconciliation failures are not disagreements about value. They are two records that never met.
    Match
  3. 03
    Calculate variance and label each row
    Expected against actual, the difference, and a match or exception status on every line.
    A number on its own invites re-checking. A labelled row tells the reader whether to stop.
    Compare
  4. 04
    Produce control totals, not just a result
    Row counts, key aggregates and the size of the exception population, every run.
    Without them the output has to be trusted. With them it can be checked in ten seconds, which is what gets an automation adopted.
    Control
  5. 05
    Trigger itself, and deliver itself
    Fires on an inbound email whose subject names the report; sends the reconciled report and exception list back out on completion.
    An automation someone has to remember to run is just a faster manual process. This is the requirement that made it stick.
    Trigger and delivery
Method

Two days of diagnosis on a five-day job

Expensive-looking, until you consider the alternative: automating the step that was never the problem.

The sprint, day by day
Five day sprint: discover, diagnose, build, validate, hand over, with the acceptance rule and design principle
Findings

Four questions, answered in order

01Which step is actually costing the time?
SIPOC of the reconciliation process with the cleaning and comparison steps marked as the automation opportunity
SIPOC, with the automation opportunity isolated inside the process
What it showed

All three inputs already arrived reliably. The bottleneck was not sourcing the data, it was preparing and comparing it — and that is the part with no judgement in it.

02Which causes can a week actually fix?
Fishbone diagram of the reconciliation bottleneck. Six cause categories feed a spine into the effect: Software, Technology and People above; Environment, Data and Process below. Data and Process are marked as the two categories a one-week sprint could reach. The effect is that cleaning and comparing by hand consumed 48 minutes of every reporting cycle.
Six categories, one effect. Only Data and Process held anything a week could actually fix
What it showed

Data and process held the quick wins. Incompatible systems sat top-right: real, expensive, and the fastest way to spend a week and ship nothing.

03What does the built workflow actually do?
The Alteryx workflow from email trigger through input, cleanse, formula, join, filter, summarize and output, ending with the pack emailed out
Email in, reconciled pack out. Each tool replaces one manual action
What it showed

Thirteen tools, and every one of them replaces something a person was doing by hand. None of them resolves an anomaly — the break still goes to a human, with the comparison already done.

04Did it actually save anything?
Before and after timings across cleaning, reconciliation, report preparation, touchpoints and rework issues
Timed per cycle, activity by activity, before and after
What it showed

About 31 minutes a cycle, and the shape matters more than the total: the biggest cut was in comparison, the smallest in judgement. That is what a correctly scoped automation looks like.

How the options were generated

The first idea is always “automate the comparison”

Which is where most people stop. SCAMPER is a forcing device: seven prompts run against the same bottleneck, so the shortlist is wider than the first thing that came to mind. These are the working notes as they were written, one of them deliberately empty.

Substitute

Take the typing out

Replace manual data entry with automated extraction. Replace the spreadsheet with a tool built for the volume. Reassign tasks to whoever the skill actually sits with.

Combine

One pass instead of two

Bring the three sources onto one platform, and fold cleaning and validation into a single automated step rather than two manual ones.

Modify · minify

Reconcile less, not faster

More automation through cleaning, formatting and reconciliation. Fewer manual touchpoints. And cut the reconciled field set down to the ones that actually carry a decision.

Put to another use

The output is also an input

Reconciled data can predict where discrepancies recur, and one common source file can feed several reports instead of each being built alone.

Eliminate

Delete before automating

Redundant entry and approval steps, fields nobody reconciles against, and the waiting time between one person finishing and the next starting.

Reverse · rearrange

Validate before entry, not after

Move validation in front of data entry so the error never enters the process. The cheapest reconciliation is the break that was never created.

Adapt came back empty, and it stayed empty

Nothing in the analogous-process prompt produced anything worth carrying. A blank on one prompt is a result, not a gap — filling it to make the sheet look complete is how a shortlist gets padded with options nobody intends to build.

The result

How it was proven

For an automation this size, validation is one question: can it reproduce a reconciliation the business already trusts?

Validation checklist · every line had to pass before it replaced anything
CheckWhat had to be true
Source completenessRow counts for all three inputs match the source files
Key integrityBlank and duplicate reconciliation keys isolated, not silently dropped
TransformationDates, numerics and rate formats convert consistently
Join logicKnown matches join, and known breaks stay visible
Variance logicCalculated differences agree with the manual benchmark
Control totalsSums and counts consistent before and after processing
OutputReconciled report and exception file carry the expected columns
RepeatabilitySame inputs produce the same outputs on re-run
The acceptance rule

The automation had to reconcile to the validated manual result before it replaced the manual step. Where they disagreed, the manual benchmark won until the business rule said otherwise.

What shipped

What produced each result

Produced the 65%

The workflow itself

Thirteen tools from input through cleanse, normalise, join, variance, filter and summarize to output, triggered by email and delivering by email.

Produced the 65%

The scoping decision

SIPOC and root cause first, impact against effort second. Automating cleaning and comparison rather than reaching for system integration is why a week was enough.

Produced the 83%

Standardisation at the front

Cleaning and format alignment moved into the workflow, so the formatting slips that caused rework stopped being possible rather than being caught later.

Part two of two

How it was actually done

Five days. Two of them before a single tool was placed on the canvas.

!Before you open this

Everything above is the bottom line. If that is what you came for, you already have it.

What follows is the full working: 6 diagrams and 8 sections of working, the analysis behind each decision, and why it went that way instead of the obvious way. It is long on purpose. It is written to be checked, not skimmed.

Only wanted the overview? Stop here. You will not miss a single result — every number is already above this line.

← All work
Back to the portfolio
Same bank →
ASIC and MAS regulatory reporting rewrite