Use of excel worksheets with user-friendly interface to minimize the makespan in a permutation flow shop production system

Current research addresses the Permutation Flow Shop scheduling problem, characterized as Production Scheduling in Batch Process (PSBP) to minimize total time to complete the schedule (Makespan). A method to approach the problem of production scheduling is to turn it into Mixed Integer Linear Programming (MILP) and to solve it by using commercial mathematical programming packages. An electronic spreadsheet with user-friendly interface (ESUFI) was developed in Microsoft Excel. Results showed that it is possible to use ESUFI to solve small problems.


Introduction
Nowadays the planning of industrial activities is a necessity for their survival.Intense competition requires diversified products and delivery according to consumers´ requirements.These activities require quick decision-making and lowest possible cost.
The need for scheduling in the chemical industry is becoming more mandatory especially in the batch production mode.According to Reklaitis (1985), batch production may be described as income-oriented production, with equipment network connectivity and resource limitations.Mendéz et al. (2006) classifies this type of operation as single stage, subdivided into single or parallel resources, and multiple stages, subdivided into multipurpose (Job Shop) and multiproduct (Flow Shop).
The diversity and complexity of a batch production industry recommend the application of production planning techniques to meet consumer demands.Thus, scientific development in the area of Production Scheduling is increasing, especially with regard to batch processes.Scheduling is defined, according to Baker (1974), as the allocation of resources over time to perform a collection of tasks.
The scheduling problem addressed in current research is of the batch type -multiproduct -Permutation Flow Shop.According to MacCarthy and Liu (1993) and Baker (1974), Flow Shop is a type of process where all tasks have a similar flow pattern, that is, they have the same processing schedule on all resources and the number of resources in each stage of production equals one.
The environment of the permutation Flow Shop production scheduling problem is described by MacCarthy and Liu (1993), Baker (1974), Taillard (1993), and Moccellin (1995) as a type of Flow Shop in which the processing order of tasks is the same in all resources.
Batch-Multiproduct plants are in general employed for a set of products whose income structure is the same and production lines are also referred to as Flow Shop.States many chemical processing industries, such Acta Scientiarum.Technology Maringá, v. 36, n. 3, p. 453-461, July-Sept., 2014 as oil and paint, pharmaceutical and fine chemistry industries, fit into this category.Sundaramoorthy and Karimi (2005) reported on the production scheduling problem in the short term for plants in multipurpose batches.The above study provided one formulation based on mixed integer linear programming (MILP), using a continuous representation of synchronous time slots and a new idea of multiple balances (time, mass, resources, etc.).The model works without big-M constraints and is equally effective to maximize profits and minimize makespan.Burkard and Hatzl (2006) researched the problem of production scheduling in the chemical industry.The objective of the study was to implement an objective function to minimize the makespan by the employment of heuristics.Their study proposed an iterative algorithm construction that alternated between phases of construction and deconstruction.Strategies for diversification and intensification were also suggested to obtain optimal solutions in good moderate running times.Computational results showed the power of this algorithm.
Shakeri and Logendran ( 2007) studied the problem of production scheduling model as a mixed integer linear programming (binary) developed for scheduling tasks in multitasking environments, for which the number of completed tasks was not a good measure.The authors fixed issues for small, medium and large, with the aid of a tabu search algorithm.The solution obtained from the algorithm was compared with that of the optimal solution or the upper bound found by using Lagrangian relaxation.Pan et al. (2008) describe a study for shortterm scheduling of multipurpose batch plants using Mixed Integer Linear Programming.The research presented the network states and tasks (STN) to eliminate the inconvenience of precedence-based formulations which typically include a large number of batches.Rules have been proposed in the study heuristics for solving the problem.The results were effective to find the best solution to problems.Lin and Liao (2012) presented a paper about a scheduling problem for a two-stage assembly shop in a machinery factory.Its aim was to minimize the weighted sum of makespan, total completion time and total tardiness.A Mixed Integer Programming (MIP) model was developed for solving small-size problems and three heuristics were proposed for solving medium-and large-size problems.Koné et al. (2013) have studied scheduling problem that takes into account storage resources which may be produced or consumed by activities.The role model was elaborated in the Mixed Integer Linear Programming.

