Automated payroll accounting system in Google Sheets
This project is a modern alternative to traditional Excel spreadsheets for payroll accounting in a small company (manufacturing/warehouse). I have developed a structure in Google Sheets that allows for the automation of calculations for accruals, deductions, payments, and debts for employees. The system consists of several sheets linked by formulas and scripts for convenient data entry and report generation.
Key features:
System sheets:
"Employees": A directory with names, positions, phone numbers, salaries, and notes (with drop-down lists to avoid errors).
"Accruals": Accounting for worked days/hours, bonuses, and accruals for the period (formulas for automatic calculation).
"Deductions and Payments": Accounting for deductions (products, fines), cash payments, and debts (with automatic transfer of debt from the previous period).
"Piece Rate Payment": Accounting for piece rate payment by nomenclature (with price pulling from "Nomenclature Directory").
"Nomenclature Directory": A catalog of goods/services with prices for piece rate payment.
"Salaries" and "Positions": Additional directories for rates and positions.
"Payroll": Generation of a payroll report for printing (with a button on the script) — pulls data for the selected period, showing only active employees.
Automation:
Formulas (VLOOKUP, SUMIFS, INDEX/MATCH) for pulling data and calculations.
Google Apps Script for generating the payroll report: the accountant enters a date range, the script sums the accruals (from "Accruals" + "Piece Rate Payment"), deductions/payments (from "Deductions and Payments"), and forms a table for printing with columns: No., Name, Position, Hours Worked, Accruals, Deductions, Payments, Total to Pay, Signature, Debt at End.
Error handling: If there is no data — message "No data for the period."
Advantages:
Scalability: Easy to add employees/periods.
Convenience: Filters, drop-down lists, shared access.
Integration: Ability to add Google Forms for entering attendance/deductions.
This project optimizes accounting, reduces errors, and saves time. Developed based on real data from an Excel file, transitioning to an automated system in Google Sheets.
Key features:
System sheets:
"Employees": A directory with names, positions, phone numbers, salaries, and notes (with drop-down lists to avoid errors).
"Accruals": Accounting for worked days/hours, bonuses, and accruals for the period (formulas for automatic calculation).
"Deductions and Payments": Accounting for deductions (products, fines), cash payments, and debts (with automatic transfer of debt from the previous period).
"Piece Rate Payment": Accounting for piece rate payment by nomenclature (with price pulling from "Nomenclature Directory").
"Nomenclature Directory": A catalog of goods/services with prices for piece rate payment.
"Salaries" and "Positions": Additional directories for rates and positions.
"Payroll": Generation of a payroll report for printing (with a button on the script) — pulls data for the selected period, showing only active employees.
Automation:
Formulas (VLOOKUP, SUMIFS, INDEX/MATCH) for pulling data and calculations.
Google Apps Script for generating the payroll report: the accountant enters a date range, the script sums the accruals (from "Accruals" + "Piece Rate Payment"), deductions/payments (from "Deductions and Payments"), and forms a table for printing with columns: No., Name, Position, Hours Worked, Accruals, Deductions, Payments, Total to Pay, Signature, Debt at End.
Error handling: If there is no data — message "No data for the period."
Advantages:
Scalability: Easy to add employees/periods.
Convenience: Filters, drop-down lists, shared access.
Integration: Ability to add Google Forms for entering attendance/deductions.
This project optimizes accounting, reduces errors, and saves time. Developed based on real data from an Excel file, transitioning to an automated system in Google Sheets.
Kharkiv 
Available for hire