Enforce validation errors for improperly rounded aggregate/cumulative values

[trumanc]

Some inputs to the Treasury portal have failed because values entered in input spreadsheets for aggregate/cumulative amounts don't exactly match the real aggregate amounts calculated by treasury.

(These include fields like Quarterly_Obligation_Amt_Aggregates__c and Quarterly_Expenditure_Amt_Aggregates__c in the "aggregate wards < 50k" tab)

This happens because some of the individual awards that are being aggregated might have fractional cents (usually because of a formula, like subtracting .03% from an award for a fee).

Those individual award amounts display as if they only had 2 decimal points, but still contain the fractional cents behind the scenes. If a user were to use excel formulas to SUM those values into the aggregate field, then the summation will incorrectly include those fractional cents, and the final tally may be off slightly.

We can detect/protect against this issue by checking that any aggregate amounts actually add up to a correctly calculated aggregate, and reporting validation errors if they do not.

[chrisxkeith]

@trumanc I'm thinking of dipping my toe into the ARPA waters. :) Some questions:

• I'm assuming that this would be done by rounding values to whole dollars. Would this work, or is there some other complexity that I need to know about?
• Is there any way to get a complete list of the field names that need to be checked?
• What kind of validation/testing is needed? Is any automated testing possible?

Thanks!

[trumanc]

Excited to welcome more people to the ARPA side! And I'm glad you asked: The currency amounts can include cents that we need to respect, so rounding off to the whole dollar would cause issues. Rather, we need to be very careful to round to the hundreds place, as we do here: https://github.com/usdigitalresponse/usdr-gost/blob/_staging/packages/server/src/arpa_reporter/lib/format.js#L34

I think this error only applies to fields where we logically need to add up multiple other values, so fields like Current_Period_Expenditures__c and Current_Period_Obligations__c which can be the some of several awards/expenditures for the current quarter, and Total_Expenditures__c and Total_Obligations__c which are sums of all the awards/expenditures of this quarter and past quarters.

That said, we haven't looked particularly closely at an example of this bug (it has been reported to us by partners, but no-one has given us a file with the described bug) so it is kind of just speculation at this point. I think our first step should be to get an example workbook from a partner to confirm our understanding of the bug. Maybe we can ask in tomorrow's partner meeting?

[chrisxkeith]

Maybe we can ask in tomorrow's partner meeting?

Thursdays I volunteer at a food bank, so I can't make meetings on that day. Could you upload files and add more comments here as you learn more?

[trumanc]

Sure! I'll see what I can find for you

[trumanc]

We asked the partners to provide an example, and a few of them said they would look into their old submissions and send one over (no one experienced this bug this quarter, so they didn't have anything readily available). I'll let you know as soon as I get my hands on a test case!

[as1729]

This can be tracked down by looking at an old Rhode Island file. @trumanc will be going through this process.

[trumanc]

We still haven't received an example from the partners, but I was able to construct a test case by looking at one of their old (valid) files and seeing how they were using excel functions to calculate certain values throughout the workbook. Here is a rundown of how the bug works, plus you can download my testcase workbook at https://usdigitalresponse.slack.com/archives/C031R1Y49KL/p1677619256521629?thread_ts=1677619207.755499&cid=C031R1Y49KL.

The main cause of the issue is that partners are using formulas to calculate certain monetary amounts throughout the workbook, and those formulas sometimes introduce fractional cents. We can get into a situation where multiple fields have all rounded up in a way that makes it appear that an additional cent has appeared out of nowhere. This causes bugs when it is ultimately uploaded to treasury, because they have a validation checking a field (the "current period expenditures" for a single project" is not smaller than the sum of all the expenditures for that project for this period. But if the two expenditures both rounded up but the total didn't, then that validation will fail. Here's a simplified explanation showing how the bug appears in the test file:

This bug can be solved by being more diligent with how the user implements their formulas, to protect against rounding errors. Here's a way that improved formulas would allow the workbook to avoid this phantom-penny bug:

[trumanc]

There are a couple things we can do in the arpa reporter to protect against this:

We could add a validation that no currency field should be allowed to have fractional cents. This could throw an error or just display a warning if we notice rounding issues appearing. This validation might end up creating more work for users, because the vast majority of the time the rounding cents amounts work out correctly (usually one value rounds down and one rounds up, and it works out fine). It's only in this special case with exactly half a cent that both values round up and cause the bug.

We could add a validation that sums the amounts for the current period and checks that they are in fact less than or equal to the reported total amount This validation is already being tracked in https://docs.google.com/spreadsheets/d/1DSZgCCGQck2HXAjw6NfaOT7HjvYL523hKG6m9xEyRMQ/edit#gid=0 While this will allow users to detect when this error is occuring, the cause of the error might still be unclear. We should be careful to write that error message in a way that directs them to a more detailed explanation of this rounding error.

[TylerHendrickson]

@trumanc Can you recommend one of these approaches over another?

[trumanc]

I was hoping to hear back from some of the partner's with feedback before making a decision, so maybe we should ask again in this Thursday's meeting?

I'll summarize my thoughts though: Number 1: Pros:

• would be very fast and easy
• would nudge users towards using more correct formulas in their workbooks Cons:
• Might cause some false-positive errors (sometimes the rounding would have correctly cancelled each other out, and they actually didn't have to fix it to send to treasury).
• It would still be possible to have the phantom-penny error if users didn't "correct" their formulas the right way. (Notice in the example that we couldn't just add rounding to both values, because they both would have rounded up)

Number 2: Pros:

• Addresses the actual error that users are seeing in Treasury, not a weird hidden underlying cause
• We already plan to add validations for verifying how various monetary fields relate to each other. This is just a part of those existing plans. Cons:
• A bit more complex than the rounding fix.

Overall I think the best option is probably to do both, but if we are time/resource limited then I would wait to see which one the partners prioritize

[TylerHendrickson]

@jcomeau1 Can you assist in prioritization and determining the going-forward direction/outcomes for this ticket?

[jcomeau1]

This is very low priority. In fact, it really should just be an FAQ to help partners create formulas that don't cause this issue (which itself is rare). @TylerHendrickson not sure what you want to do with this.

[mhuang-usdr]

I'll stick it in the icebox until I have the FAQ system set up.