We have working Google Sheets for tracking working hours and salaries at a construction company.
1. The "Employee Accounting" table has two sheets
- for dropdown lists
- a field for data entry
2. The "Work Accounting" table has two sheets
- for dropdown lists
- a field for data entry
3. A report table
After testing, there is a need to add some functionality
1. Add a database of employees (a separate table) with the following set of columns
- Employee ID
- Full Name (Ivanov Ivan Ivanovich)
- phone
- address
- date of birth
- position
- date of hiring
- date of dismissal
2. Formulas should be set up so that the necessary data is automatically pulled into the Employee Accounting table in the list sheet. Dismissed employees should not be in the dropdown lists
3. Dismissed employees should not appear in current reports
4. In the employee database, there will be a full Last Name First Name Patronymic (Ivanov Ivan Ivanovich), but in all other tables, it should be Last Name and initials (Ivanov I.I.)
5. The reports already have weekly and monthly reports on employees. A new report on employees needs to be added similar to the weekly one, but with the ability to filter for the first half of the month (from the 1st to the 15th) and for the second half of the month (from the 16th to the last day of the month).
To the existing monthly report, add 2 columns:
1."Actual Salary Paid"
2. Amount Due
6. Add a payroll statement (a separate table) that can be generated as of any date.
- list of employees
- accrued salary
- actual salary paid (automatically filled in according to the accrued amount, but there should be an option to change the amount)
- difference (if more was paid than accrued, it should be red with a "-" sign)
Logic: select the payment date, select the period, then everything is generated automatically. There is an option to exclude any last name from the current statement !!!!
7. In the "Employee Accounting" table, list sheet, a column "Budget" needs to be added that corresponds to a specific location. It is filled in manually when adding a new location.
8. Add a financial report by locations (the logic is as follows: from the Budget we entered for a specific location, we subtract the total salary for the entire period for this location. (if more was accrued than planned in the budget, it should be red with a "-" sign),
