[發問] 請問如何用excel VBA寫一個以先進先出的方式來取得產品的數量與平均價格 FIFO

white5168

個問大大如題
請問如何用excel VBA寫一個以先進先出的方式來取得產品的數量與平均價格
有以下的資料,但小弟實在想不出該如何寫出來,買進賣出須以日期為主
要取得目前產品的剩餘數量與剩餘數量的成本均價,產品明細如附件

購買日期,產品編號,購買價格,進貨數量,出貨數量
20120102,1166,27,0,2000
20120102,116P,27.2,0,2000
20120102,5511,27.2,0,1000
20120102,5515,27.1,0,1000
20120102,585J,27,5000,0
20120102,585J,27.05,1000,0
20120102,592a,27.1,1000,0
20120102,5962,27.2,0,2000
20120102,616K,27.25,0,1000
20120102,7001,27,0,2000
20120102,7001,27.05,0,1000
20120102,7001,27.1,0,1000
20120102,700K,27.3,1000,0
20120102,779z,27.7,0,1000
20120102,8560,27.05,1000,0
20120102,8560,27.1,2000,0
20120102,8560,27.25,1000,0
20120102,8560,27.7,0,3000
20120102,8843,27,0,1000
20120102,9108,27.25,1000,0
20120102,9108,27.7,0,1000
20120102,913R,27.7,5000,0
20120102,9187,27.05,0,1000
20120102,9313,27.3,0,1000
20120102,9363,27,0,1000
20120102,9801,27.2,10000,0
20120102,9817,27.1,0,1000
20120102,983Z,27.2,0,3000
20120102,984C,27.25,0,1000
20120102,984K,27,2000,0
20120102,9887,27,0,1000
20120102,9887,27.2,0,2000
20120103,1035,27.05,2000,0
20120103,1160,27.45,5000,0
20120103,1166,27.05,0,1000
20120103,116L,27.05,0,1000
20120103,116L,27.45,0,1000
20120103,538N,27.45,0,2000
20120103,572D,27.5,0,5000
20120103,5926,27.05,4000,0
20120103,592M,27.5,0,2000
20120103,7003,27.45,2000,0
20120103,7003,27.5,10000,0
20120103,700K,27.45,0,1000
20120103,700W,27.05,1000,0
20120103,700j,27.05,4000,0
20120103,8560,27.05,2000,0
20120103,8560,27.45,0,1000
20120103,8560,27.5,0,2000
20120103,8850,27.45,0,1000
20120103,9183,27.45,1000,0
20120103,9185,27.05,1000,0
20120103,918D,27.05,0,10000
20120103,921Y,27.05,0,1000
20120103,9303,27.05,1000,0
20120103,9303,27.5,0,1000
20120103,9649,27.05,0,1000
20120103,979H,27.05,0,1000
20120103,9817,27.45,0,2000
20120104,1035,26.8,0,3000
20120104,1112,27,0,1000
20120104,1162,27.3,1000,0
20120104,116G,26.85,0,1000
20120104,5182,27,0,1000
20120104,551X,27,0,1000
20120104,585V,26.8,0,6000
20120104,700K,27.15,1000,0
20120104,700j,26.8,6000,0
20120104,700j,27,1000,0
20120104,8560,26.8,0,1000
20120104,8560,27,1000,0
20120104,8560,27.15,0,1000
20120104,8560,27.3,0,1000
20120104,884A,26.8,0,3000
20120104,9108,27,1000,0
20120104,9135,27.15,0,2000
20120104,9183,27.15,0,1000
20120104,9274,26.85,1000,0
20120104,9647,27,1000,0
20120104,9801,26.8,8000,0
20120104,981a,26.8,0,5000
20120104,982A,26.8,1000,0
20120104,9887,27,0,2000
20120104,9891,27,1000,0
20120104,989V,26.8,3000,0
20120104,989V,27.15,2000,0
20120104,989Z,27.15,1000,0

register313

回復 1# white5168

這樣的說明,還是不清楚你要的?

1.先進先出?
2.剩餘數量=進貨數量-出貨數量  為何有的產品進貨數量為0,卻有出貨數量?
3.剩餘數量的成本均價如何計算?

white5168

1.先進先出?
所謂先進先出是指,先進貨的就在下一次的出貨時,優先賣出,所以要搭配日期,如
                      商品   買進   賣出
20120401      A    500         0
20120402      B          0   100
20120403      A    400         0
20120404      A    400    200

20120401買進A產品500,沒有賣出A產品,
20120403買進A產品400,沒有賣出A產品,
20120404沒有買進A產品,賣出A產品200
所以到20120405的A產品剩下數量就要以20120401的減20120404的200再加上20120403的400與20120404的400
因此剩餘數量為(500-200)+400+400

