Question

Provide your solved Excel Model Spreadsheet + Answer Report + Sensitivity Report.

There are 1,400/2,500/1,200/700 applicants at Whoop! University to the English/Civil Engineering/Chemical Engineering/Physics programs respectively. Tuition per student is set at $2,000/$3,000/$4,000/$2,500 respectively. The total number of engineering students must be at most 3,000. The number of computer hours per week required by each student is 2/16/20/10 respectively and the available total computer hours per week are 75,000.The dormitories can house at most 5,000 in total. Whoop! University would like to select the best admission policy to maximize their total tuition.

Answer 1

Let,

xi = number of students in program i where i = {English= 1,Civil Engineering=2,Chemical Engineering=3,Physics programs=4}

Objective is to maximize tuition so objective function = Max 2000x1+3000x2+4000x3+2500x4

Subject to,

Number of applicants

x1 <= 1400
x2 <= 2500
x3 <= 1200
x4 <= 700

x2+x3 <= 3000 (Maximum total Engineering students)

2x1+16x2+20x3+10x4 <= 75000 (computer hours)

x1+x2+x3+x4 <= 5000 (max accommodation in dormitories)

xi >= 0 (non-negativity constraint)

Solving in solver we get,

maximized tuition = 14550000

Number of students in English = 1300,Civil Engineering = 1800,Chemical Engineering = 1200,Physics = 700

Solver screenshot

Solver formula

Answer report

Sensitivity report