Data Loading...
Rostering and Staff Duty Scheduling excel template user guide ver-2a Flipbook PDF
Rostering and Staff Duty Scheduling excel template user guide ver-2a
120 Views
27 Downloads
FLIP PDF 670.82KB
Staff Scheduling / Duty Rostering Excel Template User Guide Excel-based Staff Rostering Template
This Excel-based template allows users to quickly and easily create duty rosters for employees. The template can be used in situations where one, two or three individual rosters are required, e.g. (1) a daily roster from 8am to 5.30 pm, (2) an 8 to 5 roster followed by 7 to 10 or (3) in the case of three rosters it might be 8 to 4 followed by 4 to midnight and midnight to 8 am. Multiple schedules may also be used where there are three different locations/outlets. For convenience and ease of use, the template is configured for 30 employees. However, should you require more than 30 employees, it can easily be reconfigured or please contact us. All rosters are laid out to be easily printed. Each role is colour coded to facilitate a quick overview. The template highlights where each role is under or over scheduled. It is totally customizable, uses only standard Excel features and contains no macros.
[email protected] 10/17/2018
10/17/2018
STAFF SCHEDULING / DUTY ROSTERING EXCEL TEMPLATE USER GUIDE Excel-based Staff Rostering Template
INTRODUCTION This Excel-based template allows users to quickly and easily create duty rosters for employees. The standard template is configured for up to 30 employees and 10 roles, e.g. supervisor, cashier, etc. Each role is colour coded to facilitate a quick overview. The template highlights where each role is under or over scheduled. It is totally customizable, uses only standard Excel features and contains no macros. The typical weekly roster is depicted in Figure 1 below.
Figure 1 Weekly Roster Excel Template
www.businesstoolsstore.com Copyright © 2018 The Business Tools Store
1
10/17/2018
USER INSTRUCTIONS Setup The template Setup tab is used to set up the roles and related abbreviation codes. The first day of the roster week is also defined in the setup. The employees to be scheduled in one or more of the rosters are entered in the setup tab. Their normal role/department and the staff number can optionally be entered and edited via the Setup. All values can be changed at any time as per figure 2 below.
Figure 2 Setup Parameters
CHANGE STAFF ROLES AND ASSOCIATED ABBREVIATIONS
The Setup worksheet, as depicted in figure 2 above, shows sample roles and associated abbreviations in columns G and H in rows 6 to 15. These abbreviations and colors are displayed in the rosters. These can be edited to reflect your roles by editing the text for the description and abbreviation code in the relevant cells. The colors can NOT / should NOT be changed. CHANGE THE START DAY OF THE ROSTER WEEK
The Setup worksheet, as depicted in figure 2 above, allows the Start Day of the roster week to be changed. The start day is selected from the dropdown list in cell G18.
www.businesstoolsstore.com Copyright © 2018 The Business Tools Store
2
10/17/2018 ENTERING EMPLOYEE DETAILS
Employee details are entered in the Setup worksheet, as depicted in figure 3 below, in columns B, C and D from row 5 down. These details are then automatically updated and used throughout the system.
Employee
Department
Staff Number
Joe Blog
Accounts
20
Jim Bigg
Sales
23
Jane Doe
Admin
20
Figure 3 Employee Details Setup
To enter a new employee: • • •
Enter Employee Name in column B Enter the Employee’s Department in column C Enter Staff Number in column D.
Both Employee Department and Staff Number are optional. ENTERING ROSTER DETAILS
To enter roster details: • Enter the number of staff required for each role for each day as depicted in Figure 5 below. This provides a running total and roster status as individuals are added to the roster. This facilitates the identification of over or under rostering. • Employees are listed in column B. For each employee to be rostered, the abbreviation code for the role for which the employee is to be rostered is entered for the days of the week as per [1] in Figure 4 below, and the work period is entered in the corresponding column. • For ease of use the Role descriptions and corresponding Abbreviations are displayed above the roster as per [2] in Figure 4 below. The cell color is automatically changed, and the roster summary is updated. Repeat the process for each employee to be rostered. 2
Figure 4 Entering Roster Details
www.businesstoolsstore.com Copyright © 2018 The Business Tools Store
3
1
10/17/2018 ROSTER SUMMARY
As the roster is being built, the number rostered for each role against the actual number required is updated as displayed as per figure 5 below. A traffic light system is used to show the status of the roster. Where the number scheduled for each role is equal to the number required, the cell color changes to green. Where the number scheduled exceeds that required, the cell color is changed to red to highlight it. While additional staff are required for any role the cell color is yellow.
Figure 5 Weekly Roster Summary
MULTIPLE ROSTERS
There is a facility to have up to three Rosters. These can be used, for example a 24hour three shift business or a roster for three different locations. The rosters can be found in the three sperate worksheets in the overall Roster workbook.
www.businesstoolsstore.com Copyright © 2018 The Business Tools Store
4
1
10/17/2018 1
PRINTING ROSTERS
Figure 6 Roster Printing
The particular weekly Roster and Roster Summary to be printed can be selected from the dropdown list as per [1] in figure 6 above. Choose the desired Printer. Choose to print “Selection” Set the page orientation to Portrait or Landscape as appropriate The report can be scaled to 1 Page Wide or 1 Page Tall or Fit-to-Page Printing, depending on the Printer type The specific details may vary depending on the printer chosen.
www.businesstoolsstore.com Copyright © 2018 The Business Tools Store
5
10/17/2018 DISTRIBUTE ROSTERS ELECTRONICALLY
Rosters can be distributed electronically as an email attachment. There may be occasions where it is appropriate to distribute the complete Excel template incorporating the roster and editing capability. However, for most situations, it is preferable to distribute the completed roster as a one-page PDF document that cannot be easily edited or changed. A PDF document can be created from an Excel spreadsheet by using the “Microsoft Print to PDF” facility.
1
Figure 7 Roster as a PDF document
The procedure to create the one-page PDF document is as follows: • • • • •
Select the roster to be printed as described on the previous page. On the Print menu choose Microsoft Print to PDF as per [1] in figure 7. Choose to print “Selection”, i.e. the particular roster you have chosen Set the page orientation Portrait or Landscape as appropriate Once the print is ready you will be asked for a file name and folder for the newly created PDF.
To distribute the roster electronically, email this newly created PDF file as an attachment.
www.businesstoolsstore.com Copyright © 2018 The Business Tools Store
6