PLEASE USE EXCEL SOLVER TO ANSWER. I WOULD LIKE TO HAVE SCREENSHOTS OF SPREADSHEET WITH FORMULAS. THANKS!!

Case Problem

The Department of Management Science and Information Technology at Tech

T

he management science and information technology department at Tech offers between $36$ and forty $3-$hour course sections each semester. Some of the courses are taught by graduate student instructors, whereas $20$ of the course sections are taught by the $10$ regular, tenured faculty in the department. Before the beginning of each year, the department head sends the faculty a questionnaire, asking them to rate their preference for each course using a scale from $1$ to $5,$ where $1$ is "strongly preferred," $2$ is "preferred but not as strongly as $1,3$ is "neutral," $4$ is "prefer not to teach but not strongly," and $5$ is "strongly prefer not to teach this course." The faculty have returned their preferences, as follows:

For the fall semester the department will offer two sections each of $3424$ and $4464$; three sections of $3434,3444,4434,4444,$ and $4454$; and one section of $3454.$

The normal semester teaching load for a regular faculty member is two sections. $($Once the department head determines the courses, he will assign the faculty he schedules the course times so they will not conflict.$)$ Help the department head determine a teaching schedule that will satisfy faculty teaching preferences to the greatest degree possible.

$\backslash$table$[[,$Course,$],[$Faculty Member,$3424,3434,3444,3454,4434,4444,4454,4464],[$Clayton$,2,4,1,3,2,5,5,5],[$Houck$,3,3,4,1,2,5,5,4],[$Huang$,2,3,2,1,3,4,4,4],[$Major$,1,4,2,5,1,3,2,2],[$Moore$,1,1,4,4,2,3,3,5],[$Ragsdale$,1,3,1,5,4,1,1,2],[$Rakes$,3,1,2,5,3,1,1,1],[$Rees$,3,4,3,5,5,1,1,3],[$Russell$,4,1,3,2,2,5,5,5],[$Sumichrast$,4,3,1,5,2,3,3,1]]$