Auto update price

[KratZ88]

Hi,

I am looking for a solution to auto update price for an item to newer price when the old price stock is finished.

Example:
I bought apples twice,
5 apples for $1 each
6 apples for $2 each

And when I sold the first batch out, I want the base price to auto update to new price reflecting the second batch price. Is there any way to do it?

Thanks.

 

[maabadi]

Try this:

Test_Input_Output.xlsm
	A	B	C	D	E	F	G
1
2	No. of Sold	Price			No. Of Apple	Buying or Solding Price
3	1	1.2			5	1.2
4	2	2.4			6	2.3
5	1	1.2
6	2	3.5
7	1	2.3
8
Sheet1

Cell Formulas
Range	Formula
B3	B3	=IF(SUM($A$3:A3)<=$E$3,A3*$F$3,IF(SUM($A2:A$3)<$E$3,(SUM($A$3:A3)-$E$3)*$F$4+($E$3-SUM($A2:A$3))*$F$3,A3*$F$4))
B4:B7	B4	=IF(SUM($A$3:A4)<=$E$3,A4*$F$3,IF(SUM($A$3:A3)<$E$3,(SUM($A$3:A4)-$E$3)*$F$4+($E$3-SUM($A$3:A3))*$F$3,A4*$F$4))

 

[maabadi]

With more than 2 price Use column C formula:

Test_Input_Output.xlsm
	A	B	C	D	E	F	G	H
1
2	No. of Sold	Price	Index Price		Sum of Number	No. Of Apple	Buying or Solding Price
3	1	1.2	1.2		5	5	1.2
4	2	2.4	2.4		11	6	2.3
5	1	1.2	1.2		18	7	2.5
6	2	3.5	3.5		26	8	3.2
7	1	2.3	2.3		35	9	3.5
8	3		6.9
9	3		7.3
10	2		5
Sheet1

Cell Formulas
Range	Formula
B3	B3	=IF(SUM($A$3:A3)<=INDEX($E$3:$E$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),0)+1),A3*INDEX($E$3:$G$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),0)+1,3),IF(SUM($A2:A$3)<INDEX($E$3:$E$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),0)+1),(SUM($A$3:A3)-INDEX($E$3:$E$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),0)+1))*INDEX($E$3:$G$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),0)+2,3)+(INDEX($E$3:$E$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),0)+1)-SUM($A2:A$3))*INDEX($E$3:$G$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),0)+1,3),A3*INDEX($E$3:$G$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),0)+2,3)))
B4:B7	B4	=IF(SUM($A$3:A4)<=$F$3,A4*$G$3,IF(SUM($A$3:A3)<$F$3,(SUM($A$3:A4)-$F$3)*$G$4+($F$3-SUM($A$3:A3))*$G$3,A4*$G$4))
E3:E7	E3	=SUM($F$3:F3)
C3	C3	=IF(SUM($A$3:A3)<=INDEX($E$3:$E$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),1)),A3*INDEX($E$3:$G$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),1),3),IF(SUM($A2:A$3)<INDEX($E$3:$E$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),1)),(SUM($A$3:A3)-INDEX($E$3:$E$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),1)))*INDEX($E$3:$G$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),1)+1,3)+(INDEX($E$3:$E$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),1))-SUM($A2:A$3))*INDEX($E$3:$G$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),1),3),A3*INDEX($E$3:$G$7,IFERROR(MATCH(SUM($A$3:A3),$E$3:$E$7,1),1)+1,3)))
C4:C10	C4	=IF(SUM($A$3:A4)<=INDEX($E$3:$E$7,IFERROR(MATCH(SUM($A$3:A4),$E$3:$E$7,1),1)),A4*INDEX($E$3:$G$7,IFERROR(MATCH(SUM($A$3:A4),$E$3:$E$7,1),1),3),IF(SUM($A$3:A3)<INDEX($E$3:$E$7,IFERROR(MATCH(SUM($A$3:A4),$E$3:$E$7,1),1)),(SUM($A$3:A4)-INDEX($E$3:$E$7,IFERROR(MATCH(SUM($A$3:A4),$E$3:$E$7,1),1)))*INDEX($E$3:$G$7,IFERROR(MATCH(SUM($A$3:A4),$E$3:$E$7,1),1)+1,3)+(INDEX($E$3:$E$7,IFERROR(MATCH(SUM($A$3:A4),$E$3:$E$7,1),1))-SUM($A$3:A3))*INDEX($E$3:$G$7,IFERROR(MATCH(SUM($A$3:A4),$E$3:$E$7,1),1),3),A4*INDEX($E$3:$G$7,IFERROR(MATCH(SUM($A$3:A4),$E$3:$E$7,1),1)+1,3)))

 

