Calculating weighted average unit cost using histroical purchasing data

dosinger · Feb 6, 2022

No VBA
No Macros

I need help calculating weighted average unit cost of inventory on hand.

Problem:
I have 25 units on hand.
Recent purchases

Possible methodology:
Sum the quantities purchased, until I reach the current quantity on hand.

Then sum the total cost for each purchase up to total quantity
View attachment 57152

Ave Cost = $243 / $25 = $9.72

I have 10,000 parts and over 1 million lines of purchase history.
How do I calculate the ave cost for all 10,000 parts, systemically, so I can repeat this process every month.

Thank you.

BSALV · Feb 7, 2022

"10.000 parts and 1 million lines of history - no VBA, no macros !!! Oeps."

You can do it like this, the data for buying and purchasing is in the same table to avoid circulair problems.
After every transaction, you get a new average cost per unit in stock.
As you do this on a monthly base, perhaps you can calculate with a monthly average cost.
Table is sorted for ascending date and descending quantity

inventory.xlsx

A

B

C

D

E

F

G

H

I

J

1

datum

product

QTY

Price

value

avg price

inv.QTY

inv.Value

inv. AVG

last row

2

9/jan

BCD

100

10,00 €

3

10/jan

ABC

10

8,00 €

4

10/jan

ABC

-5

5

10/jan

BCD

-1

6

11/jan

BCD

-1

7

12/jan

BCD

-1

8

13/jan

BCD

-1

9

14/jan

BCD

-1

10

15/jan

ABC

5

9,00 €

11

15/jan

ABC

-5

12

15/jan

BCD

-1

13

16/jan

ABC

3

15,00 €

14

16/jan

ABC

-3

15

16/jan

BCD

-1

16

17/jan

BCD

-1

17

18/jan

BCD

-1

18

19/jan

BCD

-1

19

20/jan

BCD

-1

20

21/jan

BCD

-1

21

22/jan

BCD

-1

22

23/jan

BCD

-1

23

24/jan

BCD

-1

24

25/jan

BCD

-1

25

26/jan

BCD

-1

26

27/jan

BCD

-1

27

28/jan

ABC

7

9,00 €

28

28/jan

ABC

-7

29

28/jan

BCD

-1

30

31/jan

ABC

5

10,00 €

31

31/jan

ABC

-5

32

7/feb

ABC

-1

Blad1

Cell Formulas
Range	Formula
E2:E32	E2	=[@QTY]*IF([@QTY]>0,[@Price],[@[avg price]])
F2:F32	F2	=IF([@QTY]<0,OFFSET(Tabel1[[#Headers],[inv. AVG]],[@[last row]],,,),0)
G2:G32	G2	=IF([@[last row]]>0,OFFSET(Tabel1[[#Headers],[inv.QTY]],[@[last row]],,,),0)+[@QTY]
H2:H32	H2	=IF([@[last row]]>0,OFFSET(Tabel1[[#Headers],[inv.Value]],[@[last row]],,,),0)+[@value]
I2:I32	I2	=+[@[inv.Value]]/[@[inv.QTY]]
J2:J32	J2	=MAX(IF($B$1:$B1=[@product],ROW($B$1:$B1)-ROW($B$1),0))

dosinger · Feb 7, 2022

An interesting solution. It assumes that we have total history of each part, such that the beginning quantity of each part is 0, and that you can trace all of the pluses and minuses to calculate the quantity on hand. Unfortunately, we don't have total history on each part.

All we know is that we have a quantity of 25 currently on hand, and recent purchasing history. That is why I my initial example started with the most recent purchase. By doing so, I know for a fact that I have 5 units purchase at $10, 7 purchased at $9, etc. When I get to 25 units, I know the total cost I paid for all 25 units. This way, I don't need complete history of the part.

Here is an excerpt from the actual data for a single part straight from our system, and how I think I would calculate it under two scenarios.

Eager to see what you can come up with.

BSALV · Feb 7, 2022

After your annual inventary, you can actualise for all the articles the qty and the value and start from there.
In multiple tables, it's more complicated.
Excel can suggest a unit price when you purchase a quantity in a separate column, but you have to copy manually (as VBA isn"t allowed) that price, otherwise i have a circular reference error.
All your data is sorted for "trans date" in a descending way ?

- The value of the stock is almost "calculated weighted average", not for 100%
- What happens when invoices for buying articles is booked later then the invoices for purchasing the same articles, that's a fundamental problem.

dosinger · Feb 7, 2022

We are a US government contractor, so certain short cuts are not allowed, like calcing qty and value at the end of the year and bringing that forward.
I followed the login you were using and was able to build some formulas to get me the solution I needed. It's rather clunky, but it works.

Thanks for your efforts. It did help.

BSALV · Feb 7, 2022

okay, good luck.

itsmekamalkkr · May 31, 2023

In the context of a trading portfolio(lets say stocks), upon sell txn(i.e. quantity with negative value), the average price should not change. How to modify the formula in such cases?

Calculating weighted average unit cost using histroical purchasing data

dosinger

New Member

Attachments

Excel Facts

BSALV

Banned user

dosinger

New Member

BSALV

Banned user

dosinger

New Member

BSALV

Banned user

itsmekamalkkr

New Member

Similar threads

Forum statistics

Share this page

Calculating weighted average unit cost using histroical purchasing data

dosinger

New Member

Attachments

Excel Facts

BSALV

Banned user

dosinger

New Member

BSALV

Banned user

dosinger

New Member

BSALV

Banned user

itsmekamalkkr

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock