Cách lập bảng phân bổ chi phí trả trước trên Excel

Trong bài viết này

1. Bảng phân bổ chi phí trả trước dùng để kiểm tra gì?
2. Chuẩn bị chứng từ và chốt nguyên tắc trước khi nhập Excel
3. Chọn giá trị ghi nhận và thời điểm bắt đầu
4. Chọn số kỳ và phương pháp nhất quán
5. Thiết kế bảng phân bổ chi phí trả trước trên Excel
6. Nhập công thức tính mức tháng, lũy kế và số còn lại
7. Tính số dư trước phân bổ và mức tháng
8. Chặn phân bổ ngoài kỳ và xử lý tháng cuối
9. Bài tập phân bổ chi phí trả trước có đáp án theo tháng
10. Xử lý khoản bắt đầu sử dụng giữa tháng
11. Chuyển số dư sang kỳ sau và nối dữ liệu cuối năm
12. Lập bút toán phân bổ theo đúng bộ phận sử dụng
13. Đối chiếu bảng Excel với TK 242 trước khi khóa sổ
14. Sửa những lỗi thường gặp khi lập bảng phân bổ
15. Tự luyện theo lộ trình chứng từ, bảng tính rồi đến sổ
16. Kiểm tra lần cuối trước khi dùng bảng cho kỳ tiếp theo

Cách lập bảng phân bổ chi phí trả trước là theo dõi từng khoản bằng mã riêng, xác định giá trị và số kỳ, rồi tính phần đưa vào chi phí mỗi tháng. Sau đó, bạn kiểm tra lũy kế, số còn lại và đối chiếu với TK 242 trước khi ghi sổ.

Nếu cần tư vấn học thực hành phần hành này, bạn có thể Gọi 0818 552 558 cho Trung Tâm Đào Tạo Kế Toán Biên Hòa và nói rõ mình đang vướng ở bảng tính hay bút toán.

Bảng phân bổ chi phí trả trước dùng để kiểm tra gì?

Bảng phân bổ cho biết một khoản đã ghi nhận được đưa vào chi phí bao nhiêu trong kỳ và còn bao nhiêu để theo dõi tiếp. Một dòng chỉ có số tiền chia cho số tháng thì chưa đủ. Bạn còn cần biết khoản đó thuộc chứng từ nào và đã phân bổ đến đâu.

Ví dụ giả định: tiền thuê văn phòng phục vụ nhiều tháng được ghi nhận vào TK 242. Số tiền thanh toán không phải toàn bộ chi phí của riêng tháng trả tiền. Bảng theo dõi giúp nối giá trị ban đầu với phần được tính cho từng kỳ hưởng dịch vụ.

Ngược lại, khoản đặt cọc có thể thu hồi không tự động trở thành chi phí trả trước. Tiền ứng trước cho người bán cũng cần xét bản chất giao dịch và hồ sơ nhận hàng, nhận dịch vụ. Đừng đưa mọi khoản đã chuyển tiền vào cùng một bảng.

Ở mỗi mã, hãy nhìn được ba số: giá trị ghi nhận ban đầu, tổng đã phân bổ và phần còn lại. Quan hệ giữa chúng phải khớp. Bảng thu chi chỉ giải thích tiền đi đâu; bảng phân bổ giải thích chi phí thuộc kỳ nào.

Chuẩn bị chứng từ và chốt nguyên tắc trước khi nhập Excel

Trước khi viết công thức, bạn cần chốt giá trị, ngày bắt đầu và số kỳ từ hồ sơ. Sai đầu vào thì phép tính vẫn chạy, nhưng kết quả không phản ánh đúng khoản chi.

Chọn giá trị ghi nhận và thời điểm bắt đầu

Hồ sơ làm việc thường gồm hợp đồng, hóa đơn, biên bản bàn giao hoặc căn cứ bắt đầu sử dụng và chứng từ thanh toán liên quan. Gắn chúng với một mã theo dõi để khi kiểm lại bạn tìm được đúng bộ hồ sơ.