Material and methods
For the development of a user-friendly interface to solve a production scheduling model for the permutation Flow Shop system, the paper was divided into five parts: Model development, Model formulation, Model resolution in Excel spreadsheets of Microsoft, Creation of interface between user and Worksheet in Visual Basic Applications language (VBA) of Microsoft's Visual Basic incorporated in all Microsoft Office programs and Evaluation of the consistency of the interface.
The problem proposed with regard to a multiproduct batch plant, characterized as a permutation Flow Shop environment, was analyzed for the model development.At this stage, the objective function was defined whilst considering the case of production scheduling as a mixed integer linear programming problem (MILP).
In this permutation Flow Shop programming environment, three research scenarios were modelled, all of them to minimize total task completion time (Makespan).
The model constraints are: a) the storage of intermediate products should not be available between the processing resources, that is, if a product is processed at resource j and resource j+1 is not available at the time of completion, the finished product should be kept at resource j, until resource j+1 is ready; b) after finishing the processing of a product at the last resource (equipment), this product is immediately sent to the stock of finished products; c) all resources are initially empty at time zero and the manufacture of any product may be delayed at an arbitrary amount of time to keep it in the previous resource; d) the ordering of tasks on each resource is the same.After finishing the modelling, the mathematical equations of all three scenarios were manually transcribed into Excel spreadsheets and solved by the Add-in Solver.
VB language was used to create an interface between the user and the Excel spreadsheet.Consequently, all mathematical equations modeled on the three scenarios, along with the Add-in Solver, were developed to create a generalized programming model for this environment, with the restrictions imposed by the proposed model.
The evaluation of the consistency of the spreadsheet interface with the user was done by comparing the results manually obtained with Excel spreadsheets with those obtained VBA language.
User-friendly interface for the resolution of a model of production scheduling in a permutation flow shop system with makespan reduction

Model development
Table 1 shows a production scenario with four tasks (T1, T2, T3, T4) processed in three features in series.Table 1 shows that the first task is first processed in machine (Resources) 1 in 3.5h; machine (Resources) 2 in 4.3h; machine (Resources) 3 in 8h.The processing order of tasks 1, 2, 3 and 4 may be any combination of the four tasks but it must comply with the script on the machine first set 1, 2 and 3.

Model formulation
The mathematical modeling of the scenario in Table 1 is shown by the equations provided in this section where Equation 1 represents the main goal of the problem to be solved.

