Question: Assignment Model

Formulate the problem in a standard LP mathematical model, solve it using Excel Solver, describe in details how you structure the model in Excel spreadsheet, what are your optimal solutions, and please attach your Excel spreadsheet file along with the submission.

(Assignment problem) Elliot and Elliot is a public accounting firm with four persons working in the auditing department: Smith, Jones, Davis, and Nguyen. Each of these individuals is uniquely qualified with experience in specific industries. The manager of this department has just received four jobs that must be assigned to these individuals, and only one job may be assigned to each individual. The estimated number of days required to complete each of these jobs by the four individuals is given in the following table:

Individual Job1 Job 2 Job 3 Job 4

Smith 4 10 8 9

Jones 5 14 8 10

Davis 4 13 9 12

Nguyen 5 11 7 11

The manger would like to minimize the number of days required for the completion of these jobs. Formulate this as an LP problem. Solve this using excel solver. How many work days will be required to complete these jobs? Who will be assigned to each job?