Giá trị đưa vào bảng là số đã xác định để ghi nhận chi phí trả trước. Không máy móc lấy tổng thanh toán hoặc luôn loại toàn bộ thuế trên hóa đơn. Phần thuế đủ điều kiện khấu trừ được xử lý riêng; phần không được khấu trừ cần xem cách ghi nhận phù hợp.

Ngày chuyển tiền có thể sớm hơn ngày bắt đầu hưởng dịch vụ. Với công cụ dụng cụ, kiểm thêm thời điểm xuất dùng. Khoản thiếu căn cứ cần được làm rõ trước khi phân bổ.

Chọn số kỳ và phương pháp nhất quán

Số kỳ căn cứ vào tính chất khoản chi và khoảng thời gian hưởng lợi phù hợp, không mặc định mọi khoản cùng một thời hạn. Các ví dụ hạch toán ở đây đặt trong phạm vi Thông tư 133/2016/TT-BTC; khi áp dụng, bạn kiểm tra chế độ và chính sách kế toán của doanh nghiệp.

Mô hình chính giả định bắt đầu đầu tháng, phân bổ đều theo tháng. Tình huống giữa tháng dùng phương pháp ngày thực tế và được tách riêng bên dưới.

Thiết kế bảng phân bổ chi phí trả trước trên Excel

Một bảng phân bổ chi phí trả trước trên Excel nên tách cột nhập liệu khỏi cột tính toán, đồng thời giữ mã ổn định qua các tháng. Bản thực hành này dùng ô B1 làm ngày đầu kỳ, chẳng hạn nhập bằng công thức =DATE(2026,9,1).

Đặt tiêu đề cột tại dòng 5 và bắt đầu dữ liệu ở dòng 6. Chỉ đưa vào bảng kỳ này những khoản đã được ghi nhận đến cuối kỳ. Khoản đã ghi nhận nhưng chưa đến thời gian phân bổ vẫn cần theo dõi số còn lại.

Cột	Nội dung	Cách dùng
A	Mã khoản chi	Nhập, không trùng
B	Tên khoản chi	Nhập từ hồ sơ
C	Ngày bắt đầu phân bổ	Đầu tháng trong bài tập chính
D	Tổng số tháng	Số nguyên dương
E	Giá trị ban đầu	Giá trị đã ghi nhận
F	Lũy kế đến cuối kỳ trước	Lấy từ bảng đã chốt
G	Còn lại trước phân bổ	E trừ F
H	Mức tháng thông thường	E chia D, làm tròn
I	Phân bổ kỳ này	Tính có điều kiện
J	Lũy kế cuối kỳ	F cộng I
K	Còn lại cuối kỳ	E trừ J
L	Tài khoản chi phí	Nhập theo đối tượng

Mỗi mã trong mẫu chỉ gắn một đối tượng sử dụng. Hồ sơ nguồn có thể giữ ở sheet riêng, gồm số chứng từ, ngày ghi nhận và bộ phận. Cách tách dữ liệu gốc và báo cáo trong Excel kế toán cũng hữu ích cho việc kiểm lại bảng này.

Nhập công thức tính mức tháng, lũy kế và số còn lại

Công thức cần tính đúng mức tháng, dừng đúng thời gian và giữ số phân bổ không vượt phần còn lại. Các công thức sau dùng dấu phẩy ngăn cách đối số; nếu Excel của bạn yêu cầu dấu chấm phẩy, hãy đổi dấu phân cách.

Tính số dư trước phân bổ và mức tháng

Tại G6 nhập =E6-F6. Tại H6 nhập =ROUND(E6/D6,0). Đơn vị bài tập là đồng, làm tròn đến số nguyên. Trước khi kéo công thức, kiểm tra D6 lớn hơn 0 và F6 nằm trong khoảng từ 0 đến E6.

