Calculating the Weighted Average Cost of products

Question

I have to calculate my products stock cost, so for every product after each buy, i have to recalculate the Weighted Average Cost.

I got a view thats bring me the current product's stock after each in/out:

document_type   document_date   product_id  qty_out qty_in  price       row_num stock_balance
SI              01/01/2014      52          0       600     1037.28     1           600
SI              01/01/2014      53          0       300     1357.38     2           300
LC              03/02/2014      53          100     0       1354.16     3           200
LC              03/02/2014      53          150     0       1355.25     4           50
LC              03/02/2014      52          100     0       1035.26     5           500
LC              03/02/2014      52          200     0       1035.04     6           300
LF              03/02/2014      53          0       1040    1356.44     7           1090
LF              03/02/2014      52          0       1560    1045        8           1860
LC              04/02/2014      52          120     0       1039.08     9           1740
LC              04/02/2014      53          100     0       1358.95     10          990
LF              04/02/2014      52          0       600     1038.71     11          2340
LF              04/02/2014      53          0       1040    1363.3      12          2030
LC              05/02/2014      52          100     0       1037.78     13          2240
LF              15/03/2014      53          0       20      1365.87     14          2050
LF              15/03/2014      52          0       50      1054.19     15          2290

I want to add a calculated WAC field as above:

document_type   document_date   product_id  qty_out qty_in  price           row_num     stock_balance   WAC 
SI              01/01/2014      52          0       600     1 037,28        1           600             1037,28000000000
SI              01/01/2014      53          0       300     1 357,38        2           300             1357,38000000000
LC              03/02/2014      53          100     0       1 354,16        3           200             1357,38000000000
LC              03/02/2014      53          150     0       1 355,25        4           50              1357,38000000000
LC              03/02/2014      52          100     0       1 035,26        5           500             1037,28000000000
LC              03/02/2014      52          200     0       1 035,04        6           300             1037,28000000000
LF              03/02/2014      53          0       1040    1 356,44        7           1090            1356,48311926606 --((1357,38*50)+(1040*1356,44))/(1090)
LF              03/02/2014      52          0       1560    1 045,00        8           1860            1043,75483870968 --((1037,28*300)+(1560*1045))/(1860)
LC              04/02/2014      52          120     0       1 039,08        9           1740            1043,75483870968
LC              04/02/2014      53          100     0       1 358,95        10          990             1356,48311926606
LF              04/02/2014      52          0       600     1 038,71        11          2340            1042,46129032258 --((1043,75483870968*1740)+(600*1038,71))/(2340)
LF              04/02/2014      53          0       1040    1 363,30        12          2030            1359,97000000000 --((1356,48311926606*990)+(1040*1363,3))/(2030)
LC              05/02/2014      52          100     0       1 037,78        13          2240            1042,46129032258
LF              15/03/2014      53          0       20      1 365,87        14          2050            1360,03301857239 --((1359,97551136621*2030)+(20*1365,87))/2050
LF              15/03/2014      52          0       50      1 054,19        15          2290            1042.71737568672 --((1042.46129032258*2240)+(50*1054.19))/2290

There is only one and just one document type 'SI' (initial stock) for each product, and the price associated with it is the initial WAC.

Here is a SQL Fiddle sample.

If someone can help with this, i can't figure it out.

Edit: I've juste updated the calculated numbers by increasing precision by displaying 9 numbers after the decimal point.

Answer 1

Here what i did using function:

CREATE TYPE stock_table_with_wac AS
   (document_type character varying,
    document_date date,
    product_id bigint,
    qty_out double precision,
    qty_in double precision,
    price double precision,
    row_num bigint,
    stock_balance double precision,
    wac double precision);


CREATE OR REPLACE FUNCTION calculate_wac_value()
  RETURNS SETOF stock_table_with_wac AS
$BODY$
    DECLARE
    r_article stock_table_with_wac%rowtype;--maintain the liste of all products with there wac's value
    r_in_out_article stock_table_with_wac%rowtype;--Each other records
    BEGIN
    --For each products
    FOR r_article IN SELECT *, price FROM stock_table where document_type='SI' order by row_num
        LOOP
        return next r_article; 
        FOR r_in_out_article IN SELECT * FROM stock_table where document_type<>'SI' and product_id=r_article.product_id order by row_num
        LOOP
        --If there is an entry calculate the wac
        if r_in_out_article.qty_in >0 then 
            r_in_out_article.wac:=((r_article.price * (r_in_out_article.stock_balance - r_in_out_article.qty_in)) + (r_in_out_article.qty_in * r_in_out_article.price))/(r_in_out_article.stock_balance);       
            --Update the wac value of the product
            r_article.price:= r_in_out_article.wac;
        else --The waca value still inchanged:      
            r_in_out_article.wac:= r_article.price;
        end if;     
            RETURN NEXT r_in_out_article; -- return current row with caluculated wac if any
        END LOOP;
        return next r_article;
        END LOOP;
        RETURN;
    END
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION calculate_wac_value()
  OWNER TO postgres;

