Monthly Reporting Balance

Hi, just came across this forum, and hope you can help.

My problem is as follows:
1. Each month I have to send out a Report to a list of partners that states the revenues they generated that month, and their balance.
2. If their balance is equal or above $50, I pay them the full balance.
3. If the balance is below $50, it accumulates until at least $50 is reached.
4. When their balance is paid out, it goes back to "0" and starts building up again until it reaches at least $50 again, and so on.
5. Each month the balance needs to be calculated so that it deducts previous payments if there were any.

Finally, this will be imported to a Google Spreadsheet so best if simple Excel formulas are used and no Macros or other advanced programming (even if the formula in the end is much longer), as Google Spreadsheets is more limited than Excel, and I need to have the file online as it links to other databases and mail merge tools.

I've been doing the calculations by hand, but as things have been growing, I've felt the need to automate the calculations. It's rather easy to get this done with 10/20 partners, but not as much when you are over 200.

It seems as a fairly simple problem, but I'm not being able to set the formula straight.

In the example I'm posting, I've set several blocks of lines to illustrate the behaviour of different partner accounts over the year so that you can see how the issue evolves in different partner accounts.
View attachment example.xlsx

Hope anyone can help.

your cross post indicates you are adamant that the layout, as posted, not change as this is only a portion of something else with links to it.

How far to the right would a person need to go to use additional columns that would not interfere with anything else on the sheet?

You say you don't clear any values, so what happens going from Dec 2014 to Jan 2015?

Hey NoS,
Thanks for your help and great questions. You're right, if at all possible, the layout should be preserved, as far as lines are concerned. As far as columns are concerned, there is a need to keep the next 12 columns to the right free, other than that, it's pretty much open.
Thanks!

Maybe something like this?

Created a workable list starting in column AD.
Will get the monthly revenue from your top list and do the calculations.
Formula in column N will find the balance it needs.
Will need to adjust range in the N formula to suit the actual list you have.
Play with this and let me know how it goes.

Good luck with the project.

Hey NoS,
Thanks again for your help. It was a solution but I eventually had to sacrifice my layout to get it all working. The final solution was found having a column do the balance each months and deducting it if it was equal/above 50. So, ad although simple, the layout was the problem so I had to get the developer to put in some extra time!

Thanks anyway for yuor help and I will be stopping by to return the help to other users if I can!