Cột F chỉ chứa số đã phân bổ trước kỳ hiện tại. Khoản mới ghi nhận có F bằng 0. Không điền phần còn lại vào F và không thay E bằng số dư đầu tháng.

Chặn phân bổ ngoài kỳ và xử lý tháng cuối

Tại I6 nhập công thức sau, áp dụng riêng cho mô hình bắt đầu đầu tháng:

=IF(OR($B$1<C6,$B$1>=EDATE(C6,D6)),0,IF(EDATE($B$1,1)=EDATE(C6,D6),G6,MIN(G6,H6)))

Hàm EDATE dịch ngày theo số tháng. Điều kiện đầu trả về 0 nếu kỳ đang xem chưa bắt đầu hoặc đã hết thời hạn. Đến tháng cuối, công thức lấy toàn bộ phần còn lại; những tháng khác lấy mức nhỏ hơn giữa G6 và H6.

Tiếp theo, J6 nhập =F6+I6 và K6 nhập =E6-J6. Dấu $ giữ cố định ô kỳ khi sao chép xuống. Cần chuyển đúng lũy kế mỗi tháng; chỉ đổi B1 không đủ để tái tính lịch sử.

Ví dụ giả định giá trị 10.000.000 chia 3 tháng: hai tháng đầu mỗi tháng 3.333.333, tháng cuối 3.333.334. Tổng vẫn bằng giá trị ban đầu. Nếu từng bỏ sót phân bổ, bạn phải kiểm tra và xử lý kỳ thiếu trước; không coi công thức tháng cuối là cách tự sửa sai sót.

Bài tập phân bổ chi phí trả trước có đáp án theo tháng

Bài tập phân bổ chi phí trả trước dưới đây dùng hai khoản giả định, cùng bắt đầu ngày 01/09/2026 và cùng phục vụ bộ phận quản lý. Các số tiền chỉ phục vụ luyện tính, không phải giá khóa học hay chi phí của một khách hàng thật.

Khoản VP01 là tiền thuê văn phòng, giá trị ghi nhận 18.000.000, phân bổ 6 tháng. Khoản DC01 là công cụ văn phòng xuất dùng, giá trị ghi nhận 9.600.000, phân bổ 12 tháng. Bài tập bỏ qua phần thuế của giao dịch đầu vào để tập trung vào giá trị đã ghi nhận.

Nhập VP01 ở dòng 6 và DC01 ở dòng 7. Cả hai có F bằng 0, cột L là 6422. Với kỳ tháng 9, bạn nên tự tính bằng phép chia trước khi xem đáp án.

Mã	Giá trị gốc	Phân bổ tháng 9	Còn cuối tháng 9
VP01	18.000.000	3.000.000	15.000.000
DC01	9.600.000	800.000	8.800.000
Tổng	27.600.000	3.800.000	23.800.000

Sang tháng 10, F của VP01 là 3.000.000 và F của DC01 là 800.000. Mức phân bổ tháng 10 vẫn lần lượt là 3.000.000 và 800.000. Lũy kế cuối tháng 10 đạt 7.600.000, số còn lại là 20.000.000.

Thử đổi số tháng của một bản sao bài tập rồi tính lại từ kỳ đầu. Nếu chỉ sửa thời hạn giữa chừng trong bảng đang dùng, số lũy kế cũ và lịch phân bổ mới có thể không còn nhất quán.

Xử lý khoản bắt đầu sử dụng giữa tháng

Với khoản bắt đầu giữa tháng, cần tính theo phương pháp doanh nghiệp đã lựa chọn, thay vì đưa ngày thực tế vào công thức tròn tháng rồi mặc nhiên chấp nhận kết quả. Một cách luyện riêng là phân bổ đều theo số ngày hưởng dịch vụ.

