Lập Bảng Cân đối Số Phát Sinh Trên Excel Cực Kỳ đơn Giản

Lập bảng Cân đối số phát sinh trên Excel cực kỳ đơn giản 22/08/2017 05:13

Là một kế toán bạn không thể bỏ qua việc làm Cân đối số phát sinh (CĐSPS) hàng tháng, hàng quý và năm.Với một số doanh nghiệp hoặc công ty nhỏ chưa sử dụng phần mềm thì vẫn sử dụng Excel để làm bảng Cân đối số phát sinh. Sau đây, kế toán Đức Minh sẽ hướng dẫn bạn đọc "Lập bảng Cân đối số phát sinh trên Excel cực kỳ đơn giản"

Bảng cân đối số phát sinh hay còn gọi là Bảng cân đối tài khoản dùng để phản ánh tổng hợp số hiện có đầu năm, số phát sinh tăng, giảm trong năm và số hiện có cuối năm được phân loại theo tài khoản kế toán của các loại tài sản, nguồn vốn chủ sở hữu, nợ phải trả, doanh thu, thu nhập khác, chi phí và kết quả hoạt động kinh doanh của doanh nghiệp.

1. Cách lập bảng cân đối số phát sinh tài khoản chi tiết từng chỉ tiêu

Ví dụ về mẫu bảng Cân đối số phát sinh như sau:

Mẫu bảng Cân đối số phát sinh

Cột 1: Số hiệu tài khoản:

Ghi số hiệu của từng Tài khoản cấp 1 (hoặc cả tài khoản cấp 1 và cấp 2) doanh nghiệp đang sử dụng trong năm báo cáo

Cột 2: Tên tài khoản:

Ghi tên của từng tài khoản theo thứ tự từng loại mà DN đang sử dụng.

Cột 3, 4 Số dư đầu kỳ:

– Phản ánh số dư Nợ đầu năm và dư Có đầu năm theo từng tài khoản.

– Số liệu để ghi được căn cứ vào sổ cái hoặc nhật ký – sổ cái, hoặc căn cứ vào số liệu ghi ở cột 7, 8 của Bảng cân đối tài khoản năm trước.

Cột 5, 6 Số phát sinh trong kỳ:

– Căn cứ vào tổng số phát sinh bên Nợ, số phát sinh bên Có của từng tài khoản trong kỳ báo cáo.

– Số liệu để ghi được căn cứ vào tổng số phát sinh bên Nợ và tổng số phát sinh bên Có của từng tài khoản ghi trên sổ cái hoặc nhật ký sổ cái trong kỳ báo cáo.

Số 7, 8 Số dư cuối kỳ

– Dùng để phản ánh số dư Nợ cuối kỳ và số dư Có cuối kỳ theo từng khoản mục của kỳ báo cáo.

– Số liệu ghi được tính như sau:

Số dư cuối kỳ = Số dư đầu kỳ + Số phát sinh tăng – Số phát sinh giảm

2. Cách lập bảng cân đối số phát sinh trên Excel

Hướng dẫn cách lập bảng cân đối tài khoản trên Excel theo mẫu như hình minh họa ở phần 1

Các bước cụ thể như sau:

2.1. Cột Mã tài khoản, Tên Tài khoản: Dùng hàm Vlookup hoặc Copy từ Danh mục tài khoản về (Lưu ý với tài khoản cấp 2 của các tài khoản 333)

Lưu ý: Phải đảm bảo rằng danh mục tài khoản luôn được cập nhật thường xuyên các TK về Khách hàng và phải đầy đủ nhất.

2.2. Cột dư Nợ đầu kỳ và dư Có đầu kỳ: Dùng hàm Vlookup tìm ở phần số dư đầu kỳ của bảng cân đối phát sinh của Tháng 1 hoặc số dư cuối kỳ trước về (Phần dư đầu kỳ)

