This downloadable virtual product contains a Excel Solver job shop capacity plan template file and model. It furthermore comprises a small case study solving a job shop capacity planning problem with Excel Solver. A job shop manufacturer faces an accumulation of open sales orders and existing backlog orders. Orders vary in quantity and product category. Consequently, the resulting workload per order for the various work areas differs by order, and so do operational margins. Each work area has defined capacities, specified in available machine hours, and defined cost rates, specified in USD/hr. The job shop wants to maximize income from operations.
The Excel Solver job shop capacity planning model contained by this downloadable product derives the optimal production plan.
In detail, this virtual product contains:
- PDF file with a description of the job shop capacity planning problem.
- The PDF furthermore contains relevant layout overview and data summaries.
- Excel sheet with Excel Solver model implementing job shop capacity planning.
- Instructions on how to setup and solve the problem in Excel Solver.
The Excel sheet is the Excel Solver model implementation and serves as a template for customization, i.e. adaption to specific problems.
Who will benefit from this job shop capacity planning model?
This downloadable product serves supply chain analysts, operations researchers, production planners, and students who want to:
- Get their hands on a job shop capacity planning problem.
- Learn how to use mathematical programming for capacity planning.
- Learn Excel Solver.
- See how to implement capacity planning in Excel using optimization.
- Obtain a linear continuous optimization template in Excel Solver.
Brief case study description
A job shop manufacturer faces an accumulation of schedules sales orders and existing backlog orders. Orders vary in quantity and product category. Consequently, the resulting workload per order for the various work areas of the job shop differ for each order, and so do operational profit margins. Each work area has a defined capacity, specified in amount of available machine hours, and defined cost rates, specified in USD/hr. The job shop wants to maximize its profits, i.e. operational profits.
There are four work areas in the job shop:
- Drilling & milling
- Sand blasting
Below is a conceptual overview of the job shop.
For the upcoming week the production planner must decide which work orders should be fulfilled. The resulting capacity plan balances available capacities with production orders.
The capacity plan considers one week of production and aims at optimizing operational profits. Setup times between work orders are minimal and thus neglected. There is no optimal changeover or setup sequence. All work areas in the production layout have buffer areas for buffer storage and work in progress. Partially completed customer orders may be shipped, i.e. sales orders can be partially fulfilled.
Relevant KPIs traced and outputted by this model
The model contained by this downloadable product tracks relevant KPIs.
The following KPIs are supported by the model:
- Manufacturing costs [USD].
- Sales revenue fulfilled [USD].
- Operational income [USD].
- Capacity utilization by work area [%].
- Order fulfillment degree [%] (= parts produced vs. parts ordered or in backlog).
Learn more about job shop planning and optimization
You can find several other contributions on SCDA related to job shop planning, job shop optimization, job shop scheduling, job shop simulation, capacity planning and production planning.
Here are some exemplary articles for you to read: