There is a fixed amount of goods that business1(B1) wants to buy from business2(B2) spread throughout the year. B1 gives B2 a purchasing plan where it indicates the amount of goods it plans to purchase each month.
To make it easier for B1 to plan this amount, B2 offered the following scheme:
For the first 3 months, B2 is liable to buy 100% of the goods that he indicated in his purchasing plan, for the next 3 months (2nd quarter) B2 is liable to buy 70% of goods that he plans to purchase, for the 3rd quarter he is liable to buy 40% of goods he planned and in the last quarter it’s only 30%.
For example:
Percentage of liability 100% 100% 100% 70% 70% 70% 40% 40% 40% 30% 30% 30% Months Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Units planned 10 10 10 10 10 10 10 10 10 10 10 10 Units liable to take 10 10 10 7 7 7 4 4 4 3 3 3
Additionally B1 is allowed to purchase either more or less units per month, under the condition that the quantity undertaken or overtaken, will be either added or subtracted to the quantity which B1 is liable to take, not later than after 6 months.
Example1 (according to the table above):
Customer is liable to take 10 units in January. But he takes 8, instead of 10. In the next 5 months he takes exactly the amount he is liable to take. This means that in July, besides 4 units that he is liable to take he must take another 2 pieces (10-8=2) he did not take in January, totaling to 6 units.
Example2 (according to the table above):
According to his liability plan, customer takes 8 units instead of 10 in January, 12 units instead of 10 in February and 5 units instead of 10 in March. This means that in 6 months, namely in July, he will be liable to take only 4 units (because even though he undertook 2 units in January, he overtook the same 2 in February), in August, he is liable to take also only 4 units but in September, he will have to take 9 units (5 units he did not take in March + 4 units that he is liable to take in this month).
Taking these 2 examples into consideration, we see that there are many scenarios that can take place, depending on how customer takes his goods. I tired solving this in Excel, but the formula becomes very complex and doesn't work in every case, therefore I would like to make a tool in VBA. Such tool has to start counting such liability after the first 6 months and add it or subtract it where necessary. As I am new to VBA, I have no clue how to do it, can somebody help with the code please?
Thanks a lot in advance to the brave helper!