Microsoft excel experts - Need a hand

BenJ · 2 Nov 2010 at 19:41

I'm struggling to value my stock.

Here is a basic example

uploaded version here http://www.filefactory.com/file/b41aec4/n/example.xls

I need to value using a weighted average, based upon current stock levels and the relevant purchase price.

Any ideas folks, this as I say is a very basic example and in reality there are thousands of stocked parts for which I do not fancy going through and manually working out weighted averages based on stock levels.

dazzerd · 2 Nov 2010 at 19:54

=sumif(A:A,A1,B:B)/sum(B:B)*E1 (then paste down)or something like that will give you the weighted average price of each product line per line. Then do a somif on the product type which will give you the entire average per product line.

The weighted average across all product lines is surely just the sum of the price divided by the sum of stock. Long time since I did something like this but I think i'm on the right lines.

BenJ · 2 Nov 2010 at 20:05

dazzerd said:
=sumif(A:A,A1,B:B)/sum(B:B)*E1 (then paste down)or something like that will give you the weighted average price of each product line per line. Then do a somif on the product type which will give you the entire average per product line.

The weighted average across all product lines is surely just the sum of the price divided by the sum of stock. Long time since I did something like this but I think i'm on the right lines.

I've uploaded the actual .xls, do you think you could have a quick play as that formula did nothing for me.

I don't think you are taking into account the current stock levels either.

E.g product 2, current levels are 36,000 units, so you have to take the weighted average (col b x col e for lines 16-11, plus the remaining units from line 10, 7960 x col e. Divided by the total in stock. That gives the correct weighted average for the current stock, but it's a dead manual and seriously time consuming method to which I hope there is quicker and easier way?

dazzerd · 2 Nov 2010 at 20:09

give me ten minutes and i;ll see what I can do.

BenJ · 2 Nov 2010 at 20:18

dazzerd said:
give me ten minutes and i;ll see what I can do.

cheers dude

dazzerd · 2 Nov 2010 at 20:21

Hmm think we may be talking about a different thing but according to my calcs the weighted price for product 1 is 1.46 and for product 2 is 5.70.

Stick this in column F of your examples: $B2/SUMIF($A:$A,$A2,$B:$B)*$E2

This basically says take the number of stock from line row A2 divide it by total stock called product 1 (etc etc etc) then times it by the price that amount of stock cost. This then gives you the weighted average unit price of that stock based on the buying price and the number of stock in total. i.e. column F will show the weighted average unit cost.

If you want that weighted average purchase price then just sum up column F for each product.

BenJ · 2 Nov 2010 at 20:37

dazzerd said:
Hmm think we may be talking about a different thing but according to my calcs the weighted price for product 1 is 1.46 and for product 2 is 5.70.

Stick this in column F of your examples: $B2/SUMIF($A:$A,$A2,$B:$B)*$E2

This basically says take the number of stock from line row A2 divide it by total stock called product 1 (etc etc etc) then times it by the price that amount of stock cost. This then gives you the weighted average unit price of that stock based on the buying price and the number of stock in total. i.e. column F will show the weighted average unit cost.

If you want that weighted average purchase price then just sum up column F for each product.

Your weighted averages are not correct bud, I've uploaded the sheet with the correct weighted average purchase price for the current stock.

http://www.filefactory.com/file/b41aga5/n/example.xls

Hmm, exactly what is that forumla doing? I've added it but can't see that it helps me much?

dazzerd · 2 Nov 2010 at 21:23

sec let me see how you got to your results first.

Edit:

OK first thing my formula was looking at the wrong column should have been looking at column c instead (so should have been =$C2/SUMIF($A:$A,$A2,$C:$C)*$E2) . This would then give me 93p for product 1 and £5.14 for product 2 closer to yours but still not the same.

Ive mailed you in turst I;ll email the example to you so you can at least see if it works.

BenJ · 2 Nov 2010 at 22:24

dazzerd said:
sec let me see how you got to your results first.

Edit:

OK first thing my formula was looking at the wrong column should have been looking at column c instead (so should have been =$C2/SUMIF($A:$A,$A2,$C:$C)*$E2) . This would then give me 93p for product 1 and £5.14 for product 2 closer to yours but still not the same.

Ive mailed you in turst I;ll email the example to you so you can at least see if it works.

replied via mail but if anyone else wants to chip in I will update here too.

Problem with your formula is it is taking a complete historical weighted average and is not taking into consideration the current stock levels which are to be valued.

In simple terms. If on Monday I bought 5 chocolate bars at £10 each and on Friday I bought 2 chocolate bars for £1 each. Then on Saturday my current stock is 2 chocolate bars, the value is 2 x £1 = £2. Your method would include the £10 bars even though they should not be included since the current stock is covered by the most recent purchase price.