BREWSTERS SCHEDULING GUIDE 2004
Labour/Scheduling Workbook Reference Manual
Produced By:
Dave Yipp
Brewsters Brewing Company and Restaurant
5519 53 Street S.E.
Calgary, AB.
T2C 4V1.
Phone: (403) 225-1767
Fax: (403) 225-1653
(0) Table of Contents
Section
(1) Overview p.3
(1.1) Features p.3
(1.2) Workbook Structure p.4
(2) Procedures p. 5
(2.1) Starting Out p. 5
(2.2) Adding/Subtracting Employees p. 5
(2.3) Inputting Sales Projections and Targets p. 6
(2.4) Creating Shifts p. 6
(2.5) Color Coding p. 6
(3) Trouble Shooting and FAQ p. 7
(4) Contact Information p. 8
(1) Overview
The purpose of this scheduling program is:
1) To help managers hit their appropriate labour goals by projecting the labour costs of their schedules on a daily and weekly basis
2) To make scheduling consistent between locations for employees and managers that work at more than one location
Please note: It is not the intention of Brewsters Administration to sacrifice customer service in exchange for achieving labour costing goals. This workbook is intended as a tool for scheduling staff in the most efficient manner possible. Although labour costs are a major element in running a profitable store, location size, layout and fluctuating sales, are variables that are taken into consideration.
(1.1) Features
a) Easy to read layout with room for up to 30 employees.
b) ‘On the fly’ calculations of:
-Estimated labour cost
-Average hourly wage pay out
-Over/under calculations of scheduled hours
-Over/under calculations of dollars
-Total weekly hours worked for each employee on the schedule
-Daily calculations of dollars spent on labour, projected daily costs
d) Optional customizable color coding for designated jobs, days off and on call shifts
e) Hidden information that is viewable only by the schedule maker. EG. Wages, manager’s salaries, etc.
(2.2) Structure
This workbook has 6 worksheets.
1) 1 worksheet named ‘How To Demo’
2) 5 worksheets for making schedules up to 5 weeks in advance
The ‘How To Demo’ worksheet contains hidden comments, instructions and tips regarding the schedule making.
The other 5 worksheets named ‘Week 1’, ‘Week 2’, etc. are for building your schedules up to 5 weeks in advance.
1
BREWSTERS SCHEDULING GUIDE 2004
(2) Procedures
(2.1) Starting Out
Before using this workbook please follow these simple steps to ensure that your workbook is accurate and set up properly.
1) Save the workbook from its original disk to wherever you are going to keep track of your schedule. Preferably in a separate folder specifically for schedule files. Rename the workbook:
(department you want_schedule.xls) and save it.
2) Open the workbook. Click on the worksheet named ‘How To Demo’.
3) On the ‘How To Demo’ worksheet, there is a demonstration schedule already built. This is the general format that your schedules should follow.
4) You will notice that on some cells there is small red corner in the top right. These red ‘flags’ signify that there are hidden tips and instructions on the operation of the schedule workbook. Please roll the mouse pointer over these flagged cells to become familiar with the schedules operations and features.
(2.2) Adding Employees to the Schedule
1) Start by opening the workbook and selecting ‘Week 1’ worksheet at the bottom. You will see that the far left of the ‘Week 1’ schedule is where you will enter your employee names and their wages. (Please note that none of the wages will be visible when the schedule is printed. This information will always remain confidential and will only be visible by the schedule maker.)
2) Enter each employee’s name and hourly wage in the first 2 columns. All salaried employees should be entered in the first 2 rows labeled ‘manager’ at the top of the schedule. (Note: managers do not need to schedule themselves for the sheet to work; however the total fixed cost for salaried employees must be entered into the ‘rate’ cell.)
3) Employees can be added at anytime during the schedule. Wage information and names are not dependant on other forms or data sheets. You can also ‘copy/paste’ employees from one schedule to the next. Once you have built your ‘Week 1’ schedule, it is recommended that you copy and paste the employees to all of the other schedules ( Week 2, Week 3, etc.) in order to save from entering them individually.
4) Once all of the employees and wages have been entered, you are ready to start scheduling shifts and calculating your projected labour cost.
(2.3) Inputting Sales Projections and Targets
The blue box in the top left area of the schedule is where you will enter all of the information concerning your schedule. The red box in the top right of the schedule is where all of the calculations concerning costs will be displayed. There is no need to enter any information in the red box.
1) Enter the starting date of your schedule in the format:
mm/dd/year
This date will automatically change the days of the week and the dates on the schedule.
2) Enter the labour goal for your department in the format:
.25, .15, .50 etc.
3) Enter the daily projected sales amount for the days of the week in the cells the bottom of the schedule below the daily Total Cost cells. These cells will automatically be totaled by the spreadsheet and appear in the ‘Schedule Analysis’ area at the top right of the workbook.
4) Enter the CPP,WCB and EI multipliers in the top right of the workbook. These numbers are different depending on the location.
(2.4) Creating Shifts
Creating shifts works the same way that every schedule does. The start time goes in the ‘In’ box and the finish time goes in the ‘Out’ box. The only difference with this schedule workbook is the format in which the shifts must be typed.
Shifts must be entered in one of these 2 formats:
1) HH:MM (space) AM/PM (11:30 am)
2) HH (space) AM/PM (7 pm)
Deviations from this format will result in errors when the sheet calculates the labour costs.
(2.5) Color Coding
This is an optional feature. Depending on your version of Excel you may or may not be able to use this feature
Near the top of the schedule there is a color code bar that can be customized to designate specific jobs on the schedule by color. 2 of the colors have been locked in order to maintain consistency between locations. Grey will be used to signify ‘days off’. Red will be used to signify ‘on call’ shifts. All other colors can be set to whatever jobs you see fit. Simply click on one of the color cells in the ‘Color Legend’ and type in the job that you would like that color to signify.
When designating jobs on the schedule, select the cells that the shift are in and fill them with the appropriate color.
1
BREWSTERS SCHEDULING GUIDE 2004
(3) Trouble Shooting and FAQ
(Q) I have more than 30 employees on my staff. How do I add rows to accommodate more employees?
(A) You can’t have more than 30 employees per schedule if you want it to print on one page. Please contact Dave at the Head office in order to get a customized schedule for your staff size.
(Q) Does the projected labour cost include fixed costs other than manager’s salaries?
(A) Yes. All projected calculations include the rough costs of WCB, EI, CPP and vacation pay.
(Q) There are errors in some of the calculation displays. Why?
(A) Most likely, if your schedule workbook shows errors, the cells containing your shifts contain mistakes. For example, if you would like to schedule someone at 9 in the morning, the shift must be entered in the format of either:
HH:MM (space) AM/PM (09:00 am)
or
HH (space) AM/PM (9 am)
Please refer to the ‘How To Demo’ sheet contained in the workbook for any other questions regarding the workbook’s operation, or contact the head office.
(4) Contact Information
Administrators
Dave Yipp (403) 225-1767 or cell (403) 827-8600
Don McDonald (403) 225-1767
1