Một tình huống khác: giả định dịch vụ có giá trị ghi nhận 9.100.000, sử dụng từ ngày 15/09/2026 đến hết ngày 14/12/2026. Bài này tính cả ngày đầu và ngày cuối. Tổng thời gian là 91 ngày; mức mỗi ngày bằng 9.100.000 chia 91, tức 100.000.

Tháng 9 có 16 ngày thuộc khoảng dịch vụ, nên phân bổ 1.600.000. Tháng 10 có 31 ngày, phân bổ 3.100.000; tháng 11 có 30 ngày, phân bổ 3.000.000. Tháng 12 còn 14 ngày, phân bổ 1.400.000. Cộng bốn kỳ phải ra 9.100.000.

Trên một sheet riêng, đặt ngày bắt đầu dịch vụ ở B2, ngày kết thúc ở C2; ngày đầu kỳ ở E2 và ngày cuối kỳ ở F2. Công thức đếm ngày giao nhau là =MAX(0,MIN(C2,F2)-MAX(B2,E2)+1). Mức phân bổ kỳ bằng số ngày giao nhau nhân giá trị gốc, chia cho tổng số ngày dịch vụ.

Nếu chia không hết, làm tròn số tiền từng kỳ và điều chỉnh phần lẻ ở kỳ cuối. Đây là mô hình khác với chia đều từng tháng. Khi đối chiếu với phần mềm, bạn phải kiểm tra cùng cách tính tháng đầu, tháng cuối.

Chuyển số dư sang kỳ sau và nối dữ liệu cuối năm

Sang kỳ mới, giữ giá trị ban đầu và tổng số kỳ, rồi chuyển lũy kế cuối kỳ trước vào cột F theo đúng mã. Phần còn lại được tính từ đó. Không lấy số dư còn lại chia lại cho toàn bộ số tháng ban đầu.

Với VP01, sau tháng 9 còn 15.000.000. Nếu chia tiếp 15.000.000 cho 6, mức tháng 10 thành 2.500.000 và lịch ban đầu bị kéo dài. Cách đúng trong bài tập là giữ E bằng 18.000.000, D bằng 6 và chuyển 3.000.000 vào F.

Khi hai bảng không còn cùng thứ tự dòng, đừng sao chép lũy kế bằng vị trí. Hãy tra theo mã, kiểm mã trùng và xem riêng các mã chưa tìm thấy. Một kết quả tra cứu trả về 0 chưa đủ để kết luận đó là khoản mới.

Cuối năm cũng nối dữ liệu như chuyển tháng. Chẳng hạn sau tháng 12, VP01 đã phân bổ 12.000.000, còn 6.000.000; DC01 đã phân bổ 3.200.000, còn 6.400.000. Tổng số dư chuyển sang kỳ tiếp theo là 12.400.000.

Danh mục đã hết phân bổ vẫn nên giữ trong bản lưu để tra lịch sử. Nếu loại khỏi bảng làm việc kỳ mới, ghi rõ lý do và kiểm lại danh sách mã. Bản đã khóa sổ cần được lưu riêng trước mỗi lần chỉnh công thức.

Lập bút toán phân bổ theo đúng bộ phận sử dụng

Bút toán phân bổ ghi tăng chi phí của bộ phận sử dụng và ghi giảm khoản đang chờ phân bổ. Các bút toán minh họa sau dùng hệ thống tài khoản theo Thông tư 133/2016/TT-BTC, với đối tượng là bộ phận quản lý.

Trong bài tập, tiền thuê đã đủ căn cứ ghi nhận, thanh toán qua ngân hàng và bỏ qua phần thuế: Nợ TK 242: 18.000.000 / Có TK 1121: 18.000.000. Công cụ văn phòng đã nhập kho và nay xuất dùng: Nợ TK 242: 9.600.000 / Có TK 153: 9.600.000.

Cuối tháng 9, căn cứ bảng phân bổ, ghi Nợ TK 6422: 3.800.000 / Có TK 242: 3.800.000. Có thể tách chứng từ theo từng mã để kiểm tra. Không ghi Có tài khoản tiền thêm lần nữa khi phân bổ vì đây không phải một lần thanh toán mới.

