Logic to Solve different companies mapping to SKU as input

aishabhy05
aishabhy05
in Modeling

Hello All,

 

 I'm trying to solve the below logic but unable to; need your help on the same;

The below module applies to the list SKU. and maps to the company which is applicable based on the boolean.

aishabhy05_0-1624765800699.png

I have input data sales price by SKU and by month and by all the company

aishabhy05_2-1624766461011.png

Now, I would like to get the sales price by SKU by month and by company

 

Example:

aishabhy05_3-1624766658344.png

SKU Code 123 belongs to the company DFA and FGA. Hence the Sales Price 33681 should be divided between these 2 companies for Apr 20.

SKU Code 789 belongs to only company DFA and sales price 34183 should be allocated only to Dec 20

SKU Code 356 belong to 3 company and sales price 32870 should be allocated to 3 companies (32870/3) for Dec 20. 

 

Output module applies to SKU,Month, Company list

aishabhy05_4-1624767036420.png

 

aishabhy05_5-1624767219382.png

Any thoughts on how to solve this? 

 

Best Regards,

Aish

Tagged:

Best Answer

  • manu.mathur
    manu.mathur
    Answer ✓

    Hi Aish, 

     

    please see below steps to solve this, Hope it helps:

     

    manumathur_0-1624821699761.png

     

    manumathur_1-1624821699773.png

     

    manumathur_2-1624821699786.png

     

    manumathur_3-1624821699791.png

     

    manumathur_4-1624821699799.png

     

    manumathur_5-1624821699805.png

     

    manumathur_6-1624821699815.png

     

    manumathur_7-1624821699822.png

     

    manumathur_8-1624821699831.png

     

Answers

  • LeonardiT
    LeonardiT

    Hi Aish,

     

    Without any further understanding of the requirement, I assume that the time period input will be some sort of exception per C7 code. Please find the quick mockup that I did. Hopefully it helps with what you're trying to do.

     

    I made a modification to the input per SKU to only show Unit Sales Price and Time Period as you have already made the checkbox selection on SKU Data.

     

    Step 1: Define the check box by c7 code and company then translate that it into a count.

    Screenshot 2021-06-27 at 10.03.31 PM.png

    Screenshot 2021-06-27 at 10.07.03 PM.png

     

    Step 2: Create another module with c7 code as list and define the price exception (assuming this is the case for all the checkboxes per c7 code).

    Screenshot 2021-06-27 at 10.03.21 PM.png

    Step 3: Create another module with c7 code, company and time as lists then refer to the sales price exception. For those not defined, I assume that they will just refer to the price defined per c7 code. If this is not the case, you can easily refer to a different module or just stick to the prorate formula:

     

    SKU Input.Unit Sales Price[SUM: SKU Input.Month] / SKU Data.Count[SELECT: Company.All]

     

    Screenshot 2021-06-27 at 10.02.42 PM.pngScreenshot 2021-06-27 at 10.03.05 PM.png

     

    Let me know if it works.

    Good luck!

     

    Leo

  • aishabhy05
    aishabhy05
    Awesome!! This Works. Thanks

Categories