Weighted Average Calculation for Inventory

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Weighted Average Calculation for Inventory

Author

Topic

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2012-09-04 : 10:59:37

I have to calculate the cost of current inventory using a perpetual weighted average calculation. See this link for a brief explanation of the difference between perpetual and periodic weighted average cost.[url]http://community.dynamics.com/product/nav/navtechnical/b/navscmsupport/archive/2008/03/04/perpetual-or-moving-vs-periodic-or-weighted-average-costing-methods.aspx[/url]

Anyway, it appears that since the perpetual method requires events to be processed in chronological order, a cursor-based method must be used to calculate it correctly. If anyone knows of a faster way to do this calculation, I would certainly appreciate a link or an explanation, as I have roughly 8 million records I'm going to have to go through.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-04 : 11:04:18

could be done with a recursive CTE, just not by me.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

vijayan.vinu3
Starting Member

19 Posts

Posted - 2012-09-05 : 07:19:58

Please post some Sample Data and the Desired output.

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2012-09-05 : 17:45:06

OK, there are three main types of transactions: Receipts, Sales, and Variances.

1) Receipts - inventory coming in from manufacturers or other branches

2) Sales - inventory going out via sales to customers or other branches

3) Variances - this is the tricky one that is giving me fits. We may get 3 or four receipts from a regular vendor during the month, priced at the current price, and then at the end of the period we will get an invoice with a new price for the parts. We then have to go back to the earliest receipt that is affected by the new price, then run the events forward again in order to re-calculate the new weighted average cost.

PartNo QtyRct UnitPriceRct QtySold UnitPriceSold QtyVariance UnitPriceVariance EffectiveDate

MX2200 23 10.00 0 0 0 0 2012-09-01
MX2200 85 10.00 0 0 0 0 2012-09-04
MX2200 0 0 7 10.00 0 0 2012-09-05
MX2200 0 0 0 0 50 11.00 2012-09-03

So what is happening is you receive two shipments, both priced at $10 per unit, then sell 7 units at a cost to you of $10. Later, you get a bill from the manufacturer stating that 50 of the units you received were actually priced at $11, not $10. So now you have to go back and re-calculate the WAC (Weighted Average Cost) in order to get the average unit price on both the receipts and the sales because the price on 50 of the units have changed.

The WAC calculation is as follows:

SUM(Qty*UnitCost)/SUM(Qty)

So at first, your cost would be (108*10)/(108) = $10

But later it recalculates to:
((58*10)+(50*11))/108 = $10.46

So the cost of selling the 7 units jumps from $70 to $73.24.

I have a PeriodBalance table that I use to hold the WAC value of every month. Rather than update that table after every calculation, I simply update the values of variables in my code, and then once I have finished the new month's calculation, I apply the values to last month's WAC record to generate the current WAC and then update the Current WAC record if it exists, otherwise I insert a new current WAC period record.

I have to step through this series of calculations for 308,000 different parts on a daily basis. It would be really great if I could do a set-based calculation

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2012-09-10 : 13:16:50

OK, I've made significant progress over the weekend. However, I am running into an issue. I need to calculate the next unit cost based on calculating the previous unit cost. Here's my scratch code, I know it's not optimized, but right now I'm just trying to work out the logic so I'm solving the problem one step at a time. the four values for WAC that I am getting at the end are 1.50, 1.525, 1.602, and 1.667. The correct values, according to the accountants, are 1.50, 1.525, 1.5873, and 1.6153. I know what the issue is, I'm calculating my values based off of the previous VERIFICATIONS unit cost rather than the calculated value. I need a way to pull the previous row's calculated value into the next row.

CREATE TABLE #WACTran(
[TranOrder] [int] NULL,
[TranType] [varchar](3) NULL,
[Qty] [int] NULL,
[UnitCost] [decimal](14, 4) NULL,
[ExtendedCost] [decimal](14, 4) NULL
)

