The client and the context
Agence Française de Développement (AFD), the French development agency, relies on a network of more than 80 offices around the world, grouped into regional directorates, with about 1,000 positions in total.
Each year, the department in charge of this network prepares its budget through a budget round. Office directors check their list of positions and report the changes expected by the end of the year. Regional directors state their needs for the following year, in positions to create, remove or convert. The department consolidates these requests, makes its decisions, then tells each region how many positions it will have.
On this project, we work with Moonday Consulting, the consulting firm that leads the engagement with AFD.
The problem
Until 2023, one person kept the network’s position database in an Excel file. That person entered the updates, provided the figures to anyone who asked and ran the budget round. Each summer, they sent Excel files to the offices to collect the data, then consolidated them by hand. Management saw this setup as a bottleneck and as a risk of losing information.
The file itself was a problem. It held one row per person, with no position identifier, and each update overwrote the previous value. The history of a position was therefore lost, and the headcount of each office was not reliably known.
AFD wanted each office to update its own positions and declare its needs in a shared tool, in time for the summer 2023 round.
What we did
We built a Shiny application, hosted on AFD’s infrastructure. The first version was delivered seven weeks after the order, ten days before the round opened.
Each user sees their own scope. An office director sees the office’s positions, with the person in the role, the contract type and the start and end dates, and can correct an error or declare a change, such as a departure or a new contract type. A regional director sees all the offices in the region and enters the needs for the following year. The administrator sees the whole network and approves or rejects requests.
Screenshots taken with fictitious data.
A database built around the position. The first version stored three states of the network, one per stage of the round. That model reached its limit the following year, when AFD asked for different access levels for offices, regional directorates, geographic departments and the administrator. We then rebuilt the application around a database where each position has an identifier and each change adds a row to its history. From it, the application derives three views of the same network: before the round, after the round and for the following year.
Approval only for changes to a position. Not every entry needs approval, and the application separates corrections from changes. A correction concerns the name, employee ID or dates of the person in the role: it edits the row and is saved immediately. A change adds a row to the history. When it affects the position itself, through its contract type, its location, its creation or its removal, it waits for the administrator’s decision. The administrator therefore only receives the requests that alter the structure of the network.
The results
Since the first round, offices have entered their positions in the application themselves and no longer exchange Excel files. Every position in the network has an identifier and a history.
The application has been used for four rounds, from 2023 to 2026, by about a hundred office directors, regional directors and geographic department directors.
The administrator has become more autonomous too. They open and close the round, create or remove a position, add a location and download Excel exports from the application. At first, these operations went through us.
Do you have a database kept by one person in an Excel file that several teams should be feeding? Get in touch.