Cnm Minimize
(1) Variable C in Equation 1 represents the Makespan, while N and M represent respectively the number of tasks and the number of resources.Thus, the objective function expressed by Equation 1comprises finding, among the various combinations of tasks and resources, the NM sequence that provides the lowest Makespan.For scenario 1, N = 4 and M = 3 in the objective function.This objective function is subject to a number of constraints, as described below.As Equations 2 and 3 show, the first constraints are binary.
Variable i represents the task to be processed at the resource and k represents the position of this task in the order of sequencing.Equations 2 and 3 thus represent a discrete optimization problem involving the decision between two alternatives, that is, if a task is processed at a given resource, the other tasks cannot be processed concurrently at the same resource.Therefore, X ik is a binary variable defined as: X ik = 1, if task i is in position k, and X ik = 0, otherwise.Each task is sequentially processed by resources 1 (machine -1), 2 (machine -2) and 3 (machine -3), respecting the permutation Flow Shop programming environment.
Equation 4 represents another problem constraint which also involves Makespan.In this equation, C k,j is the time of end of processing, by resource j, of the task occupying position k in the sequence; N is the number of tasks; X s,k is the binary variable, and TP s,j is the time for processing task i at resource j.
Generally, the solution of the various alternatives of Equation 4shows that, for a scheduled task to be processed in resource j, from the second order of sequencing, it must present a Makespan greater than or equal to the Makespan of a task placed earlier on the same resource j, plus the processing time of the same task on resource j.
Another important constraint is Equation 5 which also involves the Makespan of the tasks in the resources.Equation 5shows that the Makespan of the task of order k on resource j is greater than or equal to the Makespan of the same task on resource j-1 plus the sum of the products of the binary variable X s,k and the processing times TP s,j , starting with resource 2 (machine -2) and finishing with resource 3 (machine -3).
The Makespan of the task placed in sequence 1 of the production scheduling, at resource j, is expressed in Equation 6. Equation 6 provides the constraint that the Makespan of such task is greater than or equal to the Makespan of the task placed in sequence 1 of the production scheduling, at resource j-1, plus the sum of the products of the binary variable X s,1 and the processing times TP s,j .In this equation, resource j must start with resource 2 (machine -2) and finish with resource 3 (machine -3), according to the model initially proposed.
Another important constraint is the Makespan of the task sequenced with order 1 on resource 1 (machine -1), represented by Equation 7 which shows that the Makespan of this task must be greater than or equal to the sum of the products of the binary variable (X 1,1 , X 2,1 , X 3,1 , X 4,1 ) and the processing times (TP 1,1 , TP 2,1 , TP 3,1 , TP 4,1 ).
Constraint in Equation 8shows that the Makespan sequenced in the order k on resource j must be greater than or equal to the Makespan sequenced in the order k-1 on resource j +1, starting from the order of sequence 1 on resource 1 (machine -1).
Finally, the last constraint of the problem concerns the question of non-negativity of the model, which is represented by the general Equation 9.
Model resolution in excel spreadsheets Equations (1) through (9) of the four tasks and three resource models were inserted in the worksheet, in different cells.Further, the Add-in Solver, available within the Microsoft Excel electronic spreadsheet, was used to optimize the scheduling of the tasks on the resources.
In general, so that the manual resolution of the processing orders scheduling model proposed in this study could be developed, all available data were first organized on an Excel spreadsheet, separating the cells representing the decision variables and the objectivefunction.For each problem constraint, a formula was developed in a separate cell of the spreadsheet, corresponding to the left-hand side (LHS) and the right-hand side (RHS) of the restriction.
LHS and RHS correspond to the transformation of the set of constraints into a set of equivalent equations, by introducing variables that represent the gap between the left (LHS) and right (RHS) sides of the inequalities, as shown in Figure 1.
After inserting all the equations of the PSBP model, representing the objective function, decision variables and restrictions, in the Microsoft Excel electronic spreadsheet shown in Figure 1, the Addin Solver, available in the tool bar of this spreadsheet, was used to find the optimal solution for the problem.
The steps required to use Solver are: Click the Office button, Excel Options, which opens a dialog box, where the user must click on 'Add-ins', which displays the options of the selected menu, with various add-ins.The user should then select 'Solver'.In the selection box 'Manage', select 'Excel Add-ins' and click the 'Go' button to enable the Microsoft Excel Solver.After that, it is necessary to choose the add-in Solver in the new dialog box that is opened.After enabling Solver, the user must select cell D3 of the Microsoft Excel spreadsheet, shown in Figure 1.The user must then click on 'Data' and 'Solver', as provided in the toolbar.This will open a data window, called 'Solver Parameters'.
It is necessary to indicate in the Solver Parameters box which cells represent the objective function, the decision variables and the problem´s constraints.Thus, in the location 'Set Target Cell', the user must choose the cell that represents the objective function and in 'Equal to' choose minimization, since the objective function of the problem is to minimize the total time of task completion.'By changing cells' represent the cells of the decision variables of the problem, and 'Subject to the constraints' represent the LHS and RHS of the constraints.
After all variables of the model have been inserted in the Solver dialog box, the model is asked to solve the objective function.Prior to that, one should choose options and adjust some parameters.In a specific case, a maximum time of 100 seconds was set, with a number of iterations 1000, precision 0.000001, tolerance 5%, and convergence 0.001, assuming a non-negative linear model, tangent estimates, forward derivatives, and Newton search.These parameters presented were not modified, or rather, they are available and set by the Excel Solver.
After the parameters in the 'Solver Options' box have been defined, one must choose the option 'OK', which will open a new dialog box with an option to choose the Solver reports.In this case, the report 'Answer' is chosen.
After performing all the steps above, the add-in Solver, chosen to perform the optimization of the Makespan, that is, the minimization of the objective function of the problem, and therefore the reduction of the total time for the completion of tasks, will solve all the iterations until the optimal solution is reached, shown in cell D3 in Figure 1 in the Excel spreadsheet, at the value of 35 hours.
Creation of the friendly interface between the user and the excel spreadsheet in the language Visual Basic For Applications (Vba) For the creation of the user-friendly interface with the Excel spreadsheet (ESUFI) used to solve the model to reduce the Maskespan following the constraints set forth in the methodology, it was necessary to first define a standard spreadsheet.The standard spreadsheet refers to a Microsoft Excel spreadsheet, composed of all the equations described in the study, organized in a standard model.This means that each equation should be placed in an array of rows and columns, available to perform the generalization of the model.The generalization of this model was performed by the VBA programming language.
The implementation of VBA code in the spreadsheet made possible the generalization of the model of four tasks and three resources for the configuration of (n) tasks and (m) resources.A friendly interface between the user and the Microsoft Excel spreadsheet was thus created and the user should only report the number of tasks (n) and resources (m) and the processing time of each task at the respective resource.The user may then click on the button 'Optimize Scheduling Order' to obtain the best production sequence that minimizes the Makespan.Figure 2 exemplifies the model of the Microsoft Excel spreadsheet with the user-friendly interface.
To build the user-friendly interface created for ESUFI provided in Figure 2, it was necessary to carry out some validation tests for each of the model equations.These tests serve to validate the model, or rather, to show the veracity of each of the equations from the expansion of the four-task and threeresource model for different situations of (N) tasks and (M) resources.The tests also served to find a number of possible combinations of (N) and (M) to be applied by the Solver tool.

