Managing your staff costs is key to running a successful and profitable hospitality operation, but many still plan their staffing profile based on gut feel and experience, without supporting this with solid analysis. Floodlight makes it easy for you to achieve this, by leveraging our Floodlight Focus advanced reporting suite. This guide is designed to help you to download and prepare the report, to help you clearly understand your business operations. It then runs through the steps to interpret it and take action to control your costs and improve your profitability.
Download and Prepare Report
1. Download the ‘Optimise Venue Opening and Closing Times’ report from the ‘Reduce Staff Costs’ section of Floodlight Focus.
- Navigate to ‘Floodlight Focus’ on the left sidebar
- Scroll down to ‘Optimise Venue Opening and Closing Times’
- Select required Start Date and End Date
- Click ‘Download CSV’
2. Open the CSV file in your spreadsheet application of choice
- The ‘dayOfWeek’ column is the name of each week day (‘dayOfWeekNumber’ is the corresponding number from Monday = 1 to Sunday = 7):
- The ‘timeOfDay’ column is the start hour of each trading hour – the critical time buckets we need to look at as we want to align our staff profile based on expected sales by time of day:
- The middle block of columns is the raw sales data for each of each day along with the sales total, however for this purpose we will hide this section and ignore it:
- The most important columns at the end of the raw data are the averages, which are subtly different:
- Accounting Average is the total sales over the period divided by the total weeks selected.
- Trading Average takes into account weeks when there was no trade, and adjusts accordingly. In the example above, there were 14 weeks selected but only 13 weeks with any trade on a Friday. This means that the accounting average for e.g. Friday 10-11am would be $595 (Total Sales/14), but the Trading Average would be $641 (Total Sales/13). The Trading Average can give you a better idea of the actual expected sales, as it removes non-trading periods from the calculation.
3. Add in margin and tax calculations
- Work out your average gross margin percentage from your accounting software or accountant (1 – Cost of Goods/Total Sales), and enter it into the cell below. It doesn’t matter whether you use values with or without tax as long as you are consistent:
- Add this formula in to multiply your sales by your profit margin and calculate your gross profit (including tax, as we report sales number inclusive of sales tax):
- Remember to add the dollar sign after the ‘AA’ to fix the coordinates for the calculation:
- Copy the calculation down (double click on the bottom right corner of the cell, or copy and paste it down), and reformat to dollars for tidiness. This is your average profit including tax for each trading hour of each day of the week.
- Now don’t make the rookie error of forgetting to include your tax in the calculation! You pay sales tax on your revenue minus cost of goods, so repeat the previous step with the following formula:
- This gives you the average profit per trading hour, net of all monies owed to the tax office or paid/owed to your suppliers. This is the key number that you then need to compare with your staff costs in order to understand whether each hour is actually profitable:
4. Add in approximate average hourly staff cost rate
- For this analysis we will use a simple approximation of average hourly rate – this is a simplification as it does not take varying hourly staff rates or salaried staff costs, but it should still give a good idea of the approximate profile you are looking for.
- A simple way of working this out is taking a typical week and dividing the total payroll bill including extras (like superannuation), and dividing it by the total hours worked for all staff (casual and salaried).
- If I know that my average hourly staff cost on a week day is $25 per hour plus 9.25% superannuation, I can then calculate this as 25 * 1.0925 = $27.31 and add this to the next top cell:
- Now I want to calculate how many staff I can have working at this rate in order to break even for each hour, by entering the following formula into the next cell:
- Ensure that you fix the coordinates of the cell AC$1 using a dollar sign and copy down as before:
5. Select and analyse individual days
- I know want to check this for e.g. Friday, so I’ll apply a filter to the sheet, select ‘dayOfWeek’ as Friday and ensure that the ‘hourOfDay’ column is sorted in ascending order. This will give me my approximate breakeven staff sales profile for each consecutive hour from 7:00 to 23:00:
- Looking at the values in this column now gives me some extremely valuable information to help me understand my operations, and enabling me to control my staff cost and improve my overall profit.
Interpret Report and Action Outcomes
There are X main points I’ll draw from analysing this specific day, which I will then use to plan an optimised staff profile.
1. Opening and early trading time: 07:00-08:00
Observation: I have only made sales between 7-8am on Friday three times out of fourteen weeks, and when I did make sales they averaged $9.37.
Reasoning: The ‘Zero Data’ column in the ‘hourOfDay’ = 7 row shows me that 11 of the 14 weeks were zero.
Action: If this site is open between 7-8am on Fridays then if I require any dedicated members of staff to serve customers I should remain closed at this time, as the sales cannot cover the wage cost.
2. Opening and early trading time: 08:00-09:00
Observation: This site opens between 8-9am on Fridays and average revenue of $212 at 65% margin can sustain approximate 4.5 members of staff during this initial opening hour.
Reasoning: The ‘Non-Zero Data’ column in the ‘hourOfDay’ = 8 row shows me that 13 of the 14 weeks had sales in this hour. In fact, the only reason that I have a week without sales is that I’ve included the current week but Friday has not yet happened at the time of writing. This is why the Trading Average is a more accurate measure for staff planning that the Accounting Average. The calculated columns let me know what the profit after cost of goods and tax are, and how many staff this can sustain.
Action: When I plan my staff profile I should ensure that I do not deploy more than 4.5 staff hours (e.g. 4 staff working a full hour and one starting at 08:30), and preferably fewer to maximise my profit in this hour. If I require significantly more that this staffing level to operate then I need to reassess whether it is appropriate to open at this time for customer satisfaction or retention.
2. Ramp up to lunchtime peak
Observation: My hourly revenues then steadily rise until my peak hour between 12:00-13:00, where my expected revenue can support 21.2 staff members.
Reasoning: Observing number in the calculated columns for rows ‘hourOfDay’ from 9 to 12. Also the ‘dayOfWeekPercentage’ column shows me that 12 is the highest percentage of day at 12.1%.
Action: I will plan my server shifts to steadily increase levels until lunch. However I also know that if I can schedule my most efficient, quickest members of staff for the peak period, it’ll give me more opportunity to have a highly profitable lunchtime period.
3. Ramp down to afternoon lull
Observation: After lunch my revenues steadily decrease to a low of $431 between 18:00-19:00, at which point they approximately sustain a staff level of 9.
Reasoning: Observing numbers in the calculated columns for rows ‘hourOfDay’ from 13 to 18.
Action: I will plan my shifts around this profile and taper off the staff members working during the afternoon to maintain hourly profits. This means that I might have to be a little creative around shift patterns, but this will give a clear financial benefit.
4. Evening peak
Observation: My revenues peak again in the evening between 20:00 and 21:00, during which time I can sustain approximately 14 members of staff.
Reasoning: Observing number in the calculated columns for rows ‘hourOfDay’ from 19 to 21.
Conclusion: I need to ensure that I have enough staff on hand to manage this second peak and maintain service levels, so I might actually decide to keep staff on site but plan breaks over the previous lull to maintain hourly profitability. This will prevent me having to schedule additional short shift to cover it.
5. Closing time
Observation: It appears that my closing time for this venue is 22:00 as I have only made sales past this point on one occasion. My sales between 21:00 and 22:00 are strong and can sustain nearly 11 members of staff.
How I know this: The ‘Zero Data’ column in the ‘hourOfDay’ = 22 row shows me that 13 of the 14 weeks had no sales in this hour. The ‘dayOfWeekPercentage’ shows me that I make 6.1% of my daily sales in this hour.
Conclusion: As sales still appear to be strong between 21:00-22:00, this implies that my venue is still pretty full and potentially could sustain more sales later into the evening. I would investigate the potential for opening later, either within existing permitted conditions or potentially consider an application for later trading if possible.