Production Capacity & Scenario Planning Model
Real capacity changes day by day. This model reads the actual production calendar, works out what every date can deliver, and shows how much each operating option adds.
Open full image ↗The challenge
At Atlas Honda’s high-volume assembly plant, capacity couldn’t be worked out reliably as working days multiplied by daily output. What the plan could really use moved through the year with:
- Public holidays and partial working days
- Reduced working hours during Ramadan
- Alternate weekend shutdowns
- Different overtime rules for normal days and the day before a holiday
- Limits on overtime on consecutive days
- Weekend production when it was needed
- Changes in working hours, shift patterns and how many assembly lines were running
So the headline capacity of a line could be very different from the capacity actually available to the production plan.
My approach
I built a formula-driven Excel model that classifies every calendar date, applies the right operating rule to it, and consolidates the result into monthly and annual capacity. Nothing is averaged across a month.
Each date is classed as a normal production day, a public holiday, a partial or reduced-hours day, a normal weekend, a scheduled weekend shutdown or an extra production weekend, and checked for normal or extended overtime. The available hours are then converted into units with configurable line and operating parameters.
The overtime logic
Overtime can’t simply be added to every working day; normal overtime has to follow an allowed pattern rather than run on consecutive days. The model reads the surrounding calendar and finds the dates where overtime fits.
It also spots a production day that comes right before a holiday or non-working day. There a longer overtime window can be used, because nobody has to be back for normal production the next morning. That separates normal overtime capacity from extended pre-holiday overtime capacity without a planner checking each date by hand.
Five operating scenarios
- Base capacityNormal calendar, standard working pattern and routine overtime rules.
- Additional weekendsScheduled non-working Saturdays switched on when more output is needed.
- Weekends + overtimeExtra weekend working combined with the overtime that applies.
- Extended operationFurther production days where they are operationally possible.
- Maximum availableThe ceiling of the current setup, before extra manufacturing resources would be needed.
Questions it answers
- Can this month’s requirement be met within standard capacity?
- Which months are structurally short of capacity?
- How much does weekend working add, and how much does overtime add?
- Which dates give the most useful overtime?
- Is Sunday or extra weekend production actually needed?
- What is the most the current configuration can produce, and at what demand would more resources or investment be needed?
Outcome
A repeatable way to turn the real production calendar into realistic capacity, traceable back to the days and assumptions behind each number.
Its main value was finding the least disruptive operating configuration that meets a given requirement, so planners and management could step through the options instead of going straight to maximum overtime or extra production days.
Exact working hours, overtime limits, line rates and capacity figures are internal and not published here. The method is shown; the operating data is not. The screenshot is a recreation with illustrative data, not the original file.
Planning should turn complexity into action.
Open to Production Planner, Materials Planner, Production Controller and Supply Chain Planning roles in the UK.