This is a spreadsheet book, oriented to compare the hours worked against the hours proposed as a target. If the worker exceeds the target hours, he/she can be paid overtime, if not, only the hours worked.
It is a book for hourly jobs, which includes leave control, incidents, holidays and annual calendar with public holidays.
We explain the details of the composition of the calculation ledger:
The spreadsheet book is made up of 23 sheets; an initial selection sheet, a sheet for the configuration of the annual calendar and other parameters, twelve sheets (monthly) to record leave and holidays, a summary sheet of leave and holidays, a sheet for the control of hours worked by each worker and their target hours, a summary sheet of hours worked, three sheets to indicate incidents of the current and two previous years, plus another summary sheet of incidents. Finally, there is a hidden sheet that is used as a template to facilitate the creation of a summary sheet of hours, which can be created by means of a button (associated with a macro), for each worker.
The configuration sheet:
The user must specify the year and the public holidays of that year. The sheet calculates on which day of the week each date falls, the number of weeks of each month and of the year. On the same sheet, the parameters concerning working days, working hours, training hours and billable hours are also entered. These values serve as the basis for subsequent calculations. The calendar is perpetual.
Monthly sick leave and holiday leave sheets:
Each monthly sheet allows the annotation of the days of sick leave and the days of holidays taken. These sheets mark holidays and weekends with colours, depending on what is noted on the calendar sheet. Each line is for one day of the month and the columns are for the sick leave and holidays of the workers.
As a summary, we will find a final sheet, with the annual values for each month and each worker:
The Hours and Targets Control sheet per worker:
This is the main sheet, on which the hours worked are recorded. First a start date of the worker must be specified. It is also possible to enter an end date, if the worker’s relationship with the company ends before the end of the year. Next, we can enter the monthly hours worked. Each line is one month. When entering the hours worked, in the target column, the hours are calculated on the basis of the date on which the employee started his or her employment relationship with the company. Billable hours and training hours are recorded. Afterwards, the programme performs a consolidation of both. The table per worker includes a column for leave and a column for holidays; the values for both are taken from the monthly leave and holiday sheets. There is also a column to note down possible errors made by the worker, for the incidences caused. Finally, there is a column indicating the balance of hours between the hours worked and the target hours, taking into account sick leave and holidays taken.
There are a total of 10 columns for each worker’s entries (Billable, Training and Consolidated hours, performed plus target and absences, holidays, etc.) and 12 lines, for each month, plus totals per quarter and year. The annual hours pending are also calculated and a verification is carried out.
Finally, there is a button that allows you to generate a summary sheet from this table. It is used to deliver the collected data and present it to each worker. To generate this sheet, there is a macro that uses another hidden sheet as a template and performs the relevant calculations.
The Summary Timesheet:
This sheet shows a summary of Billable, Formative and Consolidated hours, for each worker and month. The values presented are calculated automatically.
The incident sheets:
There are four incident sheets, to record the errors made by the workers and which the company has to take responsibility for vis-à-vis its customers. These are sheets of notes that indicate the number of incidents per month. They are used to see the effectiveness of each worker, but they do not add or subtract hours, they only present information.
Application made with Excel. To use this application it is necessary to have MS Excel. The application is in Catalan language. It can be translated and adapted. Note: The data inserted in the images are fictitious.
