It is a spreadsheet book, oriented to schedule the tasks of the workers, dividing them into different work groups and with the support of different vehicles. The spreadsheets distinguish by colours, the teams that are formed according to the vehicles. The name of the client, the number of workers in each team and the number of hours spent on the work are specified. In addition, the number of staff available each day, whether there are workers on sick leave and whether temporary and external workers are hired are specified.
The purpose of the sheets is to add up the number of hours worked by colour. Afterwards, the number of available, contracted and invoiced hours is analysed on a daily and weekly basis.
We explain the details of the composition of the calculation ledger:
The spreadsheet book consists of 13 sheets; one configuration sheet for the year and one sheet for each month of the year.
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.
From this weekly and monthly calendar, plus the public holidays, we can set up a spreadsheet for each month, with the weekly work tables. Holidays that fall on working days will be blocked so that the operator cannot enter data by mistake. In addition, these days will be coloured in red and indicated as public holidays.
The monthly sheets:
Each monthly sheet will have up to five weekly tables, one for each of the five possible weeks of each month.
Not all months have five weeks, so it must be calculated whether week number five should be filled in or not. If it is not filled in, the weekly table will be blocked. And it will have no dates indicated.
Each week presents only the five working days. If one of these days is a public holiday, the date box is marked in red and indicates that it is a public holiday. This is already calculated when the public holiday dates of each year are indicated in the configuration sheet. In addition, the public holiday will be blocked, so that no jobs can be entered by mistake.
There is a combined field for each of the jobs and for each day. This combined field allows you to select the work team that will intervene. When one of the teams is selected, the work band will be marked with a colour. Afterwards, the number of workers involved and the number of hours spent must be indicated.
Finally, the sheet calculates for each day, each week and each month, the total hours spent.
The analysis of Available, Contracted and Invoiced hours allows us to know if the hours are covered, if there are too many workers or a shortage of work, and if there is a need to work overtime or hire temporary and/or external workers.
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.
