chen_cook

簡單說 就是第一筆出貨用第一筆進貨去扣除(由上而下),再計算餘數的平均價!!
那如果第二筆出貨大於第一筆庫存時....你要如何算??

white5168

那如果第二筆出貨大於第一筆庫存時....你要如何算??

我前面有詳細舉例,請仔細閱讀,
小弟剛接觸Excel VBA實在很不熟析,請各位大大幫忙

原始資料
                    商品     價格   買進   賣出
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(這是我需要的)

c_c_lai

回復 12# white5168
A 項產品產品的處理過程業已了解，
日期、商品、價格、買進、賣出、產品均價 (是否增列？以便隨時能提供參考資訊)，
再請教 20120402      B    1500               0    100  這項產品，
你會怎麼來處理，且無 B 產品之進貨紀錄？

white5168

每項產品的處理方式皆相同,只差各項不同產品穿插其中,B項產品可如下表所示

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

還有ㄧ點,能否算出每項產品賣出後的盈虧?

Hsieh

回復 14# 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(這是我需要的)

依你以上敘述，20120404之前A產品共有3筆資料(20120401，A，2000，500，0)、(20120403，A，2020，400，0)、(20120404，A，2050，400，200)
既然先進先出20120404這筆賣出，應該是用20120401這個價位2000
那麼剩下的不是應該(2000*(500-200)+2020*400+2050*400)/(500+400+400-200)才是成本價位嗎?
這種專業的會計知識我一點都沒有，不知道我的理解與實務差別在哪?
建議您將想要顯示的結果直接用手算出後，填入想要實現的位置，並在隔壁欄位填入你計算的依據
這樣或許比較容易釐清所謂先進先出的概念。

white5168

Hsieh說的很對,感謝Hsieh糾正,是我搞錯了觀念也讓大家一起誤會了,在此跟大家說聲抱歉,耽誤大家這麼多的時間
我重新整理一下我需要內容的正確觀念

原始資料
                    商品     價格   買進   賣出
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-200=300,表示20120401當天交易數量還有剩300
                       商品     價格          買進   賣出
20120401      A     2000         300         0
20120402      B     1500               0    100
20120403      A     2020          400         0
20120404      A     2050          400         0
20120405      A     2010               0    200
而計算到20120404交易後,A產品全部的均價為(2000*300 + 2020*400 + 2050*400)/(300+400+400)=2025.5,數量剩為300+400+400=1100
20120404結算時,盈虧為(2050-2000)*200=10000

20120405結算時,20120401的A產品平均買進成本仍為2000,數量剩為300-200=100,表示20120401當天交易數量還有剩100
                      商品     價格          買進   賣出
20120401      A    2000         100         0
20120402      B     1500              0    100
20120403      A    2020          400         0
20120404      A    2050          400         0
20120405      A    2010               0         0
而計算到20120405交易後,A產品全部的均價為(2000*100+ 2020*400 + 2050*400)/(100+400+400)=2031.1,數量剩為100+400+400=900
20120405結算時,盈虧為(2010-2000)*200=2000

第二筆出貨大於第一筆庫存的說明如下
如果再多ㄧ個20120406的交易如下
                      商品     價格          買進   賣出
20120401      A    2000          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的A產品平均買進成本仍為2000,當20120406交易後,由於20120401只剩下100不夠20120406賣出的數量200,需以20120403的數量來補足,所以20120403剩餘400-200=200,而20120403的A產品平均買進成本為2020
                      商品     價格          買進   賣出
20120401      A    2000               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(這是我需要的)
20120406結算時,盈虧為(2040-2000)*100+(2040-2020)*200=8000

使用資料在ㄧ開始PO文時,即付上檔名為test.rar,請各位仔細察看

register313

回復 16# white5168

本帖已經到了16樓了,好像答案沒有一點進展,發問者該思考這個問題

發問者的工作:提供範例(考量各種數據)作詳細清楚之說明,讓答題者一下就知道你要什麼
剩下的交給答題者

hsieh的建議:建議您將想要顯示的結果直接用手算出後，填入想要實現的位置，並在隔壁欄位填入你計算的依據
我把產品編號一樣的集中,請把你要的答案填入並作說明,這樣較清楚(或是你答案要如何呈現也另作說明)
答題者有疑惑,發問者必須要幫忙釐清,不然不會有答案,久了也會失去耐心

下載 (126.97 KB)

2012-4-30 11:00

white5168

如圖分別為資料與最後資料
最後資料的部份對於日期是可調整的,如日期變動,則最後資料所呈現的畫面也會改變
相關的Excel檔在附件中,如有不清楚的請再提出發問
小弟比較不清楚各位所需的資訊到何種程度?所以如有冒犯請多多見諒

Hsieh

回復 18# white5168
貼圖的資料並不是附件中CSV的資料
依照上述先進先出邏輯試著寫看看，你自己去比對看看結果正不正確

下載 (575.66 KB)

2012-5-1 17:07

1. Sub Get_Data()
   
2. Dim Ar(), Ay(), x, y
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. Set d1 = CreateObject("Scripting.Dictionary")
   
