As part of Morgans' requirement to improve their payroll processes, a workbook was needed that could be sent out to each permanent member of staff to capture their annual leave and their daily hours worked on each project (the latter on a monthly basis).
The sheets in the delivered workbook are as follows:
Entry Screen - This sheet is displayed initially, with the others hidden (and unhideable). It provides a link to a custom form that displays the changes in the current version, and another to continue. As the continue link runs a macro that displays the other sheets, the user cannot proceed unless macros are turned on. This is intended behaviour as any editing of the main sheets by the user could otherwise lead to errors or inconsistencies, given that much of the behaviour and validation is handled through automatically-triggered macro code.
Setup Sheet - Some initial configuration is carried out here. When the employee name is chosen, the line manager, payroll number etc are all populated automatically from reference data on the 'Staff' tab.
Holiday Bookings Sheet - Here the user enters their annual leave. As they do so, automatic macro code updates the corresponding date ranges on the appropriate monthly timesheets. Mandatory fields are conditionally formatted in yellow until they are filled in, and a new row is added when the '(click to add)' cell is selected. There is also a button to email this sheet (by itself) to the user's manager, which brings up a prepared email in outlook with the file attached.
Monthly Timesheet - Here the user adds the projects/cost centres that they have worked on using the project chooser (see below), and then for each day enters the number of hours spent on each. They can override the default start/finish times (tinted blue), after which the user is warned if they have entered a day length that is shorter than their contracted number of hours. If it is longer, overtime is registered. In the top section they can enter absence hours and choose from a predefined list of reasons. A check row 'hours over/under' at the bottom tells the user if they have recorded too many hours vs the day length they have entered (red/green). The 'lodge' checkboxes indicate the user stayed away from home that night.
Once the timesheet has been completed, the user can submit it to payroll using the button at the top. In this operation, the record status changes to 'submitted' and the sheet is locked for editing. A cut-down version of the workbook is then created, which is then copied to a payroll folder on the network. On the payroll side, once all staff have submitted their timesheets, a central Timesheets Summary workbook is used to run a batch process that collates all the data and creates a file for upload into the company's project accounting and payroll systems.
The project chooser is a custom form that provides the user with the list of available cost centres, which reduces in length as text is typed. It is shown when either the '(click to add)' cell or an existing cost centre name is selected.
Reference Data - The Staff and Projects tabs are reference data tables loaded from the HR and payroll SQL Server databases by running behind-the-scenes SQL queries. The former is used to autopopulate cells in the setup and holiday sheets, and to provide the manager's email address. The projects data provides the list of cost centres for the project chooser above. The tables are refreshed whenever the user clicks the button at the top.
If you would like to discuss a potential project, XY provides a free 30 minute telephone consultation.
If you are interested, you can request one here.