Khi bảng có nhiều tài khoản chi phí, tổng hợp số phân bổ theo tài khoản rồi đối chiếu với chứng từ. Ví dụ cột L lưu tài khoản dưới dạng văn bản, tổng cho 6422 dùng =SUMIFS(I6:I100,L6:L100,”6422″). Hàm SUMIFS cộng các dòng thỏa điều kiện, nên vùng cộng và vùng điều kiện phải tương ứng.

Khoản dùng cho nhiều bộ phận cần bảng chi tiết phân chia và tiêu thức có căn cứ. Tổng phân chia phải bằng số phân bổ của khoản đó. Mẫu một dòng một đối tượng chưa tự thực hiện việc này.

Đối chiếu bảng Excel với TK 242 trước khi khóa sổ

Hãy đối chiếu cả số phân bổ trong kỳ lẫn số còn lại cuối kỳ. Tổng khớp nhưng một mã bị thiếu và một mã bị thừa vẫn có thể làm sai chi phí của từng bộ phận.

Cầu nối cần kiểm là: số dư đầu kỳ + phát sinh tăng − phân bổ − giảm khác = số dư cuối kỳ. Trong bài tập tháng 9, đầu kỳ bằng 0, tăng 27.600.000, phân bổ 3.800.000 và không có giảm khác. Số dư cuối kỳ là 23.800.000, bằng tổng cột K.

Cột E là giá trị ban đầu của danh mục, không phải tổng phát sinh Nợ TK 242 riêng tháng đang xem. Đến tháng 10, E vẫn giữ 27.600.000 dù bài tập không phát sinh khoản mới. Đừng lấy tổng nguyên giá để thay cho số tăng trong kỳ.

Riêng cột G không hoàn toàn là số dư đầu kỳ: với khoản mới trong tháng, G bao gồm giá trị vừa ghi nhận. Khi lập cầu nối, cần tách khoản có từ trước và khoản tăng trong kỳ theo ngày ghi sổ ở bảng nguồn.

So từng mã với sổ chi tiết, cộng cột I để kiểm bút toán phân bổ và cộng cột K để kiểm số dư Nợ cuối kỳ TK 242. Khi so với báo cáo MISA, chọn cùng kỳ, đơn vị và phạm vi dữ liệu. Chênh lệch phải được tìm về mã hoặc chứng từ trước khi sửa bảng.

Sửa những lỗi thường gặp khi lập bảng phân bổ

Hãy sửa từ dữ liệu nguồn đến công thức, sau đó mới xem bút toán. Gõ thêm một số điều chỉnh để tổng khớp thường khiến kỳ sau khó kiểm hơn.

Ngày đang ở dạng chữ: ô nhìn như ngày nhưng phép so sánh hoặc EDATE trả lỗi. Thử =ISNUMBER(C6); ngày Excel hợp lệ cho kết quả TRUE. Kiểm tra thêm thứ tự ngày, tháng khi nhập dữ liệu từ tệp khác.

Mã bị lặp: hai khoản cùng mã làm việc tra lũy kế dễ lấy nhầm dòng. Kiểm bằng =COUNTIF(A6:A100,A6), kết quả lớn hơn 1 cần xem lại. Không đổi mã khoản cũ chỉ để bảng nhìn đồng nhất hơn.

Tổng thiếu dòng mới: công thức chỉ cộng đến dòng 100 trong khi dữ liệu đã vượt phạm vi. Bổ sung đồng thời vùng tổng và vùng điều kiện, hoặc tổ chức dữ liệu thành Excel Table. Luôn kiểm dòng cuối sau khi thêm khoản.

Phân bổ âm hoặc vượt giá trị: xem lại F có lớn hơn E không, công thức có bị thay bằng số nhập tay không và kỳ đang tính đã vượt thời hạn chưa. Không dùng hàm che lỗi trên toàn bảng trước khi biết nguyên nhân.