[KratZ88]

Hi maabadi,

Thank you very much for your solution. Maybe I was not being clear enough in my problem, I am sorry.
I am currently handling quite many inventory ins and outs, so I don't think hard codding the formula like this is efficient in my case.
But you do give me a hint on how to tackle this problem.
Once I have found my solution, I will be sure to post my solution here.

Until then, I will still be checking this thread. So any solutions, any hints are very much welcome.
Thanks a lot.

 

[KratZ88]

Hi maabadi,

I have found the logic to my question. I will be incorporating this logic to my excel.

FIFO-Inventory-Data-tables.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	IN					OUT	8
2
3	Date	Units	Price/unit	Total		Unit OUT	Base Price	Units left	Value		MIN(B4,sales-SUM($F$3:F3))
4	31-Dec	6	10	60		6	60	0	-		MIN(6,8-SUM(0))
5	01-Jan	5	20	100		2	40	3	60		MIN(6,8)
6	02-Feb	2	13.5	27		0	-	2	27		6
7	03-Mar	3	15	45		0	-	3	45
8	04-Apr	2	12.5	25		0	-	2	25		MIN(B5,sales-SUM($F$3:F4))
9	05-May	1	11	11		0	-	1	11		MIN(5,8-SUM(6))
10	06-Jun	3	20	60		0	-	3	60		MIN(5,2)
11	Total	22									2
Sheet1

Cell Formulas
Range	Formula
F4:F10	F4	=MIN(B4,sales-SUM($F$3:F3))
G4:G10	G4	=F4*C4
H4:H10	H4	=B4-F4
I4:I10	I4	=H4*C4
D4:D10	D4	=B4*C4
B11	B11	=SUM(B4:B10)

Named Ranges
Name	Refers To	Cells
sales	=Sheet1!$G$1	F4:F10

Cells	Formula
F4	=MIN(B4,sales-SUM($F$3:F3))
F5	=MIN(B5,sales-SUM($F$3:F4))
F6	=MIN(B6,sales-SUM($F$3:F5))
F7	=MIN(B7,sales-SUM($F$3:F6))
F8	=MIN(B8,sales-SUM($F$3:F7))
F9	=MIN(B9,sales-SUM($F$3:F8))
F10	=MIN(B10,sales-SUM($F$3:F9))

Thank you very much and Happy New Year!

 

[maabadi]

Glad you can find your answer and way to slove it. Happy new year & good Luck.

 

[Ssd]

Hi maabadi,

I have found the logic to my question. I will be incorporating this logic to my excel.

FIFO-Inventory-Data-tables.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	IN					OUT	8
2
3	Date	Units	Price/unit	Total		Unit OUT	Base Price	Units left	Value		MIN(B4,sales-SUM($F$3:F3))
4	31-Dec	6	10	60		6	60	0	-		MIN(6,8-SUM(0))
5	01-Jan	5	20	100		2	40	3	60		MIN(6,8)
6	02-Feb	2	13.5	27		0	-	2	27		6
7	03-Mar	3	15	45		0	-	3	45
8	04-Apr	2	12.5	25		0	-	2	25		MIN(B5,sales-SUM($F$3:F4))
9	05-May	1	11	11		0	-	1	11		MIN(5,8-SUM(6))
10	06-Jun	3	20	60		0	-	3	60		MIN(5,2)
11	Total	22									2
Sheet1

Cell Formulas
Range	Formula
F4:F10	F4	=MIN(B4,sales-SUM($F$3:F3))
G4:G10	G4	=F4*C4
H4:H10	H4	=B4-F4
I4:I10	I4	=H4*C4
D4:D10	D4	=B4*C4
B11	B11	=SUM(B4:B10)

Named Ranges
Name	Refers To	Cells
sales	=Sheet1!$G$1	F4:F10

Cells	Formula
F4	=MIN(B4,sales-SUM($F$3:F3))
F5	=MIN(B5,sales-SUM($F$3:F4))
F6	=MIN(B6,sales-SUM($F$3:F5))
F7	=MIN(B7,sales-SUM($F$3:F6))
F8	=MIN(B8,sales-SUM($F$3:F7))
F9	=MIN(B9,sales-SUM($F$3:F8))
F10	=MIN(B10,sales-SUM($F$3:F9))

Thank you very much and Happy New Year!

Hi,

I am super happy you found the solution with the formula =MIN(B4,sales-SUM($F$3:F3)), but would you be kind enough to explain how does it return the value in the cell F4.

Thank you in Advance.
Regards,
SSD