Treasurer: How can you obtain quality traceability of your Excel processes?

Whether or not you have a Treasury Management System (TMS), Excel occupies a major place in your business. You use the spreadsheet every day, and it is likely that the data you want to analyze comes from multiple sources: information systems, staff, partners, departments, and subsidiaries …

Therefore, the question of the traceability of your Excel processes is of paramount importance. Let us try to make this a little clearer.

For example, you are treasurer at the headquarters of an international company and must produce regular cash forecasts at the group level. To do this, you have to organize data transfers from each of your 20 subsidiaries.

The most common method (the one you may be using today) will be to create a template in Excel so that the treasurers of each of your subsidiaries fill it out. You make it available on your Sharepoint or send it by mail to everyone. On your end, you create a second Excel file where all the returns will be aggregated. And to remind the delayed treasurers: a sheet on the corner of your desk to note down who answered you.

This is one option. But is it optimal?

Issue number 1: aggregating results

You have received all the answers from your subsidiaries: so you have to enter them into your summary file. “copy/paste” here we come! Problem: you will have to convert a large number of data (exchange rates, currencies, units of measurement …). Carefully, you convert and aggregate all information from each subsidiary. Unfortunately, your results do not seem consistent: maybe an error slipped in during the gathering of data.

Result: you must seek the cause of the error among all the files received … good luck!

You could instead use Gathering Tools. Staff members receive your Gathering Tools form (copy of your usual Excel file) and click on “send”. The entered data will be automatically sent and proposed for integration into your database, thus reducing the number of errors due to “copy/paste”. If a piece of information is inconsistent during the transmission attempt, a pop-up appears with the origin of the error, prompting the user to correct it to allow the data to be sent. Result: much more reliable data and an invaluable time saving for you.

Issue number 2: versioning

Your Swedish subsidiary, Nils, has sent you its data on Wednesday at 10.35 a.m. You immediately enter them into your table. You note on your paper that you have received information about Sweden.

Thursday morning, Nils realizes that it sent you the numbers for the wrong period. It immediately corrects it and sends you another table. In a hurry, you open the email and think that he sent you the same file. Thinking he made a mistake, you ignore the email.

Result: your cash forecasts are false, but you do not know it.

Alternatively, you can use Gathering Tools, which gathers all the answers received, with the following information: issuer, date and time, comments. So, if you receive multiple versions, you can see at a glance which one is the most recent. In addition, the issuer may insert a comment with additional information. This will allow you to reduce the number of errors and to obtain the history of all exchanges with the concerned subsidiary.

Issue number 3: control of responses

John, your contact for the English subsidiary, sends you his file stating that the table is incomplete and that he will provide you with the missing data later. Mechanically, you note on your sheet that you have received information from the English subsidiary.

A few days before your subsidiaries’ deadline for sending files, you decide to send a reminder to the late senders!  You glance at your tracking sheet. John’s name is checked: you do not send him a reminder.

Result: your cash forecasts are false, but you do not know it.

Alternatively, you can group all your Excel processes into one and the same structuring tool: Gathering Tools. All received answers are automatically reported. The software categorizes respondents and non-respondents as well as complete, incomplete or even wrong answers. You can then remind the right contacts in just a few clicks. This will save you time and reduce the number of errors.

Finally, you can thank Nils and John. Thanks to them, you have realized the critical flaws of your Excel processes. The good news is, you have found the solution.