Linear Programming Ellies Electric has employ three electric

Linear Programming

Ellie\'s Electric has employ three electricians. On a particular day, six jobs are scheduled to be completed . A job does not need more than one electrician. The cost for each electrician to do each job is shown below.For each of the following questions, formulate a linear mathematical model in a standard format and implement it in Excel to find the optimal answer. Clearly define your decision variables, objective function and constraints in your formulation and provide screenshots of your Excel solutions. You can define the decision variables only once but you need to have a complete formulation for each question. a. What is the minimum cost assignment when each electrician can do two jobs? Provide both the formulation and the Excel solution b. What is the minimum cost assignment to do any three jobs when each electrician can do only one job? Provide both the formulation and the Excel | solution c. What is the minimum cost assignment when each electrician can do any number of jobs given that all jobs must be done? Provide both the formulation and the Excel solution d. What is the minimum cost assignment if every job must be performed by an electrician but an electrician can do zero, one, or more jobs along with the following conditions: if Job 2 is done by electrician A, then electrician A cannot do Job 1. -if Job 1 is assigned to electrician A, then electrician B cannot do Job 5. Jobs 4, 5, and 6 must be done by electricians A or B (or a combination of both) Provide both the formulation and the Excel solution for part for all of the conditions in one model (i.e., do not create different models for the different conditions) Note: The model must remain linear, and therefore the use of IF-THEN or similar functions is NOT permitted in the model

Solution

The problems are assigning jobs to the electricians, therefore decision variables are required to be double subscripts or takes the form of from to like that of transportation and assignment problems.Making use of the symbols used in the question, let me define a1,a2, a3,a4,a5,a6 representing allocation of electrician A to Jobs 1,2,3,4,5,6 respectively, b1,b2,b3,b4,b5,b6 representing electrician B assigned the jobs 1,2,3,4,5,6 respectively and similarly c1,c2,c3,c4,c5,c6 assignments of electrician C to jobs 1,2,3,4,5,6 respectively. These variables are binary, capable of taking only two values as zero and one. If electrician A is assigned job2 then a2=1 and if not then a2=0, similarly all other variables can take the value 1 in case of assignment and 0 on not assigned. These represents two mutually exclusive and exhaustives possibilities.

a) Decision variables are already defined as above, we are required to formulate the objective function and constraints in terms of the decision variables

Objective is to have the Minimum cost of assignments which is the sumproducts of the elements of cost coefficients (given matrix)) and decision variable matrix having similiar rows and columns having elements as 0 and 1.

Minimize sigma Caj*aj + Cbj*bj + Ccj*cj where j is varying from 1 to 6

Subject to the constraint each electrician can take two jobs but each job needs only one electrician as follows:

sigma aj = 2, sigma bj = 2 and sigma cj = 2

a1+b1+c1 =1 ,a2+b2+c2 = 1, a3+b3+c3 = 1, a4+b4+c4 = 1, a5+b5+c5 = 1, and a6+b6+c6 = 1

b. In case each electrician is supposed to do only on job, change in the constraints are

sigma aj =1, sigma bj = 1 and sigma cj = 1 ( row totals like columns totals are also equal to one)

Objective function and other constraints remains the same

c. With the condition that electricians are allowed to have any number of jobs, then the right hand side of the row constraints will be 6, the total number of jobs and are represented as

sigma aj <= 6,   sigma bj <= 6   and   sigma cj <= 6

Objective function and other constraints remains the same

d. If job 2 is done by A then A cannot do job1, mathematically   a2 = 1 then a1 = 0

--if job1 is assigned to A then B casnnot do job5, mathematically a1 = 1 then b5 = 0

--jobs 4,5 and 6 must be done by A or B or incombination, mathematically c4 = c5 = c6 = 0

Excel solutions a. A assigned job2 and job3, B assignedjob1 and job4 ,

b. A assigned job2 or job3, bassigned job5 and c takes job6