Reconciliation across three SAP systems: how a hospital operator automated 52 Excel analyses a month

Every month, the finance team of a hospital operator asks the same question: has every document been posted correctly, or are costs still open somewhere? The answer sat in three SAP systems and took 52 Excel analyses, each one 4 to 8 hours of manual work.

Today a UiPath robot pulls the data from SAP overnight and checks every posting. By morning the result is ready. Lunatec automated the process together with the finance department, and by our estimate the hospitals now get back more than 20 working days a month.

Where the time went

The reconciliation ran across three SAP systems, one of them a legacy system that now serves as an archive. For every hospital and every form, someone on the team pulled a separate table out of SAP. Loading alone often took 10 to 15 minutes per table.

Then the real work started in Excel. The data was filtered, matched by hand to the right account numbers and allocation values across 56 mapping tables, and finally copied into a template. That added up to 4 to 8 hours for a single analysis.

And there were 52 of them every month. Anyone comparing account numbers across five documents on three screens can easily miss a hit, however carefully they work. A robot is simply better suited to this kind of job.

How the robot works

Lunatec split the reconciliation across several UiPath processes that work together through UiPath Orchestrator. Four performer processes handle the downloads. They log in through the SAP interface, open the transaction with the right variant and wait until even the large tables have fully loaded. Each finished download is stored as a queue item in Orchestrator.

A separate calculation process does the rest in the background. It reads each queue item to see which hospital and which form it belongs to, then picks the matching script from around 20. The scripts link account numbers and allocation values via the 56 mapping tables that used to be searched by hand.

The robot loads from SAP overnight and does the calculation in the background. By morning, a finished file is waiting for every analysis.

One technical decision paid off in particular. The scripts are written in JavaScript and process a whole table in one pass, instead of touching each row in a For Each loop. In this project that was around 20 times faster.

What is ready in the morning

Each analysis produces an Excel file with two sheets. The first lists the open items, the second all postings with a yes/no check against the archive system. The robot flags documents over €100 separately, so the hospitals can follow them up directly. The run starts overnight. When the finance team arrives in the morning, the files are waiting.

 BeforeWith the robot
Analyses per month52, by hand in Excel52, fully automated overnight
Effort per analysis4 to 8 hoursfinished file in the morning
SAP loading time (around 11 hours a month)the team waits for SAPthe robot waits
Matching across 56 mapping tablesby handby script in every run

The more than 20 working days are easy to check. Even at the lower bound of 4 hours, 52 analyses come to 208 hours, or 26 working days a month. The figure of more than 20 days is deliberately conservative.

Started small, extended every month

The project began with the downloads from SAP and grew step by step from there. After each monthly run, the Lunatec team sits down with the finance department and goes through the results. Whatever stands out feeds into the next run, and the analysis gains new sheets and values.

This meant the finance team did not have to write down every rule in advance. They could point at a real result and say what was still missing, which is quicker than any requirements document.

What other finance departments can take from this

Document reconciliation is a hospital topic here, but the pattern behind it turns up in almost every finance department that works with SAP and Excel. Four points from the project carry over well:

  • Waiting time is a good place to start. Where staff regularly wait 10 to 15 minutes for an SAP table, a robot can just as well do it at night.
  • Keep downloads and calculation apart. With queue items in Orchestrator, every download is a work step of its own. The calculation process picks them up without anyone moving files around.
  • Large tables belong in scripts. Row-by-row loops are too slow for these volumes.
  • Keep building with the finance team, month by month. Every real run shows which values and sheets are missing next, and the analysis grows with demand.

What does your month-end look like?

Lunatec is a UiPath Diamond Partner and automates processes like this one in healthcare and the public sector. If your team pulls tables from SAP every month and pieces them together in Excel, we are happy to look at the process with you. Afterwards you will know which part is worth starting with and how much time you can realistically get back.