Chi phí kế toán và điều kiện được trừ khi tính thuế là hai việc cần kiểm riêng. Không tự thay thời hạn chỉ vì nghe một con số phổ biến. Với hồ sơ thật, cần đối chiếu quy định hiện hành và căn cứ của từng khoản.

Tự luyện theo lộ trình chứng từ, bảng tính rồi đến sổ

Lộ trình luyện phần hành này nên bắt đầu từ việc đọc hồ sơ, sau đó lập bảng và cuối cùng giải thích bút toán bằng chính số liệu đã tính. Nếu chỉ nhớ hàm, bạn sẽ khó nhận ra một khoản nhập sai ngày hoặc sai đối tượng.

Ở lượt đầu, tự nhập hai khoản của bài tập và tính tháng 9 bằng tay. Lượt tiếp theo, chuyển sang tháng 10, tra lũy kế theo mã và đối chiếu số dư. Sau đó mới thử khoản giữa tháng trên sheet riêng. Mỗi lần thêm tình huống, giữ lại bản đã tính đúng để so sánh.

Phần tham chiếu ô hoặc tổ chức dữ liệu còn khó thì bạn có thể xem nội dung khóa học Excel ứng dụng cho kế toán. Nếu chưa nối được chứng từ với định khoản, khóa học kế toán tổng hợp thực hành tại Biên Hòa là nội dung liên quan để tìm hiểu.

Về học phí và lộ trình phù hợp, thông tin được hướng dẫn tại trang học phí khóa học kế toán và tư vấn lộ trình. Kết quả học phụ thuộc nền tảng và thời gian luyện tập của mỗi người; bạn nên mô tả đúng phần mình đang làm để được tư vấn.

Muốn trao đổi lộ trình luyện từ bảng Excel đến sổ chi tiết, bạn có thể Gọi 0818 552 558 và cho biết mình đã biết định khoản hay mới bắt đầu học.

Kiểm tra lần cuối trước khi dùng bảng cho kỳ tiếp theo

Trước khi lưu bảng, hãy kiểm lại kỳ đang chọn, dữ liệu chuyển tiếp và số liệu đã ghi sổ. Một bảng dùng được cần giúp bạn lần ngược từ số tổng về từng khoản, rồi từ từng khoản về chứng từ.

1. Kiểm ô kỳ là ngày đầu tháng và phương pháp tính phù hợp với ngày bắt đầu sử dụng.
2. Đối chiếu mã, giá trị gốc, số tháng và lũy kế với hồ sơ hoặc bản kỳ trước đã chốt.
3. Kiểm tổng phân bổ với chứng từ ghi sổ, tổng còn lại với sổ chi tiết TK 242.
4. Lưu phiên bản đã chốt trước khi chuyển lũy kế và đổi kỳ.

Mẫu thực hành chưa tự xử lý việc thanh lý, nhận lại tiền, thay đổi thời gian phân bổ hoặc phân chia một khoản cho nhiều bộ phận. Khi phát sinh các tình huống này, cần thêm dữ liệu điều chỉnh và kiểm bút toán riêng. Đừng xóa khoản đang còn số dư để làm gọn danh sách.

Để tự kiểm mức hiểu bài, thử giải thích vì sao số phân bổ tháng 10 vẫn bằng tháng 9 trong bài tập, trong khi số còn lại đã giảm. Khi trả lời được bằng giá trị gốc, số kỳ và lũy kế, bạn có thể kiểm tra bảng chủ động hơn.

Trung Tâm Đào Tạo Kế Toán Biên Hòa ở 91 Đoàn Văn Cự, phường Tam Hiệp, Đồng Nai (Biên Hòa, Đồng Nai cũ). Nếu cần tư vấn học thực hành theo phần đang vướng, bạn có thể Gọi 0818 552 558 và mô tả bước mình chưa tự kiểm được.