Bonus Calculation

Hi Excel Experts.
I have got problem about creating bonus calculation sheet because its a lot complicated. Actually I do not know whether it can be accomplished or not. (VBA or formulas).
Uploaded the file to examine. There is required results and explanations for each result.

Thank you all in advance

Originally Posted by zbor

I don't get it: are each month corelated to last or to all previous months?

It may be correlated and may not be. The main logic here is payments. For example.
in second month there is sales and payments at the same time. But from previous month there is 4000 USD unpaid amount. Thus we complete first month's sale at the first step. The remained 3000 USD will be related to current month. On the other hand, 4000 will be multiplied to 3% because its relevant sales bonus is 3%.

Originally Posted by zbor

But month 9 look months 6 and 8

Yes. Its due to payment.
In 6th month 4000 USD remained unpaid.
In 7th month 1000 USD paid by customer.(3000 left for 6th month)
In 8th month extra 3000 USD realised (In total 6000 debt)
In 9th month 4000 USD sales realized and 11000 USD payment made.
As a result we should devide this payment to month. 3000x3% (6th month) + 3000x4%(8th month) + 4000x6%(9th month) = 450 USD
There is 1000 USD extra payment and it will be considered in bonus calculation when the sales will be made in future months.

Any Suggestion or idea plz? That would be great to know if this problem can be solved or not.

Hi kvsrinivasamurthy
Thank you very much for your effort.

I tested you fromulas in different sheet but after 7th month it gives #NUM error.

Uploaded file again. Its in Test tab with answers.

In my opinion formulas should loo to all previous months because without it it cannot know if the previous sales' paymants have been completed or not.

Any idea plz?
Any number of helper column or vba can be used

I am sure this can be more simple!

Originally Posted by Izandol

I am sure this can be more simple!

Hi Izandol. Great thanks. Thank you very much. Your formulas works perfectly. Tested it few times and it gives correct numbers except first month of sales shouldn't be blank. I think I can manage it.

Many many thanx who tried to solve this problem. This site and its experts are wonderful.

Hi Izandol

I tested again but there is problem with calculation again. Sorry for early response.

Uploading same file with new numbers. Can you examine it again?

I think UDF will be simpler. Please try attached workbook.

I tested it works correct.
Can I use offset or other functions inside your UDF (getbonus)? Because In my original file I going to calculate more than 100 companies' bonuses. They are in row order. (below this another and etc.)

Thank you very much. You are genius.

If the company is each one below the other, you must only change the starting row for the ranges to match starting row for each company.

I understood. Thank you again.
Now i can finish my entire file