Financial reporting for a Horizon 2020 project

subtopic

#21

Would it help if we could find someone in the Commission that knows about these things, and run a solution by him or her?


#22

If I find an already existing time tracking solution that seems to fit the purpose, no need to ask (it could only complicate matters for us, I think …).

But if we can’t find anything, having a contact would be great to propose the Commission funds an open source, cross-platform client/server project that does the job. Because in that case, there must be a lot of inefficient “manual IT” work of copy&pasting numbers in and out, in all these projects funded from EC actions.

So let me have a look at existing time tracking platforms first …


#23

@matthias, this won’t work. This is because

  1. Some of us are assigned to more than one work package. Me, I do data analysis and admin. In tech projects like OpenCare I also write myself into the development work, so that I have some time budgeted to spend with the developers doing testing, specifications etc. So, the same bill will contain time spent on different work packages. Furthermore, this is just useless form filling, because a different document tracks the allocation of time across WPs: the timesheet. If you have timesheets (and we are obliged to have them), you do not need to track the WP of bills, even if bills were allocated to just one work package. Therefore, I propose the indication of the WP is no longer required on bills (where people claim payment for their time).

  2. When it comes to things other than work time (i.e. travel + purchase of goods and services), tracking by WP is also not required. On the grant agreement, such things are divided by partner, but not by WP. So, again, even when claiming expenses I propose that we do not require the WP number. Down with form filling! :slight_smile:

  3. On the other hand, our contracts do not require people to issue different bills for different projects in each period. That could make things difficult to track.

Concluding, for each project we need to always keep up to date the following information:

  • Time worked, broken down by work package
  • Total EUR spent on staff
  • Total EUR spent on travel
  • Total EUR spent on other goods and services.

I propose to use the comment line minimally. Ideally, if API queries can tell a bill apart from an expense claim, it would work like this:

  • On bills: just put the project, the entry date and the indication h2020_costtype. This can be equal to personnel, travel, or other-goods-services. That’s it.
  • On expenses: put the project and the entry date. It’s all travel anyway.

#24

Hmm ok, I’ll change it. Just note that after this change we will report to the European Commission the “agreed on costs”, not necessarily “actual costs”. Because we determine costs based on timesheets then, not based on invoices (of which we will no longer know to which work package they belong). The difference is: if somebody tracked worktime for a reporting period but did not invoice it in that reporting period (yet), we still already report it.

If that’s permissible (probably yes, as it’s like trade debits / credits in our accounting) then it’s even better for us as it allows collaborators to get deferred payouts (like I use to do it at times).

The purpose of tagging invoices by work package number was to report only the actual costs.

When making that change, we also no longer need to collect entry_date for invoices, as we will instead collect the information from timesheets. Oh and h2020_costtype=personnel could be assumed as a default if the invoice is by a collaborator. Which means there would be no need for any special process for registering bills in FreeAgent, then.

Ok, I’ll change that too.

Not the contracts, but our invoicing process requires it. It’s needed for project wise accounting in FreeAgent anyway.

Maybe not, but we can assume travel by default if no costtype is given explicitly.

The project is already entered in a dropdown field in FreeAgent’s form. So for bills we collect nothing and for expenses only the entry date. And even that could be avoided with some clever logic: look at the expense receipt date instead and determine if it’s in the reporting period, but also include any existing expense claims not included in the last report (based on some FreeAgent ID number).

Anyway, we’re quite close to the final version now :slight_smile:


#25

Yes. So we must specify to people with contracts spanning two projects (mostly directors so far, plus Amelia and Marina) that we need one bills per project at every period.


#26

Continuing the discussion about how to created signed timesheets:

I just saw that LibreOffice Online (the current proposal for us in the NGI Forward project here) includes a feature to sign documents.

These signatures use Vereign, a blockchain based service. So it will come with an immutable, auditable log.

Not saying that we should use that, but it’s an interesting option …


#27

Indeed!


#28

I remember at the NGI meeting they said we had to use paper for timesheets. is this no longer the case?

And do we have any finality on entering our time yet? I just read through this topic and while there are lots of ideas, nothing seems final yet.

Noemi and I still have to sign our contracts, and since POPREBEL and NGi are combined for us, there are things about NGI that still have to get figured out such as the number of forums and languages which will effect what money gets allocated to them, And we have to decide about the community journalism program(s).

But we have already done the POPREBEL training and it would be helpful for me to get paid for that portion, when we have a final reporting scheme for it.


#29

Right now, just use the process as described in the wiki at the top of the topic.

It’s not the final version, but the first version ready to use. I’ll look for the right time tracking tool in the next two weeks and then will move over the data from FreeAgent there, and you’ll need to sign the data there again. Since all of that will be well before the first reporting period is over, no problem at all.

It’s just our usual way of working: the tools may change (improve, hopefully) but the data will be kept / migrated.


#30

Right. Thanks. Except for the financial reporting utility part - that’s not there.


#31

Ah but that part you don’t need. That’s only for the person who aggregates all the timesheets and fills the report to European Commission. Once a year.


