Phân bổ chi phí chung cho từng cửa hàng theo doanh thu bằng Excel, xem cửa hàng nào lãi thật

ExcelNâng cao Video 2:59

Bài toán

Báo cáo từng cửa hàng tháng nào cũng lãi, cộng cả công ty lại lỗ: tiền thuê văn phòng, lương quản lý, quảng cáo chung chưa chia về cửa hàng nào.

Làm xong được gì

Bảng lãi lỗ từng cửa hàng sau khi chia chi phí chung theo doanh thu; cộng phần đã chia đúng bằng tổng chi phí chung, không lệch một đồng.

Dùng trong bài

SUMIFSROUNDAutoSumdòng kiểm

File thực hành gồm

PhanBoChiPhi_T09.xlsx
Sheet DoanhThu có 150 dòng doanh thu tháng 9, mỗi ngày mỗi cửa hàng một dòng. Sheet ChiPhi có 71 dòng chi phí, trong đó 14 dòng chi phí chung để trống cột Cửa hàng. Hai sheet này giữ hình dạng file xuất từ phần mềm: ba dòng tiêu đề gộp ô, dòng 4 là tên cột, cuối sổ có dòng Tổng cộng. Sheet PhanBo là khung bảng năm cửa hàng, chưa có công thức.
PhanBoChiPhi_T09_dap_an.xlsx
Bản đã gắn công thức, mở ra để so kết quả.

Các bước làm

1
Cộng chi phí chung cần chia
Ở sheet PhanBo, bấm ô F3 và gõ công thức dưới đây: cộng cột Số tiền bên sheet ChiPhi, điều kiện là cột Cửa hàng để trống (hai dấu nháy kép gõ liền nhau). Enter, ô F3 ra 2.921.019.344, to hơn cả doanh thu của tháng. Sang sheet ChiPhi bấm Ctrl+End: dòng 76 Tổng cộng cũng để trống cột Cửa hàng nên bị cộng luôn.
=SUMIFS(ChiPhi!G:G,ChiPhi!F:F,"")
2
Loại dòng Tổng cộng
Về ô F3, bấm F2 để sửa, thêm một điều kiện vào trước dấu ngoặc đóng: cột Số chứng từ khác trống. Dòng Tổng cộng không có số chứng từ nên bị loại. Ô F3 còn 377.039.000, đúng bằng các khoản chung.
=SUMIFS(ChiPhi!G:G,ChiPhi!F:F,"",ChiPhi!B:B,"<>")
3
Doanh thu từng cửa hàng
Ô B6 (doanh thu của Cầu Giấy) cộng cột Doanh thu chưa thuế bên sheet DoanhThu, điều kiện là cột Cửa hàng bằng tên ở cột A. Enter rồi kéo nút fill từ B6 xuống B10 cho đủ năm cửa hàng.
=SUMIFS(DoanhThu!D:D,DoanhThu!B:B,A6)
4
Chi phí riêng và lãi trước phân bổ
Ô C6 gõ công thức dưới đây rồi kéo xuống C10. Giá vốn, mặt bằng, lương nhân viên cửa hàng đều nằm trong số này. Ô D6 gõ =B6-C6 rồi kéo xuống D10: năm cửa hàng đều lãi. Bôi B11:D11, bấm Alt+= để có dòng cộng.
=SUMIFS(ChiPhi!G:G,ChiPhi!F:F,A6)
5
Tỉ lệ doanh thu
Ô E6 lấy doanh thu cửa hàng chia tổng doanh thu. Dấu $ khoá dòng 11 để kéo xuống E10 công thức vẫn chia cho đúng ô tổng.
=B6/B$11
6
Phần chi phí chung của bốn cửa hàng đầu
Ô F6 lấy chi phí chung nhân tỉ lệ, bọc ROUND cho tròn đồng. Kéo nút fill từ F6 xuống F9, dừng ở cửa hàng thứ tư.
=ROUND(F$3*E6,0)
7
Cửa hàng cuối gánh phần lẻ
Ô F10 lấy chi phí chung trừ bốn cửa hàng trên. Phần lẻ do làm tròn dồn hết vào đây, ô F10 ra 52.239.817. Nếu bấm máy tính riêng cho cửa hàng này thì ra 52.239.815 và cả bảng cộng lại hụt 2 đồng.
=F3-SUM(F6:F9)
8
Lãi lỗ sau phân bổ
Ô G6 gõ công thức dưới đây rồi kéo xuống G10. Hà Đông (G8) lỗ 37.409.030, Thanh Xuân (G10) lỗ 33.061.386, ba cửa hàng còn lại vẫn lãi. Bôi E11:G11, bấm Alt+=. Ô G11 ra số lỗ 27.243.344 của cả công ty.
=D6-F6
9
Dòng kiểm và thử đổi một khoản chi
Ô F13 gõ công thức dưới đây, ra 0: phần đã chia cộng lại bằng đúng chi phí chung. Thử sang sheet ChiPhi, sửa ô G8 (quảng cáo đợt 1) từ 39.972.000 thành 59.972.000. Về sheet PhanBo, ô F3 thành 397.039.000, cột F chia lại cho năm cửa hàng, F13 vẫn bằng 0.
=F11-F3

Lưu ý khi làm với file thật

Sổ xuất từ phần mềm có dòng Tổng cộng ở cuối. Dòng này để trống cột Cửa hàng nên phải thêm điều kiện số chứng từ khác trống, không thì dòng tổng bị cộng lẫn vào chi phí chung.
Tháng sau thay sổ mới vào hai sheet DoanhThu và ChiPhi, bảng PhanBo tự ra số.

Bài chỉ dùng SUMIFS, ROUND và AutoSum (Alt+=), không dùng hàm mảng.

File thực hành của bài này

EzTech sẽ cập nhật link tải sau.

Dữ liệu trong bài là dữ liệu minh hoạ.

Bài học khác

Tất cả bài học