Tổng hợp các phương pháp tính trị giá hàng tồn kho

Thảo luận trong 'Excel và Kế Toán' bắt đầu bởi handung107, 9 Tháng mười một 2007.

  1. handung107

    handung107 Administrator

    1. Định nghĩa hang tồn kho theo chuẩn mực số 2 :

    Hàng tồn kho bao gồm:
    • Nguyên liệu, vật liệu, công cụ dụng cụ tồn kho, đã mua đang đi trên đường hoặc gửi đi gia công chế biến
    • Hàng hoá mua để bán : Hàng hoá tồn kho, hàng mua đang đi trên đường, hàng gửi đi gia công, chế biến, hàng gửi bán
    • Hàng hoá thành phẩm : Thành phẩm tồn kho, và thành phẩm gửi bán
    • Sản phẩm dở dang : Sản phẩm chưa hoàn thành hoặc sản phẩm hoàn thành nhưng chưa làm thủ tục nhập kho
    • Chi phí dịch vụ dở dang

    Tất cả những thứ này được coi là hàng tồn kho và chiếm một phần lớn trong tỷ lệ tài sản kinh doanh của doanh nghiệp, bởi vì doanh thu từ hàng tồn kho là một trong những nguồn cơ bản tạo ra doanh thu và những khoản thu nhập thêm sau này cho doanh nghiệp. Đó là những tài sản đã sẵn sàng để đem ra bán hoặc sẽ được đem ra bán.
    Nếu để tồn hàng tồn kho quá lâu thì sẽ làm ảnh hưởng không tốt tới quá trình kinh doanh, bởi vì doanh nghiệp sẽ phải tốn chi phí dự trữ, chi phí thanh lý hay cải tiến hàng bị lỗi thời, và thanh lý hàng hư hỏng. Tuy nhiên, việc không dự trữ đủ hàng tồn kho cũng là một rủi ro bởi vì doanh nghiệp có khả năng đánh mất những khoản doanh thu bán hàng tiền năng hoặc thị phần nếu sau này giá lên cao mà doanh nghiệp không còn hàng để bán..

    2. Các phương pháp tính trị giá hang tồn kho :

    Có nhiều phương pháp :
    - Phương pháp nhập trước - xuất trước (FIFO)
    - Phương pháp nhập sau - xuất trước
    - Phương pháp giá hạch toán
    - Phương pháp giá thực tế đích danh : Áp dụng đối với doanh nghiệp có ít mặt hàng hoặc mặt hàng ổn định và nhận diện được
    - Phương pháp giá bình quân : bình quân theo giá hang tồn kho đầu kỳ, cuối kỳ hoặc trong kỳ. Cũng có khi được tính theo từng thời kỳ hoặc vào mỗi lần nhập một lô hang (bình quân gia quyền liên hoàn) phụ thuộc vào tình hình của doanh nghiệp

    3. Ưu và nhược điểm của từng phương pháp :

    - Phương pháp nhập trước - xuất trước (FIFO)

    Phương pháp này áp dụng dựa trên giả định là hàng được mua trước hoặc sản xuất trước thì được xuất trước, và hàng còn lại cuối kỳ là hàng được mua hoặc sản xuất ở thời điểm cuối kỳ. Theo phương pháp này thì giá trị hàng xuất kho được tính theo giá của lô hàng nhập kho ở thời điểm đầu kỳ hoặc gần đầu kỳ, giá trị của hàng tồn kho được tính theo giá của hàng nhập kho ở thời điểm cuối kỳ hoặc gần cuối kỳ còn tồn kho.

    Phương pháp này giúp cho chúng ta có thể tính được ngay trị giá vốn hàng xuất kho từng lần xuất hàng, do vậy đảm bảo cung cấp số liệu kịp thời cho kế toán ghi chép các khâu tiếp theo cũng như cho quản lý. Trị giá vốn của hàng tồn kho sẽ tương đối sát với giá thị trường của mặt hàng đó. Vì vậy chỉ tiêu hàng tồn kho trên báo cáo kế toán có ý nghĩa thực tế hơn.

    Tuy nhiên, phương pháp này có nhược điểm là làm cho doanh thu hiện tại không phù hợp với những khoản chi phí hiện tại. Theo phương pháp này, doanh thu hiện tại được tạo ra bởi giá trị sản phẩm, vật tư, hàng hoá đã có được từ cách đó rất lâu. Đồng thời nếu số lượng chủng loại mặt hàng nhiều, phát sinh nhập xuất liên tục dẫn đến những chi phí cho việc hạch toán cũng như khối lượng công việc sẽ tăng lên rất nhiều.

    - Phương pháp nhập sau - xuất trước (LIFO)

    Phương pháp này giả định là hàng được mua sau hoặc sản xuất sau thì được xuất trước, và hàng tồn kho còn lại cuối kỳ là những hàng được mua hoặc sản xuất trước đó. Theo phương pháp này thì giá trị hàng xuất kho được tính theo giá của lô hàng nhập sau hoặc gần sau cùng, giá trị của hàng tồn kho được tính theo giá của hàng nhập kho đầu kỳ hoặc gần đầu kỳ.

    Như vậy với phương pháp này chi phí của lần mua gần nhất sẽ tương đối sát với trị giá vốn của hàng thay thế. Việc thực hiện phương pháp này sẽ đảm bảo được yêu cầu của nguyên tắc phù hợp trong kế toán. Tuy nhiên, trị giá vốn của hàng tồn kho cuối kỳ có thể không sát với giá thị trường của hàng thay thế

    - Phương pháp giá hạch toán

    Đối với các doanh nghiệp có nhiều loại hàng, giá cả thường xuyên biến động, nghiệp vụ nhập xuất hàng diễn ra thường xuyên thì việc hạch toán theo giá thực tế trở nên phức tạp, tốn nhiều công sức và nhiều khi không thực hiện được. Do đó việc hạch toán hàng ngày nên sử dụng giá hạch toán.

    Giá hạch toán là loại giá ổn định, doanh nghiệp có thể sử dụng trong thời gian dài để hạch toán nhập, xuất, tồn kho hàng trong khi chưa tính được giá thực tế của nó. Doanh nghiệp có thể sử dụng giá kế hoạch hoặc giá mua hàng hoá ở một thời điểm nào đó hay giá hàng bình quân tháng trước để làm giá hạch toán. Sử dụng giá hạch toán để giảm bớt khối lượng cho công tác kế toán nhập xuất hàng hàng ngày nhưng cuối tháng phải tính chuyển giá hạch toán của hàng xuất, tồn kho theo giá thực tế. Việc tính chuyển dựa trên cơ sở hệ số giữa giá thực tế và giá hạch toán.

    Phương pháp hệ số giá cho phép kết hợp chặt chẽ hạch toán chi tiết và hạch toán tổng hợp về hàng trong công tác tính giá, nên công việc tính giá được tiến hành nhanh chóng do chỉ phải theo dõi biến động của hàng với cùng một mức giá và đến cuối kỳ mới điều chỉnh và không bị phụ thuộc vào số lượng danh điểm hàng , số lần nhập, xuất của mỗi loại nhiều hay ít.

    Phương pháp này phù hợp với các doanh nghiệp có nhiều chủng loại hàng và đội ngũ kế toán có trình độ chuyên môn cao.

    Tuy nhiên, trong điều kiện hiện nay, giá thực tế của hàng nhập kho luôn biến động phụ thuộc vào các yếu tố thị trường, các chính sách điều tiết vi mô và vĩ mô, cho nên việc sử dụng giá hạch toán cố định trong suốt kỳ kế toán là không còn phù hợp nữa.

    - Phương pháp giá thực tế đích danh

    Theo phương pháp này sản phẩm, vật tư, hàng hoá xuất kho thuộc lô hàng nhập nào thì lấy đơn giá nhập kho của lô hàng đó để tính. Đây là phương án tốt nhất, nó tuân thủ nguyên tắc phù hợp của kế toán; chi phí thực tế phù hợp với doanh thu thực tế. Giá trị của hàng xuất kho đem bán phù hợp với doanh thu mà nó tạo ra. Hơn nữa, giá trị hàng tồn kho được phản ánh đúng theo giá trị thực tế của nó.

    Tuy nhiên, việc áp dụng phương pháp này đòi hỏi những điều kiện khắt khe, chỉ những doanh nghiệp kinh doanh có ít loại mặt hàng, hàng tồn kho có giá trị lớn, mặt hàng ổn định và loại hàng tồn kho nhận diện được thì mới có thể áp dụng được phương pháp này. Còn đối với những doanh nghiệp có nhiều loại hàng thì không thể áp dụng được phương pháp này.

    - Phương pháp giá bình quân

    Theo phương pháp này giá trị của từng loại hàng tồn kho được tính theo giá trị trung bình của từng loại hàng tồn kho đầu kỳ và giá trị từng loại hàng tồn kho được mua hoặc sản xuất trong kỳ. Phương pháp bình quân có thể được tính theo thời kỳ hoặc vào mỗi khi nhập một lô hàng , phụ thuộc vào tình hình của doanh nghiệp.

    Tóm lại :

    Các phương pháp khác nhau có ảnh hưởng khác nhau tới các chỉ tiêu trên các báo cáo tài chính.

    Ở đây, chúng ta không bàn sâu về những ảnh hưởng này, mà chỉ nói về việc cách sử dụng Excel để tính trị giá hàng tồn trong các phương pháp cụ thể như sau :

    1 Phương pháp giá thực tế đích danh
    2. Phương pháp giá bình quân :
    a. Trị giá hàng tồn được tính theo giá trị hàng tồn kho từng thời kỳ​
    b.Trị giá hàng tồn được tính theo giá bình quân liên hoàn, mỗi lần nhập hàng, sẽ tính lại giá trị xuất hàng​
    3. FIFO và LIFO :
    a. Trị giá hàng tồn được tính theo phương pháp kê khai thường xuyên ​
    b. Trị giá hàng tồn được tính theo phương pháp kiểm kê định kỳ​
     
  2. handung107

    handung107 Administrator

    Để chuẩn bị cho loạt bài này, mời các bạn tham khảo thêm về các đề tài sau :

    - Cách đặt tên cho dãy, vận dụng tên cho công thức
    - Các hàm dò tìm VLOOKUP, INDEX, MATCH
    - Các hàm mảng, công thức mảng, hàm SUMPRODUCT
    - Các hàm tham chiếu : OFFSET
    - Các hàm luận lý : IF, OR, AND...
    - Các hàm bẫy lỗi : ISERROR, ISNA...
    - Kiến thức VBA không dám nêu tại đây, vì phạm vi này rộng quá

    Xin mời tất cả các bạn tham gia thảo luận đề tài này, chúng ta sẽ từ từ tổng hợp vấn đề này một cách có chiều sâu hơn
     
  3. digita

    digita Guest

    Bài này là cho các kế toán gia đây không phải nghề của mình. Tuy nhiên, xin tặng các bạn 1 zip file tôi thu thập trong net trước đây để tiện việc tham khảo & cải thiện nếu cần.

    File có 1 cái template để tính theo cách FIFO có dùng các hàm offset, match v.v.

    Mến
     

    Các file đính kèm:

    • FIFO.zip
      Kích thước:
      18.5 KB
      Đọc:
      10,713
  4. handung107

    handung107 Administrator

    1. Phương pháp giá thực tế đích danh


    Giả sử chúng ta có bảng dữ liệu sau :

    [​IMG]

    Để tính giá xuất kho theo phương pháp đích danh thì chắc hẳn ai cũng biết. Tuy vậy, tôi nghĩ chúng ta cũng cần hệ thống lại đôi chút các công thức có thể áp dụng cho vấn đề này tuỳ cách bố trí CSDL của bạn

    - Dùng các hàm dò tìm : VLOOKUP, INDEX, MATCH
    - Dùng hàm SUMIF, hàm mảng hay hàm SUMPRODUCT (thường ít sử dụng)

    Để tiện dụng, chúng ta sẽ đặt tên cho mảng $A$4:$B$20 là DMMH

    [​IMG]

    Mảng $A$4:$A$20 là MH

    [​IMG]

    Mảng $B$4:$B$20 là DG.

    [​IMG]

    Các bạn cũng có thể tham khảo thêm cách đặt tên cho dữ liệu động trong các tài liệu về “tên và nhãn trong công thức” vào lúc khác

    Chúng ta sẽ lập công thức tính đơn giá theo phương pháp đích danh tại bảng $E$4:$H$6

    a. Sử dụng hàm VLOOKUP

    Tại cột F, chúng ta đặt công thức :

    F4 = VLOOKUP(E4, DMMH, 2, 0)

    [​IMG]

    Để tránh lỗi #NA trong các công thức chứa các hàm dò tìm, người ta thường dùng kết hợp thêm các hàm ISERROR hay ISNA

    F4 = IF(ISNA(VLOOKUP(E4,DMMH,2,0),0,VLOOKUP(E4,DMMH,2,0))
    Hoặc :
    F4 = IF(ISNA(VLOOKUP(E4,DMMH,2,0),””,VLOOKUP(E4,DMMH,2,0))

    b. Sử dụng hàm INDEX kết hợp với MATCH :

    Tại cột G chúng ta đặt công thức :

    G4 = INDEX(DMMH,MATCH(E4,MH,0),2)

    [​IMG]

    Kết hợp với ISNA để tránh lỗi #NA như sau :

    G4 = IF(ISNA(INDEX(DMMH,MATCH(E4,MH,0),2)),0,INDEX(DMMH,MATCH(E4,MH,0),2))

    c. Sử dụng hàm SUMIF và hàm SUMPRODUCT

    Tại cột H chúng ta đặt công thức ;

    H4 = SUMIF(MH,E4,DG)
    H5 = SUMPRODUCT((MH=E5)*DG)

    [​IMG]
     
  5. LikeIt

    LikeIt New Member

    Liên quan đến quản lý hàng hoá tồn kho, em mạo muội bổ sung thêm một chút nữa là trong quản lý tồn kho còn có thuật ngữ "FEFO" (first expire date first out) tạm dịch là "hàng có thời hạn hết trước thì xuất trước", cái này chủ yếu nó quản lý về tuổi hàng trong kho, còn về giá trị thì tính theo LIFO như Chị HD đã trình bày ở trên. Đây là thuật ngữ mà toàn bộ các hàng hoá hiện nay trong kho bãi phải quan tâm. Vì hàng hoá sản xuất sau, nhưng vòng đời sản phẩm nhanh do vậy phải rất quan tâm đến FEFO.
     
  6. smbsolutions

    smbsolutions New Member

    Đúng rồi, phương pháp này thường áp dụng cho những sản phẩm có vòng đời ngắn, nói nôm na dễ hiểu là trên bao bì có chữ "Exp. date" như thuốc, đồ mỹ phẩm, sữa, đồ hộp, thực phẩm, v.v.... Việc quản lý hàng loại này liên quan tới lô hàng (lô sản xuất, lô nhập) và việc tính NXT cũng quản lý theo lô (hiện lô còn bao nhiêu hàng, v.v...)

    Bên cạnh việc tính trị giá hàng tồn kho, người ta cũng rất hay quan tâm tới việc quản lý hàng tồn kho như thế nào cho hiệu quả. Quản lý hàng tồn kho ko chỉ đơn thuần chỉ ra trong kho còn bao nhiêu (lượng, giá trị) mà còn biết được thông tin vị trí địa lý như nó nằm ở kho nào, hàng (row) nào, kệ (bin) nào..., kích thước, thể tích ra sao thì phù hợp với vị trí nào và vận chuyển ra sao cho hiểu quả nhất, quản lý theo lô, serial ra sao? lắp ráp, tháo dỡ, bảo hành thế nào (đối với hàng theo serial), v.v... Nhưng có lẽ những vấn đề đó ko phù hợp chủ đề "các phương pháp tính trị giá hàng tồn kho" cho lắm.
     
  7. handung107

    handung107 Administrator

    2. Phương pháp giá bình quân ​
    :

    a. Trị giá hàng tồn được tính theo giá trị hàng tồn kho từng thời kỳ:

    - Theo trị giá tồn hàng cuối tháng trước :

    Cách tính khá đơn giản, nên không nhắc lại và cũng ít DN áp dụng cách tính này

    - Theo trị giá tồn hàng cuối tháng :

    Để tính trị giá và số lượng nhập trong kỳ cho từng mặt hàng, ta thường dung các hàm mảng hoặc SUMIF, SUMPRODUCT, tuỳ cách bạn bố trí CSDL
    Giả sử ta có bảng dữ liệu sau tại Sheet Data

    [​IMG]

    Ta đặt tên cho các mảng như sau :

    - $B$5:$B$20 : SoPhieu
    - $C$5:$C$20 : MaMH
    - $D$5:$D20 : SL
    - $F$5:$F$20 : TG

    Tuỳ CSDL của bạn thiết kế, ta có thể sắp xếp các cột SL nhập và số lượng xuất tại các 2 cột riêng, các cột TG nhập và TG xuất cũng riêng. Hoặc bạn có thể nhập liệu chung 1 cột, và căn cứ theo định khoản, số phiếu NK hay XK để xác định đâu là số lượng nhập, đâu là số lượng xuất. Hoặc bạn có thể quy ước nhập lieu số lượng nhập >0, và số lượng xuất <0.
    Ở đây, tôi quy ước SLNhap >0, và SL Xuat <0

    Công thức tính số lượng nhập trong kỳ :

    Tại Sheet NXT, tôi có các công thức để tính SL Nhập trong kỳ như sau :

    Hoặc :

    E4 = SUMPRODUCT((MaMH=A4)*SL*(LEFT(SoPhieu,2)="PN"))

    Hoặc : nếu số lượng nhập được quy ước là >0

    E5 = SUMPRODUCT((MaMH=A5)*(SL>0)*SL)

    Hoặc công thức mảng :

    E6 = {SUM(IF(MaMH=C5,(SL>0)*SL,0))}

    [​IMG]

    Công thức tính trị giá nhập trong kỳ : tương tự trên

    Hoặc :

    F4 = SUMPRODUCT((MaMH=A4)*TG*(LEFT(SoPhieu,2)="PN"))

    Hoặc : nếu số lượng nhập được quy ước là >0

    F5 = SUMPRODUCT((MaMH=A5)*(SL>0)*TG)

    Hoặc công thức mảng :

    F6 = {SUM(IF(MaMH=C5,(SL>0)*TG,0))}

    Công thức tính số lượng xuất trong kỳ : tương tự trên

    Hoặc :

    G4 = -SUMPRODUCT((MaMH=A4)*SL*(LEFT(SoPhieu,2)="PX"))

    Hoặc : nếu số lượng xuất được quy ước là <0

    E5 = -SUMPRODUCT((MaMH=A5)*(SL<0)*SL)

    Hoặc công thức mảng :

    E6 = {-SUM(IF(MaMH=C5,(SL<0)*SL,0))}

    Đến đây, ta đã tính được SL Nhap trong kỳ, TG Nhập trong kỳ, SL Xuất trong kỳ.

    [​IMG]

    Tình đơn giá xuất kho :

    Đơn giá xuất kho = ( Trị giá nhập trong kỳ + Trị giá tồn đầu kỳ) / (SL nhập + SL tồn đầu)

    Trị giá xuất = ĐG Xuất * SL Xuất

    SL tồn cuối kỳ = SL tồn đầu kỳ + SL Nhập – SL Xuất

    TG tồn cuối kỳ = TG tồn đầu kỳ + TG Nhập – TG Xuất

    [​IMG]
     
    Last edited: 9 Tháng mười một 2007
  8. handung107

    handung107 Administrator

    Trị giá hàng tồn được tính theo giá bình quân liên hoàn


    Với cách tính này, mỗi lần nhập hàng sẽ tính lại đơn giá cho lần xuất kế tiếp

    Ta sẽ đi từ việc thiết lập các công thức tính toán từ dễ đến khó.

    Giả sử ta có 2 Sheet :

    Sheet DMMH có chứa mã MH và các số dư đầu kỳ. Tại Sheet này ta đặt 3 tên :
    - TonMaMH cho cột chứa Mã MH,
    - TonDauTG cho trị giá tồn đầu kỳ
    - TonDauSL cho số lượng tồn đầu kỳ

    [​IMG]

    Sheet thứ 2 là Sheet NhapXuatHH, Sheet này dùng để nhập các dữ liệu về Nhập Xuất HH phát sinh trong tháng

    Tại Sheet này ta có các cột sau : Cột C là Mã MH, cột D là Số Lượng Nhap, cột E là TGNhap, cột F là SLXuat, cột G là TGXuat, cột H tính đơn giá vốn

    [​IMG]

    Tại Cell đầu tiên tính đơn giá vốn, Cell H5, ta có công thức sau :

    =IF(OR($C5="",SUMIF(TonMaMH,$C5,TonDauTG)=0),0,SUMIF(TonMaMH,$C5,TonDauTG)/SUMIF(TonMaMH,$C5,TonDauSL))

    Bắt đầu Cell H6, công thức sẽ trở thành :

    =IF(C6="",0,(SUMIF(TonMaMH,C6,TonDauTG)+SUMPRODUCT(($C$5:C5=C6)*($E$5:E5-$G$5:G5)))/(SUMIF(TonMaMH,C6,TonDauSL)+SUMPRODUCT(($C$5:C5=C6)*($D$5:D5-$F$5:F5))))

    [​IMG]

    Một cách khác để rút gọn công thức là đặt tên cho từng đoạn công thức nhu sau :
    Bạn đặt con trỏ ngay tại Cell H5 , rồi vào Insert / Name/ Define

    Đặt tên cho các công thức sau :

    SLDuDau = SUMPRODUCT((TonMaMH=NhapXuatHH!$C5)*TonDauSL)

    [​IMG]

    TGDuDau = SUMPRODUCT((TonMaMH=NhapXuatHH!$C5)*TonDauTG)

    [​IMG]

    Công thức trong Cell H5 sẽ trở thành :

    =IF(OR(H5="",SLDuDau=0),0,TGDuDau/SLDuDau)

    Bây giờ, ta đặt con trỏ tại Cell H6, và tiếp tục đặt tên cho công thức :

    SLDuCuoi = SLDuDau+SUMPRODUCT((NhapXuatHH!$C$5:$C5=NhapXuatHH! $C6 )*(NhapXuatHH!$D$5:D5-NhapXuatHH!$F$5:F5))

    TGDuCuoi = TGDuDau+SUMPRODUCT((NhapXuatHH!$C$5:$C5=NhapXuatHH!$C6)*(NhapXuatHH!$E$5:$E5-NhapXuatHH!$G$5:$G5)))

    Công thức tại Cell H6 sẽ được viết thành :

    =IF(OR(C6="",SLDuCuoi=0),0,TGDuCuoi/SLDuCuoi)
     
    Last edited: 9 Tháng mười một 2007
  9. handung107

    handung107 Administrator

    Ta cũng có thể thiết lập CSDL với các cột sau :

    [​IMG]

    Đặt tên cho các mảng dữ liệu :

    - $C$5:$C$20 = MH
    - $D$5:$D$20 = SL
    - $E$5:$E$20 = DGNhap
    - $F$5:$F$20 = DGVon
    - $G$5:$G$20 = TG

    Với bảng này, cột số lượng được nhập chung cả SL Nhập và SL Xuất với quy ước SLNhap >0, và SL Xuất < 0

    Cột TG cũng vậy với công thức tính là :

    G5 = =IF(D5>0,D5*E5,D5*F5) (D5 là cột SL, nếu SL>0, DGNhap*SL và ngược lại)

    Cột DG xuất (Cột F) sẽ có các công thức sau :

    F5 = IF(OR($C5="",SUMIF(TonMaMH,$C5,TonDauSL)=0),0,SUMIF(TonMaMH,$C5,TonDauTG)/SUMIF(TonMaMH,$C5,TonDauSL))

    Từ F6 trở xuống, công thức sẽ là :

    F6 =IF(C6="",0,(SUMIF(TonMaMH,C6,TonDauTG)+SUMPRODUCT((C$5:$C5=C6)*($G$5:$G5)))/(SUMIF(TonMaMH,C6,TonDauSL)+SUMPRODUCT((C$5:$C5=C6)*(D$5:$D5))))

    [​IMG]

    Ta cũng có thể đặt tên cho các công thức để rút gọn như đã nói ở phần trên

    Dùng công thức SUMIF kết hợp với OFFSET :

    - SUMPRODUCT(($C$5:$C5=C6)*($G$5:G5)) : Mảng $C$5:$C$20 được đặt tên là MH, mảng $G$5:$G$20 là TG nên công thức này tương đương với

    = SUMIF(OFFSET(MH,0,0,ROW()-5,1),C6,OFFSET(TG,0,0,ROW()-5,1))

    Công thức này hơi khó hiểu, nhưng hầu như được sử dụng nhiều trong những hàm tính FIFO sẽ nói ở phần sau
     
    Last edited: 10 Tháng mười một 2007
  10. handung107

    handung107 Administrator

    3. FIFO và LIFO :

    Thiết kế File Excel để tính trị giá hàng tồn theo FIFO và LIFO tùy từng người, nhưng nhìn chung có các dạng sau :
    - Tính trực tiếp trị giá tồn HH trên bảng Nhập Xuất Tồn được tổng hợp dữ liệu từ các Sheet Nhập hàng và Xuất hàng (pp kiểm kê định kỳ)
    - Tính số lượng hàng tồn còn lại và trị giá theo dõi từng đợt nhập hàng (pp kiểm kê định kỳ)
    - Tính trị giá vốn và trị giá hàngtồn ngay thời điểm xuất kho (pp kê khai thường xuyên)

    Chúng ta thường thấy việc tính toán này sử dụng VBA để tạo hàm UDF , và hiếm thấy các File Excel sử dụng công thức thong thường. Ở đây, tôi xin giới thiệu với các bạn một số File không dung VBA, tất nhiên, có nhiều hạn chế, nhưng chúng ta vẫn có nhiều điểm để có thể tham khảo và học tập

    A. Trị giá hàng tồn được tính theo phương pháp kê khai thường xuyên :

    Tham khảo thêm bài của Workman : Vận dụng công thức mảng để tính FIFO

    http://www.giaiphapexcel.com/forum/showthread.php?t=68&highlight=FIFO
     

    Các file đính kèm:

    • FIFO1.rar
      Kích thước:
      3.4 KB
      Đọc:
      4,504
    Last edited: 21 Tháng mười một 2007
  11. handung107

    handung107 Administrator

    Giải thích thêm về cách vận dụng các công thức trong File FIFO trên

    Giả sử ta có bảng dữ liệu sau :

    [​IMG]

    Đầu tiên, ta đặt tên cho mảng $B$4:$B$30 là Q, và mảng $C$4:$C$30 là P, cách đặt tên như thế nào hoặc các bạn có thể đặt tên cho vùng dữ liệu động, chúng ta sẽ không bàn tại đây

    Yêu cầu cấu trúc của File này là :

    - Số lượng Nhấp và Xuất đặt cùng 1 cột và quy ước SLNhap >0, SLXuat <0
    - Dữ liệu được nhập theo thứ tự ngày tháng. Hàng trên cùng bao giờ cũng là số >0, tương ứng với số lượng tồn cuối kỳ, hoặc nhập hàng vào rồi mới xuất bán

    Để có thể hiểu được bài này, chúng ta cần nói về một số công thức mảng kết hợp bởi các hàm sau :

    1/ Hàm ROW(Q) : sẽ trả về giá trị là một mảng gồm các số hạng là vị trí các hàng của các phần tử của mảng Q, tính từ phần tử đầu tiên. Công thức mảng ROW(Q) sẽ cho như hình dưới đây

    [​IMG]

    2/ Hàm TRANSPOSE(ROW(Q)) : Sẽ cho ta một mảng ngang khác bằng kích thước với mảng ROW(Q) như hình vẽ sau :

    [​IMG]

    3/ Lệnh ROW(SL)>=TRANSPOSE(ROW(SL)) sẽ xuất ra mảng gồm những phần tử trong mảng có giá trị là TRUE :

    [​IMG]

    Ta chuyển giá trị TRUE, False sang giá trị 1, 0 bằng cách thêm vào 2 dấu - - như sau :

    {=(--(ROW(SL)>=TRANSPOSE(ROW(SL))}, ta sẽ có một mảng gồm giá trị :

    [​IMG]

    Ta gọi mảng trên đây là mảng 1

    Bạn hỏi vì sao phải tạo ra mảng này ? Thực ra, phải đi từ mục đích cuối cùng ra. Mục đích chính là tìm thời điểm (dòng số mấy) của cái gọi là “First in”. Muốn làm như thế, bắt buộc phải tạo ra một mảng thể hiện tổng số hàng nhập kho, sau đó, trừ đi xuất kho, ta sẽ tìm được thằng “First in” mới.

    Bạn hãy xem mảng (Q>0)*Q sẽ cho ra các phần tử nào nhé :

    5
    5
    0
    2
    0
    2
    0
    0
    4
    2
    0
    1
    3
    3
    1
    0
    0
    3
    0
    5
    3
    0
    0
    0
    0
    0

    [​IMG]

    Đây chính là mảng số lượng B4:B30 của bảng dữ liệu, chỉ có điều số lượng xuất < 0, sẽ được Convert thành số 0, chỉ còn các phần tử là số lượng nhập trong kỳ mà thôi. Ta gọi mảng này là mảng 2
     
    Last edited: 20 Tháng mười một 2007
  12. handung107

    handung107 Administrator

    4) Hàm MMULT : hàm này ít ai xài, nhưng nếu kết hợp với các hàm về mảng khác lại cho ra kết quả khá tốt. Đây chính là hàm ma trận, nhân các phần tử trong mảng này với các phần tử trong mảng kia. Kết hợp hàm MMULT để nhân mảng 1 và mảng 2 ta có kết quả như sau :

    [​IMG]

    Đây chính là mảng chúng ta cần tìm. Mảng này thể hiện tổng số hàng nhập kho, ta chỉ cần tìm mảng thể hiện số xuất kho, lấy mảng này trừ đi, ta sẽ tìm được vị trí First In mới
    Bạn sẽ bật cười vì để tạo mảng này, ta có thể chỉ cần hàm SUMIF(B$4:B4,”>0”) rồi kéo xuống, hơi đâu mà phài làm công thức dài loằng ngoằng khó hiểu như vậy ? Nhưng thực tế, nếu dung hàm SUMIF, bạn sẽ phải thêm các cột phụ, do đó, cách giải này vẫn là Best Solution

    Bạn thử xem hình sau :

    Thoạt nhìn, bạn sẽ thấy 2 công thức :
    - =MMULT(--(ROW(Q)>=TRANSPOSE(ROW(Q))),(Q>0)*Q)
    - =SUMIF($B$4:B4,”>0”) hay
    - =SUMIF(OFFSET(Q,0,0,ROW()-4,1),">0")

    Là cho kết quả tương đương nhau khi bạn nhập chúng vào một cột như hình dưới. Nhưng thực tế, công thức 1 là công thức mảng, trong khi 2 công thức dưới không phải công thức mảng.

    [​IMG]

    Kết quả trên của hàm ma trận {= MMULT(--(ROW(Q)>=TRANSPOSE(ROW(Q))),(Q>0)*Q)} thực ra được diễn giải gồm mảng như sau :
    5 0 0 0 0 0 0 ---> 1*5 + 0*5 + 0* 0 + 2*0 + ….= 5
    5 5 0 0 0 0 0 ----> 1*5 + 1*5 + 0*0 + 2*0 + …. = 10
    5 5 0 0 0 0 0 ----> 1*5 + 1*5 + 1*0 + 2*0 + ….. = 10
    5 5 0 2 0 0 0 ----> 1*5 + 1*5 + 1*0 + 1*2 + 0*0 + …. = 12
    ………

    [​IMG]

    Chúng ta sẽ xác định tổng số lượng xuất kho bằng công thức sau :
    = SUMIF(OFFSET(Q,0,0,ROW()-4,1),”<0”) hay = SUMIF($B$4:B4,”<0”)

    [​IMG]
     
    Last edited: 20 Tháng mười một 2007
  13. handung107

    handung107 Administrator

    Tổng số của MMULT và SUMIF : = (SLNhập - SLXuất)

    Kết hợp 2 hàm này :
    =MMULT(--(ROW(Q)>=TRANSPOSE(ROW(Q))),(Q>0)*Q)+SUMIF(OFFSET(Q,0,0,ROW()-4,1),”<0”)
    Ta có mảng sau :

    [​IMG]

    Bạn sẽ thấy mảng này thể hiện số lượng hàng tồn sau mỗi lần nhập và xuất kho.
    Mảng đầy đủ sẽ là

    [​IMG]

    Mọi việc bắt đầu đơn giản rồi. Điều kiện MMULT + SUMIF >0 sẽ cho ra một mảng sau :

    [​IMG]

    Mảng đầy đủ phải được diễn giải như sau :

    [​IMG]

    Trong mảng trên, TRUE sẽ đại diện cho hàng còn tồn kho, và False đại diện cho hàng đã xuất hết kho.
    5/ Cuối cùng, ta dung hàm MATCH để xác định vị trí “TRUE” đầu tiên của mảng trên. Ý nghĩa của nó là dò tìm trong khối Q, đâu là dòng “FIRST In” đầu tiên. Công thức này là :
    {= MATCH(TRUE,(--(ROW(Q)>=TRANSPOSE(ROW(Q))),(Q>0)*Q)+SUMIF(OFFSET(Q,0,0,ROW()-4,1),”<0”)>0,0)}
    Trở về bảng dữ liệu, ta sẽ dung 2 cột phụ là cột D và cột E. Cell đầu tiên của bảng bắt đầu từ hàng thứ 4, do đó, tại D4 ta nhập 1
    Từ D5 đến D30, ta nhập công thức trên, ta sẽ được kết quả là :

    1
    1
    1
    1
    1
    2
    2
    2
    6
    6
    6
    9
    9
    9
    9
    9
    10
    13
    13
    14
    14
    14
    18
    18
    18
    18
    18
    18
    18
    18

    [​IMG]

    Sau khi đã xác định được đâu là vị trí của dòng "First in", ta sang cột E
     
  14. handung107

    handung107 Administrator

    Sau khi đã xác định được đâu là vị trí của dòng “First in”, ta sang cột E

    Tại E4 = B4, số lượng tồn đầu kỳ

    E5 = IF(D5=D4,E4+MIN(0,B4),SUMIF(OFFSET(Q,0,0,D5,1),">0") +SUMIF(OFFSET(Q,0,0,ROW()-4,1),"<0"))

    Công thức này có thể diễn giải như sau :

    - Nếu số lượng hàng cũ còn đủ xài : IF(D5=D4,
    thì số lượng hàng tồn kho bằng với E4 trừ đi số lượng hàng xuất ra (Lưu ý hàm MIN(0,B4) cho kết quả là số âm, chính là số lượng hàng xuất) : E4+MIN(0,B4)

    - còn nếu số lượng hàng cũ không đủ xài, thì lấy hàng mới mà xuất : SUMIF(OFFSET(Q,0,0,D5,1),">0")

    - số tồn kho (đợt hàng First in mới) sẽ là tổng số nhập trừ đi tổng số xuất cho đến thời điểm tính toán :

    SUMIF(OFFSET(Q,0,0,D5,1),">0") +SUMIF(OFFSET(Q,0,0,ROW()-4,1),"<0")

    Kéo công thức xuống các hàng bên dưới của cột E từ E5 đến E30, ta có hình sau :

    [​IMG]

    Như vậy : Cột E chính là số hàng tồn còn lại sau mỗi nghiệp vụ nhập hàng hay xuất hàng
     
  15. handung107

    handung107 Administrator

    Cột F là cột tính FIFO - trị giá hàng xuất bán

    - F5 =IF(B5<0,IF(E5+B5>=0,-B5*INDEX(P,D5),E5*INDEX(P,D5)+(INDEX(Q,D6)-E6)*INDEX(P,D6)+IF(D6-D5>1,SUMPRODUCT(--(OFFSET(Q,D5,0,D6-D5-1,1)>0),OFFSET(Q,D5,0,D6-D5-1,1),OFFSET(P,D5,0,D6-D5-1,1)),0)),"")

    Công thức này có vẻ dài, nhưng không đến nỗi khó hiểu, chúng ta từ từ xem từng đoạn một như sau :

    - Nếu B5 < 0 : số lượng hàng xuất bán (< 0), và nếu số tồn kho (E5) > = số lượng xuất bán (B5) : E5 + B5 >=0, trị giá xuất sẽ là đơn giá (mảng P), tại vị trí “First in” (D5) : INDEX(P,D5), nhân số lượng xuất (B5). Vì số lượng xuất của chúng ta quy định là < 0, nên ta phải đặt thêm dấu - để cho kết quả là số +

    =IF(B5<0,IF(E5+B5>=0,-B5*INDEX(P,D5), ……

    Xét tại công thức FIFO tại F6, số lượng xuất bán B6 = 3 nhỏ hơn số lượng hàng tồn E6 = 5, đợt hàng này là đợt 1(D6 = 1) có đơn giá : (INDEX(P,1) = 1) nên trị giá hàng xuất là : 3*1 = 3

    [​IMG]

    - Ngược lại, nếu số lượng hàng tồn tại vị trí (First in) < số lượng xuất bán, thì lấy hết số lượng tồn (E5) ra để bán, trị giá xuất của số lượng tồn này là : E5*INDEX(P,D5), số lượng còn thiếu sẽ được lấy từ vị trí kế tiếp của đợt nhập hàng.

    Số lượng hàng nhập của đợt hàng tiếp theo INDEX(Q,D6) – E6 (số lượng hàng tồn sau khi xuất bán nốt phần còn thiếu) = Số lượng hàng xuất bán còn thiếu. Tương tự, đơn giá đợt hàng tiếp theo là INDEX(P,E6)

    E5*INDEX(P,D5)+(INDEX(Q,D6)-E6)*INDEX(P,D6)

    [​IMG]

    Xét công thức tính FIFO tại F11, số lượng xuất bán B11 = 3, số lượng tồn cũ là E11 = 1 của đợt hàng nhập thứ 2 (D11 = 2), có đơn giá là INDEX(P,D11) = 1.1. Xuất hết số lượng E11, trị giá xuất của số hàng này là : = E11*INDEX(P,D11) = 1*1.1 = 1.1 (hàng màu xanh)

    Số lượng còn phải xuất bán thêm là :

    - Số lượng đợt nhập hàng kế tiếp : INDEX(Q,D12) = 4 có vị trí thứ 6 (D12=6) của mảng $B$4 :$B$30 đặt tên là Q, đơn giá của đợt hàng này là INDEX(P,D12) = 1.3
    - Số lượng hàng tồn của đợt hàng này sau khi đã xuất bán nốt phần còn thiếu là : E12 = 2
    - Vậy số lượng hàng xuất bán thêm trong đợt hàng sau là : (hàng màu vàng)
    = INDEX(Q,D12)-E12 = 4 – 2 = 2
    - Trị giá hàng xuất trong đợt hàng sau là : =(INDEX(Q,D12,2)-E12)*INDEX(P,D12) = 2*1.3 = 2.6
    - Trị giá hàng xuất được tính tại :

    F11 = E11*INDEX(P,D11) + (INDEX(Q,D12,2)-E12)*INDEX(P,D12) = 1*1.1 + 2*1.3 = 3.7
     
  16. handung107

    handung107 Administrator

    Nếu D6 – D5 > 1, và nếu số lượng hàng xuất lớn hơn số lượng hàng tồn, nghĩa là sẽ có nhiều đợt hàng tiếp theo đợt hàng “First in”, do đó :

    - Xuất hết số lượng tồn của đợt First in : = E5*INDEX(P,D5)

    - Xuất nốt số lượng hàng còn thiếu ở đợt hàng nhập cuối : = (INDEX(Q,D6)-E6)*INDEX(P,D6)

    - Số lượng xuất tại các đợt hàng trung gian giữa 2 giá trị D5 và D6 được xác định bằng công thức :

    = SUMPRODUCT(--(OFFSET(Q,D5,0,D6-D5-1,1)>0),OFFSET(Q,D5,0,D6-D5-1,1),OFFSET(P,D5,0,D6-D5-1,1))

    Hàm OFFSET(Q,D5,0,D6-D5-1,1) cho ta 1 mảng con của mảng Q, bắt đầu từ vị trí đầu tiên của mảng Q, dời xuống D5 hàng, có D6-D5-1 hàng và 1 cột.

    Mảng OFFSET(Q,D5,0,D6-D5-1,1)>0, sẽ cho ta giá trị Logic, do đó ta đặt 2 dấu trừ để convert giá trị True, False thành giá trị số 1, 0

    Bây giờ, ta nhìn hình vẽ sau để hiểu rõ hơn về công thức trên, xét công thức FIFO tại hàng 20 và 21. D21 = 13, còn D20 = 10

    [​IMG]

    - Mảng OFFSET(Q,D20,0,D21-D20-1,1) là mảng $B$14:$B$15 có màu xanh, chữ đỏ, bắt đầu từ hàng 14, dời xuống 10 hàng tính từ hàng thứ 4 là vị trí đầu tiên của mảng Q. Mảng này gồm 2 hàng, và có giá trị dương tại B15 là số 1

    - Mảng OFFSET(P,D20,0,D21-D20-1,1) là mảng $C$14:$C$15 có màu xanh, chữ xanh, bắt đầu từ hàng 14, dời xuống 10 hàng tính từ hàng thứ 4 là vị trí đầu tiên của mảng P.

    Hàm SUMPRODUCT sau sẽ trả kết quả là : 1*1.2 = 1.2

    = SUMPRODUCT(--(OFFSET(Q,D20,0,D21-D20-1,1)>0),OFFSET(Q,D20,0,D21-D20-1,1),OFFSET(P,D20,0,D21-D20-1,1))

    -Các hàm còn lại đã giải thích ở trên
     
  17. handung107

    handung107 Administrator

    B. Tính FIFO theo phương pháp kiểm kê định kỳ:

    Việc tính trị giá hàng tồn theo FIFO và phương pháp kiểm kê định kỳ tương đối dễ dàng hơn phương pháp kê khai thường xuyên và thường hay được áp dụng

    Chúng ta có thể tính FIFO trên bảng Nhập Xuất Tồn : thường được thiết kế bằng các Sheet Nhập, Xuất riêng, và Sheet Nhập Xuất Tồn

    Các bạn tham khảo File của ThuNghi - Quanlychungkhoan-FIFO kèm theo

    Các công thức không đến nỗi khó hiểu lắm, nên tôi không giải thích
     

    Các file đính kèm:

  18. handung107

    handung107 Administrator

    Đây cũng là File QuanlyChungKhoan + TonquyLienTuc của Son2006, các bạn có thể tham khảo thêm về các lập công thức tính FIFO
     

    Các file đính kèm:

  19. handung107

    handung107 Administrator

    File FIFO và LiFO sau giới thiệu cho các bạn một cách xây dựng cách tính FIFO và LIFO khác khá hay

    - Tính FIFO :

    Ta xem hình vẽ sau :

    [​IMG]

    Dùng các cột phụ D, E, F, G

    - Cột D : Dùng để so sánh số lượng tồn lũy kế sau mỗi lần nhập vào, với số lượng xuất bán trong kỳ.

    Công thức SUM($B$6:B6)<=$H$2 sẽ cho ra giá trị Logic, nên ta chuyển thành giá trị số bằng cách nhân với 1. Ta có thể sử dụng 2 dấu – đặt trước( = --(SUM($B$6:B6)<=$H$2) )) cũng cho kết quả tương tự

    - Tổng số tại ô D14 cho ta biết có mấy đợt nhập hàng trong kỳ có số lượng hàng xuất bán hết

    - Cột E : dùng để xác định lượt nhập hàng thứ mấy có số lượng xuất bán còn lại sau cùng.

    Công thức cột này là : =((A6-1)=$D$14)*1

    - Các công thức tương đối dễ hiểu ở các cột phụ, nên ta có thể tự suy ra
    - F6 =D6*B6
    - G6 =($H$2-$F$14)*E6

    - Tính LIFO :

    [​IMG]

    - Cell D14 là tổng số lượng xuất ra trong kỳ

    - Cột D dùng để xác định số lượng tồn của mỗi đợt hàng sau khi xuất ra cho đủ số lương tại Cell D14, tính ngược từ đợt nhập hàng cuối cùng

    - Cột E dùng để xác định xem đợt hàng nào còn hàng tồn

    - Cột F dùng để xác định xem đợt hàng cuối cùng nào có số lượng xuất

    - Công thức tại các cột như sau :

    1. D6 =(D7-B6)
    2. E6 = (D6>=0)*1
    3. F6 = (A6=$E$14)*1
    4. G6 = E6*B6
    5. H6 = ($I$2-$G$14)*F6
     

    Các file đính kèm:

    Last edited: 21 Tháng mười một 2007
  20. handung107

    handung107 Administrator

    Ta cũng có thể dùng công thức đơn giản nhất là MAX và MIN để tính FIFO và LIFO như sau :

    Giả sử ta có bảng dữ liệu

    [​IMG]

    Ta sẽ dùng các hàm MAX, MIN kết hợp để tính FIFO và LIFO

    - Tính FIFO :

    Dùng cột D tính số lượng tồn, tại D6 ta nhập công thức :

    D6 = IF(A6=””,0,MAX(MIN(B6,SUM($B$6:$B6)-$C$3),0) )

    Kéo công thức xuống dưới

    [​IMG]

    Ta có thể dùng công thức sau để tính, nếu không muốn có thêm cột TGTon

    TGTon = SUMPRODUCT($C$6:$C$26,$D$6:$D$26)

    TGBan = SUMPRODUCT($C$6:$C$26,$B$6:$B$26)-SUMPRODUCT($C$6:$C$26,$D$6:$D$26)

    - Tính LIFO :

    Để tính LIFO cho bảng dữ liệu trên, ta thay công thức tại D6 là :

    D6 = =$B6-MAX(MIN($B6,$C$3-SUM($B7:B$26)),0)

    [​IMG]
     

Chia sẻ trang này