Sau mỗi một đến hai tuần, hoặc khi tổng hợp cuối tháng, nhà trường có thể cần phân bổ lại một tổng ngân sách cho các ngày hay khoản mục. Yêu cầu thường gặp là giữ tương quan giữa các khoản như bảng hiện tại, nhưng thay đổi số tiền để tổng mới khớp chính xác mức cần phân bổ.
Nếu bảng chỉ có vài dòng, phép tính không phức tạp. Tuy nhiên, khi có nhiều khoản mục, một số khoản phải giữ nguyên và kết quả cần làm tròn đến đồng, việc sửa trực tiếp trong Excel dễ tạo ra phần chênh lệch ở tổng cuối cùng.
Ví dụ phân bổ một tổng ngân sách mới
Giả sử bảng hiện tại có bốn nhóm chi với tổng 20.000.000 đồng:
| Khoản mục | Số tiền hiện tại | Tỷ lệ | Phân bổ từ tổng 24.000.000 |
|---|---|---|---|
| Thực phẩm tươi sống | 10.000.000 | 50% | 12.000.000 |
| Gạo và thực phẩm khô | 5.000.000 | 25% | 6.000.000 |
| Gia vị, phụ gia | 3.000.000 | 15% | 3.600.000 |
| Khoản chi khác | 2.000.000 | 10% | 2.400.000 |
| Tổng cộng | 20.000.000 | 100% | 24.000.000 |
Trong ví dụ này, các tỷ lệ đều là số tròn nên kết quả khớp ngay. Với dữ liệu thực tế, tỷ lệ thường có nhiều chữ số thập phân và số tiền sau phân bổ có thể phát sinh phần lẻ.
Công thức phân bổ theo tỷ lệ trong Excel
Nếu số tiền hiện tại nằm trong vùng B2:B5 và tổng ngân sách mới nằm ở ô E1, số tiền mới của dòng đầu tiên có thể tính bằng:
=B2/SUM($B$2:$B$5)*$E$1
Sau đó sao chép công thức xuống các dòng còn lại. Phần B2/SUM($B$2:$B$5) xác định tỷ lệ hiện tại của từng khoản; nhân tỷ lệ đó với tổng mới sẽ cho số tiền được phân bổ.
Vì sao tổng sau làm tròn có thể bị lệch?
Khi số tiền từng dòng được làm tròn, tổng các dòng có thể cao hoặc thấp hơn tổng ngân sách vài đồng. Chẳng hạn, nhiều kết quả cùng có phần thập phân gần 0,5; làm tròn độc lập từng dòng sẽ cộng dồn sai số.
Có thể dùng ROUND cho từng dòng rồi tính phần chênh lệch ở dòng cuối. Tuy nhiên, cách cộng toàn bộ chênh lệch vào một khoản có thể làm thay đổi tỷ lệ nhiều hơn cần thiết, nhất là khi khoản đó có giá trị nhỏ.
Trường hợp cần giữ nguyên một số khoản
Một số khoản đã chốt hoặc đã có chứng từ có thể không được thay đổi. Khi đó, cần trừ tổng các khoản cố định khỏi ngân sách mới, sau đó chỉ phân bổ phần còn lại cho những khoản được phép điều chỉnh theo tỷ lệ hiện tại của chúng.
Nếu thiết lập trong Excel, công thức phải tách rõ vùng cố định và vùng cần phân bổ. Với bảng thay đổi thường xuyên, người dùng cũng phải kiểm tra lại phạm vi công thức trước mỗi lần tính.
Cách phân bổ nhanh từ bảng Excel hiện có
- Chuẩn bị hai cột: tên nội dung và số tiền hiện tại.
- Sao chép các dòng cần phân bổ từ Excel và dán vào công cụ Phân bổ ngân sách.
- Nhập tổng tiền mong muốn.
- Khóa những khoản cần giữ nguyên.
- Thực hiện phân bổ, kiểm tra tổng rồi sao chép kết quả trở lại Excel.
Những điểm cần kiểm tra trước khi chốt
- Tổng các dòng sau phân bổ đã khớp tổng ngân sách cần đạt.
- Các khoản đã khóa vẫn giữ đúng số tiền ban đầu.
- Kết quả từng dòng phù hợp với mục đích chi và chứng từ thực tế.
- Không nhầm giữa phân bổ lại số tiền và điều chỉnh số lượng thực phẩm.
Phân bổ theo tỷ lệ giúp giữ tương quan của bảng hiện tại, nhưng tỷ lệ cũ không phải lúc nào cũng phù hợp với nhu cầu mới. Người phụ trách vẫn nên kiểm tra lại từng khoản trước khi sử dụng kết quả để lập hoặc hoàn thiện chứng từ.