Inventory and cost management

[Balmer07]

Hi,

I was hoping someone could help me with an excel problem I have.
I run a business and have a purchase list on one tab with all the products I have purchased, the quantity purchased and cost each time they are purchased. I then have a sales list tab which records the sales price, any fees associated with selling and the quantity sold. The missing part of the piece is to pull through the correct price for a product each time it sells.

For example:
I purchase 10 units of Product A for £10.00 each totalling £100.
I then purchase a further 10 units of Product A for £11.00 each totalling £110.
Total stock value is now £210.00 across 20 units.

I then sell 10 units of Product A and so would like the cost pulled through as £10.00 each
I then sell a further 10 units of Product A and so would then like to pull the cost of £11.00 through as there is none of the £10.00 Product left in stock due to the previous sale.

How do I do this? Currently I just do a vlookup and then manually change when I know the price isn't correct but this is not maintainable and I hope there is a solution within excel.

To add a further level of complexity here can a consideration be made if say 11 units of Product A were purchased, therefore 10 units would have the £10.00 cost while 1 would have the £11.00 cost so totalling £111.00


Hopefully this makes sense and there is reasonable solution? Appreciate the help and let me know if any questions.

 

[felixstraube]

Can you tell us what version of excel you have? The answer varies depending greatly on your version.
And i believe you can update your profile with that information.

 

[Balmer07]

Hi,

I have a subscription to Office 365 current version is 2311.

 

[Fluff]

I have a subscription to Office 365

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’)

 

[felixstraube]

Ok here is something I came up with, with the little time I had. Maybe it could work for you. But i guess a better solution should be done with VBA.

Ok here it is:

I have one Purchase table like so:

Purchases_Sales.xlsx
	A	B	C	D
1	Purchases
2	Date	Product	Quantity	Price
3	01/11/2023	A	10	100,00
4	01/11/2023	B	5	50,00
5	01/11/2023	C	2	20,00
6	01/12/2023	A	11	110,00
7	01/12/2023	B	6	55,00
8	01/12/2023	C	3	30,00
9	15/12/2023	A	4	115,00
Purchase

And a Sales table like so:

Purchases_Sales.xlsx
	A	B	C	D	E	F
1	Sales
2	Date	Product	Product purchase date	Available Quantity [ Purchase date / Quantity / Price ]	Quantity	Price
3	15/12/2023	A	01/11/2023		5	100
4	16/12/2023	A	01/11/2023		4	100
5	17/12/2023	A	01/12/2023		11	110
6
Sale

Cell Formulas
Range	Formula
D3:D6	D3	=IF(([@Product]<>"")*([@Quantity]=""), LET( cp,[@Product], d,[@[Product purchase date]], p,Purchase[[Date]:[Quantity]], pp,Purchase[Price], ap,VSTACK(p,HSTACK([Product purchase date],[Product],-1*[Quantity])), fap,IF(d="",FILTER(ap,(CHOOSECOLS(ap,2)=cp)*(CHOOSECOLS(ap,3)<>0)),FILTER(ap,(CHOOSECOLS(ap,2)=cp)*(CHOOSECOLS(ap,1)=d)*(CHOOSECOLS(ap,3)<>0))), ud,UNIQUE(CHOOSECOLS(fap,1)), r,HSTACK(ud,BYROW(ud,LAMBDA(x,SUM(FILTER(CHOOSECOLS(fap,3),CHOOSECOLS(fap,1)=x)))),BYROW(ud,LAMBDA(x,FILTER(pp,(CHOOSECOLS(p,2)=cp)*(CHOOSECOLS(p,1)=x))))), rf,FILTER(r,CHOOSECOLS(r,2)<>0), TEXTJOIN(" "&CHAR(10),,BYROW(rf,LAMBDA(y,"[ "&TEXT(CHOOSECOLS(y,1),"aaaa-mm-dd")&" / "&CHOOSECOLS(y,2)&"u / $"&CHOOSECOLS(y,3)&" ]"))) ),"")
F3:F6	F3	=IF(([@Product]<>"")*([@[Product purchase date]]<>""), FILTER(Purchase[Price],(Purchase[Product]=[@Product])*(Purchase[Date]=[@[Product purchase date]])),"")

If you add a new row to the sales table and enter the Product then you get the list of available units for this product in "Available quantity". If more than one purchase date/price of this product is available you get all those available like this:

If you cant see all the options just adjust the text:

Now if you enter the purchase date of one of the listed products then it will only show the number available for that purchase date:

I you enter the quantity then the available text will disappear:

If you wanted to sell 5 products for example but you had 1u purchased on 2023-11-01 at 100 and 4u purchased on 2023-12-15, then you need to add to lines to the sales table like so:

And you have to enter first the product, then the sales date and last the quantity.

I know this isn't perfect but maybe you can use it or change it to something better.
Anyway for anything more advanced maybe VBA is your answer.

Here is a working copy of my file:

Purchases_Sales.zip

And for the date to show correctly in English i think you have to change where it says "aaaa-mm-dd" to "yyyy-mm-dd" in the formula.