Evaluation of the interface´s consistency
The assessment of the consistency of the generalized equations in the Excel spreadsheet with user-friendly interface was performed by comparing the results manually obtained with Excel spreadsheets with those obtained with the user-friendly interface, using VBA language.
For the assessment of the consistency of generalized equations in the Excel worksheet with user-friendly interface, tests were performed in cases of production scheduling in small and medium businesses.Besides verifying the veracity of equations ( 1) through ( 9), expanded on the Excel worksheet with user-friendly interface, the tests sought to find the limits of adjustable cells on the spreadsheet.Adjustable cells mean the largest number of tasks (N) and resources (M) of the subclasses that have the possibility of solving the objective function (minimizing the Makespan) in the Excel worksheet with user-friendly interface, regardless of the final solution.
The correlation coefficient (r) given by Equation 10 (MOREIRA, 2000) was used to assess the relationship between the number of tasks (N) and resources (M) of the limiting subclasses of the Excel spreadsheet model with user-friendly interface.

Results and discussion
Tests of each problem of class {2} verified that all equations were consistent with the generic model equations.However, scope to increase the number of resources was still extant, since the expansion of the equations was not at the limit allowed in the Excel spreadsheet.Therefore, a new set of problems with N  {2} and M > 60 was tested, and the limit {2.98} was found.
The same behavior was observed with the class {3}.Consequently, a new set of problems with N  {3} and M > 60 was tested and the limit {3,62} was found.All equations of problems of class {4} were consistent with the generic model.However, subclass {4.60} could not be verified, since it went beyond the limit of Solver adjustable cells present in ESUFI.Thus, the limit {4,46} was found after the performance of further tests.The same behavior was observed for the class {5}.It was found that subclasses {5,40} and {5,60} went beyond the limit.New tests determined the limit {5,35}.Further tests with the class {6} pointed to the limit {6,27}, after it was found that subclasses {6,30}, {6,40} and {6,60} went beyond the limit of Solver adjustable cells present in ESUFI.
Following the same pattern, the limit observed for the subclasses {11,2} to {11,10}, {11,15}, {11,20}, {11,25}, {11,30}, {11,40}, and {11,60} was {11,7}, while for the subclasses {12,2} to {12,10}, {12,15}, {12,20}, {12,25}, {12,30}, {12,40}, and {12,60} the limit was {12,4}.As for the results of Observing the limits found for the classes of problems in Table 4, there is a clear reduction in the number of resources (M) as the number of tasks (t) increases.This reduction in the number of resources, concerning the limitation of adjustable cells in the ESUFI for the use of the Solver tool to solve the objective function of the PSBP problem, may be assessed by the application of the correlation coefficient (r), given by Equation 10.The value of -0.89 shows a very high inverse correlation ship between the number of tasks (n) and the number of resources (m), that is, for the adjustable cells of ESUFI.As the number of tasks increases, the number of resources decreases, resulting in a reduction in the number of resources to be programmed in ESUFI.
When carrying out the validation tests of the macros employed to automate the use of the Microsoft Excel using Visual Basic Application (VBA), a limitation of the spreadsheet concerning the amount of adjustable cells was detected, that is, as the number of tasks to be programmed in the spreadsheet increases, the number of resources decreases.Since production scheduling is limited to 13 tasks, the above shows that, within standard Excel, ESUFI may be employed for small-scale problems.
It may be suggested that it is important to develop studies applied to real cases and to use a professional Excel version to increase the number of adjustable cells, which would extend the limit of variables in the production scheduling and therefore permit working with larger problems.Further development of algorithms for solving the problem in question may be recommended, coupled to the comparison of results obtained with these algorithms and those obtained in current research.It is also highly important to investigate the effect of varying the correlation coefficient (R) in the number of resources (M) and the number of tasks (t).
However, it is worth noting that current study may be applied to several real cases, since it presented a diverse mix of products and tasks that may be sequenced according to the performance goal of production characterized as decreased total time finish tasks (MAKESPAN).

