How to create a workflow for the below case (inventory adjustment)

[hishashvat]

The thing is I am finding solution for the below case. Here I wish to find the that orderQ is spread across to how many months.

E.g.

> for the month of February orderQ 55 compares  it with Fvalue  and I should get the figure in new column as 20 (55-35) 

> Again it compares it with next row since the result is positive (20-15) and I should get the figure in new column as 5

> again it compares it with next row since the result is positive (5-2) as 3 and so on.

--> Comparison should not go beyond December

--> In case of comparison coming out to be negative,  this logic should not proceed

Problem _

Code1	Month	Fvalue	Code2	orderQ
abc123	January	49
	February	35	abc123	55
	March	15
	April	2
	May	16	abc123	15
	June	10	abc123	34
	July	12
	August	10	abc123	10
	September	21
	October	32	abc123	45
	November	44
	December	16		20
xyz456	January	23	xyz456	45
	February	16
	March	10	xyz456	67
	April	12	xyz456	67
	May	10
	June	21	xyz456	56
	July	32
	August	35	xyz456	34
	September	15	xyz456	23
	October	23
	November	12
	December	10	xyz456	23

Expected Result

Code1	Month	Fvalue	Code2	orderQ	NewField	Explanation
abc123	January	49
	February	35	abc123	55	20	(55-35)
	March	15			5	(20-15)
	April	2			3	(5-2)
	May	16	abc123	15	-1	(15-16)
	June	10	abc123	34	24	(34-10)
	July	12			12	(24-12)
	August	10	abc123	10	12	(12+10-10)
	September	21			-9	(12-21)
	October	32	abc123	45	13	(45-32)
	November	44			-31	(13-44)
	December	16		20	4	(20-16)
xyz456	January	23	xyz456	45
	February	16
	March	10	xyz456	67
	April	12	xyz456	67
	May	10
	June	21	xyz456	56
	July	32
	August	35	xyz456	34
	September	15	xyz456	23
	October	23
	November	12
	December	10	xyz456	23

Thanks in advance

[DavidP]

I nearly get the expected result, except for August. I don't understand the logic for August. I built the logic to only look at the 1st 12 rows, but if you wanted to repeat the same logic for xyz4546, this can easily be done with grouping in the multi-row formula.

If you can explain the logic for August, I can adjust the workflow for you.

[grossal]

Hi @DavidP,

he posted the problem here with some additional information, but he hasn't answered the latest question. I stumbled across the same thing as you did.

[hishashvat]

@DavidP  @grossal 

I am extremely sorry for missing out on the part of the information in my problem. I apologize for the same.

Actually I was looking out for the vales of Ordered quantity for the spread of over 3 months only. Therefore I stopped at the month of April.

But if above logic is difficult to incorporate in the workflow and will make it complex then it will be fine if all the entries are adjusted likewise

Corrected table as per the discussion  (May month adjusted )

Code1	Month	Demand	Code2	Ordered Q	Inventory	Explanation
abc123	January	49
	February	35	abc123	55	20	(55-35)
	March	15			5	(20-15)
	April	2			3	(5-2)
	May	16	abc123	15	2	(15-16+3)
	June	10	abc123	34	26	(34+2-10)
	July	12			14	(26-12)
	August	10	abc123	10	14	(14+10-10)
	September	21			-7	(14-21)
	October	32	abc123	45	6	(-7+45-32)
	November	44			-38	(6-44)
	December	16		20	-34	(-38+20-16)

Inventory = Inventory previous month + Ordered quantity - Demand

[DavidP · Accepted solution]

In that case, this formula will do that