2.剩餘數量=進貨數量-出貨數量  為何有的產品進貨數量為0,卻有出貨數量?
數量為0時,表示當天只有進貨或出貨,而另一邊不作買賣的動作,換句話說,如果買進為0,賣出為5000,就表示當天只有賣出產品

3.剩餘數量的成本均價如何計算?
以累計加減當前買進或賣出數量*買進或賣出價格再除以目前產量剩餘數量,而買賣原則就是以先進先出為原則

white5168

先進先出的說明
http://wiki.mbalib.com/zh-tw/%E5%85%88%E8%BF%9B%E5%85%88%E5%87%BA%E6%B3%95

成本均價
                      商品     價格   買進   賣出
20120401      A    2000   500         0
20120402      B     1500       0    100
20120403      A    2020   400         0
20120404      A    2050   400    200
20120405      A    2010        0    200


一般的計算
所以到20120405時, A的平均買進成本 (2000*500 + 2020*400 + 2050*400 - 2050 *200 - 2010*200)/(500+400+400-200-200)=2017.78

先進先出
所以到20120405時, A的平均買進成本  
20120401 買進20120404賣出後的產品均價為: (2000*500- 2050*200)/(500-200)=1966.67,數量剩為500-200=300
                      商品     價格          買進   賣出
20120401      A    1966.67   300         0
20120402      B     1500              0    100
20120403      A    2020          400         0
20120404      A    2050          400         0
20120405      A    2010               0    200

20120401 剩餘的在20120405再賣出後的均價為 (1966.67*300 - 2010*200)/(300-200)=1880,數量剩為100
                      商品     價格          買進   賣出
20120401      A    1880          100         0
20120402      B     1500              0    100
20120403      A    2020          400         0
20120404      A    2050          400         0
20120405      A    2010               0         0

最後在20120405當天的A產品的均價為 : (1880*100+2020*400 + 2050*400)/(100+400+400) = 2017.78,數量為900
                      商品     價格          買進   賣出
20120401      A    1880          100         0
20120402      B     1500              0    100
20120403      A    2020          400         0
20120404      A    2050          400         0
20120405      A    2010               0         0

chen_cook

回復 3# white5168

所謂先進先出是指,先進貨的就在下一次的出貨時,優先賣出,所以要搭配日期,如
                      商品   買進   賣出
20120401      A    500         0
20120402      B          0   100
20120403      A    400         0
200

20120401買進A產品500,沒有賣出A產品,
20120403買進A產品400,沒有賣出A產品,
20120404沒有買進A產品,賣出A產品200
              20120404      A    400    <----沒有買進何來 400???            
所以到20120405的A產品剩下數量就要以20120401的減20120404的200再加上20120403的400與20120404的400
因此剩餘數量為(500-200)+400+400 <----- 何來這個庫存   
不好意思我看不太懂,我這個二十年倉管看不懂大大你的的先進先出??
版大是指導如何解決問題,而不是理所當然須義務告知解決方法!!!:@

chen_cook

回復 4# white5168


    再我來看這個問題只是
    1.)統計產品的統數.....
    2.)計算進貨價...
    3.)平均庫存單價...
    4.)再進貨數量累計....
    5.)再進資累計平均單價....
        沒有所謂的先進先出!!!

chen_cook

回復 4# white5168


    (2000*500- 2050*200)/(500-200)=1966.67,數量剩為500-200=300
    你用的不是 2050 的 200 pcs  為何會扣除它呢?? 300 X 2000進價不變才是啊??? AVG 還是 2000 或2020 [  若是要算也是 (2000X300+2050X200)/500 ] 那來的1996.67 這個生意老闆不會做的!! 會計的平均單價是正確的算法!!!
(1966.67*300 - 2010*200)/(300-200)=1880,數量剩為100
       2010 的 200 pcs 也是同樣的問題 !!!

register313

回復 4# white5168

是有許多疑點未釐清

總之先給些數據(一般的算法),再一步一步弄清楚吧

1. Sub aa()
   
2. Dim d1, d2 As Object
   
3. Set d1 = CreateObject("scripting.dictionary")
   
4. Set d2 = CreateObject("scripting.dictionary")
   
5. [G1:I65536] = ""
   
6. [G1:I1] = Array("產品編號", "剩餘數量", "平均價格")
   
7. For B = 2 To [B2].End(xlDown).Row
   
8.   If Not d1.exists(Cells(B, 2).Value) Then
   
