How few people can cover a seven-day week?
Seven days of demand, seven ways to work five days in a row, and a rota that opens at 28 people. The optimizer covers every day with 23, and the two days off are what make it harder than dividing by five.
Operations Starter Optimization free
After you install, this is the model to open.
How Few People Can Staff the Week?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
What it does
A shop, a ward or a support desk is open seven days, and everyone works five days in a row and then has two off. The People needed column in the lower block says how many people each day needs, from 11 on Sunday to 19 on Thursday. The seven rows at the top are the seven possible patterns, one for each starting day, and the ones and zeros beside each show which days that pattern works.
The sheet opens on four people on every pattern. That is 28 people, it puts 20 on duty every day, and it covers the week with room to spare. Click Run and the optimizer returns 23, five people fewer, with every day still covered. The instinct is to add up the week and divide. The seven days need 105 shifts between them, each person works five, so 21 people should do it.
They cannot. People come in whole numbers and in blocks of five consecutive days, so covering Thursday's 19 also puts people on days that do not need them, and 23 is the fewest that reaches every day. There is more than one rota of 23 that works, so read the headcount as the answer and the split across patterns as one way of getting there.
After the run the sheet shows how many are on duty each day beside how many are needed, and the result in the panel names the days that came back Binding, which are the ones setting your headcount. Lower the need on one of those days and the total can fall. Lower it on any other day and nothing changes. To use your own week, overwrite the People needed column and run again.
The model
It arrives on a tab called Template: How Few People Can Staff the Week, carrying these columns:
- People on this pattern (count)
- Works Mon (1 = yes)
- Works Tue (1 = yes)
- Works Wed (1 = yes)
- Works Thu (1 = yes)
- Works Fri (1 = yes)
- Works Sat (1 = yes)
- Works Sun (1 = yes)
with the model computed beside the data:
| Monday | 20 |
| Tuesday | 20 |
| Wednesday | 20 |
| Thursday | 20 |
| Friday | 20 |
| Saturday | 20 |
| Sunday | 20 |
| People on the rota (count) | 28 |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- How many units does one delivery wait really need on the shelf?How many units to stock for 90% odds of no stockout?
- How many hires does a support week really need, in odds rather than averages?How many hires keep 85% odds of hitting the support target?
- Is a second shift enough for the peak week, or does it take a second line?What capacity gives 95% odds of covering peak demand?
- How much should you order when demand is uncertain?Inventory Order Quantity (Newsvendor)
- Should you hold your price, or cut first?Price War: Hold or Cut?
- Which tasks actually set your launch date?Launch Plan: Find the Critical Path
Every model like this one, and the method behind them: Optimization in Google Sheets.