Is it cheaper to staff the night or to buy it in?

Put in the cover each window needs and the cost of each shift pattern and get back the cheapest roster. Six four-hour windows, six shift patterns and an outsourced desk that charges more an hour and still saves you money. The optimizer buys the night and rosters the day.

SaaS Intermediate Optimization free

Install free (opens the Google Workspace Marketplace in a new tab) How optimization works here

After you install, this is the model to open.

Cover the Support Hours for the Least Money

  1. 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.
  2. Click Start from a template and put that name in the search box.
  3. Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.

The answer

Cheapest day
$2,892 against $3,180 for the all-in-house roster
Saving
$288 a day about $105,000 a year
The roster
9 shifts all starting in daylight
Bought in
3 agent-windows both overnight windows go to the outsourced desk

Six four-hour windows around the clock, an eight-hour in-house shift that covers two consecutive windows, and an outsourced desk you can buy one window at a time. The agents-needed column is the shape of a real ticket day: quiet overnight, a morning build, a late-afternoon peak at six agents, and a long tail to midnight. The sheet opens on the cheapest all-in-house roster, eleven shifts at $3,180 a day.

Click Run and the optimizer returns $2,892, saving $288 a day or about $105,000 a year, and it gets there by rostering nine shifts that all start in daylight and buying the two overnight windows from the outsourced desk, three agent-blocks in total. That answer looks wrong on the rate card. Outsourced costs $46 an agent-hour and an in-house day agent costs $32.50.

But an in-house overnight shift is $420 for eight hours, which is $52.50 an hour, and it makes you buy eight hours to cover two windows that need two agents and one agent. Buying exactly the windows you need at the higher hourly rate beats rostering a whole shift at the higher shift rate. The rule to take away is that an hourly comparison is the wrong comparison whenever your own capacity comes in fixed blocks.

Second run: change the overnight shift rate from $420 to $340 and rerun. The shape of the answer changes rather than the outsourcing disappearing: the desk now rosters one overnight shift of its own and buys a single block to top up the busier of the two night windows, $2,864 for the day. The crossover is at about $368 a shift, and it never reaches zero outsourcing at any rate an unsocial-hours uplift would plausibly take, because the first night window needs two agents and a shift is a blunt instrument for buying the second one.

Both cost lines read the rate cells rather than repeating the rates, so changing a shift rate or the block price really does change the answer, which is worth checking on any sheet before you trust a second run. What the model does not price is quality, handover, or the fact that an outsourced agent does not know your product, and it assumes an agent can start work in any window, which a real roster with rest rules cannot always do.

To use it, replace the agents-needed column with your own arrival data, change the two shift rates and the block price where they sit beside their labels, and add rows if your windows are shorter than four hours.

The model

It arrives on a tab called Template: Covering the Support Desk, carrying these columns:

  • Agents needed (count)
  • In-house shift starting here
  • Agents on shift (count)
  • Outsourced agents (count)
  • Short (agents)

with the model computed beside the data:

In-house shifts rostered (count)11
In-house cost ($)3,180
Outsourced agent-windows bought (count)0
Outsourced cost ($)0
Total cost for the day ($)3,180

Once it is in your sheet

  1. The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
  2. 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.
  3. 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

Every model like this one, and the method behind them: Optimization in Google Sheets.

Install free (opens the Google Workspace Marketplace in a new tab) All 379 templates