9.      d1.Add Cells(B, 2).Value, Cells(B, 4) - Cells(B, 5)
   
10.      d2.Add Cells(B, 2).Value, (Cells(B, 4) - Cells(B, 5)) * Cells(B, 3)
    
11.   Else
    
12.      d1(Cells(B, 2).Value) = d1(Cells(B, 2).Value) + Cells(B, 4) - Cells(B, 5)
    
13.      d2(Cells(B, 2).Value) = d2(Cells(B, 2).Value) + (Cells(B, 4) - Cells(B, 5)) * Cells(B, 3)
    
14.   End If
    
15. Next B
    
16. [G2].Resize(d1.Count, 1) = Application.Transpose(d1.keys)
    
17. [H2].Resize(d1.Count, 1) = Application.Transpose(d1.items)
    
18. [I2].Resize(d2.Count, 1) = Application.Transpose(d2.items)
    
19. For I = 2 To d2.Count + 1
    
20.   If Cells(I, "H") = 0 Then
    
21.      Cells(I, "I") = 0
    
22.   Else
    
23.      Cells(I, "I") = Cells(I, "I") / Cells(I, "H")
    
24.   End If
    
25. Next I
    
26. End Sub

複製代碼

white5168

商品     價格   買進   賣出
20120401      A    2000   500         0
20120402      B     1500       0    100
20120403      A    2020   400         0
20120404      A    2050   400    200
20120405      A    2010        0    200

20120401買進A產品500,沒有賣出A產品,
20120403買進A產品400,沒有賣出A產品,
20120404沒有買進A產品,賣出A產品200
              20120404      A    400    <----沒有買進何來 400???            

這裡的每一筆買賣皆為單獨獨立,我並未強調我所列的範例是倉管,請勿以生意的角度來看

white5168

抱歉讓大家有點搞錯,以下我再重新解釋一次我所謂的先進先出,也許跟一般人所認知的倉管方式有落差,請各位多多包函,我應該說這是類似先進先出的作法
我需要後面第一筆賣出的交易來減去第一筆買進的,而且需以第一筆買進的部份所剩餘數量來計算當前日期所剩商品數量的平均成本

原始資料 
                    商品     價格   買進   賣出
20120401      A    2000   500         0
20120402      B     1500       0    100
20120403      A    2020   400         0
20120404      A    2050   400    200
20120405      A    2010        0    200

類先買進先賣出
20120404結算時, 20120401的A產品平均買進成本,需以當時剩餘來計算即(2000*500- 2050*200)/(500-200)=1966.67,數量剩為500-200=300,表示20120401當天交易數量還有剩300(這是我需要的)
                       商品     價格          買進   賣出
20120401      A    1966.67   300         0
20120402      B     1500              0    100
20120403      A    2020          400         0
20120404      A    2050          400         0
20120405      A    2010               0    200
而計算到20120404交易後,A產品全部的均價為(1966.67*300 + 2020*400 + 2050*400)/(300+400+400)=2016.365,數量剩為300+400+400=1100(這是我需要的)

20120405結算時, 20120401的A產品平均買進成本,需以當時剩餘來計算即(1966.67*300-2010*200)/(300-200)=1880,數量剩為300-200=100,表示20120401當天交易數量還有剩100(這是我需要的)
                      商品     價格          買進   賣出
20120401      A    1880          100         0
20120402      B     1500              0    100
20120403      A    2020          400         0
20120404      A    2050          400         0
20120405      A    2010               0         0
而計算到20120405交易後,A產品全部的均價為(1880*100+ 2020*400 + 2050*400)/(100+400+400)=2017.78,數量剩為100+400+400=900(這是我需要的)

如果再多ㄧ個20120406的交易如下
                      商品     價格          買進   賣出
20120401      A    1880          100         0
20120402      B     1500              0    100
20120403      A    2020          400         0
20120404      A    2050          400         0
20120405      A    2010               0         0
20120406      A    2040               0    300

20120406結算時, 20120401與20120403的A產品平均買進成本,需以當時剩餘來計算即(1880*100+2020*400-2040*300)/(100+400-300)=1920,數量剩為100+400-300=200,表示20120401當天交易數量已經沒有剩了,需要20120403的數量來支援才行,所以20120403剩餘200(這是我需要的)
                      商品     價格          買進   賣出
20120401      A    1880               0         0
20120402      B    1500               0    100
20120403      A    2020          200         0
20120404      A    2050          400         0
20120405      A    2010               0         0
20120406      A    2040               0         0
而計算到20120406交易後,A產品全部的均價為(2020*200 + 2050*400)/(200+400)=2040,數量剩為200+400=600(這是我需要的)