Ví dụ: Năm 2014, doanh nghiệp cũng sử dụng mẫu Bảng cân đối số phát sinh như trên. Các bạn sử dụng hàm Vlookup tìm kiếm giá trị số dư đầu kỳ bên Nợ của Tài khoản 1111 như sau:

Hướng dẫn cách lập bảng cân đối tài khoản trên Excel 1

Tại cột 3, số dư Nợ đầu kỳ của Tài khoản 1111

=VLOOKUP(A7,’CĐPS 2014’!$A$4:$H$142,7,1)

Trong đó:

A7: là Mã Tài khoản

‘CĐPS 2014’!$A$4:$H$142: Là bảng cân đối số phát sinh năm 2014

7: Giá trị ô cần lấy trong bảng CĐPS năm 2014, trong trường hợp này là số dư Nợ cuối năm 2014

1: Dò tìm giá trị tương đối

Cách làm tương tự với số dư Có đầu kỳ, Giá trị cần lấy là ô số 8 trong bảng CĐPS năm 2014

Các tài khoản khác, các bạn chỉ cần copy công thức xuống

(Giá trị đầu kỳ năm 2015 là giá trị cuối kỳ năm 2014 chuyển sang)

2.3 Cột phát sinh Nợ, Phát sinh Có trong kỳ: Sử dụng hàm SUMIF tổng hợp số liệu từ bảng Nhật ký chung của từng tháng sang (dãy ô điều kiện vẫn là cột TK Nợ/ TK Có).

Ví dụ: Năm 2014, doanh nghiệp cũng sử dụng mẫu Bảng cân đối số phát sinh như trên. Các bạn sử dụng hàm SUMIF để tính tổng số phát sinh trong kỳ bên Có của Tài khoản 1111 như sau:

Hướng dẫn cách lập bảng cân đối tài khoản trên Excel 2

Tại cột 6, Số phát sinh trong kỳ bên Có của Tài Khoản 1111:

=SUMIF(‘NKC&SC’!H4:H2250,CĐPS2015!A7,’NKC & SC’!I4:I2250)

Trong đó:

‘NKC & SC’!H4:H2250: là cột bên Có của sổ Nhật ký chung

CĐPS2015!A7: Tài khoản cần tính tổng là Tài khoản 1121

NKC & SC’!I4:I2250: Vùng cần tính tổng tức là số tiền phát sinh của sổ Nhật ký chung

Cách làm tượng tự như cột phát sinh trong kỳ bên Nợ của Tài khoản 1111

Các tài khoản khác, các bạn chỉ cần copy công thức xuống

2.4 Số dư cuối kỳ

Cột Nợ = Max (Số dư Nợ đầu kỳ + Số PS Nợ trong kỳ – Số dư Có đầu kỳ – Số PS Có trong kỳ,0)

Cột Có = Max (Số dư Có đầu kỳ + Số PS Có trong kỳ – Số dư Nợ đầu lỳ – Số PS Nợ trong kỳ, 0)

Ví dụ, Năm 2014, doanh nghiệp cũng sử dụng mẫu Bảng cân đối số phát sinh như trên. Các bạn sử dụng hàm Max tìm để tính giá trị Số dư Nợ cuối kỳ của Tài khoản 1111:

Hướng dẫn cách lập bảng cân đối tài khoản trên Excel 3

Tại cột 7, Số dư cuối năm bên Nợ của Tài khoản 111:

=MAX(C7+E7-D7-F7,0)

Trong đó:

C7: Số dư đầu kỳ bên Nợ của Tài khoản 1111

E7: Phát sinh trong kỳ bên Nợ của Tài khoản 1111

D7: Số dư đầu kỳ bên Có của Tài khoản 1111

F7: Phát sinh trong kỳ bên Có của Tài khoản 1111

Các tài khoản khác, các bạn chỉ cần copy công thức xuống

Đối với bên Có của các Tài khoản, các bạn áp dụng công thức như đã nêu ở trên nhé

2.5. Dùng hàm SUBTOTAL tính lại tổng cho từng TK cấp 1 (Chỉ cần tính cho những tài khoản có phát sinh)