5. Set d2 = CreateObject("Scripting.Dictionary")
   
6. fs = ThisWorkbook.Path & "\DataBase.csv"
   
7. Open fs For Input As #1
   
8. Do Until EOF(1)
   
9.    Line Input #1, mystr
   
10.    a = Split(mystr, ",")
    
11.    If Val(a(0)) > 0 And Val(a(0)) <= [B1] Then
    
12.    If IsEmpty(d(a(1))) Then
    
13.        For i = 1 To Val(a(3))
    
14.           ReDim Preserve Ar(i)
    
15.           Ar(i - 1) = Val(a(2))
    
16.        Next
    
17.        If Val(a(3)) > 0 Then d(a(1)) = Ar
    
18.        Else
    
19.        Ar = d(a(1))
    
20.        s = UBound(Ar)
    
21.          For i = 1 To Val(a(3))
    
22.            ReDim Preserve Ar(s + i)
    
23.            Ar(s + i - 1) = Val(a(2))
    
24.          Next
    
25.        s = UBound(Ar)
    
26.          d(a(1)) = Ar
    
27.     End If
    
28.     If Val(a(4)) > 0 Then
    
29.        If IsEmpty(d1(a(1))) Then
    
30.        For i = 1 To Val(a(4))
    
31.           ReDim Preserve Ar(i)
    
32.           Ar(i - 1) = Val(a(2))
    
33.        Next
    
34.        If Val(a(4)) > 0 Then d1(a(1)) = Ar
    
35.        Else
    
36.        Ar = d1(a(1))
    
37.        s = UBound(Ar)
    
38.          For i = 1 To Val(a(4))
    
39.            ReDim Preserve Ar(s + i)
    
40.            Ar(s + i - 1) = Val(a(2))
    
41.          Next
    
42.          d1(a(1)) = Ar
    
43.     End If
    
44.     End If
    
45.     End If
    
46.    Erase Ay: Erase Ar
    
47. Loop
    
48. Close #1
    
49. For Each ky In d1.keys
    
50.    If IsArray(d1(ky)) Then Ar = d1(ky): x = UBound(Ar) Else x = 0 '出貨
    
51.    If IsArray(d(ky)) Then Ay = d(ky): y = UBound(Ay) Else y = 0 '進貨
    
52.    If x = 0 And y > 0 Then '只進不出
    
53.       For i = 0 To y - 1
    
54.         'sp = sp + Ar(i)
    
55.         bp = bp + Ay(i)
    
56.       Next
    
57.       bp = bp / y
    
58.       d2(ky) = Array(ky, y, 0, 0, Abs(y - x), y - x, Round(bp, 2), 0)
    
59.       bp = 0
    
60.       ElseIf y = 0 And x > 0 Then '只出不進
    
61.       For i = 0 To x - 1
    
62.         sp = sp + Ar(i)
    
63.       Next
    
64.       sp = sp / x
    
65.       d2(ky) = Array(ky, y, x, 0, 0, y - x, 0, Round(sp, 2))
    
66.       sp = 0
    
67.       ElseIf x > 0 And y > 0 Then
    
68.          If x > y Then '出大於進
    
69.          w = 0: w1 = y - x
    
70.          For i = 0 To y - 1
    
71.          pr = pr + Ar(i) - Ay(i)
    
72.          Next
    
73.          For j = i To x - 1
    
74.          nr = nr + Ar(i)
    
75.          Next
    
76.          nr = nr / (x - y) '不足量
    
77.          ElseIf x < y Then '進大於出
    
78.          w1 = 0: w = y - x
    
79.          For i = 0 To x - 1
    
80.          pr = pr + Ar(i) - Ay(i)
    
81.          Next
    
82.          For j = i To y - 1
    
83.          sr = sr + Ay(i)
    
84.          Next
    
85.          sr = sr / Abs(x - y) '不足量
    
86.          End If
    
87.          
    
88.          d2(ky) = Array(ky, y, x, pr, w, w1, Round(sr, 2), Round(nr, 2))
    
89.          pr = 0: nr = 0: sr = 0
    
90.    End If
    
91.    Erase Ay: Erase Ar
    
92. Next
    
93. [A4:H65536] = ""
    
94. [A4].Resize(d2.Count, 8) = Application.Transpose(Application.Transpose(d2.items))
    
95. End Sub

複製代碼

white5168

很感謝Hsieh大大幫忙,不過目前我還無法下載所有大大所回覆的附件,只能等到升級後才能看到結果
想請問Hsieh大大, Excel VBA的程式,是否能做到跟C,C++等高階語言一樣模組化的程度?
這裡我要澄清說明,附件的原始資料的確是貼圖的資料,我只是將原始資料中比較簡單的不複雜的資料項剔除
貼圖的資料是我將原始資料留下複雜的部份來做說明用的
在撰寫程式的過程中會遇到問題的資料,基本上程式撰寫只要複雜的能寫的出來,其他的都不成問題
可以請Hsieh大大自行刪除比較不複雜且簡單的資料即可看出跟貼圖資料相同的內容
謝謝