Question

Solve in EXCEL FORMAT, step by step

The dean of the western college of Business must plan the schools course offerings for the fall semester. Student demands make it necessary to offer at least 30 undergraduate and 20 graduate courses in the term. Faculty contracts also dictate that at least 60 courses be offered in total. Each undergraduate course taught costs the college an average of $2,500 in faculty wages, and each graduate course costs $3,000. How many undergraduate and graduate courses should be taught in the fall so that the total faculty salaries are kept to a minimum?

thank you!

Answer 1

Using excel solver simplex LP.

The objective is to

Minimize total faculty salaries by changing variable cells i.e the number of graduate and undergraduate courses such that :

Constraints C1: Number of undergraduate courses >=30

C2 :Number of graduate courses =20

C3:Total number of courses >=60

Total faculty salaries =(Number of undergraduate courses*Average faculty wages for the undergraduate course) +(Number of graduate courses * Average faculty wages for graduate course )

Total UG courses to be taught =40

Total Graduate courses to be taught =20

In excel :

Minimize C7 cell

C7=SUMPRODUCT(C3:C4,D3:D4)

By changing cells : C3:C4

Constraints :C3>=30

C4=20

C5>=60