#32

Cool. Thanks.


#33

Quick outline of the latest development … means, my decision on which tools to use how. cc @alberto

  1. I will install the Kimai v2 open-source time tracking software for teams on our server. (A demo is available.)

  2. People will enter their worktimes there, starting in a few days.

  3. Approval process v1: At the end of each month, collaborators get an e-mail reminding them to finish entering their worktimes. A few days later, an Edgeryders admin person exports the timesheets to PDF (with custom elements I will add to Kimai v2 to conform to EC requirements) and then use digisigner.com to let each timesheet be signed by the collaborator and their team leader. For that we’ll get a single DigiSigner Pro account (15 USD/month, paid monthly). It includes an “auditable log” for signatures, and no way to change documents once in the signing process. The disadvantage is that, as long as this process is active, we have to take over the numbers from the signed timesheets into the reporting templates manually.

  4. Approval process v2: I will contract a PHP developer in Nepal (whom I know) to create a few extensions of Kimai v2, in the form of plugins called “Symfony bundles”. That may take 2-4 months. After that, the following process becomes possible:

    • Users can change their timesheet entries at any time (like now), but for approved entries will be warned that the entry has to be approved again.

    • However when timesheet entries are marked as “cleared” (usually used for billed / exported), they could no longer be edited. We’d use that to mark entries that have been reported to EC.

    • Team leaders can approve timesheet entries, but only those of their team members.

    • We’d extend Kimai v2 to have an auditable log (here’s how), according to the EC requirements: all actions by all users that affect timesheet entries and their approval would be written to a log file. And (optionally) we could have a mechanism to prove that nobody modified that log, using an eternal logfile and publishing hashes of that logfile to some kind of public blockchain.

    • The small Python based utility proposed above will talk to Kimai v2 by API, fetch approved timesheet entries, aggregate them, and fill the reporting templates accordingly.

    • For a proper and flexible local time tracking software, we might optionally also adapt tom-ui, the cross-platform time tracker my brother is building for own usage. Kimai v2 has an API, so interfacing to that would be quite simple.

There are no good alternatives:

  • EMDESK is a whole software package specialized on H2020 projects, and does not include time tracking.

  • There are several SaaS time tracking applications that can provide audit logs – I found Clockshark, Kissflow, Avaza and Clicktime. But they are all too expensive for the functions they provide ­– more expensive than all of our server hosting, in fact. Instead of paying that rent for nothing, we better invest the same money to create an open source alternative.

  • There is Kamai v1, but it is said to be 10 years old, hard to maintain PHP software, and does not include any audit log.

  • There is Anuko, the second team-capable open source timetracker that I know. And there is a current initiative for a timesheet approval feature. However, Anuko is also at least 10 years old PHP software, does not really offer plugins, and while it does its job has a rather old feel to it. We are better off extending Kamai v2, even though right now it’s a pre-release software with some bugs left. But our projects will run for three years …


#34

@matthias, can I ask what the thinking is? It seems to me the time tracking function is nicely fulfilled by FreeAgent:

  • No need for an extra tool, we are already paying for it.
  • Time tracking functionalities available to users with low levels of access.
  • Good usability (at least, this is my impression after testing).

What is missing from FreeAgent is an auditable signing mechanism, which would be provided by Digisigner. So, it seems to me we are facing two possibilities:

Solution 1: FreeAgent + Digisigner

  • No extra tools for employees (bills + expenses claims + time tracking all on FreeAgent).
  • Medium-time permanent: no further process change.
  • No extra software development/maintenance costs and hassles

####Solution 2: Kimai augmented with auditable log

  • No ongoing fees
  • Tailored to our needs, more flexible
  • Open source software contribution

I do not understand this:

Surely, the Python tool can query FreeAgent APIs and generate the CSV or JSON file that then we use to do reporting, right? Additionally, collaborators can already export from FreeAgent timesheets in PDF form, that are than authenticated on Digisigner. If this is true, both solutions support automatic generation of time reporting.

If my understanding is correct, I would probably recommend Solution 1. My main reason is that is faster, requires one fewer painful transition from one process to the next, and absorbs less of your bandwidth. I am curious to why you lean towards Solution 2…


#35

Querying the FreeAgent API works with data that has no audit log, so it may differ from the timesheet the user signed. While working with the DigiSign’ed PDFs does not provide a way to properly take over data automatically for reporting – PDF scraping is just a hack. Somebody would have to do it manually.

An option would be to use the FreeAgent API and then manually make sure the values conform to the approved values in the PDFs. But that’s again manual work, not proper IT.

Also there’s more manual work in this system as users can’t submit their own timesheets to DigiSigner – for a reasonable price we’ll only have one login there, so an admin person will have to create the timesheet exports and submit them to users and their team leaders for signing. That’s good as a first solution (and I include the equivalent in the proposal above) but not good long-term.

