Tôi là Lương Văn Long, 52 tuổi, đại diện một số bạn đồng nghiệp kế toán. Qua bài “Tự động hóa công tác kế toán với Excel” trên LBMVT số 208, nay mong tòa soạn có bài viết rõ hơn, rộng hơn, có thí dụ cụ thể nhiều hơn để chúng tôi dễ tiếp thu hơn nhằm ứng dụng cho công việc. Bài viết trên rất bổ ích, hỗ trợ cho công việc của tôi nói riêng và những người làm công tác kế toán nói chung, nhưng vì trình độ của chúng tôi còn hạn hẹp nên chưa nắm rõ được những tiện ích đã gợi ý trong bài. Rất mong được trợ giúp.
Bài viết sau đây nhằm chia sẻ với các bạn về việc ứng dụng các kiến thức cơ bản và một số hàm đơn giản của Excel, kiến thức cơ bản về kế toán để xây dựng hệ thống kế toán đơn giản tại đơn vị. Các bạn có thể tham khảo file nguồn của chương trình (file nén có dung lượng 660 KB) tại địa chỉ:
http://www.zshare.net/download/283710167e054a/
hoặc http://www.mediafire.com/?1qddn2mdgu1
hoặc http://www.bigupload.com/d=4F867C3D.
Để kích hoạt chương trình, các bạn giải nén tập tin gốc và khởi động Excel, điều chỉnh tùy chọn Tools > Options > Security > Medium > OK > OK sau đó mở file gốc để xem thiết kế mẫu của chương trình.
Thực chất để thiết kế một chương trình kế toán Excel hoàn chỉnh đòi hỏi bạn phải có kiến thức vững vàng về kế toán và lập trình Excel. Tuy nhiên, không phải ai cũng có đủ được cả 2 khả năng này, có người rất giỏi về nghiệp vụ kế toán nhưng lại không rành lắm về Excel và ngược lại. Nếu vận dụng tốt cả 2 kiến thức nói trên bạn sẽ tự mình xây dựng được các phần mềm quản lý kế toán Excel, giúp tiết kiệm được một khoản chi phí kha khá cho đơn vị, có thêm kinh nghiệm sử dụng và nâng cao tay nghề với Excel.
n CÁC KIẾN THỨC CƠ BẢN CẦN BIẾT ĐỐI VỚI KẾ TOÁN
1. Hình thức sổ kế toán:
Trong kế toán có 5 hình thức sổ kế toán cơ bản: Nhật ký sổ cái, Nhật ký chung, Chứng từ ghi sổ, Nhật ký chứng từ và hình thức kế toán trên máy vi tính. Tuy nhiên, để áp dụng lên máy tính với Excel thì hình thức Nhật ký chung là dễ dàng và thuận tiện nhất. Hình thức này bao gồm các sổ sách kế toán như: sổ nhật ký chung, sổ cái, sổ nhật ký đặc biệt và các sổ kế toán chi tiết (sổ phụ).
- Sổ nhật ký chung là sổ kế toán cơ bản dùng để ghi chép các nghiệp vụ kinh tế phát sinh theo trình tự thời gian và quan hệ đối ứng tài khoản của các nghiệp vụ đó làm căn cứ để ghi vào sổ cái.
- Sổ cái là sổ kế toán tổng hợp dùng để tập hợp và hệ thống hóa các nghiệp vụ phát sinh của từng tài khoản tổng hợp. Số liệu của sổ cái cuối tháng được dùng để ghi vào bảng cân đối phát sinh, từ đó ghi vào bảng cân đối kế toán và các báo biểu kế toán khác.
- Sổ nhật ký đặc biệt được dùng trong trường hợp nghiệp vụ phát sinh nhiều, nếu tập trung ghi cả vào nhật ký chung thì có nhiều trở ngại, cho nên phải mở các sổ nhật ký đặc biệt để ghi chép riêng cho từng loại nghiệp vụ chủ yếu. Sổ nhật ký đặc biệt bao gồm 4 loại sau: bán hàng chịu, mua hàng chịu, thu tiền mặt và chi tiền mặt. Khi dùng sổ nhật ký đặc biệt thì chứng từ gốc trước hết được ghi vào sổ nhật ký đặc biệt, sau đó, hàng ngày hoặc định kỳ tổng hợp số liệu của nhật ký đặc biệt ghi một lần vào sổ cái.
- Các sổ, thẻ kế toán chi tiết dùng để ghi chép một cách chi tiết các đối tượng kế toán cần phải theo dõi chi tiết nhằm phục vụ cho yêu cầu tính toán một số chỉ tiêu tổng hợp, phân tích và kiểm tra của đơn vị mà sổ kế toán tổng hợp không thể đáp ứng được.
2. Trình tự ghi sổ kế toán theo hình thức nhật ký chung:
Hàng ngày, dựa vào các chứng từ được dùng làm căn cứ ghi sổ, trước hết ghi nghiệp vụ phát sinh vào sổ nhật ký chung, sau đó căn cứ số liệu trên sổ nhật ký chung để ghi vào sổ cái theo các tài khoản kế toán phù hợp. Nếu các nghiệp vụ kinh tế phát sinh cần theo dõi chi tiết thì phải ghi thêm vào các sổ, thẻ kế toán chi tiết...
Trường hợp đơn vị có mở các sổ nhật ký đặc biệt thì hàng ngày, căn cứ vào các chứng từ được dùng làm căn cứ ghi sổ, ghi nghiệp vụ kinh tế phát sinh vào các sổ nhật ký đặc biệt có liên quan. Định kỳ (3, 5, 10... ngày) hoặc cuối tháng, tùy theo khối lượng nghiệp vụ phát sinh, tổng hợp từng sổ nhật ký đặc biệt, lấy số liệu ghi vào các tài khoản phù hợp trên sổ cái, sau khi đã loại trừ số trùng lắp do một nghiệp vụ được ghi đồng thời vào nhiều sổ nhật ký đặc biệt. Cuối tháng, cuối quý, cuối năm, cộng số liệu trên sổ cái, lập bảng cân đối số dư và số phát sinh. Sau khi kiểm tra đối chiếu khớp đúng, số liệu ghi trên sổ cái và bảng tổng hợp chi tiết được dùng để lập các báo cáo tài chính. Nguyên tắc chung là tổng số phát sinh Nợ và tổng phát sinh Có trên bảng cân đối số dư và số phát sinh phải bằng tổng tiền đã ghi trên sổ nhật ký chung (hoặc sổ nhật ký chung và các sổ nhật ký đặc biệt sau khi đã loại trừ số trùng lắp trên các sổ nhật ký đặc biệt) cùng kỳ.
3. Các yêu cầu khác:
Ngoài ra, bạn cần phải nắm vững thêm về các kiến thức kế toán khác có liên quan cũng như các mẫu sổ và phương pháp ghi sổ của hình thức kế toán nhật ký chung. Các sổ này bao gồm: nhật ký thu tiền, nhật ký chi tiền, nhật ký mua hàng, nhật ký bán hàng, sổ cái, sổ nhật ký chung... và các sổ kế toán chi tiết như: sổ chi tiết vật liệu, sản phẩm, hàng hóa, sổ chi phí sản xuất kinh doanh, thẻ tính giá thành, sổ chi tiết tiền gửi, tiền vay...
Kết cấu sổ nhật ký được qui định thống nhất theo mẫu ban hành trong chế độ sổ kế toán:
Cột 1: ghi ngày, tháng ghi sổ.
Cột 2 + 3: ghi số và ngày, tháng lập chứng từ dùng làm căn cứ ghi sổ.
Cột 4: ghi nội dung nghiệp vụ kinh tế, tài chính phát sinh.
Cột 5: đánh dấu các nghiệp vụ ghi sổ nhật ký chung đã được ghi vào sổ cái.
Cột 6: ghi số hiệu các tài khoản ghi Nợ, ghi Có theo định khoản kế toán các nghiệp vụ phát sinh. Tài khoản ghi Nợ trước, tài khoản ghi Có sau, mỗi tài khoản ghi trên 1 dòng riêng.
Cột 7: ghi số tiền phát sinh các tài khoản ghi Nợ.
Cột 8: ghi số tiền phát sinh các tài khoản ghi Có.
Cuối trang sổ cộng số phát sinh lũy kế để chuyển sang trang sau. Đầu trang sổ ghi số cộng trang trước chuyển sang. Các mẫu sổ này các bạn có thể xem chi tiết hơn trong file nguồn đính kèm. Phần này các bạn nên xem thêm Quyết định 15/2006/QĐ-BTC, Thông tư 20/2006/TT-BTC, Thông tư 21/2006/TT-BTC của Bộ Tài chính ban hành để biết thêm những chế độ, qui định mới nhất về kế toán.
n CÁC KIẾN THỨC CẦN BIẾT ĐỐI VỚI EXCEL
Trước tiên, bạn cần nắm lại một số kiến thức cơ bản về Excel như:
- Thao tác xử lý tập tin (tạo mới, lưu, mở, đóng, in ấn, xóa... bằng cách mở menu File, chọn lệnh tương ứng).
- Thao tác về bảng tính như chèn, xóa cột hàng, đặt tên khối, tên vùng dữ liệu, tên sheet, sao chép dữ liệu, công thức, thiết lập giá trị, font chữ mặc định...
- Thao tác sắp xếp dữ liệu, trích lọc dữ liệu cơ bản và nâng cao, trích lọc có điều kiện phức tạp, vẽ đồ thị...
- Thao tác tạo subtotal, liên kết dữ liệu từ nhiều sheet khác nhau...
- Thao tác định dạng bảng tính, dàn trang bảng tính, xử lý in ấn...
Tiếp theo, bạn cần nắm vững một số hàm cơ bản sau đây để có thể thiết lập các công thức tính toán trong Excel:
- Hàm dạng văn bản: hàm LEFT(text, n); hàm RIGHT(text, n); hàm MID(text, kt, n).
- Hàm dạng logic: hàm AND(đk1, đk2, đk3...); hàm OR(đk1, đk2, đk3...); hàm IF(đk, gt đúng, gt sai).
- Hàm dạng ngày tháng: hàm DAY(ngày); MONTH(ngày); YEAR(ngày); TODAY.
- Hàm dạng thống kê, tính tổng: hàm SUM(gt1, gt2, gt3,...); hàm MAX(gt1, gt2, gt3,...); hàm MIN(gt1, gt2, gt3,...), hàm AVERAGE(gt1, gt2, gt3,...), hàm COUNT(gt1, gt2, gt3,...).
Hàm SUMIF (range, criteria, sumrange); hàm COUNTIF(range, crirteria); hàm DSUM(database, fieldnumber, criteria); DCOUNTA(database, fieldnumber, criteria).
- Hàm dạng toán học: hàm INT(giatri / n); hàm MOD (giatri, n); hàm ROUND(giatri, n).
- Hàm dạng tra cứu: hàm VLOOKUP(lookupvalue, table array, column index number, range lookup); hàm HLOOKUP(lookupvalue, table array, column index number, range lookup).
Với những hàm đơn giản này, giả định bạn đã biết cách sử dụng trong từng trường hợp cụ thể. Ở đây, chúng ta sẽ lồng ghép các hàm này thành những công thức tính toán cụ thể để xử lý các số liệu kế toán.
n VẬN DỤNG KẾ TOÁN VÀ EXCEL ĐỂ VIẾT PHẦN MỀM QUẢN LÝ
1. Tạo 2 sổ kế toán chứa dữ liệu gốc ban đầu (Sổ Kế Toán Máy và Bảng Danh mục Tài khoản)
Đây là phần trọng tâm của chương trình, bạn phải tổ chức công tác kế toán trên Excel sao cho phù hợp với công việc kế toán của bạn để từ đó giải quyết được yêu cầu đặt ra là xử lý hoàn chỉnh số liệu kế toán cho đơn vị.
Trước tiên, bạn hãy tạo một sheet mới đặt tên là SOKTM (sổ kế toán máy) để phản ảnh, định khoản toàn bộ các nghiệp vụ kinh tế phát sinh trong kỳ của đơn vị. Đây là dữ liệu gốc để từ đó bạn sẽ dùng các hàm của Excel để xử lý số liệu sau này, do đó cần thực hiện cẩn thận, chính xác. Kết cấu của sổ này bao gồm các cột sau:
- Số seri: phản ảnh số xê ri của hóa đơn thuế GTGT (những hóa đơn không có số xê ri thì bỏ trống).
- Ngày ghi sổ: là ngày định khoản kế toán, bạn nên nhập theo định dạng chuỗi.
- Số chứng từ: lưu trữ số chứng từ, bạn nên nhập theo định dạng chuỗi.
- Ngày chứng từ: lưu trữ ngày chứng từ, bạn nên nhập theo định dạng chuỗi.
- Tên cơ sở kinh doanh: tên người mua, người bán, bạn nên nhập theo định dạng chuỗi.
- Mã số thuế: mã số thuế của người mua, người bán.
- Diễn giải: ghi trích yếu nội dung của nghiệp vụ kinh tế phát sinh.
- Tài khoản ghi nợ, tài khoản ghi có: nhập bút toán định khoản tài khoản ghi nợ, ghi có.
- Số lượng: ghi nhận số lượng phát sinh khi nhập / xuất.
- Số tiền phát sinh: phản ánh số tiền phát sinh của từng tài khoản trong các bút toán định khoản.
- Thuế GTGT, thuế suất GTGT: phản ánh số tiền thuế GTGT và thuế suất GTGT của các mặt hàng mua vào hay bán ra tương ứng.
- Kiểm tra tài khoản Nợ / Có: dùng để kiểm tra các tài khoản sử dụng đã được mở và đăng ký trong bảng danh mục tài khoản.
Tiếp theo, bạn hãy tạo một sheet mới đặt tên là BDMTK (bảng danh mục tài khoản) dùng để thống kê, tập hợp tất cả các nghiệp vụ kinh tế phát sinh đã được định khoản sẵn theo trình tự thời gian trong sổ kế toán máy. Kết cấu bảng này gồm các cột: Mã cấp, Số hiệu tài khoản, Loại tài khoản, Tên tài khoản, Số lượng tồn đầu kỳ, Số dư nợ đầu kỳ, Số dư có đầu kỳ, Tổng số phát sinh nợ, Tổng số phát sinh có, Số lượng tồn cuối kỳ, Số dư cuối kỳ, Mã cấp trung gian tài khoản.
- Cột Mã cấp: dùng để xác định vị trí của từng tài khoản trong bảng cân đối kế toán. Mã cấp bao gồm 3 cụm số, mỗi cụm 3 kí tự. Ví dụ: 100-110-111 là tiền mặt, tài khoản này nằm ở mục 100, tiểu mục 110 trong bảng cân đối kế toán, cách ký hiệu này tương tự cho những tài khoản còn lại.
- Cột Số hiệu tài khoản: mỗi tài khoản có một số hiệu riêng, tài khoản được mở chi tiết cho từng đối tượng theo dõi cụ thể của kế toán chi tiết (mỗi vật tư, mỗi hàng hóa, mỗi khách hàng...) theo nhu cầu quản lý và lập báo cáo kế toán. Tài khoản mở cho đối tượng nào thì sử dụng mã đối tượng đó ghép với số hiệu tài khoản tương ứng. Ví dụ: tài khoản 152A01, 1112USD, 131C...
- Loại tài khoản: các tài khoản có số phát sinh Nợ tăng ký hiệu N, các tài khoản có số phát sinh Có tăng ký hiệu C.
- Số lượng tồn đầu kỳ: dùng để theo dõi số lượng tồn của các tài khoản, “hàng tồn kho” cho từng hàng tồn kho.
- Số dư đầu kỳ: dùng để phản ánh số dư đầu kỳ trên từng tài khoản chi tiết đã mở.
Đặt tên vùng dữ liệu để lập công thức bằng cách bôi đen vùng dữ liệu theo cột hoặc hàng hoặc khối ô sau đó đưa con trỏ chuột vào mục Name box đặt tên và nhấn Enter hoặc chọn thông qua lệnh Insert > Name > Define (các tên này dùng để truy xuất qua các hàm của Excel).
Lập các công thức tính toán để đưa số liệu từ Sổ kế toán máy vào Bảng danh mục tài khoản (xem chi tiết ở file nguồn đính kèm).
=SUMIF(TKGHINO,B5,SOTIENPS) hoặc =SUMIF (TKGHICO,B5,SOTIENPS) hoặc =IF(OR(LEFT (B5,3)=”152",LEFT(B5,3)=”153",LEFT(B5,3)= ”155",LEFT(B5,3)=”156"),E5 + SUMIF(TKGHINO,B5, SOLUONGPS)-SUMIF (TKGHICO,B5,SOLUONGPS),””)
2. Tạo các sổ sách kế toán có liên quan:
Tiến hành xây dựng các sổ nhật ký và các sổ sách kế toán khác:
- Nhật ký thu tiền, nhật ký chi tiền, nhật ký mua hàng, nhật ký bán hàng, nhật ký chung, sổ cái, sổ quỹ tiền mặt, sổ quỹ tiền gửi ngân hàng.
- Sổ số dư và phát sinh tài khoản 131, 331, sổ chi tiết khách hàng phải thu, phải trả, sổ số dư và phát sinh tài khoản 152, 153, 155, 156, sổ chi tiết hàng tồn kho, sổ chi phí sản xuất kinh doanh tài khoản 621, 622, 627, 641, 642, 154, 632, thẻ tính giá thành sản phẩm.
- Bảng cân đối tài khoản, bảng kê hàng hóa mua vào, bán ra, tờ khai thuế GTGT, bảng cân đối kế toán, bảng báo cáo kết quả sản xuất kinh doanh phần lãi lỗ.
Ví dụ: đối với sổ nhật ký thu tiền.
Trước tiên, bạn xây dựng 1 sheet mới đặt tên là NK_THUTIEN, tại sheet này bạn tạo tên các cột dữ liệu theo mẫu, bao gồm: tên sổ, số hiệu, tài khoản, ngày ghi sổ, chứng từ (số, ngày), diễn giải, ghi nợ tài khoản, ghi có tài khoản (đối ứng) số tiền và số hiệu đi kèm. Định dạng bảng tính cho đúng yêu cầu. Kế tiếp, bạn xây dựng các công thức tính toán cho các cột số liệu (lấy từ Sổ kế toán máy) như sau:
Ngày ghi sổ:
=IF(LEFT(SoKTM!I4,3)=”111", SoKTM!C4,””)
Cột Chứng từ (số, ngày), diễn giải lập công thức tương tự chỉ khác cột giá trị trả về.
Ghi nợ tài khoản:
=IF(LEFT(SoKTM!$I4,3)=”111", SoKTM!L4,””)
Ghi có tài khoản (đối ứng):
=IF(AND(LEFT(SoKTM!$I4,3)=”111",LEFT(SoKTM!$J4,3)=NK_ Thutien!F$5),NK_Thutien!$E9,””)
Tổng cộng số tiền ghi nợ:
=SUMIF(TKGHINO,”111*”,SOTIENPS)
Số tiền ghi có (tài khoản đối ứng):
=SUMIF(TKNOCO,”111*”& F$5&”*”,SOTIENPS)
Sau khi tính toán xong 1 hàng, bạn cần sao chép công thức tính toán của nó xuống các hàng bên dưới (tùy thuộc vào số lượng nghiệp vụ kinh tế phát sinh bên Sổ kế toán máy), bạn tiến hành trích lọc dữ liệu để lấy kết quả bằng cách chọn bôi đen hàng số liệu được đánh số từ 1- 12 đến hết bảng tính, chọn tiếp menu Data > Filter > Auto Filter > chọn nút lệnh xổ xuống ở ô số 4 (diễn giải) chọn tiếp lệnh Non Blanks.
Các hàng có dữ liệu sẽ được trích lọc riêng thành 1 bảng có chứa dữ liệu. Các sổ còn lại, các bạn cũng lập tương tự (tham khảo thêm ở file gốc đính kèm).
Trên đây, chỉ là một vài hướng dẫn mang tính gợi ý, các bạn có thể tham khảo thêm tài liệu (sách tham khảo) hoặc trên các diễn đàn về kế toán máy để hiểu thêm và có thể xây dựng cho riêng mình một chương trình kế toán Excel như ý.
VÕ NGỌC HIỆP