select * from calculate_wac_value();

It seems to have a correct output. Is it a good idea to process like this?

Answer 2

I have used the data provided in the following article which was meant for FIFO logic.

https://www.red-gate.com/simple-talk/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/

Here, the returns are considered, but I take them at average price.

Here is the table structure:

ID ArticleID TranDate TranCode Items Price WACPrice WACRunningTotal 1 11782 2009-01-01 00:00:41.000 IN 809 256.82 256.82 NULL 2 16967 2009-01-01 00:00:50.000 IN 372 134.44 134.44 NULL 3 13078 2009-01-01 00:01:21.000 IN 532 201.69 201.69 NULL 4 10918 2009-01-01 00:01:34.000 IN 717 348.79 348.79 NULL 5 18871 2009-01-01 00:01:34.000 IN 1045 88.25 88.25 NULL 6 22379 2009-01-01 00:03:01.000 IN 401 326.59 326.59 NULL 7 24049 2009-01-01 00:03:24.000 IN 222 54.54 54.54 NULL 8 12570 2009-01-01 00:03:33.000 IN 731 29.25 29.25 NULL 9 10327 2009-01-01 00:03:33.000 IN 407 222.69 222.69 NULL 10 21548 2009-01-01 00:03:49.000 IN 400 254.05 254.05 NULL 11 15155 2009-01-01 00:03:51.000 IN 719 320.02 320.02 NULL 12 22706 2009-01-01 00:04:00.000 IN 331 25.91 25.91 NULL 13 19126 2009-01-01 00:04:16.000 IN 289 305.47 305.47 NULL 14 21722 2009-01-01 00:04:39.000 IN 434 3.80 3.80 NULL 15 20811 2009-01-01 00:05:57.000 IN 1043 316.57 316.57 NULL 16 21998 2009-01-01 00:06:01.000 IN 1009 15.18 15.18 NULL 17 12928 2009-01-01 00:06:45.000 IN 1122 265.71 265.71 NULL 18 14150 2009-01-01 00:07:36.000 IN 730 148.91 148.91 NULL 19 22307 2009-01-01 00:08:09.000 IN 986 184.38 184.38 NULL 20 17472 2009-01-01 00:08:34.000 IN 1182 62.73 62.73 NULL

please follow the ddl statement in the above mentioned post.

I have use Cte, but for 1000001 records, it maxes out the maxrecursion count. So I created a procedure to execute by one item id which can be iterated by another procedure.

I have added two columns to the stock table, WACPrice and WACRunningTotal.

please find my code below:

alter proc sp_GetInventoryDetails_ByWAC

-- sp_GetInventoryDetails_ByWAC 10017 @ArticleId int as begin

-- select * from #stocktemp order by trandate --select *, 0 as WACRunningTotal into #StockTemp from stock where articleid=10000 order by TranDate; ;WITH y AS ( SELECT articleid,TranDate,trancode, items, rn = ROW_NUMBER() OVER (ORDER BY TranDate) FROM stock where ArticleID =@ArticleId ), x AS ( SELECT articleid, TranDate,trancode, rn, items, rt = items FROM y WHERE rn = 1 UNION ALL SELECT y.articleid,y.TranDate,y.trancode, y.rn, y.items,case when y.TranCode='OUT' then x.rt - y.items else x.rt+y.Items end FROM x INNER JOIN y ON y.rn = x.rn + 1 )

update st set st.WACRunningTotal=x.rt from stock st inner join x on x.ArticleID=st.ArticleID and x.TranDate=st.TranDate and x.TranCode=st.TranCode and isnull(st.WACRunningTotal,0)=0

OPTION (MAXRECURSION 0);

;WITH StockCTE AS (SELECT articleid, items, WACRunningTotal, WACPrice, trandate, ROW_NUMBER() OVER (PARTITION BY articleid ORDER BY trandate) RowNum FROM stock where ArticleID =@ArticleId),