Cú pháp = Subtotal(9, dãy ô cần tính)

Lưu ý: Sử dụng hàm SUBTOTAL cho TK 333

Ví dụ, Năm 2014, doanh nghiệp cũng sử dụng mẫu Bảng cân đối số phát sinh như trên. Các bạn sử dụng hàm SUBTOTAL để tính tổng cho từng TK cấp 1 của Tài khoản 1111:

Hướng dẫn cách lập bảng cân đối tài khoản trên Excel 4

Các Tài khoản còn lại, các bạn áp dụng tương tự như Tài khoản 1111

3. Những lưu ý khi lập xong bảng cân đối phát sinh tài khoản

Trên CĐPS thì tổng phát sinh bên Nợ phải bằng tổng phát sinh bên Có

Các tài khoản loại 1 và loại 2 không có số dư bên Có. Trừ một số tài khoản như 131, 214,.,

Các tài khoản loại 3 và loại 4 không có số dư bên Nợ, trừ một số tài khoản như 331, 3331, 421,..

Các tài khoản loại 5 đến loại 9 cuối kỳ không có số dư.

TK 112 phải khớp với Sổ phụ ngân hàng/Sao kê ngân hàng

TK 133, 3331 phải khớp với chỉ tiêu trên tờ khai thuế

TK 131 và TK 331 phải khớp với bảng tổng hợp công nợ

TK 152, 153, 155, 156 phải khớp với dòng tổng cộng trên Báo cáo NXT kho

TK 211, 213, 214 phải khớp với dòng tổng cộng trên Bảng khấu hao 211, 213

Tham khảo thêm một số bài viết về kế toán tại website: http://ketoanducminh.edu.vn

- Ngọc Anh –

>>> Cách lập bảng cân đối số phát sinh

>>> 3 bước xử lý khi bảng cân đối số phát sinh không cân

>>> Hướng Dẫn Hạch Toán Kế Toán Trên Excel Với Các Nghiệp Vụ Phát Sinh Tháng

>>> Học kế toán trên excel "Hướng dẫn vào sổ kế toán theo hình thức nhật ký chung"

>>> Hướng dẫn lập bảng cân đối kế toán

Với mục tiêu “Sự thành công của học viên là niềm tự hào của Đức Minh”, Công ty đào tạo kế toán và tin học Đức Minh là nơi đào tạo kế toán thực tế và tin học văn phòng uy tín và chuyên nghiệp nhất Hà Nội hiện nay. Đức Minh luôn sẵn sàng hỗ trợ hết mình vì học viên, luôn đồng hành cùng học viên trên bước đường đi tới thành công.

Lịch học dạy kèm linh động từ thứ 2 đến thứ 7 hàng tuần cho tất cả các học viên:

Ca 1: Từ 8h -> 11h30 * Ca 2: Từ 13h30 -> 17h * Ca 3: Từ 18h -> 20h

Bảng giá khóa học

TỔ CHỨC THI VÀ CẤP CHỨNG CHỈ CỦA VIỆN KẾ TOÁN ĐỨC MINH

Mọi chi tiết vui lòng liên hệ:

HỌC VIỆN ĐÀO TẠO KẾ TOÁN - TIN HỌC ĐỨC MINH

Cơ Sở 1: Tầng 2 - Tòa nhà B6A Nam Trung Yên - đường Nguyễn Chánh – Cầu Giấy HN - 0339.156.806 Cơ Sở 2: P902 tầng 9 tòa Licogi 12 . Số 21 Đại Từ - Đại Kim ( đối diện khu chung cư Eco Lake View) - Hoàng Mai - Hà Nội. ĐT / ZALO: 0342.254.883 Cơ Sở 3: Phòng 2516, tầng 25, tòa nhà SDU số 143 Trần Phú, Hà Đông,HN (ngay điểm ga tàu điện Văn Quán) - 0339.421.606

• Chia sẻ
• Tweet