Air Ticket Provision & Entitlement Automation

01 — Business problem

Air-ticket provision is a month-end balance that is easy to get wrong.

Employees accrue air-ticket entitlement every day they are in service. At month-end, finance has to turn that entitlement into a provision, value it at the right ticket rate, and agree it to the balance already in the general ledger.

In practice the inputs are imperfect: leavers, duplicate IDs, missing or unmatched destinations, invalid dates and tickets used in advance. If those records flow straight into the number, the provision is misstated and the journal is posted on a figure nobody can fully explain.

• Entitlement accrued employee by employee, across staff and labour categories
• Ticket rates that must match each employee’s destination
• Data-quality issues that should stop a posting, not disappear into a total
• A ledger balance that has to be reconciled before any adjustment is made

02 — What I built

A month-end control application, from employee file to posting decision.

The application reads an Employee File and a Ticket Rate File, calculates each employee’s accrued ticket balance to the selected as-at date, values it at the matched destination rate, and checks every in-scope record against 20 exception rules.

It then reconciles the required provision to the existing GL balance and shows the adjustment needed and whether the journal is ready to post — or must be held until critical exceptions are resolved. Every figure can be traced back to the employee-level calculation and exported to Excel.

Six views

• Executive Overview
• Provision Analysis
• Employee Detail
• Exceptions & Data Quality
• Accounting Reconciliation
• Data & Methodology

03 — Key capabilities

The month-end steps finance needs, in one controlled flow.

• Entitlement accrual Tickets accrue continuously, employee by employee, from the entitlement start date to the as-at date (staff 1 ticket a year, labour 0.5).
• Rate matching Each employee’s balance is valued at the ticket rate matched on Country and City.
• Provision calculation Required provision is calculated per employee and aggregated by department, category, home country and destination.
• Exception control 20 checks run on every in-scope employee; any critical exception places the GL posting on hold.
• GL reconciliation The existing ledger balance is interpreted, compared with the required provision, and a decision rule sets the posting status.
• Audit trace An employee drill-down shows inputs, each calculation step and the reason for the result.
• Excel reporting A full Excel report, employee results, an exceptions report and blank input templates can be downloaded.
• Date-based recalculation Changing the as-at date recalculates the entire provision and reconciliation.

04 — Month-end control case

One workflow, two outcomes.

Employee calculations → validation → required provision → GL reconciliation → adjustment → posting decision.

Both scenarios as at 30 September 2026.

06 — Why it matters

A provision finance can explain, defend and post.

07 — Validation & controls

Tested as a finance control, not only as software.

• Calculation engine 0 differences across 28 reference scenarios
• Automated tests 224-test development suite
• Public package 970 safety checks, 0 failures; 39 of 39 isolated-package checks passed
• Exception rules 20 checks: 17 critical (block posting), 3 warnings (reported only)
• Decision rules Do not post (on hold) · Additional provision · Provision release · No material adjustment

08 — Technology as leverage

The technology serves the finance logic, not the other way round.

• Python
• Streamlit
• pandas
• openpyxl
• Altair
• Excel input and output

09 — Current version

What it does today.

• Runs as a working Streamlit application with clean, exception and upload-your-own-files modes
• Recommends the adjustment; journals are prepared, approved and posted in the accounting system by the user
• Reads Employee and Ticket Rate files from Excel; no ERP or HR/payroll system connection
• No user login, persistence or audit database — each session is independent
• Entitlement rules, accounting policy and ticket rates must be configured for an organisation before production use

Built with portfolio demonstration data.