INSERT INTO [#WACTran](TranOrder, TranType, Qty, UnitCost, ExtendedCost)
VALUES
(1 ,'S',-50 ,0.0000, 0.0000 )
,(2 ,'RCT',15 ,0.0000, 0.0000 )
,(3 ,'S',-5 ,0.0000, 0.0000 )
,(4 ,'VC',15 ,1.6000, 0.0000 )
,(5 ,'S',-5 ,0.0000, 0.0000 )
,(6 ,'TO',-4 ,0.0000, 0.0000 )
,(7 ,'TI',2 ,0.0000, 0.0000 )
,(8 ,'VTI',2 ,1.6500, 0.0000 )
,(9 ,'RCT',10 ,0.0000, 0.0000 )
,(10,'S',-5 ,0.0000, 0.0000 )
,(11,'VC',10 ,1.7500, 0.0000 )
,(12,'S',-5 ,0.0000, 0.0000 )
,(13,'TO',-4 ,0.0000, 0.0000 )
,(14,'TI',2 ,0.0000, 0.0000 );

CREATE TABLE #BeginningBalance(TranOrder INT
, TranType VARCHAR(3)
, Qty INT
, UnitCost DECIMAL(14,4)
, ExtendedCost DECIMAL(14,4)
);

INSERT INTO #BeginningBalance(TranOrder, TranType, Qty, UnitCost, ExtendedCost)
VALUES
(0 ,'BB',100 ,1.5000, 150.0000 )

SELECT
t2.TranOrder
,t2.TranType
,t2.Qty
,SUM(t1.Qty) AS QtyOnHand
INTO #QUANTITIES
FROM ( SELECT [TranOrder],[TranType],[Qty],[UnitCost],[ExtendedCost]
FROM #BeginningBalance
UNION
SELECT [TranOrder],[TranType],[Qty],[UnitCost],[ExtendedCost]
FROM [#WACTran]) AS t1
JOIN ( SELECT [TranOrder],[TranType],[Qty],[UnitCost],[ExtendedCost]
FROM #BeginningBalance
UNION
SELECT [TranOrder],[TranType],[Qty],[UnitCost],[ExtendedCost]
FROM [#WACTran]) AS t2 ON t2.TranOrder >= t1.TranOrder
AND t1.TranType NOT LIKE 'V%'
AND t2.TranType NOT LIKE 'V%'
GROUP BY t2.TranOrder
,t2.TranType
,t2.Qty

SELECT RANK() OVER(ORDER BY TranOrder) AS ranking,TranOrder, TranType, Qty, UnitCost
INTO #VERIFICATIONS
FROM (SELECT [TranOrder],[TranType],[Qty],[UnitCost],[ExtendedCost]
FROM #BeginningBalance
UNION
SELECT [TranOrder],[TranType],[Qty],[UnitCost],[ExtendedCost]
FROM [#WACTran]
WHERE [#WACTran].[TranType] LIKE 'V%') AS t1;

SELECT v1.ranking
,MAX(q1.TranOrder) AS QtyTranOrder
,MAX(v1.TranOrder) AS VMAXTranOrder
,MAX(ISNULL(v2.TranOrder,v1.TranOrder)) AS VMINTranOrder
INTO #TranAssociation
FROM #QUANTITIES AS q1
JOIN #VERIFICATIONS AS v1 ON q1.TranOrder <= v1.TranOrder
LEFT JOIN #VERIFICATIONS AS v2 ON v2.ranking < v1.ranking
GROUP BY v1.ranking;

--SELECT * FROM #QUANTITIES

--SELECT * FROM #TranAssociation

SELECT q1.TranOrder
,q1.Qty
,q1.QtyOnHand
,((v1.Qty*v1.UnitCost)+((q1.QtyOnHand-v1.Qty)*v2.UnitCost))/q1.QtyOnHand AS WAC
FROM #TranAssociation AS ta
JOIN #QUANTITIES AS q1 ON q1.TranOrder = ta.QtyTranOrder
JOIN #VERIFICATIONS AS v1 ON v1.TranOrder = ta.VMAXTranOrder
JOIN #VERIFICATIONS AS v2 ON v2.TranOrder = ta.VMINTranOrder

--SELECT *
--FROM #TranAssociation AS ta
--JOIN #QUANTITIES AS q1 ON q1.TranOrder = ta.QtyTranOrder
--JOIN #VERIFICATIONS AS v1 ON v1.TranOrder = ta.VMAXTranOrder
--JOIN #VERIFICATIONS AS v2 ON v2.TranOrder = ta.VMINTranOrder

/*
DROP TABLE #BeginningBalance;
DROP TABLE #WACTran;
DROP TABLE #QUANTITIES;
DROP TABLE #VERIFICATIONS;
DROP TABLE #TranAssociation;

*/

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2012-09-11 : 14:33:41

Nevermind, I figured it out.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources