Should we pay the overtime or take the late penalty?
Every shop with more work than weeks argues about the same thing. Crash the schedule and pay for the extra hours, or let a job run past its date and eat the penalty. This model prices both sides at once and picks a start week for each job, and sometimes the cheapest plan is deliberately late.
Operations Advanced Optimization free
After you install, this is the model to open.
Overtime vs Late Penalty Scheduler
- 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.
The answer
Run the optimizer on the defaults and it shuffles start weeks until the weekly load profile flattens out and the total stops falling.
- Cheapest total cost
- $12,900 overtime plus penalties
- Overtime bought
- 110 hrs $6,900 across 4 weeks
- Lateness accepted
- 3 job-weeks 2 jobs, $6,000
- Saved vs all on time
- $34,500 73% below $47,400
Hitting every promised date is possible, and the cheapest way to do it costs $47,400, all of it overtime, because the dates require 1,490 hours of work finished inside eight weeks and eight weeks of regular capacity is only 1,280 hours. Letting one job slip a week and another slip two brings the total to $12,900, a 73% saving, with overtime down to 110 hours. The penalty rate is the dial worth turning: at $5,000 per week the optimizer buys 160 overtime hours instead and pulls all but one job back onto its date.
The model
Eight jobs sit in the backlog, each with the hours it draws per week, how long it runs, and the week it was promised. Capacity is one shift, overtime comes in two tiers, and lateness has a price.
| Jobs in the backlog | 8 jobs, 2 to 5 weeks each |
| Promised dates | weeks 3 to 8 |
| Total work in the backlog | 1,490 hours |
| Regular capacity | 160 hours per week |
| Overtime | $60/hr for the first 40 hours, $90/hr beyond |
| Late penalty | $2,000 per job per week late |
| Decision | start week for each job, whole weeks 1 to 8 |
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
- What order should you drive your stops in?Shortest Route Finder
- If we build one 50% bigger, what will it actually cost?What Does Twice the Capacity Cost?
- Which mix of stores should fill your square footage?Retail Space Mix Optimizer
- How much contingency does this bid actually need?How Much Contingency Does This Bid Need?
- How much should you order each time?How Much to Order Each Time (EOQ)
- Which plant should ship to which store?Which Plant Ships to Which Store?
Every model like this one, and the method behind them: Optimization in Google Sheets.