Automating Annex 15AI: what to validate before filing

Annex 15AI is the report with which a financial institution informs the SBS (Superintendencia de Banca, Seguros y AFP, Peru’s banking, insurance and pension fund regulator) of its daily liquidity position. Because it is daily, it is also the report that accumulates the most hours of manual work over a month when it is prepared from system extracts and a spreadsheet that one person on the team knows better than anyone else.

Automating it does not mean rebuilding that spreadsheet in another tool. It means defining where each figure comes from, which rules transform it, and what must be checked before the file leaves the institution. This article walks through those checks, which are what separates a report that “runs by itself” from one nobody trusts.

The root problem: the spreadsheet that grew

A spreadsheet for Annex 15AI usually starts small. Over the months it gains auxiliary tabs, manual adjustments for special cases, and formulas nobody dares to touch. Three symptoms show it has reached its limit:

• Only one or two people can produce it, and their absence is an operational risk.
• Validation is done “by eye”, comparing with the previous day, with no record of what was reviewed.
• When the SBS queries a figure, reconstructing how it was calculated takes longer than correcting it.

Automation must solve all three, not just speed up the calculation.

Where the data lives

The report combines information from at least two sources: the core system, which records asset and liability balances by product and currency, and the general ledger, which records the same concepts at account level. The first task is to document, for each line of the annex, which source it comes from, which filter obtains it, and at what time of day the cut-off is taken.

The core does not need to be modified: the extracts it already produces are enough. What is needed is for those extracts to be the same every day, with the same structure and the same schedule, because most errors in an automated report come from a source that changed without notice.

The validations worth building in

An automated report without validations only produces errors faster. These are the checks we have found most useful, in the order they should run.

1. Completeness of sources

Before calculating anything, the process must verify that each extract arrived, that it matches the report date, and that its record count is within the expected range. An empty extract, or one from the previous day, produces a report that “ties out” and is wrong.

2. Tie-out with the ledger

The asset and liability totals that feed the annex must match the ledger balances of the corresponding accounts, by currency. When they do not, the difference must be explained before moving on: a transaction recorded in the core but not posted, or a manual entry with no counterpart in the core.

3. Variance against the previous day

Each line is compared with the previous day’s and with a reasonable variance range for that line. The goal is not to block the report but to flag what changed and request a recorded explanation: a large maturity, a one-off transaction, an exchange rate movement.

4. Internal consistency

Subtotals must add up; ratios must be calculated on the same bases as their components; foreign currency figures must be converted at the exchange rate of the correct date. These are simple checks that avoid most formal observations.

5. Regulatory limits

If the resulting ratio approaches a limit, the process should warn in advance, not once the file has gone out. This validation turns the report into a management tool, not just a compliance one.

What remains once the process is in production

A well-automated Annex 15AI leaves three things that did not exist before:

• A defined data source per line, with its filter and cut-off time.
• Documented calculation rules, which can be reviewed when the regulation changes.
• A validation log per date: what was checked, what was flagged, and who approved.

The finance team still reviews the report, but it reviews exceptions flagged by the system instead of reprocessing the information. And when someone on the team is away, the report goes out all the same.

Where to start

If the report is assembled in a spreadsheet today, the first step is not choosing a platform. It is documenting the current spreadsheet: sources, rules and implicit checks. That inventory usually reveals that much of the manual work consists of validations nobody formalized, and it is the input for deciding whether the solution belongs in the institution’s system, in Power Platform, or in a workbook with controls and versioning.