Calculate opening stock taking into account the life of each intake of new stock

[vbavirgin]

Hi all . I hope someone can help.

Sales units by week are shown in range D6:L6.
Intake units by week are shown in range D7:L7.
Intake becomes stock in the week after it is realised.
The simple opening stock calculation = previous weeks stock + previous weeks intake – pervious weeks sales.
However, I need to amend this formula to take into account the following:
Once the intake becomes stock it has a sales life of 3 weeks before the remaining stock becomes 0

I need to calculate the opening stock taking into acount the life of the intake. Can anyone help?

 

[Anbuselvam]

In cell C7 (which will be the opening stock for Week 4), enter the formula:

=IF(DATE(YEAR($B$1),1,1)+WEEKNUM($B$1)-1=WEEKNUM(DATE(YEAR($B$1),1,1)+WEEKNUM(DATE(YEAR($B$1),1,1),1)-1),$C$4+$C$5-$C$6,IF(DATE(YEAR($B$1),1,1)+WEEKNUM($B$1)-1=WEEKNUM(DATE(YEAR($B$1),1,1)+WEEKNUM(DATE(YEAR($B$1),1,1),2)-1),$C$3,IF(DATE(YEAR($B$1),1,1)+WEEKNUM($B$1)-1=WEEKNUM(DATE(YEAR($B$1),1,1)+WEEKNUM(DATE(YEAR($B$1),1,1),3)-1),0,$C$4)))

This formula checks which week the current week falls into (Week 4 in this case), and uses that information to determine which intake to use in the calculation.

Copy the formula in cell C7 and paste it into cells D7:L7.
This will give you the opening stock for each week, taking into account the sales life of the intake. The formula will check the week number and use the appropriate intake to calculate the opening stock for each week.

 

[vbavirgin]

Thank you for your reply. I really appreciate your help.

I have put this formula into my sheet and get a #NUM! error.

The intake is showing in range D7:L7 so cannot place the formula there (should it be in C8:L8?)

Also, I dont understand why rows 4 and 5 are being referenced.

I wonder if you could share a sheet with the working example?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often
varies by version. (Don’t forget to scroll down & ‘Save’)

 

[vbavirgin]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often
varies by version. (Don’t forget to scroll down & ‘Save’)

Hi , I have update this . I am using 365