I you want to have a look at the available timetracking solutions with audit log that I found (… and found too expensive):

  • Clockshark indeed has an audit log but funny pricing.

  • Kissflow, Avaza and Clicktime have audit logs (I think) but all have funny pricing. And I can’t get rid of the impression that they all might be just the same whitelabeled product.

  • Clockify pricing looks good: free per user, extra features per installation. But the “audit log” feature is marked as “coming soon” (see) and will only appear in their 450 USD/month self-hosted version.

I’m not keen on more work and yet another tool, esp. since this was never budgeted for. But we also don’t want all that manual admin work that would just be pseudo-digitized (signing and moving files and tying their values into spreadsheets like people did with paper timesheets before). So what? I don’t know.


#36

Maybe, what we could do is this: we start with the semi-automatic “FreeAgent + Digisigner” process and use it until June. If it turns out to be too much manual hassle for our taste, we start adapting Kimai and switch over when it’s really ready and the next reporting period starts (so 2020-01-01 for POPREBEL).

It has the added advantage that Kimai will mature in the meantime (it’s pre-release software right now and has some issues …).

If we go for that solution, who’ll do the manual admin work involved: exporting timesheets from FreeAgent, getting users to sign them with DigiSigner, taking over the values into the reporting spreadsheet?


#37

I see what you mean, Matt. On the other hand, this is still rather new for us; I do not think it is a bad thing that people (team leaders + me) keep a human eye on the proceedings. I mean, at the end of the day we need to check manually all that stuff. The reporting is a management tool: I spent quite some time looking at it. How much time did we allocate to WP1? How much to WP2? Do we still have enough time budgeted for WP3 next year? Are we underspending? And so on.

I don’t trust full automation just yet (as in: people input their time, then we sign off to it and pay them, and the reporting spreadsheet is automatically generated and submitted). I imagine a system in which team leaders look at timesheets and invoices before approving them; then, generate the PDFs and sign them. At this point, we ran the API query, generate the quarterly spreadsheet and take a good look at it. If there is anything funny, we notice and go back to the person who under- over-reported her time, and ask for an explanation.

The goal, in my head, is not what you call “full IT”. It is going paperless: not have to keep around sheets of paper for five years after the projects end. We could achieve that just with an electronic signature tool. On top of that, the automatic filling of the spreadsheet is a bonus, but I don’t care about it being authoritative. Notice also that, if it were, we would no longer need digital signing.

Sorry if that was unclear…


#38

The process is finished now, see the updated documentation at the very top. Changes include:

  1. Rework of the time record process. Found a nice process that is both off-the-shelf and fully automateable. It uses a tool similar to DigiSigner, but better and cheaper. And no time tracker is needed at all (by Edgeryders) as we only need “work hours per work package” numbers per month from collaborators, not per day or intra-day.

  2. Updates to what information is required for the invoice comment field, as per your earlier annotations.

Could you please organize who takes on the Edgeryders admin’s role in “2.2. Signing time records” above. It’s to collect people’s signatures on time records, which we are required to do once per month at the minimum.

 

P.S.: For your interest in interoperable digital signatures of European national ID cards: have a look at the cards supported by OpenSC. It includes many national ID cards as they are all smartcards, and in fact both the Estonian, Belgian, German etc. governments use OpenSC to implement their “national” flavor of the software.

To sign documents, OpenSC needs to be complemented with a software with a graphical user interface. These days, that would be j4sign (still in active development). Before, there was Open Signature for that, but sadly it is unmaintained since 2008. Such a tool would enable to use all cards in the same manner and with the same software to sign and verify .pdf files, not requiring the DigiDoc .bdoc stuff etc… Funny enough, Estonia made the DigiDoc software the legal requirement for legal signatures :laughing: but that might be not important in practice when not dealing with the Estonian state and agreeing on a different type of signature in a contract beforehand.


#39

@matthias I edited the wiki (added a Section 0) to include an explanation of the rationale behind the audits and ask people to use the comment field extensively.

Before we roll this out: section 3.2 needs simplification, right? We delete most of those requirements?

Also: who is supposed to do the SignRequest stuff?


#40

There is no formal requirement for that, but for expenses that surely makes sense (where anything is not really obvious about an expense).

But you also want that done for tracking time? Means, are you sure it’s valuable enough in an audit to go all the way for collecting this? We no longer require people to use FreeAgent as a time tracker (it has no auditable log anyway), and for example I don’t plan to use it. Most time trackers allow notes / comments but it’s a PITA and we’d have to collect these documents to be useful. And these documents might be in all kinds of different formats.

No, why? The reason we were able to avoid the same requirements for invoices is only because time records are used to report person months and costs in the reporting template that goes to the EU.

The only one that could be avoided for expense records is entry_date – if you can assure me that expenses must be in the reporting period according to the expense’s receipt date anyway. But even then, in case somebody enters an expense too late for reporting we want a way to select these for an auditor and explain that that we didn’t include them in the reporting / reimbursement claim and why.

Whoever you want, but not me. I think I invested enough unpaid time for this process by now. (And anyway, we should rethink that system of “admin work has to be done for free”. It’s so much these days that it can easily slash people’s hourly earnings in half.)