Consulting

Results 1 to 4 of 4

Thread: Sleeper: Calculating liability

  1. #1
    VBAX Newbie
    Joined
    Apr 2015
    Posts
    2
    Location

    Question Sleeper: Calculating liability

    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!

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I'm not at all familiar with the new BI part of Excel, but it seems to me that you need a Units Taken Row and a Six Month Liability Row.

    In the attached, Play with the numbers in the yellow and watch the green.
    Attached Files Attached Files
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Newbie
    Joined
    Apr 2015
    Posts
    2
    Location
    Dear SamT,

    Thanks a lot for your answer. It's a bit trickier with the liability though. It does incur only in 6 months, BUT, if, for example, you have taken 2 pieces less in the 3rd month (March) and then taken 2 pieces more in the 4th month (April), in October (March +6 months) you don't have to take an extra 2 pieces you did not take in March, because you already have taken 2 more in April. It's like having an imaginary stock, where you can put things and take them out again. Do you have an idea how to make that? This is the point where I get lost..

    Thanks a lot for your help again!

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •