1

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.

Chad Portman
  • 123

2 Answers2

2

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.

saulspatz
  • 53,131
  • yes the software has an audit trail but something is wrong and I have been asked to find out what the balances should be. So for this particular thing each account can only earn 12 times a year and for a max of 12 on each earn meaning the max you balance could ever be is 144. However there are accounts that are over 350 in balance. So I have have a hard time trusting anything but the transnational level data. I was just hoping to avoid needing the full history. Thank you for looking. – Chad Portman Aug 08 '19 at 18:29
  • Thanks for the help. Can use tat to get an estimate of the problem while we work on getting the correct balances. – Chad Portman Aug 08 '19 at 19:36
0

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: enter image description here

NoChance
  • 6,427