Below video demonstrates the use and application of the Excel Solver model contained by this downloadable file, implementing an exemplary case study:
This downloadable virtual product contains a template for optimal machine setup sequencing with Excel Solver, derived from a setup matrix. The Excel model is supposed to be an adjustable template suitable for any setup matrix with up to 200 cells. The template implements the setup matrix and derives the optimal setup sequence of a complete setup cycle in which every product is set up once. The products considered in the product are A, B, C, D, E, F, G, and H. The sequence identified by the tool minimizes the total sum of all setup times.
In detail, this virtual product contains:
- Excel file implementing exemplary setup matrix.
- Video tutorial that explains how to use the template.
The video explains how the underlying mathematical program is implemented using Excel Solver, and shows how the setup matrix can be reduced or expanded to contain a wider or more narrow product range.
Who will benefit from setup sequencing with Excel Solver
This downloadable product suits production and manufacturing managers, operation excellence managers, supply chain and production planning consultants, and production planners that:
- Need to identify optimal setup sequences in Excel.
- Want to understand how machine setup sequences are modeled.
- Want to learn mathematical programming for production planning.
Excel Solver, used for implementing the underlying mathematical setup-sequencing model, supports a setup matrix with up to 200 cells. If you have more than 200 job-to-job setup combinations, you should group your products in families or groups, and define setup times for these groups instead. Or, if this is no possible, you should use a Python model instead.
Brief description of the machine setup sequencing problem
This Excel template implements an example with eight product groups running on a machine: A, B, C, D, E, F, G, and H. Between each product group setup times differ. For example, preparing an offset printing machine depends on the previous and next printing job.
The user defines setup times for each setup pair, “from” (previous job) to “next” (upcoming job) product. Each product is setup once, and the model optimizes (i.e. minimizes) the total setup time for a complete setup cycle. That is, a complete sequence where there is a first setup for the first product (no previous product) and setups between all other products.
You can watch the video included by this downloadable product to understand how the underlying mathematical program and its implementation in Excel Solver.
Relevant KPIs considered and outputted by this model
The following KPI is supported by the model:
- Total setup time for a complete sequence (every product produced once).
Learn more about production planning and analytics
Optimal setup sequencing using mathematical programming is just one example of how you can use mathematical programming in daily operations. You can find many more application examples in this shop and on the SCDA blog.
Here are some other inspirational articles for you to read: