Question

please answer question A and B of Problem 4 showing all revelant working to arrive at the answers.

Problem 4 Kelly Spaugh, course scheduler of a technical college's business department, needs to assign instructors to courses next semester. As a criterion for judging who should teach each course, Kelly reviews the student evaluations for teaching for the past two years. These ratings are shown in the table below. Professor Statistics Management Finance Economics Management Science Strausbaugh 85 Kelley 80 75 Davidson 65 55 Merkle A. Find the best assignment of professors to courses to maximize the overall teaching ratings.. (40pt) B. Which course will require the school to hire a new professor? (10 pt)

Answer 1

Step 1: Create spreadsheet model as follows:

B16 - @ fx =SUMPRODUCT(B3:F6, B10:F13) А 1 Ratings 2 Professor 3 Strausbaugh 4 Kelley 5 Davidson 6 Merkle Course Statistics Management Finance Economics Mgmt Sc 70 60 80 75 85 80 60 80 75 85 65 55 80 60 65 95 4065 55 50 8 Optimal assignment 9 Professor Statistics Management Finance Economics Mgmt Sc Row Total 10 Strausbaugh 11 Kelley 12 Davidson 13 Merkle 0 14 Column Total 0 0 0 0 0 16 Total rating 1 0

EXCEL FORMULAS:

Optimal assignment
Professor	Statistics	Management	Finance	Economics	Mgmt Sc	Row Total
Strausbaugh						=SUM(B10:F10)
Kelley						=SUM(B11:F11)
Davidson						=SUM(B12:F12)
Merkle						=SUM(B13:F13)
Column Total	=SUM(B10:B13)	=SUM(C10:C13)	=SUM(D10:D13)	=SUM(E10:E13)	=SUM(F10:F13)
Total rating	=SUMPRODUCT(B3:F6,B10:F13)

Step 2: Enter Solver Parameters as follows:

S Solver Parameters Set Objective: $B$16 To: Max Min O Value Of: 0 By Changing Variable Cells: $B$10:$F$13 Subject to the Constraints: $B$14:SF514 <= 1 $G$10:5G913 = 1 Add Change Delete Reset All Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems. Solve Close

Step 3: Click Solve on Solver Parameters to generate the solution:

B16 G H I J K L M N A 1 Ratings 2. Professor 3 Strausbaugh 4 Kelley 5 Davidson 6 Merkle - fx =SUMPRODUCT(B3:F6,10:F13) B C D E F Course Statistics Management Finance Economics Mgmt SC 70 60 80 75 85 80 60 80 75 85 65 55 80 60 65 95 4065 55 50 Solver Results Solver found a solution. All constraints and optimality conditions are satisfied. Reports Answer Keep Solver Solution Sensitivity Limits O Restore Original Values Return to Solver Parameters Dialog 8 Optimal assignment 9 Professor Statistics Management Finance Economics Mgmt Sc Row Total | 10 Strausbaugh 0 0 0 1 0 11 Kelley 0 0 0 0 1 12 Davidson 0 0 1 0 0 13 Merkle 0 0 0 0 1 1 14 Column Total 1 0 1 1 1 OK Cancel 1 Solver found a solution. All constraints and optimality conditions are satisfied. When Simplex LP is used, this means Solver has found a global optimal solution. Total rating 3 35

Optimal assignment is indicated by value 1 in the Optimal assignment matrix (yellow colored table).

Optimal assignment is:

Professor	Course
Strausbaugh	Economics
Kelley	Mgmt Sc
Davidson	Finance
Merkle	Statistics

Maximum teaching ratings = 335

b)

Management course is not assigned. So, the school has to hire a new professor for this course

Management