Software that works out collaborators' commissions from the client companies' files
Files from three companies go into a single job, the rate table does the maths and only the rows the rules don't cover are left for a person. The monthly calculation goes from 8-10 days to 2-3 hours, reviews included.
- Client
- Phoenix Solutions SRLS
- Sector
- Debt collection · Palermo, Italy
- Services
- Web & App Development · UI/UX & Design System
The context
Phoenix Solutions SRLS is a debt collection firm in Palermo working for several client companies, with door-to-door collectors and phone collectors. Every month the companies send the list of cases worked, and the firm has to work out what each collaborator is owed. Before Compensi this was done by hand in Excel sheets, row by row, and took 8-10 days. M's Works wrote them a Windows program that does the calculation and leaves people only the cases that need a decision.
The problem
Each company sends files in its own format: fixed-width CSV exports, PDF statements one per name, summary reports.
The same person has a different code at each company, and sometimes the same code is written several ways.
The rate depends on the company, the contract, the collaborator's role and how the debt was paid, in cash or with promissory notes.
Reversals and closed repayment plans produce negative rows and adjustments, exactly where hand calculation goes wrong most easily.
Nothing checked whether the calculated sum matched the totals the companies declared.
Every choice has a constraint behind it
The constraint
Three different formats, file names that change from month to month, and one of the formats is a PDF per name.
The choice
The format is recognised from the content: the header of the export, the tables in the statement, the first line of the summary. Columns are looked up by name, and PDF tables are rebuilt from where the text sits on the page.
Why
Recognising files by name would have worked until someone renamed one, and reading columns by position would have broken the first time a company reordered its export. Right after the import the program compares the sum it read with the total written at the end of the file: if they don't match it says so at once, before anyone starts correcting.
The constraint
Some rows can't be paid at the standard rate: an adjustment on a plan already paid out, a document code never seen before, a row with no collaborator.
The choice
Whatever the rate table doesn't cover goes into the rows to check, with the reason written next to it. Approving a row stays tied to the amount it had: if a rule changes and the amount moves, the row goes back to be checked.
Why
Fixing these cases on its own would have made the program look more automatic, but the mistake would have surfaced when the statement was handed over, in front of the collaborator. With people's pay the program calculates and flags, and the decision stays with whoever is doing the work.
The constraint
The people doing the sums come from years of Excel, and a correction often applies to dozens of rows at once.
The choice
Review happens in a grid you select like a spreadsheet: by dragging, with Shift and the arrow keys, by pasting a column from Excel. Before a correction is applied the program shows the commission before and after, and every change can be undone with Ctrl+Z.
Why
A form to open for every row, the usual shape of business software, would have been slower than the sheets it was replacing. When the rate changes on rows calculated by a rule, the program asks whether it applies to those rows only, to the whole rule or to a single contract, and for each option shows how much the commissions change.
The constraint
The lists and commissions contain personal data about debtors and collaborators, and the work is done on one office computer, sometimes on another.
The choice
A Windows program that keeps everything on the computer: it installs without administrator rights, saves every change as soon as it's made and moves the data to another PC on a USB stick.
Why
An online service would have put that data on someone else's server, with one more supplier handling personal data, for a job one person does once a month. Every save writes a temporary file first and then swaps it in, so not even a power cut halfway through can ruin the month.
What we built
The month's files, in one go
Drag the zip or the loose files from the three companies into the window: the program reads them together and says straight away whether the reading matches the declared totals.
Only the rows to check
Opening a month shows the rows that need a person, each with its reason. While you type in a cell, a box shows how the commission changes.
Rate table with preview
Rules are written per company, contract, role and payment type, and the most specific one wins. Before a change is saved you see which rows change and the new totals.
The documents to hand over
One command produces the Excel sheet and the PDFs, with totals per collaborator, per company and per operation type. A single collaborator's statement can be saved on its own.
The archive of months
Every month is kept with the original files, the sheet before and after corrections, a log of what changed and when, and the items already scheduled for the months ahead.
Screens
The screens use made-up data: names, codes, rates and amounts are not the client's.
The outcome
2-3 ore
to close the month's commissions, reviews included. It used to take 8-10 days by hand.
96,3%
of the rows in the hand-made sheets reproduced to the cent by the rate table before it was used on new months. The differences were adjustments, which now go to the rows to check on their own.
Every import checked to the cent against the totals the companies declare.
No row is corrected silently: every doubtful case reaches a person with its reason.
Technologies
Got a similar project?
Tell us where you're starting from. We'll reply with a clear proposal and realistic timelines, no commitment.