/* CleanStock - A recursive CTE. This runs down the list of values for each stock, checking the Price column, if it is null it gets the previous non NULL value./ CleanStock AS (SELECT articleid, items, WACRunningTotal, ISNULL(WACPrice ,0) WACPrice ,/ Ensure we start with no NULL values for each stock / trandate, RowNum FROM StockCTE cur WHERE RowNum = 1 UNION ALL SELECT Curr.articleid, curr.items, Curr.WACRunningTotal, case when Curr.WACPrice=0 then prev.WACPrice else ((curr.WACPricecurr.Items)+(prev.WACRunningTotal*prev.WACPrice))/curr.WACRunningTotal end as WACPrice, Curr.trandate, Curr.RowNum FROM StockCTE curr INNER JOIN CleanStock prev ON curr.articleid = prev.articleid AND curr.RowNum = prev.RowNum + 1)

/* Update the base table using the result set from the recursive CTE */ UPDATE trg SET trg.WACPrice = src.WACPrice FROM stock trg INNER JOIN CleanStock src ON trg.articleid = src.articleid AND trg.trandate = src.trandate and trg.ArticleID=@ArticleId

/* Display the results */ SELECT * FROM stock where ArticleID=@ArticleId order by TranDate

--drop table stock

end

This will yield result as

StockID ArticleID TranDate TranCode Items Price WACPrice WACRunningTotal 20119 10017 2009-01-06 10:37:40.000 IN 1088 27.91 27.91 1088 69802 10017 2009-01-20 00:40:44.000 OUT 39 0.00 27.91 1049 71338 10017 2009-01-20 10:42:25.000 OUT 967 0.00 27.91 82 91638 10017 2009-01-25 21:54:14.000 OUT 75 0.00 27.91 7 130881 10017 2009-02-05 14:38:34.000 IN 1061 312.18 310.3168 1068 174059 10017 2009-02-17 09:00:34.000 OUT 779 0.00 310.3168 289 188516 10017 2009-02-21 06:46:01.000 OUT 264 0.00 310.3168 25 192423 10017 2009-02-22 08:53:40.000 RET 8 0.00 310.3168 33 228070 10017 2009-03-04 02:24:26.000 OUT 30 0.00 310.3168 3 235671 10017 2009-03-06 03:59:49.000 IN 750 9.78 10.9773 753 247309 10017 2009-03-09 08:36:20.000 OUT 44 0.00 10.9773 709 249207 10017 2009-03-09 21:38:26.000 IN 256 155.09 49.2082 965 253665 10017 2009-03-11 02:42:14.000 OUT 379 0.00 49.2082 586 254121 10017 2009-03-11 05:59:00.000 OUT 527 0.00 49.2082 59 263772 10017 2009-03-13 20:46:09.000 OUT 11 0.00 49.2082 48 271272 10017 2009-03-15 22:09:04.000 OUT 34 0.00 49.2082 14 273709 10017 2009-03-16 13:51:34.000 OUT 1 0.00 49.2082 13 274065 10017 2009-03-16 16:13:02.000 OUT 5 0.00 49.2082 8 275679 10017 2009-03-17 02:38:20.000 IN 1165 217.19 216.0443 1173 280661 10017 2009-03-18 10:59:20.000 OUT 1053 0.00 216.0443 120

I am now working to find the COGS.

Answer 3

I have spent several hours on this moving average! Mainly because of the not reliable window function first/last/nth_value, according to Postgresql documentation on window functions: This is likely to give unhelpful results for nth_value and particularly last_value.

The answer is incomplete. Things to do:

• make WAC stay the same value when qty_in = 0 (now it is zeroed out)
• WAC is not calculated based on previous WAC but on "previous" price, that's why there are increasing differences between WAC calculated by me and presented by You

SQLFiddle

Answer 4

You need to use recursive CTE:

SQLFiddle

with recursive
stock_temp as (
  select 
    *, 
    row_number() over(partition by product_id order by row_num) as rn
  from 
    stock_table 
)

,cte as (
  select 
    document_type, document_date, 
    product_id, qty_out, qty_in, price, 
    row_num, stock_balance, rn, 
    price as wac
  from 
    stock_temp where document_type = 'SI'

  union all

  select 
    sub.document_type, sub.document_date,
    sub.product_id, sub.qty_out,  sub.qty_in, sub.price,
    sub.row_num, sub.stock_balance,  sub.rn,
    case when sub.qty_in = 0 then main.wac else 
    ((sub.stock_balance - sub.qty_in) * main.wac + sub.qty_in * sub.price) 
      / ((sub.stock_balance - sub.qty_in)  + sub.qty_in) end as wac
  from 
    cte as main
    join stock_temp as sub 
      on (main.product_id = sub.product_id and main.rn + 1 = sub.rn)
)

select * from cte

Answer 5

There already is a aggregate function in C for PostgreSQL available, and it will probably calculate much faster than any solution in SQL:

https://github.com/Kozea/weighted_mean