DYNAMICALLY CALCULATE TARGET INVENTORY EVERY 15 WEEKS FOR WEEKLY DATA

[vibes360]

I have a dataset (attached) of suppliers and components that gives me a current picture of my Production status for a part and supplier by week. I want to dynamically calculate how many parts will be needed to meet the target inventory, this is the QUANTITY TO MEET TARGET Column

The way the target works is a week is equal to 5 days; therefore, if I have a negative PARTS AVAILABLE at the week of my TARGET INVENTORY, then my target is not met. To calculate the QUANTITY TO MEET INVENTORY column, I just need to check how many parts are in negative at the WEEK NUMBER of my TARGET INVENTORY or check the ORDER column for the target value and see how many parts are negative

THE CURRENT COVER Column just shows how many weeks of cover I have based on my PARTS available column and it is calculated by checking the weeks that we have positive PARTS available

THE TARGET INVENTORY column is the target of days I am trying to meet and 1 week = 5 days

COMPLIANCE column is a column that shows my compliance against the target inventory days which is calculated by subtracting the CURRENT COVER  from the TARGET INVENTORY

QUANTITY TO MEET TARGET- How many parts will needed to meet my target and I need to calculate it dynamically

[Raj]

@vibes360 
which column are you looking to calculate?
it will be great if you can add expected solution in the file
will be in better position to help.

[vibes360]

@Raj 
The QUANTITY TO MEET TARGET COLUMN is what I am trying to calculate. I think the file is very clear as I have marked the column I am trying to calculate in yellow and also put the resulting values there. I just need the calculation to be done dynamically and not manually

[Qiu]

@vibes360 
I am also a bit of confused.
Can you illustrate how you calculate the "QUANTITY TO MEET TARGET COLUMN"?

[vibes360]

@Qiu @Raj 
Sure, the QUANTITY to MEET TARGET column can be calculated in two ways

1. Check how many parts are negative in the PARTS AVAILABLE column for the WEEK NUMBER of my TARGET INVENTORY, this means if my target inventory is 50, I will check the amount of PARTS AVAILABLE that are negative in 50 /5 = Week 10, if my target inventory is 60, I will check the amount of PARTS AVAILABLE that are negative in Week number in 60/ 5 = 12

2. I can check the  ORDER column for the target value and see the amount of PARTS AVAILABLE that are negative for that supplier and part. So for example, if my target is 60, I will check the order column for 60 and just select the amount of PARTS AVAILABLE that are negative

[Raj]

@vibes360 
there is no formula used in the file
please share the logic you used to get the values.

[vibes360]

@Raj The point of this post is to find a logic that can calculate the QUANTITY TO MEET TARGET column. No formula was used in the file, I only looked at the data to get that column based on my explanation

[Raj · Accepted solution]

@vibes360 
find the workflow attached
mark done if solved.

[vibes360]

@Raj It works, thanks