Running Balances and Expirations

Question

I am trying to do some data validation on some account balances and was not sure if there was an easier way to do the math. So the rules for how the account balance works is you can earn into the account but it must be used within 365 days of earning or it will expire.

Different ways the account can have the balance modified is Earning(+ to account), Redeem(- to account), Expire(- to account) and Adjustment(+/- to account).

This data goes back for over 10 years. The first idea which by no means works was to look at the newest 12 months and just do Earning-Redeem-Expire+/-Adjustments to get the balance. But the problem is that you don't have the beginning balance like that.

The second idea was to look at 24 months thinking that would show the beginning balance for the current 12 month active time frame. However I think this does not work for the same reason as you do not have the beginning balance of the 24 month time frame and that changes how much carries over to the beginning balance of the current 12 months.

With that being said I think this means we would have to look at the full set of data for each of the accounts. I was hoping that I mistaken and that there might be some nice math magic that can help find the answer without needing to look at lifetime transnational data.

If there is a better forum/tags for me to ask this question please feel free to reference it for me.

Edit: yes the oldest earning are redeemed first.

Answer 1

Assuming that the oldest earnings are redeemed first, which seems like the only fair way to do it, it seems like you need a separate accounting for each earning. When someone redeems $500$ you go back to the oldest earnings, and redeem from there. If the oldest earnings are insufficient, you redeem what you can and go to the oldest remaining earnings, and so on.

I don't think there is any way to do this without the full account history.

Doesn't the software leave an audit trail?

EDIT

Based on your comments, here's a quick and dirty way to get an estimate. For each account the balance should be at most this years earnings minus the excess, if any, of this year's redemptions over $144$. That is, we assume that all of this year's redemptions came from last year's earnings, except when that's impossible, and then we assume that $144$ came from last year's earnings. Since that is the most each account can be, you have a rather quick way of getting the minimum overstatement.

Still, if you need the correct balances, I don't see how you could do it without looking at the full history.

Answer 2

I think that you need to load the data into a computer (database/excel, ms-access, etc.). All the following operations are simple and quick even if you have millions of transactions.

You will need to process the transaction on at least 4 different steps as shown in the attached image.

My assumption of the original data set is of course different from real-life, but each step reflects a process step towards the final solutions. I assume that adjustment record should always have an associated earnings 'parent row' but I did not show that.

Here it is, hope it helps: