Mastering Excel Payroll: How to Create a Salary Table Based on Circular 133

In the landscape of corporate finance and human resource management, the use of Microsoft Excel for payroll calculation and timekeeping remains a cornerstone for many businesses. This is particularly true for Small and Medium Enterprises (SMEs) in Vietnam that adhere to the accounting standards set by Circular 133 (Thông tư 133).

While specialized software exists, the flexibility of Excel allows accountants to customize formulas and layouts to fit specific organizational needs. This guide provides a comprehensive, step-by-step walkthrough on how to construct a professional payroll table that complies with Circular 133, ensuring you manage employee compensation accurately and efficiently.

Step 1: Defining the Payroll Layout and Structure

The foundation of any effective spreadsheet is its layout. A payroll table compliant with Circular 133 requires a specific structure to ensure validity during audits and ease of internal use.

To begin, you must establish the header information. This includes the administrative details of your organization:

• Entity Information: The company name and address must be clearly stated in the top-left corner.
• Document Title: The phrase “BẢNG THANH TOÁN TIỀN LƯƠNG” (PAYROLL TABLE) should be capitalized, bolded, and centered below the company info.
• Time Period: Clearly indicate the Month and Year of the payroll cycle to facilitate archiving and future lookups.

Excel payroll template header adhering to Circular 133 standards

For the specific form number required by Circular 133, a pro tip is to use a Textbox (Insert > Textbox). This allows you to position the form number code flexibly in the top right corner without messing up your column widths.

Regarding the main body of the table, your columns should logically flow from employee identification to final net pay. Key columns include:

• General Information: No., Full Name, Department.
• Timekeeping Data: Standard working days, actual working days, overtime.
• Income Details: Basic salary, allowances, bonuses.
• Deductions: Social insurance, health insurance, personal income tax (PIT).
• Final Totals: Total income, total deductions, and net salary.
• Signatures: Space for the preparer, chief accountant, and director.

General layout structure of a salary table in Excel

Step 2: Referencing Employee Data with VLOOKUP

One of the most powerful features of Excel in payroll management is the ability to link data across different sheets. You should never manually type static data like “Basic Salary” or “Position” into the payroll sheet every month. Instead, this data should be pulled from a master “Personnel List” or “Labor Contract” sheet.

To achieve this, we utilize the VLOOKUP function. This ensures that if an employee’s salary grade changes in the master list, the payroll table updates automatically.

The Formula Logic:

• Lookup_value: The Employee’s Name or ID (usually in Column B).
• Table_array: The range of your Personnel List (ensure the Name/ID column is the first column in this range).
• Col_index_num: The column number in the Personnel List that contains the data you want (e.g., Salary Coefficient, Basic Pay).
• Range_lookup: Always use 0 (or FALSE) for an exact match to avoid costly payroll errors.

Using VLOOKUP to reference employee information in Excel

Step 3: Integrating Timekeeping and Performance Data

A salary table is not static; it depends heavily on attendance and performance. Similar to the employee data, the number of workdays (standard workdays vs. actual workdays) or product output should be pulled from a separate “Timesheet” or “Product Tracker” tab.

Using the same VLOOKUP method, you can import:

• Actual Working Days: From the monthly attendance sheet.
• Product Quantity: From the production tracking sheet (for piece-rate workers).

By linking these sheets, you reduce manual entry errors. When the HR department updates the attendance sheet, your payroll calculation automatically reflects the correct number of payable days.

Step 4: Handling Advances and Financial Deductions

Before calculating the final payout, you must account for any salary advances and mandatory statutory deductions.

Salary Advances:
Data for advances should be referenced from the Cash Book or a specific “Advance Tracking” sheet. Using VLOOKUP against the employee’s name allows you to pull the exact amount they have already received during the month.

Statutory Deductions (Insurance & Tax):
This is where Excel formulas shine. You should set up formulas to calculate deductions based on the “Total Income” column:

• Insurance: Multiply the salary subject to insurance by the current statutory rates (Social Insurance, Health Insurance, Unemployment Insurance).
• Personal Income Tax (PIT): Use nested IF functions to calculate tax based on the progressive tax schedule, ensuring you account for family deductions.

Step 5: Calculating Net Salary (Actual Received)

The final step is purely mathematical but critical. The “Net Salary” or “Actual Received” column represents the amount the employee will actually see in their bank account.

The Formula:

Net Salary = Total Income – Salary Advances – Total Deductions

Ensure that the bottom of your table includes a row for “Total,” summing up each column. Additionally, it is professional practice to include a “Total Amount in Words” row below the numeric total to prevent alteration and ensure clarity.

Conclusion

Creating a payroll table in Excel that adheres to Circular 133 is an essential skill for accountants and administrative professionals in Vietnam. By structuring your layout correctly and mastering functions like VLOOKUP, you transform a manual chore into an automated, accurate process.

We hope this guide helps you streamline your payroll processing. You can download the sample payroll template referenced in this tutorial here: http://bit.ly/2rEboFB. Continually updating your Excel skills will not only save time but also enhance your professional value in the digital workplace.