Conclusion
The present study shows that the ESUFI played a very important role in the production scheduling, since spreadsheets are known by most computer users and are available in almost all Office packages.
Due to the easy manipulation of ESUFI and VBA language, a user-friendly interface could be created between the user and the spreadsheet itself.
So that the production scheduling that minimizes the total time of completion of tasks may be generated, the number of tasks and resources and the processing time of the tasks in their respective resources are the only information required in the worksheet.

Figure 1 .
Figure 1.Excel electronic spreadsheet for the PSBP model.Cells J15 and J16 are equal to cells B7 and B8 and are larger than or equal to cells L15 and L16, which correspond to equations B6 + (B18 * D7 + B22 * D10 + B26 * D13 + B30 * D16) and B7 + (B18 * D8 + B22 * D11 + B26 * D14 + B30 * D17), respectively.Cell J18 is equal to cell L18 and corresponds to the equation (B18 * D6 + B22 * D9 + B26 * D12 + B30 * D15).Cells N6 to N11 are equal to cells B9, B10, B12, B13, B15, and B16, and are larger than or equal to cells P6 to P11, which in turn are equal to cells B7, B8, B10, B11, B13, and B14.Cells N13 to N24 are equal to cells B6 to B17, and are equal to or larger than cells P13 to P24, which in turn are equal to zero.After inserting all the equations of the PSBP model, representing the objective function, decision variables and restrictions, in the Microsoft Excel electronic spreadsheet shown in Figure1, the Addin Solver, available in the tool bar of this spreadsheet, was used to find the optimal solution for the problem.

Figure 2 .
Figure 2. Microsoft Excel spreadsheet model with user-friendly interface.
coefficient; n = number of limiting subclasses; X = value of the number of the task subclass (n); Y = value of the number of the resource subclass (m).