I am working with a system that calculates what payments should be, then when the actual payment is made we record what that value and save it back into the model. Sometimes there are retroactive changes, which cause a variance between the calculated and the actual amount. We want to be able to post the variance to a future period so that it can be reconciled there. In the past that was entirely done manually, but I'm trying to do it with formulas.
I want to use the POST formula to allow business users to select a period to which the variance would be posted, and add all variances to be incorporated into the corresponding actual payment.
Based on the initial calculations (which flow top to bottom), if a payment of 659 is made in May as intended, it would show a variance of -167, as it does not incorporate the 'Variance Posted' amount. This is incorrect. However, when I try to set the 'Variance' line item to reference 'Calculated Payment including Posted Variance', it returns a Circular Reference Error.
Admittedly, the error does make sense, since depending on what values I allowed in the "Post Variance to Period" line, it would indeed cause a circular reference. But I as the builder know that I included logic limiting the 'Post Variance to Period' line item to be future periods - if a period the same or earlier than the specified month is entered, it does not flow into the Post line item. I even set the default offset amount for the POST formula to 1, so even if it is posting 0 it posts it to a future period. Since it is always adding to a later period, it could theoretically operate similar to using a PREVIOUS function which successfully avoids circular references... the math checks out, if only the system could be convinced to attempt it.
Does anybody know of a formula or method that would do the trick here?
I tried using a SUM / TIMESUM / OFFSET, but it still gave the same error. I considered using the PREVIOUS function to always post it to the following month, but it would make for a messier final report if a change caused a variance multiple months back.
The only feasible workaround I can come up with at the moment is using an action to 'break' the circle - once the business users entered the necessary selections, they would run a 'Confirm Posting' action which would import the values to a new line item. However, that would decrease auditability and add complexity. Hopefully somebody can provide me the magic bullet!
