Please help set up the excel sheet and microsoft excel solver program for this optimisation problem.

Guidelines:

1. Binary variables are more appropriate to be used to formulate the question into a linear optimisation problem solved by the "simplex LP" method. Integer variables and non-linear objective functions and constraints should be avoided. Discontinuous objective functions and constraints should also be avoided.

2. "integer optimality(%)" parameter in the solver options should be changed to zero to ensure solver converges to the true optimal solution

Project B Machine Scheduling Problem Problem A set of 11 different engineering components need to be polished and three machines are available for polishing them. Each component i (for i = 1,2,3,...,11) is to be assigned to only one of the machines (for j = 1,2,3) for polishing. The polishing time required for each component is denoted by T; and specified in the table below, and this polishing time is based on the size of the respective component and does not vary according to which machine is used because the three machines have the same speed. Each machine can polish only one com- ponent at a time and there is no pause in between the polishing of two consecutive components, i.e. when a machine finishes with one com- ponent, it will immediately start to polish the next component. How should the 11 components be allocated among the three machines so that the makespan is minimized? (Note that the makespan is the total time taken by the machine that is the last to finish polishing all its al- located components, assuming all three machines begin polishing their allocated components at the same time.) i 1 2 3 4 5 6 7 8 9 10 11 T: 26 16 5 19 52 98 8 36 68 3 75 The above problem is to be solved in advance as a trial for preparing the spreadsheet before the quiz. During the quiz, the same problem needs to be solved again based on mostly the same parameters except for the possibly different set of values that will be given in the quiz question for Ti. Project B Machine Scheduling Problem Problem A set of 11 different engineering components need to be polished and three machines are available for polishing them. Each component i (for i = 1,2,3,...,11) is to be assigned to only one of the machines (for j = 1,2,3) for polishing. The polishing time required for each component is denoted by T; and specified in the table below, and this polishing time is based on the size of the respective component and does not vary according to which machine is used because the three machines have the same speed. Each machine can polish only one com- ponent at a time and there is no pause in between the polishing of two consecutive components, i.e. when a machine finishes with one com- ponent, it will immediately start to polish the next component. How should the 11 components be allocated among the three machines so that the makespan is minimized? (Note that the makespan is the total time taken by the machine that is the last to finish polishing all its al- located components, assuming all three machines begin polishing their allocated components at the same time.) i 1 2 3 4 5 6 7 8 9 10 11 T: 26 16 5 19 52 98 8 36 68 3 75 The above problem is to be solved in advance as a trial for preparing the spreadsheet before the quiz. During the quiz, the same problem needs to be solved again based on mostly the same parameters except for the possibly different set of values that will be given in the quiz question for Ti