The consultant changes the file, the management reporting doesn't

Where staff costs came from

The management reporting had started from five voice messages from the client. It calculates margins and revenue per work centre. Staff costs, however, come from outside, in the payroll consultant's file.

Why a format change stopped the figures

One day the consultant changed the file format: one column became two. A small change for whoever prepares the file, but enough to break the import and, with it, the management reporting figures.

How I rebuilt the import

I adapted the import to the new format, added employee matching with manual confirmation and a rate history.

• Import in the new format. The file is read with the new structure.
• Employee matching. Each row is linked to the right employee by staff number or by name. Anyone not recognised appears in a list to be confirmed by hand.
• A single source for costs. Costs come in only through expenses, so every figure has a single origin.
• Rate history. Every rate has a start date, so the figures for a period use the rates valid in that period.

What has changed

• Margin and average revenue per work centre are calculated over the right period, with the right rates.
• Unrecognised employees are confirmed by hand, and nobody is left out of the import.
• The import was adapted to the new format on the same day the issue was reported.

What you can take away

• An import from a third-party file should be designed for the day the format changes.
• If a piece of data doesn't match on its own, it is better to ask for confirmation than to guess.
• Rates change: without a start date, past figures change along with them.

Details have been changed to protect the client's confidentiality.