Aluacha Balaclava College

Introduction:

As a part of the final project for Math Modeling course, I would like to propose a mathematical solution to the following problem (given on page 620 in “A first course in Mathematical Modeling (5th ed)”).

Problem:

This problem is given on page 620.

1995: Aluacha Balaclava College

Aluacha Balaclava College, an undergraduate facility, has just hired a new Provost whose first priority is the institution of a fair and reasonable faculty compensation system. She has hired your consulting team to design a compensation system that reflects the following circumstances and principles.

Faculty are ranked as Instructor, Assistant Professor, Associate Professor, and Professor. Those with Ph.D. degrees are hired at the rank of Assistant Professor. Ph.D. candidates are hired at the rank of Instructor and promoted automatically to Assistant Professor upon completion of their degrees. Faculty may apply for promotion from Associate Professor to Professor after serving at the rank of Associate for 7 or more years. Promotions are determined by the Provost, with recommendations from a faculty committee. Faculty salaries are for the 10-month period September through June, with raises effective beginning in September. The total amount of money available for raises varies yearly and is generally disclosed in March for the following year.

The starting salary this year for an Instructor with no prior teaching experience was $27,000; $32,000 for an Assistant Professor. Upon hire, faculty can receive credit for up to 7 years of teaching experience at other institutions.

Principles

1. All faculty should get a raise any year that money is available.

2. Promotion should incur a substantial benefit; e.g., promotion in the minimum possible time should result in a benfiet roughly equal to 7 years of normal raises.

3. Faculty promoted after 7 or 8 years in rank with careers of at least 25 years should make roughly twice as much at retirement as a starting Ph.D.

4. Experienced faculty should be paid more than less experienced in the same rank. The effect of additional years of experience should diminish over time; that is, if two faculty stay in the same rank, their salaries should equalize over time.

Design a new pay system, first without cost-of-living increases. Incorporate cost of living increases, and then design a transition process for current faculty that will move all salaries toward your system without reducing anyone’s salary. Existing faculty salaries, ranks, and years of service are shown in Table A.11. Discuss any refinements you think would improve your system.

The Provost requires a detailed compensation system plan for implementation, as well as a brief, clear, executive summary outlining the model, its assumptions, its strengths, its weaknesses, and its expected results, which she can present to the Board and faculty.

I modified the given table to include two additional variables: ID and Designation_Number. ID will uniquely identify a row in the table, and Designation_Number represents the numerical designation of the faculty (1 for Instructor, 2 for Assistant Professor, 3 for Associate Professor, and 4 for Professor)

Table A.11

ID	Experience	Designation	Salary	Designation_Number
1	4	ASSO	54000	3
2	19	ASST	43508	2
3	20	ASST	39072	2
4	11	PROF	53900	4
5	15	PROF	44206	4
6	17	ASST	37538	2
7	23	PROF	48844	4
8	10	ASST	32841	2
9	7	ASSO	49981	3
10	20	ASSO	42549	3
11	18	ASSO	42649	3
12	19	PROF	60087	4
13	15	ASSO	38002	3
14	4	ASST	30000	2
15	34	PROF	60576	4
16	28	ASST	44562	2
17	9	ASST	30893	2
18	22	ASSO	46351	3
19	21	ASSO	50979	3
20	20	ASST	48000	2
21	4	ASST	32500	2
22	14	ASSO	38462	3
23	23	PROF	53500	4
24	21	ASSO	42488	3
25	20	ASSO	43892	3
26	5	ASST	35330	2
27	19	ASSO	41147	3
28	15	ASST	34040	2
29	18	PROF	48944	4
30	7	ASST	30128	2
31	5	ASST	35330	2
32	6	ASSO	35942	3
33	8	PROF	57295	4
34	10	ASST	36991	2
35	23	PROF	60576	4
36	20	ASSO	48926	3
37	9	PROF	57956	4
38	32	ASSO	52214	3
39	15	ASST	39259	2
40	22	ASSO	43672	3
41	6	INST	45500	1
42	5	ASSO	52262	3
43	5	ASSO	57170	3
44	16	ASST	36958	2
45	23	ASST	37538	2
46	9	PROF	58974	4
47	8	PROF	49971	4
48	23	PROF	62742	4
49	39	ASSO	52058	3
50	4	INST	26500	1
51	5	ASST	33130	2
52	46	PROF	59749	4
53	4	ASSO	37954	3
54	19	PROF	45833	4
55	6	ASSO	35270	3
56	6	ASSO	43037	3
57	20	PROF	59755	4
58	21	PROF	57797	4
59	4	ASSO	53500	3
60	6	ASST	32319	2
61	17	ASST	35668	2
62	20	PROF	59333	4
63	4	ASST	30500	2
64	16	ASSO	41352	3
65	15	PROF	43264	4
66	20	PROF	50935	4
67	6	ASST	45365	2
68	6	ASSO	35941	3
69	6	ASST	49134	2
70	4	ASST	29500	2
71	4	ASST	30186	2
72	7	ASST	32400	2
73	12	ASSO	44501	3
74	2	ASST	31900	2
75	1	ASSO	62500	3
76	1	ASST	34500	2
77	16	ASSO	40637	3
78	4	ASSO	35500	3
79	21	PROF	50521	4
80	12	ASST	35158	2
81	4	INST	28500	1
82	16	PROF	46930	4
83	24	PROF	55811	4
84	6	ASST	30128	2
85	16	PROF	46090	4
86	5	ASST	28570	2
87	19	PROF	44612	4
88	17	ASST	36313	2
89	6	ASST	33479	2
90	14	ASSO	38624	3
91	5	ASST	32210	2
92	9	ASSO	48500	3
93	4	ASST	35150	2
94	25	PROF	50583	4
95	23	PROF	60800	4
96	17	ASST	38464	2
97	4	ASST	39500	2
98	3	ASST	52000	2
99	24	PROF	56922	4
100	2	PROF	78500	4
101	20	PROF	52345	4
102	9	ASST	35798	2
103	24	ASST	43925	2
104	6	ASSO	35270	3
105	14	PROF	49472	4
106	19	ASSO	42215	3
107	12	ASST	40427	2
108	10	ASST	37021	2
109	18	ASSO	44166	3
110	21	ASSO	46157	3
111	8	ASST	32500	2
112	19	ASSO	40785	3
113	10	ASSO	38698	3
114	5	ASST	31170	2
115	1	INST	26161	1
116	22	PROF	47974	4
117	10	ASSO	37793	3
118	7	ASST	38117	2
119	26	PROF	62370	4
120	20	ASSO	51991	3
121	1	ASST	31500	2
122	8	ASSO	35941	3
123	14	ASSO	39294	3
124	23	ASSO	51991	3
125	1	ASST	30000	2
126	15	ASST	34638	2
127	20	ASSO	56836	3
128	6	INST	35451	1
129	10	ASST	32756	2
130	14	ASST	32922	2
131	12	ASSO	36451	3
132	1	ASST	30000	2
133	17	PROF	48134	4
134	6	ASST	40436	2
135	2	ASSO	54500	3
136	4	ASSO	55000	3
137	5	ASST	32210	2
138	21	ASSO	43160	3
139	2	ASST	32000	2
140	7	ASST	36300	2
141	9	ASSO	38624	3
142	21	PROF	49687	4
143	22	PROF	49972	4
144	7	ASSO	46155	3
145	12	ASST	37159	2
146	9	ASST	32500	2
147	3	ASST	31500	2
148	13	INST	31276	1
149	6	ASST	33378	2
150	19	PROF	45780	4
151	4	PROF	70500	4
152	27	PROF	59327	4
153	9	ASSO	37954	3
154	5	ASSO	36612	3
155	2	ASST	29500	2
156	3	PROF	66500	4
157	17	ASST	36378	2
158	5	ASSO	46770	3
159	22	ASST	42772	2
160	6	ASST	31160	2
161	17	ASST	39072	2
162	20	ASST	42970	2
163	2	PROF	85500	4
164	20	ASST	49302	2
165	21	ASSO	43054	3
166	21	PROF	49948	4
167	5	PROF	50810	4
168	19	ASSO	51378	3
169	18	ASSO	41267	3
170	18	ASST	42176	2
171	23	PROF	51571	4
172	12	PROF	46500	4
173	6	ASST	35798	2
174	7	ASST	42256	2
175	23	ASSO	46351	3
176	22	PROF	48280	4
177	3	ASST	55500	2
178	15	ASSO	39265	3
179	4	ASST	29500	2
180	21	ASSO	48359	3
181	23	PROF	48844	4
182	1	ASST	31000	2
183	6	ASST	32923	2
184	2	INST	27700	1
185	16	PROF	40748	4
186	24	ASSO	44715	3
187	9	ASSO	37389	3
188	28	PROF	51064	4
189	19	INST	34265	1
190	22	PROF	49756	4
191	19	ASST	36958	2
192	16	ASST	34550	2
193	22	PROF	50576	4
194	5	ASST	32210	2
195	2	ASST	28500	2
196	12	ASSO	41178	3
197	22	PROF	53836	4
198	19	ASSO	43519	3
199	4	ASST	32000	2
200	18	ASSO	40089	3
201	23	PROF	52403	4
202	21	PROF	59234	4
203	22	PROF	51898	4
204	26	ASSO	47047	3

Objective

Our main objective is to design an optimal compensation and promotion system, which is fair, and at the same time minimizes the expenses (related to salary/promotions) of the University.

Assumptions:

1. We will solve the problem without considering the inflation (cost of living)

2. In the problem it was mentioned that if a faculty serves for more than 25 years in a specific rank, then he should get at least twice the salary of a starting PhD. But in the given data it is not mentioned for how many years the current faculty members have served in their respective roles. So I am assuming that if a faculty member has more than 25 years of experience, then we consider that he has served in that rank for 25 years.

3. Starting PhD candidate is assumed as Assitant Professor (with a salary of 32000$ per annum)

4. Since we are not given the faculty’s desired retirement age, and also the age of the faculty when they started their careers, we will make this assumption: All faculty members (excluding the instructors) start at the age of 25 years and retire at the age of 65. But the faculty can continue working even after the age of 65, if they desire. If the faculty retires at the age of 65 or later, and served for at least 25 years (experience), then he should get at least 64000$ (twice the salary of Assistant professor), during his retirement.

5. Once a faculty attains 25 years of experience, an additional factor (for retirement catch up) will be added, to make sure that he receives at least 64000$, when he retires at 65 years of age (or 40 years of experience). So to get maximum benefit, the faculty, who worked for more than 25 years, should consider retiring at 65 years of age or later, to get the maximum benefit of getting twice the salary of an assistant professor (start up PhD degree holder).

6. It was mentioned in the problem that the instructors would be automatically prompted to Assistant Professor level, once they obtain their PhD degree. So we will ignore the instructors from our analysis.

7. Since all the faculty members must get a raise every year for members who are already getting more than the average pay, will be given a raise of just 1$.

8. It is not given in the data, to which department the faculty works for. Usually, some department’s faculty get more pay than other departments. But since the department details are not given in the data, we will ignore the significance of the department while designing the salary and promotion structure.

R Packages

We need the following R packages to perform the analysis

• ggplot2
• gridExtra
• dplyr
• knitr

If these packages are not available, you have to insatll them using the command: “install.packages()”

library(ggplot2)

## 
## Attaching package: 'ggplot2'
## 
## The following object is masked _by_ '.GlobalEnv':
## 
##     msleep

library(knitr)
library(gridExtra)
library(dplyr)

## 
## Attaching package: 'dplyr'
## 
## The following object is masked from 'package:gridExtra':
## 
##     combine
## 
## The following object is masked from 'package:stats':
## 
##     filter
## 
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

Graphical analysis:

Let us do the graphical analysis.

Reading the data to a data frame:

library(knitr)
#setwd("C:/Users/Sekhar/Documents/R Programs/Math Modeling/Project")
df <- read.csv("data.csv")
#kable(df)

We will plot all the ranks separately, and fit a linear equation:

df_inst_proff <- df[df$Designation_Number == 1,]
df_assist_proff <- df[df$Designation_Number == 2,]
df_asso_proff <- df[df$Designation_Number == 3,]
df_proff <- df[df$Designation_Number == 4,]

##Plotting assistant professor salary
lin_function <- lm(data=df_assist_proff,Salary~Experience)
  
predict_sal <- predict(lin_function)
  
predict_df <- data.frame(Experience=df_assist_proff$Experience,Salary=predict_sal)
  
p1 <- ggplot(df_assist_proff,aes(x = Experience, y = Salary)) + 
  geom_point(color="blue",size=3)+
  geom_line(data=predict_df,aes(y=Salary),color="red") +
  labs(title="Assistant Professor \n salary",x="Experience in years", y="Salary")


##Plotting associate professor salary
  lin_function <- lm(data=df_asso_proff,Salary~Experience)
  
  predict_sal <- predict(lin_function)
  
  predict_df <- data.frame(Experience=df_asso_proff$Experience,Salary=predict_sal)
  
p2 <- ggplot(df_asso_proff,aes(x = Experience, y = Salary)) + 
  geom_point(color="green",size=3)+
  geom_line(data=predict_df,aes(y=Salary),color="red") +
  labs(title="Associate Professor \n salary",x="Experience in years", y="Salary")

  lin_function <- lm(data=df_proff,Salary~Experience)
  
  predict_sal <- predict(lin_function)
  
  predict_df <- data.frame(Experience=df_proff$Experience,Salary=predict_sal)
  
p3 <- ggplot(df_proff,aes(x = Experience, y = Salary)) + 
    geom_point(color="violet",size=3)+
  geom_line(data=predict_df,aes(y=Salary),color="red") +
  labs(title="Professor salary",x="Experience in years", y="Salary")

grid.arrange(p1,p2,p3,ncol=3,top="Figure-1: Salary, Experience and linear fit of all Ranks\n")

In figure-1, we plotted the salary vs experience for three ranks, along with a linear equation line. We can observe that the professors salary is having negative slope. This is because of the presence of outliers. A visual inspection is showing that 4 professors with less than 5 years of experience are getting more than 65000$. These observations are outliers. Unless we have a positive slope, we cannot design a fair compensation system, since we have the constraint that everyone should receive a raise, and due to the presence of outliers, a negative slope line will stay negative (unless the lower salaries are significantly raised, which increases the costs to the University). Hence we have to eliminate the salaries of professors who are getting over paid (whose salaries are already greater than 64000$), from further analysis. For such outliers we will give a nominal increase of just 1$ per year, and avoid them in further analysis. The following observations will be eliminated:

df_proff[df_proff$Salary > 64000,]

##      ID Experience Designation Salary Designation_Number
## 100 100          2        PROF  78500                  4
## 151 151          4        PROF  70500                  4
## 156 156          3        PROF  66500                  4
## 163 163          2        PROF  85500                  4

df_proff_eliminate <- df_proff[df_proff$Salary > 64000,]
df_proff <- df_proff[df_proff$Salary <= 64000,]

The following graph shows the plot of professors salary, after eliminating the outliers:

  lin_function <- lm(data=df_proff,Salary~Experience)
  
  predict_sal <- predict(lin_function)
  
  predict_df <- data.frame(Experience=df_proff$Experience,Salary=predict_sal)
  
ggplot(df_proff,aes(x = Experience, y = Salary)) + 
    geom_point(color="violet",size=3)+
  geom_line(data=predict_df,aes(y=Salary),color="red") +
  labs(title="Figure-2: Professor salary after eliminating outliers",x="Experience in years", y="Salary")

Now we see a positive slope for professors salary. Hence it makes sense to eliminate the outliers, perform linear regression to determine the raise. For the outliers we will just raise the salary by 1$ or by inflation. All these assumptions helps us to devise the following algorithm:

Algorithm:

Our main goal is to make sure that the salaries are paid fairly, and they should be based on the experience. This means, the existing salaries must be adjusted to make sure that \(Salary \propto Experience\). This must be made gradually, satisfying the constraints given in the problem. We will use linear regression (separately for each ranks), and give a nominal increase of salary (just 1$) to members lying above the linear regression line, and for members lying below the regression line will be given a raise based on the linear regresion. If the faculty member has more than 25 years of experience, then the raise should also include an additional component, so that he gets at least 64000$ at his retirement age (65 years). In this process, we must eliminate the outliers which will make the linear regression with negative slope. Such outliers are maintained separately (outliers will not be used to fit the linear regression), and the outliers will be given just a nominal raise of $1 only.

For promotions, we will promote a member to the next rank in the following 2 scenarios:

• If the average experience of the next level is less than the member’s experience, and if the average salary of the next level is more than the member’s salary, then we will promorte him to the next level.

• If the increment for a member is significant (at least 7 times the average increment in his rank), then we will promote him to next level.

Based on the logic discussed above we will use the following algorithm for optimal salary raise and promotions of the faculty members:

  Step 0: Separate the data into three data frames: Professor, Associate_Professor, and Assistant_Professor. Set the variable Inflation = 1. Create an empty data frame to house the outliers. 
  
  Repeat the following steps for 10 times (or desired number of times depending on how many years of projection is needed):      
  
      Step 1: Examine the data of Assistant_Professor, and if a member has to be promoted to Associate professor, then remove him from Assistant_Professor data frame and add him to Associate_Professor data frame.  
      
      Step 2: Examine the data of Associate_Professor, and if a member has to be promoted to Professor, then remove him from Assciate_Professor data frame and add him to Professor data frame.
      
      Step 3: Combine the Associate_professor_outliers, Assistant_professor_outliers and professor_outliers data frames with the Associate_Professor, Assistant_Professor and Professor data frames, respectively perform the following:
      
            3a. Fit a regression line separately (for the three data frames), and check the slope of the regression lines.
            
            3b. If the slope is positive, then go to step 3e.
            
            3c. Eliminate the outliers, if you get the negative slope in the regression line.
            
            3d. Fit the regression line again.
            
            3e. Using the linear equation obtained above, compute the Predicted_Salary
            
            3f. Get the difference between the predicted salary and the Current_Salary. Diff = Predicted_Salary - Current_Salary
             
            3g. If the difference is positive, then set Increment = Diff + Inflation, else Increment = Inflation
             
            3h. Set New_Salary = Current_Salary + Increment
             
            3i. If the Experience of a member is more than 25 years and less than or equal to 39 years, then 
             
                        Retirement_Catchup =   ((64000)/ (40 - experience) - New_Salary)
                        
                 Else if Experience >= 40, then         
                 
                        Retirement_Catchup =   ((64000) - New_Salary)
                        
                 Else Retirement_Catchup = 0
                 
            3j. If Retirement_Catchup <= 0, then Retirement_Catchup = 0
             
            3k. New_Salary = New_Salary + Retirement_Catchup
             
            3l. For Assistant professors and Associate professors only:
            
                3l-1. Get the average Average_Increment
                
                3l-2. If the Increment >= 7 times the Average_Increment, then promote to the next level 
                
                3l-3. Get the average salary of the next level and average experience of the next level: Avg_Salary_Next_Level and Avg_Exp_Next_Level.
                3l-4. If the New_Salary < Avg_Salary_Next_Level and Experience > Avg_Exp_Next_Level, then Promote to next level.


    Step 4: Increment everyone's experience by 1 year
    
    Step 5: Set Current_Salary = New_Salary

The following R code will implement the above algorithm on the given data. The output of the program is given after the program code. The program prints the salaries of the existing faculty, over the next 3 years, and also shows who should be promoted to the next level:

#Step 0:

df_inst_proff <- df[df$Designation_Number == 1,]
df_assist_proff <- df[df$Designation_Number == 2,]
df_asso_proff <- df[df$Designation_Number == 3,]
df_proff <- df[df$Designation_Number == 4,]

df_assist_proff_outliers <- data.frame()
df_asso_proff_outliers <- data.frame()
df_proff_outliers <- data.frame()

#df_assist_proff$promote <- "NO"
#df_asso_proff$promote <- "NO"
#df_proff$promote <- "NO"

budget <- data.frame()

for(i in 1:3)
{

df_assist_proff$promote <- "NO"
df_asso_proff$promote <- "NO"
df_proff$promote <- "NO"


  df_assist_proff <- rbind(df_assist_proff,df_assist_proff_outliers)
  df_assist_proff_outliers <- data.frame()


repeat{

  lm_assist <- lm(data = df_assist_proff, Salary~Experience)
      if(lm_assist$coefficients["Experience"] >= 0)
        {
          break
        }
      else{
        df_assist_proff_outliers <- rbind(df_assist_proff_outliers,df_assist_proff[order(df_assist_proff$Salary),][nrow(df_assist_proff),])
        
        df_assist_proff <- df_assist_proff[order(df_assist_proff$Salary),][-nrow(df_assist_proff),]
      }

      
}


assist_predict <- predict(lm_assist)

assist_predict_df <- data.frame(Experience = df_assist_proff$Experience,Salary = assist_predict)

assist_incr <- assist_predict - df_assist_proff$Salary

assist_incr <- ifelse(assist_incr <= 0, 1, assist_incr)

assist_new_sal <- df_assist_proff$Salary + assist_incr

assist_ret_catchup <- rep(0,nrow(df_assist_proff))

assist_ret_indx <- which(df_assist_proff$Experience >= 25 & df_assist_proff$Experience <= 39)

assist_ret_catchup[assist_ret_indx] <- 
                        (64000/ (40 - df_assist_proff$Experience[assist_ret_indx])) - df_assist_proff$Salary[assist_ret_indx]


assist_ret_indx_1 <- which(df_assist_proff$Experience > 39)

assist_ret_catchup[assist_ret_indx_1] <- 
                        (64000) - df_assist_proff$Salary[assist_ret_indx_1]


assist_ret_catchup <- ifelse(assist_ret_catchup <= 0, 0, assist_ret_catchup)

assist_new_sal <- assist_new_sal + assist_ret_catchup

df_assist_proff$promote[which(assist_incr >= 7*mean(assist_incr))] <- "YES"

#df_assist_proff$promote[which(assist_new_sal > 64000)] <- "NO"

#df_assist_proff$Salary <- assist_new_sal

if(nrow(df_assist_proff_outliers) > 0){
  df_assist_proff_outliers$Salary <- df_assist_proff_outliers$Salary + 1
  }


df_asso_proff <- rbind(df_asso_proff,df_asso_proff_outliers)
df_asso_proff_outliers <- data.frame()


repeat{

  
  lm_asso <- lm(data = df_asso_proff, Salary~Experience)
      if(lm_asso$coefficients["Experience"] >= 0)
        {
          break
        }
      else{
        df_asso_proff_outliers <- rbind(df_asso_proff_outliers,df_asso_proff[order(df_asso_proff$Salary),][nrow(df_asso_proff),])
        
        df_asso_proff <- df_asso_proff[order(df_asso_proff$Salary),][-nrow(df_asso_proff),]
        
      }

      
}

asso_predict <- predict(lm_asso)

asso_incr <- asso_predict - df_asso_proff$Salary

asso_predict_df <- data.frame(Experience = df_asso_proff$Experience,Salary = asso_predict)

asso_incr <- ifelse(asso_incr <= 0, 1, asso_incr)

asso_new_sal <- df_asso_proff$Salary + asso_incr

asso_ret_catchup <- rep(0,nrow(df_asso_proff))

asso_ret_indx <- which(df_asso_proff$Experience >= 25 & df_asso_proff$Experience <= 39)

asso_ret_catchup[asso_ret_indx] <- 
                        (64000/ (40 - df_asso_proff$Experience[asso_ret_indx])) - df_asso_proff$Salary[asso_ret_indx]


asso_ret_indx_1 <- which(df_asso_proff$Experience > 39)

asso_ret_catchup[asso_ret_indx_1] <- 
                        (64000) - df_asso_proff$Salary[asso_ret_indx_1]


asso_ret_catchup <- ifelse(asso_ret_catchup <= 0, 0, asso_ret_catchup)

asso_new_sal <- asso_new_sal + asso_ret_catchup

#df_asso_proff$promote[which(asso_incr >= 7*mean(asso_incr))] <- "YES"

#df_asso_proff$promote[which(asso_new_sal > 64000)] <- "NO"

#df_asso_proff$Salary <- asso_new_sal

if(nrow(df_asso_proff_outliers) > 0){
  df_asso_proff_outliers$Salary <- df_asso_proff_outliers$Salary + 1
  }












df_proff <- rbind(df_proff,df_proff_outliers)
  df_proff_outliers <- data.frame()

repeat{
  
  
  lm_proff <- lm(data = df_proff, Salary~Experience)
      if(lm_proff$coefficients["Experience"] >= 0)
        {
          break
        }
      else{
        df_proff_outliers <- rbind(df_proff_outliers,df_proff[order(df_proff$Salary),][nrow(df_proff),])
        
        df_proff <- df_proff[order(df_proff$Salary),][-nrow(df_proff),]
      }
    }


proff_predict <- predict(lm_proff)

proff_predict_df <- data.frame(Experience = df_proff$Experience,Salary = proff_predict)

proff_incr <- proff_predict - df_proff$Salary

proff_incr <- ifelse(proff_incr <= 0, 1, proff_incr)

proff_new_sal <- df_proff$Salary + proff_incr

proff_ret_catchup <- rep(0,nrow(df_proff))

proff_ret_indx <- which(df_proff$Experience >= 25 & df_proff$Experience <= 39)

proff_ret_catchup[proff_ret_indx] <- 
                        (64000/ (40 - df_proff$Experience[proff_ret_indx])) - df_proff$Salary[proff_ret_indx]

proff_ret_indx_1 <- which(df_proff$Experience >39)

proff_ret_catchup[proff_ret_indx_1] <- 
                        (64000) - df_proff$Salary[proff_ret_indx_1]


proff_ret_catchup <- ifelse(proff_ret_catchup <= 0, 0, proff_ret_catchup)

proff_new_sal <- proff_new_sal + proff_ret_catchup

#df_proff$Salary <- proff_new_sal


if(nrow(df_proff_outliers) > 0){
  df_proff_outliers$Salary <- df_proff_outliers$Salary + 1
  }



df_proff$Experience <- df_proff$Experience  + 1
df_assist_proff$Experience <- df_assist_proff$Experience  + 1
df_asso_proff$Experience <- df_asso_proff$Experience  + 1

df_proff_outliers$Experience <- df_proff_outliers$Experience  + 1
df_assist_proff_outliers$Experience <- df_assist_proff_outliers$Experience  + 1
df_asso_proff_outliers$Experience <- df_asso_proff_outliers$Experience  + 1




#Get the average salary and experience of the next levels:
proff_exp_mean <- mean(df_proff$Experience)
assoc_exp_mean <- mean(df_asso_proff$Experience)
#assist_exp_mean <- mean(df_assist_proff$Experience)


proff_sal_mean <- mean(df_proff$Salary)
assoc_sal_mean <- mean(df_asso_proff$Salary)
#assist_sal_mean <- mean(df_assist_proff$Salary)

df_assist_proff$promote[which(df_assist_proff$Experience >= assoc_exp_mean & df_assist_proff$Salary <= assoc_sal_mean)] <- "YES"

df_asso_proff$promote[which(df_asso_proff$Experience >= proff_exp_mean & df_asso_proff$Salary <= proff_sal_mean)] <- "YES"



assist_prom_indx <- which(df_assist_proff$promote == "YES")

if(length(assist_prom_indx) > 0)
  {
for(k in 1:length(assist_prom_indx))
  {
   if(assist_new_sal[assist_prom_indx[k]] - df_assist_proff[assist_prom_indx[k],]$Salary <  7*mean(assist_incr))
     {
       assist_new_sal[assist_prom_indx[k]] <- 7*mean(assist_incr) + df_assist_proff[assist_prom_indx[k],]$Salary
     }
  
  }

}

#assist_new_sal[assist_prom_indx] <- df_assist_proff$Salary[assist_prom_indx] + assist_incr[assist_prom_indx] * 6

#assist_new_sal[assist_prom_indx] <- ifelse(assist_new_sal[assist_prom_indx] - df_assist_proff$Salary[assist_prom_indx] >= (mean(assist_incr) * 7), assist_new_sal[assist_prom_indx],(mean(assist_incr) * 7))

 


asso_prom_indx <- which(df_asso_proff$promote == "YES")

#asso_new_sal[asso_prom_indx] <- df_asso_proff$Salary[asso_prom_indx] + asso_incr[asso_prom_indx] * 6
#asso_new_sal[asso_prom_indx] <- ifelse(asso_new_sal[asso_prom_indx] - df_asso_proff$Salary[asso_prom_indx] >= rep((mean(asso_incr) * 7),length(asso_prom_indx)), asso_new_sal[asso_prom_indx],(mean(asso_incr) * 7))

if(length(asso_prom_indx) > 0)
  {
for(k in 1:length(asso_prom_indx))
  {
   if(asso_new_sal[asso_prom_indx[k]] - df_asso_proff[asso_prom_indx[k],]$Salary <  7*mean(asso_incr))
     {
       asso_new_sal[asso_prom_indx[k]] <- 7*mean(asso_incr) + df_asso_proff[asso_prom_indx[k],]$Salary
     }
  
  }
}

print_assist <- data.frame(ID=df_assist_proff$ID,
                      Experience=df_assist_proff$Experience,
                      Designation = df_assist_proff$Designation,
                      This_year_Salary = df_assist_proff$Salary,
                      New_year_salary = assist_new_sal,
                      Promote = df_assist_proff$promote,
                      Increment = assist_new_sal - df_assist_proff$Salary
                      )

Assist_g_1 <- ggplot(print_assist,aes(x=Experience, y = This_year_Salary)) +
    geom_point(size=3, color = "red")+
    geom_line(data=assist_predict_df,aes(y=Salary),color="black") +
    labs(title=paste("Assistant Professor salary.\n at the beginning of year",i),x="Experience in years", y="Salary")

Assist_g_2 <- ggplot(print_assist,aes(x=Experience, y = New_year_salary,color=Promote)) +
    geom_point(size=3)+
    geom_line(data=assist_predict_df,aes(y=Salary),color="black") +
    labs(title=paste("Assistant Professor salary.\n at the beginning of year",(i+1)),x="Experience in years", y="Salary")

grid.arrange(Assist_g_1,Assist_g_2,ncol=2
             #,top="Figure-1: Salary, Experience and linear fit of all Ranks\n"
             )

print(lm_assist)

#kable(print_assist)
  print(print_assist)








print_asso_1 <- data.frame(ID=df_asso_proff$ID,
                      Experience=df_asso_proff$Experience,
                      Designation = df_asso_proff$Designation,
                      This_year_Salary = df_asso_proff$Salary,
                      New_year_salary = asso_new_sal,
                      Promote = df_asso_proff$promote,
                      Increment = asso_new_sal - df_asso_proff$Salary
                      )


print_asso_2 <- data.frame(ID=df_asso_proff_outliers$ID,
                      Experience=df_asso_proff_outliers$Experience,
                      Designation = df_asso_proff_outliers$Designation,
                      This_year_Salary = df_asso_proff_outliers$Salary,
                      New_year_salary = df_asso_proff_outliers$Salary + 1,
                      Promote = df_asso_proff_outliers$promote,
                      Increment = rep(1,nrow(df_asso_proff_outliers))
                      )

print_asso <- rbind(print_asso_1,print_asso_2)

asso_g_1 <- ggplot(print_asso,aes(x=Experience, y = This_year_Salary)) +
    geom_point(size=3, color = "red")+
    geom_line(data=asso_predict_df,aes(y=Salary),color="black") +
    labs(title=paste("Associate Professor salary.\n at the beginning of year",i),x="Experience in years", y="Salary")

asso_g_2 <- ggplot(print_asso,aes(x=Experience, y = New_year_salary,color=Promote)) +
    geom_point(size=3)+
    geom_line(data=asso_predict_df,aes(y=Salary),color="black") +
    labs(title=paste("Associate Professor salary.\n at the beginning of year",(i+1)),x="Experience in years", y="Salary")

grid.arrange(asso_g_1,asso_g_2,ncol=2
             #,top="Figure-1: Salary, Experience and linear fit of all Ranks\n"
             )


#kable(print_asso)

print(lm_asso)
print(print_asso)






print_proff_1 <- data.frame(ID=df_proff$ID,
                      Experience=df_proff$Experience,
                      Designation = df_proff$Designation,
                      This_year_Salary = df_proff$Salary,
                      New_year_salary = proff_new_sal,
                      Promote = df_proff$promote,
                      Increment = proff_new_sal - df_proff$Salary
                      )


print_proff_2 <- data.frame(ID=df_proff_outliers$ID,
                      Experience=df_proff_outliers$Experience,
                      Designation = df_proff_outliers$Designation,
                      This_year_Salary = df_proff_outliers$Salary,
                      New_year_salary = df_proff_outliers$Salary + 1,
                      Promote = df_proff_outliers$promote,
                      Increment = rep(1,nrow(df_proff_outliers))
                      )

print_proff <- rbind(print_proff_1,print_proff_2)

proff_g_1 <- ggplot(print_proff,aes(x=Experience, y = This_year_Salary)) +
    geom_point(size=3, color = "red")+
    geom_line(data=proff_predict_df,aes(y=Salary),color="black") +
    labs(title=paste("Professor salary.\n at the beginning of year",i),x="Experience in years", y="Salary")

proff_g_2 <- ggplot(print_proff,aes(x=Experience, y = New_year_salary)) +
    geom_point(size=3,color="red")+
    geom_line(data=proff_predict_df,aes(y=Salary),color="black") +
    labs(title=paste("Professor salary.\n at the beginning of year",(i+1)),x="Experience in years", y="Salary")

grid.arrange(proff_g_1,proff_g_2,ncol=2
             #,top="Figure-1: Salary, Experience and linear fit of all Ranks\n"
             )


#kable(print_proff)
print(lm_proff)
print(print_proff)

temp <- data.frame(year=i,Budget = sum(proff_new_sal - df_proff$Salary) + sum(asso_new_sal - df_asso_proff$Salary) + sum(assist_new_sal - df_assist_proff$Salary)) 

budget <- rbind(budget,temp)

df_proff$Salary <- proff_new_sal
df_asso_proff$Salary <- asso_new_sal
df_assist_proff$Salary <- assist_new_sal

df_proff <- rbind(df_proff,df_asso_proff[(df_asso_proff$promote == "YES"),])
df_proff$Designation <- "PROF"

df_asso_proff <- df_asso_proff[(df_asso_proff$promote == "NO"),]


df_asso_proff <- rbind(df_asso_proff,df_assist_proff[(df_assist_proff$promote == "YES"),])
df_asso_proff$Designation <- "ASSO"

df_assist_proff <- df_assist_proff[(df_assist_proff$promote == "NO"),]




}

## 
## Call:
## lm(formula = Salary ~ Experience, data = df_assist_proff)
## 
## Coefficients:
## (Intercept)   Experience  
##     32084.5        408.6  
## 
##      ID Experience Designation This_year_Salary New_year_salary Promote
## 2     2         20        ASST            43508        55537.62     YES
## 3     3         21        ASST            39072        51101.62     YES
## 6     6         18        ASST            37538        49567.62     YES
## 8     8         11        ASST            32841        36170.15      NO
## 14   14          5        ASST            30000        33718.79      NO
## 16   16         29        ASST            44562        44563.00      NO
## 17   17         10        ASST            30893        35761.59      NO
## 20   20         21        ASST            48000        48001.00      NO
## 21   21          5        ASST            32500        33718.79      NO
## 26   26          6        ASST            35330        35331.00      NO
## 28   28         16        ASST            34040        46069.62     YES
## 30   30          8        ASST            30128        34944.47      NO
## 31   31          6        ASST            35330        35331.00      NO
## 34   34         11        ASST            36991        36992.00      NO
## 39   39         16        ASST            39259        51288.62     YES
## 44   44         17        ASST            36958        48987.62     YES
## 45   45         24        ASST            37538        49567.62     YES
## 51   51          6        ASST            33130        34127.35      NO
## 60   60          7        ASST            32319        34535.91      NO
## 61   61         18        ASST            35668        47697.62     YES
## 63   63          5        ASST            30500        33718.79      NO
## 67   67          7        ASST            45365        45366.00      NO
## 69   69          7        ASST            49134        49135.00      NO
## 70   70          5        ASST            29500        33718.79      NO
## 71   71          5        ASST            30186        33718.79      NO
## 72   72          8        ASST            32400        34944.47      NO
## 74   74          3        ASST            31900        32901.67      NO
## 76   76          2        ASST            34500        34501.00      NO
## 80   80         13        ASST            35158        36987.28      NO
## 84   84          7        ASST            30128        34535.91      NO
## 86   86          6        ASST            28570        34127.35      NO
## 88   88         18        ASST            36313        48342.62     YES
## 89   89          7        ASST            33479        34535.91      NO
## 91   91          6        ASST            32210        34127.35      NO
## 93   93          5        ASST            35150        35151.00      NO
## 96   96         18        ASST            38464        50493.62     YES
## 97   97          5        ASST            39500        39501.00      NO
## 98   98          4        ASST            52000        52001.00      NO
## 102 102         10        ASST            35798        35799.00      NO
## 103 103         25        ASST            43925        55954.62     YES
## 107 107         13        ASST            40427        40428.00      NO
## 108 108         11        ASST            37021        37022.00      NO
## 111 111          9        ASST            32500        35353.03      NO
## 114 114          6        ASST            31170        34127.35      NO
## 118 118          8        ASST            38117        38118.00      NO
## 121 121          2        ASST            31500        32493.11      NO
## 125 125          2        ASST            30000        32493.11      NO
## 126 126         16        ASST            34638        46667.62     YES
## 129 129         11        ASST            32756        36170.15      NO
## 130 130         15        ASST            32922        37804.40      NO
## 132 132          2        ASST            30000        32493.11      NO
## 134 134          7        ASST            40436        40437.00      NO
## 137 137          6        ASST            32210        34127.35      NO
## 139 139          3        ASST            32000        32901.67      NO
## 140 140          8        ASST            36300        36301.00      NO
## 145 145         13        ASST            37159        37160.00      NO
## 146 146         10        ASST            32500        35761.59      NO
## 147 147          4        ASST            31500        33310.23      NO
## 149 149          7        ASST            33378        34535.91      NO
## 155 155          3        ASST            29500        32901.67      NO
## 157 157         18        ASST            36378        48407.62     YES
## 159 159         23        ASST            42772        54801.62     YES
## 160 160          7        ASST            31160        34535.91      NO
## 161 161         18        ASST            39072        51101.62     YES
## 162 162         21        ASST            42970        54999.62     YES
## 164 164         21        ASST            49302        49303.00      NO
## 170 170         19        ASST            42176        54205.62     YES
## 173 173          7        ASST            35798        35799.00      NO
## 174 174          8        ASST            42256        42257.00      NO
## 177 177          4        ASST            55500        55501.00      NO
## 179 179          5        ASST            29500        33718.79      NO
## 182 182          2        ASST            31000        32493.11      NO
## 183 183          7        ASST            32923        34535.91      NO
## 191 191         20        ASST            36958        48987.62     YES
## 192 192         17        ASST            34550        46579.62     YES
## 194 194          6        ASST            32210        34127.35      NO
## 195 195          3        ASST            28500        32901.67      NO
## 199 199          5        ASST            32000        33718.79      NO
##      Increment
## 2   12029.6189
## 3   12029.6189
## 6   12029.6189
## 8    3329.1545
## 14   3718.7886
## 16      1.0000
## 17   4868.5935
## 20      1.0000
## 21   1218.7886
## 26      1.0000
## 28  12029.6189
## 30   4816.4715
## 31      1.0000
## 34      1.0000
## 39  12029.6189
## 44  12029.6189
## 45  12029.6189
## 51    997.3496
## 60   2216.9106
## 61  12029.6189
## 63   3218.7886
## 67      1.0000
## 69      1.0000
## 70   4218.7886
## 71   3532.7886
## 72   2544.4715
## 74   1001.6666
## 76      1.0000
## 80   1829.2764
## 84   4407.9106
## 86   5557.3496
## 88  12029.6189
## 89   1056.9106
## 91   1917.3496
## 93      1.0000
## 96  12029.6189
## 97      1.0000
## 98      1.0000
## 102     1.0000
## 103 12029.6189
## 107     1.0000
## 108     1.0000
## 111  2853.0325
## 114  2957.3496
## 118     1.0000
## 121   993.1057
## 125  2493.1057
## 126 12029.6189
## 129  3414.1545
## 130  4882.3984
## 132  2493.1057
## 134     1.0000
## 137  1917.3496
## 139   901.6666
## 140     1.0000
## 145     1.0000
## 146  3261.5935
## 147  1810.2276
## 149  1157.9106
## 155  3401.6666
## 157 12029.6189
## 159 12029.6189
## 160  3375.9106
## 161 12029.6189
## 162 12029.6189
## 164     1.0000
## 170 12029.6189
## 173     1.0000
## 174     1.0000
## 177     1.0000
## 179  4218.7886
## 182  1493.1057
## 183  1612.9106
## 191 12029.6189
## 192 12029.6189
## 194  1917.3496
## 195  4401.6666
## 199  1718.7886

## 
## Call:
## lm(formula = Salary ~ Experience, data = df_asso_proff)
## 
## Coefficients:
## (Intercept)   Experience  
##    42897.72        97.74  
## 
##      ID Experience Designation This_year_Salary New_year_salary Promote
## 1     1          5        ASSO            54000        54001.00      NO
## 9     9          8        ASSO            49981        49982.00      NO
## 10   10         21        ASSO            42549        61071.18     YES
## 11   11         19        ASSO            42649        44657.05      NO
## 13   13         16        ASSO            38002        44363.83      NO
## 18   18         23        ASSO            46351        64873.18     YES
## 19   19         22        ASSO            50979        69501.18     YES
## 22   22         15        ASSO            38462        44266.09      NO
## 24   24         22        ASSO            42488        61010.18     YES
## 25   25         21        ASSO            43892        62414.18     YES
## 27   27         20        ASSO            41147        44754.79      NO
## 32   32          7        ASSO            35942        43484.16      NO
## 36   36         21        ASSO            48926        67448.18     YES
## 38   38         33        ASSO            52214        70736.18     YES
## 40   40         23        ASSO            43672        62194.18     YES
## 42   42          6        ASSO            52262        52263.00      NO
## 43   43          6        ASSO            57170        57171.00      NO
## 49   49         40        ASSO            52058        70580.18     YES
## 53   53          5        ASSO            37954        43288.68      NO
## 55   55          7        ASSO            35270        43484.16      NO
## 56   56          7        ASSO            43037        43484.16      NO
## 59   59          5        ASSO            53500        53501.00      NO
## 64   64         17        ASSO            41352        44461.57      NO
## 68   68          7        ASSO            35941        43484.16      NO
## 73   73         13        ASSO            44501        44502.00      NO
## 75   75          2        ASSO            62500        62501.00      NO
## 77   77         17        ASSO            40637        44461.57      NO
## 78   78          5        ASSO            35500        43288.68      NO
## 90   90         15        ASSO            38624        44266.09      NO
## 92   92         10        ASSO            48500        48501.00      NO
## 104 104          7        ASSO            35270        43484.16      NO
## 106 106         20        ASSO            42215        44754.79      NO
## 109 109         19        ASSO            44166        44657.05      NO
## 110 110         22        ASSO            46157        64679.18     YES
## 112 112         20        ASSO            40785        44754.79      NO
## 113 113         11        ASSO            38698        43875.12      NO
## 117 117         11        ASSO            37793        43875.12      NO
## 120 120         21        ASSO            51991        70513.18     YES
## 122 122          9        ASSO            35941        43679.64      NO
## 123 123         15        ASSO            39294        44266.09      NO
## 124 124         24        ASSO            51991        70513.18     YES
## 127 127         21        ASSO            56836        56837.00      NO
## 131 131         13        ASSO            36451        44070.60      NO
## 135 135          3        ASSO            54500        54501.00      NO
## 136 136          5        ASSO            55000        55001.00      NO
## 138 138         22        ASSO            43160        61682.18     YES
## 141 141         10        ASSO            38624        43777.38      NO
## 144 144          8        ASSO            46155        46156.00      NO
## 153 153         10        ASSO            37954        43777.38      NO
## 154 154          6        ASSO            36612        43386.42      NO
## 158 158          6        ASSO            46770        46771.00      NO
## 165 165         22        ASSO            43054        61576.18     YES
## 168 168         20        ASSO            51378        51379.00      NO
## 169 169         19        ASSO            41267        44657.05      NO
## 175 175         24        ASSO            46351        64873.18     YES
## 178 178         16        ASSO            39265        44363.83      NO
## 180 180         22        ASSO            48359        66881.18     YES
## 186 186         25        ASSO            44715        63237.18     YES
## 187 187         10        ASSO            37389        43777.38      NO
## 196 196         13        ASSO            41178        44070.60      NO
## 198 198         20        ASSO            43519        44754.79      NO
## 200 200         19        ASSO            40089        44657.05      NO
## 204 204         27        ASSO            47047        65569.18     YES
##      Increment
## 1       1.0000
## 9       1.0000
## 10  18522.1754
## 11   2008.0469
## 13   6361.8257
## 18  18522.1754
## 19  18522.1754
## 22   5804.0853
## 24  18522.1754
## 25  18522.1754
## 27   3607.7873
## 32   7542.1621
## 36  18522.1754
## 38  18522.1754
## 40  18522.1754
## 42      1.0000
## 43      1.0000
## 49  18522.1754
## 53   5334.6813
## 55   8214.1621
## 56    447.1621
## 59      1.0000
## 64   3109.5661
## 68   7543.1621
## 73      1.0000
## 75      1.0000
## 77   3824.5661
## 78   7788.6813
## 90   5642.0853
## 92      1.0000
## 104  8214.1621
## 106  2539.7873
## 109   491.0469
## 110 18522.1754
## 112  3969.7873
## 113  5177.1237
## 117  6082.1237
## 120 18522.1754
## 122  7738.6429
## 123  4972.0853
## 124 18522.1754
## 127     1.0000
## 131  7619.6045
## 135     1.0000
## 136     1.0000
## 138 18522.1754
## 141  5153.3833
## 144     1.0000
## 153  5823.3833
## 154  6774.4217
## 158     1.0000
## 165 18522.1754
## 168     1.0000
## 169  3390.0469
## 175 18522.1754
## 178  5098.8257
## 180 18522.1754
## 186 18522.1754
## 187  6388.3833
## 196  2892.6045
## 198  1235.7873
## 200  4568.0469
## 204 18522.1754

## 
## Call:
## lm(formula = Salary ~ Experience, data = df_proff)
## 
## Coefficients:
## (Intercept)   Experience  
##    52532.88        28.95  
## 
##      ID Experience Designation This_year_Salary New_year_salary Promote
## 185 185         17        PROF            40748        52996.12      NO
## 65   65         16        PROF            43264        52967.17      NO
## 5     5         16        PROF            44206        52967.17      NO
## 87   87         20        PROF            44612        53082.98      NO
## 150 150         20        PROF            45780        53082.98      NO
## 54   54         20        PROF            45833        53082.98      NO
## 85   85         17        PROF            46090        52996.12      NO
## 172 172         13        PROF            46500        52880.31      NO
## 82   82         17        PROF            46930        52996.12      NO
## 116 116         23        PROF            47974        53169.84      NO
## 133 133         18        PROF            48134        53025.08      NO
## 176 176         23        PROF            48280        53169.84      NO
## 7     7         24        PROF            48844        53198.79      NO
## 181 181         24        PROF            48844        53198.79      NO
## 29   29         19        PROF            48944        53054.03      NO
## 105 105         15        PROF            49472        52938.22      NO
## 142 142         22        PROF            49687        53140.89      NO
## 190 190         23        PROF            49756        53169.84      NO
## 166 166         22        PROF            49948        53140.89      NO
## 47   47          9        PROF            49971        52764.50      NO
## 143 143         23        PROF            49972        53169.84      NO
## 79   79         22        PROF            50521        53140.89      NO
## 193 193         23        PROF            50576        53169.84      NO
## 94   94         26        PROF            50583        53256.70      NO
## 167 167          6        PROF            50810        52677.65      NO
## 66   66         21        PROF            50935        53111.93      NO
## 188 188         29        PROF            51064        53343.55      NO
## 171 171         24        PROF            51571        53198.79      NO
## 203 203         23        PROF            51898        53169.84      NO
## 101 101         21        PROF            52345        53111.93      NO
## 201 201         24        PROF            52403        53198.79      NO
## 23   23         24        PROF            53500        53501.00      NO
## 197 197         23        PROF            53836        53837.00      NO
## 4     4         12        PROF            53900        53901.00      NO
## 83   83         25        PROF            55811        55812.00      NO
## 99   99         25        PROF            56922        56923.00      NO
## 33   33          9        PROF            57295        57296.00      NO
## 58   58         22        PROF            57797        57798.00      NO
## 37   37         10        PROF            57956        57957.00      NO
## 46   46         10        PROF            58974        58975.00      NO
## 202 202         22        PROF            59234        59235.00      NO
## 152 152         28        PROF            59327        59328.00      NO
## 62   62         21        PROF            59333        59334.00      NO
## 52   52         47        PROF            59749        64001.00      NO
## 57   57         21        PROF            59755        59756.00      NO
## 12   12         20        PROF            60087        60088.00      NO
## 15   15         35        PROF            60576        60577.00      NO
## 35   35         24        PROF            60576        60577.00      NO
## 95   95         24        PROF            60800        60801.00      NO
## 119 119         27        PROF            62370        62371.00      NO
## 48   48         24        PROF            62742        62743.00      NO
## 156 156          4        PROF            66500        66501.00      NO
## 151 151          5        PROF            70500        70501.00      NO
## 541 163          3        PROF            85501        85502.00      NO
## 55  100          3        PROF            78501        78502.00      NO
##      Increment
## 185 12248.1245
## 65   9703.1720
## 5    8761.1720
## 87   8470.9820
## 150  7302.9820
## 54   7249.9820
## 85   6906.1245
## 172  6380.3146
## 82   6066.1245
## 116  5195.8395
## 133  4891.0770
## 176  4889.8395
## 7    4354.7919
## 181  4354.7919
## 29   4110.0295
## 105  3466.2195
## 142  3453.8870
## 190  3413.8395
## 166  3192.8870
## 47   2793.5046
## 143  3197.8395
## 79   2619.8870
## 193  2593.8395
## 94   2673.6969
## 167  1867.6471
## 66   2176.9345
## 188  2279.5544
## 171  1627.7919
## 203  1271.8395
## 101   766.9345
## 201   795.7919
## 23      1.0000
## 197     1.0000
## 4       1.0000
## 83      1.0000
## 99      1.0000
## 33      1.0000
## 58      1.0000
## 37      1.0000
## 46      1.0000
## 202     1.0000
## 152     1.0000
## 62      1.0000
## 52   4252.0000
## 57      1.0000
## 12      1.0000
## 15      1.0000
## 35      1.0000
## 95      1.0000
## 119     1.0000
## 48      1.0000
## 156     1.0000
## 151     1.0000
## 541     1.0000
## 55      1.0000

## 
## Call:
## lm(formula = Salary ~ Experience, data = df_assist_proff)
## 
## Coefficients:
## (Intercept)   Experience  
##     33258.1        468.7  
## 
##      ID Experience Designation This_year_Salary New_year_salary Promote
## 8     8         12        ASST         36170.15        38413.70      NO
## 14   14          6        ASST         33718.79        35601.56      NO
## 16   16         30        ASST         44563.00        54684.98     YES
## 17   17         11        ASST         35761.59        37945.01      NO
## 20   20         22        ASST         48001.00        48002.00      NO
## 21   21          6        ASST         33718.79        35601.56      NO
## 26   26          7        ASST         35331.00        36070.25      NO
## 30   30          9        ASST         34944.47        37007.63      NO
## 31   31          7        ASST         35331.00        36070.25      NO
## 34   34         12        ASST         36992.00        38413.70      NO
## 51   51          7        ASST         34127.35        36070.25      NO
## 60   60          8        ASST         34535.91        36538.94      NO
## 63   63          6        ASST         33718.79        35601.56      NO
## 67   67          8        ASST         45366.00        45367.00      NO
## 69   69          8        ASST         49135.00        49136.00      NO
## 70   70          6        ASST         33718.79        35601.56      NO
## 71   71          6        ASST         33718.79        35601.56      NO
## 72   72          9        ASST         34944.47        37007.63      NO
## 74   74          4        ASST         32901.67        34664.19      NO
## 76   76          3        ASST         34501.00        34502.00      NO
## 80   80         14        ASST         36987.28        39351.07      NO
## 84   84          8        ASST         34535.91        36538.94      NO
## 86   86          7        ASST         34127.35        36070.25      NO
## 89   89          8        ASST         34535.91        36538.94      NO
## 91   91          7        ASST         34127.35        36070.25      NO
## 93   93          6        ASST         35151.00        35601.56      NO
## 97   97          6        ASST         39501.00        39502.00      NO
## 98   98          5        ASST         52001.00        52002.00      NO
## 102 102         11        ASST         35799.00        37945.01      NO
## 107 107         14        ASST         40428.00        40429.00      NO
## 108 108         12        ASST         37022.00        38413.70      NO
## 111 111         10        ASST         35353.03        37476.32      NO
## 114 114          7        ASST         34127.35        36070.25      NO
## 118 118          9        ASST         38118.00        38119.00      NO
## 121 121          3        ASST         32493.11        34195.50      NO
## 125 125          3        ASST         32493.11        34195.50      NO
## 129 129         12        ASST         36170.15        38413.70      NO
## 130 130         16        ASST         37804.40        47926.38     YES
## 132 132          3        ASST         32493.11        34195.50      NO
## 134 134          8        ASST         40437.00        40438.00      NO
## 137 137          7        ASST         34127.35        36070.25      NO
## 139 139          4        ASST         32901.67        34664.19      NO
## 140 140          9        ASST         36301.00        37007.63      NO
## 145 145         14        ASST         37160.00        39351.07      NO
## 146 146         11        ASST         35761.59        37945.01      NO
## 147 147          5        ASST         33310.23        35132.87      NO
## 149 149          8        ASST         34535.91        36538.94      NO
## 155 155          4        ASST         32901.67        34664.19      NO
## 160 160          8        ASST         34535.91        36538.94      NO
## 164 164         22        ASST         49303.00        49304.00      NO
## 173 173          8        ASST         35799.00        36538.94      NO
## 174 174          9        ASST         42257.00        42258.00      NO
## 177 177          5        ASST         55501.00        55502.00      NO
## 179 179          6        ASST         33718.79        35601.56      NO
## 182 182          3        ASST         32493.11        34195.50      NO
## 183 183          8        ASST         34535.91        36538.94      NO
## 194 194          7        ASST         34127.35        36070.25      NO
## 195 195          4        ASST         32901.67        34664.19      NO
## 199 199          6        ASST         33718.79        35601.56      NO
##      Increment
## 8    2243.5417
## 14   1882.7743
## 16  10121.9843
## 17   2183.4138
## 20      1.0000
## 21   1882.7743
## 26    739.2518
## 30   2063.1580
## 31    739.2518
## 34   1421.6962
## 51   1942.9022
## 60   2003.0301
## 63   1882.7743
## 67      1.0000
## 69      1.0000
## 70   1882.7743
## 71   1882.7743
## 72   2063.1580
## 74   1762.5185
## 76      1.0000
## 80   2363.7975
## 84   2003.0301
## 86   1942.9022
## 89   2003.0301
## 91   1942.9022
## 93    450.5629
## 97      1.0000
## 98      1.0000
## 102  2146.0073
## 107     1.0000
## 108  1391.6962
## 111  2123.2859
## 114  1942.9022
## 118     1.0000
## 121  1702.3906
## 125  1702.3906
## 129  2243.5417
## 130 10121.9843
## 132  1702.3906
## 134     1.0000
## 137  1942.9022
## 139  1762.5185
## 140   706.6296
## 145  2191.0740
## 146  2183.4138
## 147  1822.6464
## 149  2003.0301
## 155  1762.5185
## 160  2003.0301
## 164     1.0000
## 173   739.9407
## 174     1.0000
## 177     1.0000
## 179  1882.7743
## 182  1702.3906
## 183  2003.0301
## 194  1942.9022
## 195  1762.5185
## 199  1882.7743

## 
## Call:
## lm(formula = Salary ~ Experience, data = df_asso_proff)
## 
## Coefficients:
## (Intercept)   Experience  
##    47153.06        47.42  
## 
##      ID Experience Designation This_year_Salary New_year_salary Promote
## 1     1          6        ASSO         54001.00        54002.00      NO
## 9     9          9        ASSO         49982.00        49983.00      NO
## 11   11         20        ASSO         44657.05        48054.06      NO
## 13   13         17        ASSO         44363.83        47911.80      NO
## 22   22         16        ASSO         44266.09        47864.38      NO
## 27   27         21        ASSO         44754.79        48101.48      NO
## 32   32          8        ASSO         43484.16        47485.01      NO
## 42   42          7        ASSO         52263.00        52264.00      NO
## 43   43          7        ASSO         57171.00        57172.00      NO
## 53   53          6        ASSO         43288.68        47390.16      NO
## 55   55          8        ASSO         43484.16        47485.01      NO
## 56   56          8        ASSO         43484.16        47485.01      NO
## 59   59          6        ASSO         53501.00        53502.00      NO
## 64   64         18        ASSO         44461.57        47959.22      NO
## 68   68          8        ASSO         43484.16        47485.01      NO
## 73   73         14        ASSO         44502.00        47769.53      NO
## 75   75          3        ASSO         62501.00        62502.00      NO
## 77   77         18        ASSO         44461.57        47959.22      NO
## 78   78          6        ASSO         43288.68        47390.16      NO
## 90   90         16        ASSO         44266.09        47864.38      NO
## 92   92         11        ASSO         48501.00        48502.00      NO
## 104 104          8        ASSO         43484.16        47485.01      NO
## 106 106         21        ASSO         44754.79        48101.48      NO
## 109 109         20        ASSO         44657.05        48054.06      NO
## 112 112         21        ASSO         44754.79        48101.48      NO
## 113 113         12        ASSO         43875.12        47674.69      NO
## 117 117         12        ASSO         43875.12        47674.69      NO
## 122 122         10        ASSO         43679.64        47579.85      NO
## 123 123         16        ASSO         44266.09        47864.38      NO
## 127 127         22        ASSO         56837.00        56838.00      NO
## 131 131         14        ASSO         44070.60        47769.53      NO
## 135 135          4        ASSO         54501.00        54502.00      NO
## 136 136          6        ASSO         55001.00        55002.00      NO
## 141 141         11        ASSO         43777.38        47627.27      NO
## 144 144          9        ASSO         46156.00        47532.43      NO
## 153 153         11        ASSO         43777.38        47627.27      NO
## 154 154          7        ASSO         43386.42        47437.59      NO
## 158 158          7        ASSO         46771.00        47437.59      NO
## 168 168         21        ASSO         51379.00        51380.00      NO
## 169 169         20        ASSO         44657.05        48054.06      NO
## 178 178         17        ASSO         44363.83        47911.80      NO
## 187 187         11        ASSO         43777.38        47627.27      NO
## 196 196         14        ASSO         44070.60        47769.53      NO
## 198 198         21        ASSO         44754.79        48101.48      NO
## 200 200         20        ASSO         44657.05        48054.06      NO
## 2     2         21        ASSO         55537.62        55538.62      NO
## 3     3         22        ASSO         51101.62        51102.62      NO
## 6     6         19        ASSO         49567.62        49568.62      NO
## 28   28         17        ASSO         46069.62        47911.80      NO
## 39   39         17        ASSO         51288.62        51289.62      NO
## 44   44         18        ASSO         48987.62        48988.62      NO
## 45   45         25        ASSO         49567.62        63204.25     YES
## 61   61         19        ASSO         47697.62        48006.64      NO
## 88   88         19        ASSO         48342.62        48343.62      NO
## 96   96         19        ASSO         50493.62        50494.62      NO
## 103 103         26        ASSO         55954.62        69591.25     YES
## 126 126         17        ASSO         46667.62        47911.80      NO
## 157 157         19        ASSO         48407.62        48408.62      NO
## 159 159         24        ASSO         54801.62        68438.25     YES
## 161 161         19        ASSO         51101.62        51102.62      NO
## 162 162         22        ASSO         54999.62        55000.62      NO
## 170 170         20        ASSO         54205.62        54206.62      NO
## 191 191         21        ASSO         48987.62        48988.62      NO
## 192 192         18        ASSO         46579.62        47959.22      NO
##      Increment
## 1       1.0000
## 9       1.0000
## 11   3397.0157
## 13   3547.9730
## 22   3598.2921
## 27   3346.6966
## 32   4000.8449
## 42      1.0000
## 43      1.0000
## 53   4101.4831
## 55   4000.8449
## 56   4000.8449
## 59      1.0000
## 64   3497.6539
## 68   4000.8449
## 73   3267.5348
## 75      1.0000
## 77   3497.6539
## 78   4101.4831
## 90   3598.2921
## 92      1.0000
## 104  4000.8449
## 106  3346.6966
## 109  3397.0157
## 112  3346.6966
## 113  3799.5685
## 117  3799.5685
## 122  3900.2067
## 123  3598.2921
## 127     1.0000
## 131  3698.9303
## 135     1.0000
## 136     1.0000
## 141  3849.8876
## 144  1376.4284
## 153  3849.8876
## 154  4051.1640
## 158   666.5858
## 168     1.0000
## 169  3397.0157
## 178  3547.9730
## 187  3849.8876
## 196  3698.9303
## 198  3346.6966
## 200  3397.0157
## 2       1.0000
## 3       1.0000
## 6       1.0000
## 28   1842.1798
## 39      1.0000
## 44      1.0000
## 45  13636.6272
## 61    309.0224
## 88      1.0000
## 96      1.0000
## 103 13636.6272
## 126  1244.1798
## 157     1.0000
## 159 13636.6272
## 161     1.0000
## 162     1.0000
## 170     1.0000
## 191     1.0000
## 192  1379.6011

## 
## Call:
## lm(formula = Salary ~ Experience, data = df_proff)
## 
## Coefficients:
## (Intercept)   Experience  
##    56898.47        77.99  
## 
##      ID Experience Designation This_year_Salary New_year_salary Promote
## 167 167          7        PROF         52677.65        57366.43      NO
## 47   47         10        PROF         52764.50        57600.41      NO
## 172 172         14        PROF         52880.31        57912.38      NO
## 105 105         16        PROF         52938.22        58068.37      NO
## 65   65         17        PROF         52967.17        58146.36      NO
## 5     5         17        PROF         52967.17        58146.36      NO
## 185 185         18        PROF         52996.12        58224.36      NO
## 85   85         18        PROF         52996.12        58224.36      NO
## 82   82         18        PROF         52996.12        58224.36      NO
## 133 133         19        PROF         53025.08        58302.35      NO
## 29   29         20        PROF         53054.03        58380.34      NO
## 87   87         21        PROF         53082.98        58458.34      NO
## 150 150         21        PROF         53082.98        58458.34      NO
## 54   54         21        PROF         53082.98        58458.34      NO
## 66   66         22        PROF         53111.93        58536.33      NO
## 101 101         22        PROF         53111.93        58536.33      NO
## 142 142         23        PROF         53140.89        58614.32      NO
## 166 166         23        PROF         53140.89        58614.32      NO
## 79   79         23        PROF         53140.89        58614.32      NO
## 116 116         24        PROF         53169.84        58692.32      NO
## 176 176         24        PROF         53169.84        58692.32      NO
## 190 190         24        PROF         53169.84        58692.32      NO
## 143 143         24        PROF         53169.84        58692.32      NO
## 193 193         24        PROF         53169.84        58692.32      NO
## 203 203         24        PROF         53169.84        58692.32      NO
## 7     7         25        PROF         53198.79        58770.31      NO
## 181 181         25        PROF         53198.79        58770.31      NO
## 171 171         25        PROF         53198.79        58770.31      NO
## 201 201         25        PROF         53198.79        58770.31      NO
## 94   94         27        PROF         53256.70        58926.30      NO
## 188 188         30        PROF         53343.55        59160.28      NO
## 23   23         25        PROF         53501.00        58770.31      NO
## 197 197         24        PROF         53837.00        58692.32      NO
## 4     4         13        PROF         53901.00        57834.39      NO
## 83   83         26        PROF         55812.00        58848.30      NO
## 99   99         26        PROF         56923.00        58848.30      NO
## 33   33         10        PROF         57296.00        57600.41      NO
## 58   58         23        PROF         57798.00        58614.32      NO
## 37   37         11        PROF         57957.00        57958.00      NO
## 46   46         11        PROF         58975.00        58976.00      NO
## 202 202         23        PROF         59235.00        59236.00      NO
## 152 152         29        PROF         59328.00        59329.00      NO
## 62   62         22        PROF         59334.00        59335.00      NO
## 57   57         22        PROF         59756.00        59757.00      NO
## 12   12         21        PROF         60088.00        60089.00      NO
## 15   15         36        PROF         60577.00        60578.00      NO
## 35   35         25        PROF         60577.00        60578.00      NO
## 95   95         25        PROF         60801.00        60802.00      NO
## 24   24         23        PROF         61010.18        61011.18      NO
## 10   10         22        PROF         61071.18        61072.18      NO
## 165 165         23        PROF         61576.18        61577.18      NO
## 138 138         23        PROF         61682.18        61683.18      NO
## 40   40         24        PROF         62194.18        62195.18      NO
## 119 119         28        PROF         62371.00        62372.00      NO
## 25   25         22        PROF         62414.18        62415.18      NO
## 48   48         25        PROF         62743.00        62744.00      NO
## 186 186         26        PROF         63237.18        63238.18      NO
## 52   52         48        PROF         64001.00        64002.00      NO
## 110 110         23        PROF         64679.18        64680.18      NO
## 18   18         24        PROF         64873.18        64874.18      NO
## 175 175         25        PROF         64873.18        64874.18      NO
## 204 204         28        PROF         65569.18        65570.18      NO
## 156 156          5        PROF         66501.00        66502.00      NO
## 180 180         23        PROF         66881.18        66882.18      NO
## 36   36         22        PROF         67448.18        67449.18      NO
## 19   19         23        PROF         69501.18        69502.18      NO
## 151 151          6        PROF         70501.00        70502.00      NO
## 120 120         22        PROF         70513.18        70514.18      NO
## 124 124         25        PROF         70513.18        70514.18      NO
## 49   49         41        PROF         70580.18        70581.18      NO
## 38   38         34        PROF         70736.18        70737.18      NO
## 100 100          4        PROF         78501.00        78502.00      NO
## 73  163          4        PROF         85502.00        85503.00      NO
##     Increment
## 167 4688.7810
## 47  4835.9036
## 172 5032.0671
## 105 5130.1488
## 65  5179.1897
## 5   5179.1897
## 185 5228.2305
## 85  5228.2305
## 82  5228.2305
## 133 5277.2714
## 29  5326.3123
## 87  5375.3531
## 150 5375.3531
## 54  5375.3531
## 66  5424.3940
## 101 5424.3940
## 142 5473.4349
## 166 5473.4349
## 79  5473.4349
## 116 5522.4758
## 176 5522.4758
## 190 5522.4758
## 143 5522.4758
## 193 5522.4758
## 203 5522.4758
## 7   5571.5166
## 181 5571.5166
## 171 5571.5166
## 201 5571.5166
## 94  5669.5984
## 188 5816.7210
## 23  5269.3086
## 197 4855.3152
## 4   3933.3883
## 83  3036.3019
## 99  1925.3019
## 33   304.4082
## 58   816.3219
## 37     1.0000
## 46     1.0000
## 202    1.0000
## 152    1.0000
## 62     1.0000
## 57     1.0000
## 12     1.0000
## 15     1.0000
## 35     1.0000
## 95     1.0000
## 24     1.0000
## 10     1.0000
## 165    1.0000
## 138    1.0000
## 40     1.0000
## 119    1.0000
## 25     1.0000
## 48     1.0000
## 186    1.0000
## 52     1.0000
## 110    1.0000
## 18     1.0000
## 175    1.0000
## 204    1.0000
## 156    1.0000
## 180    1.0000
## 36     1.0000
## 19     1.0000
## 151    1.0000
## 120    1.0000
## 124    1.0000
## 49     1.0000
## 38     1.0000
## 100    1.0000
## 73     1.0000

## 
## Call:
## lm(formula = Salary ~ Experience, data = df_assist_proff)
## 
## Coefficients:
## (Intercept)   Experience  
##     33872.6        520.2  
## 
##      ID Experience Designation This_year_Salary New_year_salary Promote
## 8     8         13        ASST         38413.70        40114.48      NO
## 14   14          7        ASST         35601.56        36993.56      NO
## 17   17         12        ASST         37945.01        39594.33      NO
## 20   20         23        ASST         48002.00        56388.73     YES
## 21   21          7        ASST         35601.56        36993.56      NO
## 26   26          8        ASST         36070.25        37513.72      NO
## 30   30         10        ASST         37007.63        38554.02      NO
## 31   31          8        ASST         36070.25        37513.72      NO
## 34   34         13        ASST         38413.70        40114.48      NO
## 51   51          8        ASST         36070.25        37513.72      NO
## 60   60          9        ASST         36538.94        38033.87      NO
## 63   63          7        ASST         35601.56        36993.56      NO
## 67   67          9        ASST         45367.00        45368.00      NO
## 69   69          9        ASST         49136.00        49137.00      NO
## 70   70          7        ASST         35601.56        36993.56      NO
## 71   71          7        ASST         35601.56        36993.56      NO
## 72   72         10        ASST         37007.63        38554.02      NO
## 74   74          5        ASST         34664.19        35953.26      NO
## 76   76          4        ASST         34502.00        35433.11      NO
## 80   80         15        ASST         39351.07        41154.79      NO
## 84   84          9        ASST         36538.94        38033.87      NO
## 86   86          8        ASST         36070.25        37513.72      NO
## 89   89          9        ASST         36538.94        38033.87      NO
## 91   91          8        ASST         36070.25        37513.72      NO
## 93   93          7        ASST         35601.56        36993.56      NO
## 97   97          7        ASST         39502.00        39503.00      NO
## 98   98          6        ASST         52002.00        52003.00      NO
## 102 102         12        ASST         37945.01        39594.33      NO
## 107 107         15        ASST         40429.00        41154.79      NO
## 108 108         13        ASST         38413.70        40114.48      NO
## 111 111         11        ASST         37476.32        39074.17      NO
## 114 114          8        ASST         36070.25        37513.72      NO
## 118 118         10        ASST         38119.00        38554.02      NO
## 121 121          4        ASST         34195.50        35433.11      NO
## 125 125          4        ASST         34195.50        35433.11      NO
## 129 129         13        ASST         38413.70        40114.48      NO
## 132 132          4        ASST         34195.50        35433.11      NO
## 134 134          9        ASST         40438.00        40439.00      NO
## 137 137          8        ASST         36070.25        37513.72      NO
## 139 139          5        ASST         34664.19        35953.26      NO
## 140 140         10        ASST         37007.63        38554.02      NO
## 145 145         15        ASST         39351.07        41154.79      NO
## 146 146         12        ASST         37945.01        39594.33      NO
## 147 147          6        ASST         35132.87        36473.41      NO
## 149 149          9        ASST         36538.94        38033.87      NO
## 155 155          5        ASST         34664.19        35953.26      NO
## 160 160          9        ASST         36538.94        38033.87      NO
## 164 164         23        ASST         49304.00        57690.73     YES
## 173 173          9        ASST         36538.94        38033.87      NO
## 174 174         10        ASST         42258.00        42259.00      NO
## 177 177          6        ASST         55502.00        55503.00      NO
## 179 179          7        ASST         35601.56        36993.56      NO
## 182 182          4        ASST         34195.50        35433.11      NO
## 183 183          9        ASST         36538.94        38033.87      NO
## 194 194          8        ASST         36070.25        37513.72      NO
## 195 195          5        ASST         34664.19        35953.26      NO
## 199 199          7        ASST         35601.56        36993.56      NO
##     Increment
## 8   1700.7842
## 14  1392.0011
## 17  1649.3203
## 20  8386.7329
## 21  1392.0011
## 26  1443.4649
## 30  1546.3926
## 31  1443.4649
## 34  1700.7842
## 51  1443.4649
## 60  1494.9288
## 63  1392.0011
## 67     1.0000
## 69     1.0000
## 70  1392.0011
## 71  1392.0011
## 72  1546.3926
## 74  1289.0734
## 76   931.1058
## 80  1803.7119
## 84  1494.9288
## 86  1443.4649
## 89  1494.9288
## 91  1443.4649
## 93  1392.0011
## 97     1.0000
## 98     1.0000
## 102 1649.3203
## 107  725.7858
## 108 1700.7842
## 111 1597.8565
## 114 1443.4649
## 118  435.0222
## 121 1237.6095
## 125 1237.6095
## 129 1700.7842
## 132 1237.6095
## 134    1.0000
## 137 1443.4649
## 139 1289.0734
## 140 1546.3926
## 145 1803.7119
## 146 1649.3203
## 147 1340.5372
## 149 1494.9288
## 155 1289.0734
## 160 1494.9288
## 164 8386.7329
## 173 1494.9288
## 174    1.0000
## 177    1.0000
## 179 1392.0011
## 182 1237.6095
## 183 1494.9288
## 194 1443.4649
## 195 1289.0734
## 199 1392.0011

## 
## Call:
## lm(formula = Salary ~ Experience, data = df_asso_proff)
## 
## Coefficients:
## (Intercept)   Experience  
##    48819.43        40.81  
## 
##      ID Experience Designation This_year_Salary New_year_salary Promote
## 53   53          7        ASSO         47390.16        49064.31      NO
## 78   78          7        ASSO         47390.16        49064.31      NO
## 154 154          8        ASSO         47437.59        49105.12      NO
## 158 158          8        ASSO         47437.59        49105.12      NO
## 32   32          9        ASSO         47485.01        49145.93      NO
## 55   55          9        ASSO         47485.01        49145.93      NO
## 56   56          9        ASSO         47485.01        49145.93      NO
## 68   68          9        ASSO         47485.01        49145.93      NO
## 104 104          9        ASSO         47485.01        49145.93      NO
## 144 144         10        ASSO         47532.43        49186.75      NO
## 122 122         11        ASSO         47579.85        49227.56      NO
## 141 141         12        ASSO         47627.27        49268.37      NO
## 153 153         12        ASSO         47627.27        49268.37      NO
## 187 187         12        ASSO         47627.27        49268.37      NO
## 113 113         13        ASSO         47674.69        49309.19      NO
## 117 117         13        ASSO         47674.69        49309.19      NO
## 73   73         15        ASSO         47769.53        49390.82      NO
## 131 131         15        ASSO         47769.53        49390.82      NO
## 196 196         15        ASSO         47769.53        49390.82      NO
## 22   22         17        ASSO         47864.38        49472.44      NO
## 90   90         17        ASSO         47864.38        49472.44      NO
## 123 123         17        ASSO         47864.38        49472.44      NO
## 13   13         18        ASSO         47911.80        49513.26      NO
## 178 178         18        ASSO         47911.80        49513.26      NO
## 28   28         18        ASSO         47911.80        49513.26      NO
## 126 126         18        ASSO         47911.80        49513.26      NO
## 130 130         17        ASSO         47926.38        49472.44      NO
## 64   64         19        ASSO         47959.22        49554.07      NO
## 77   77         19        ASSO         47959.22        49554.07      NO
## 192 192         19        ASSO         47959.22        49554.07      NO
## 61   61         20        ASSO         48006.64        49594.88      NO
## 11   11         21        ASSO         48054.06        49635.70      NO
## 109 109         21        ASSO         48054.06        49635.70      NO
## 169 169         21        ASSO         48054.06        49635.70      NO
## 200 200         21        ASSO         48054.06        49635.70      NO
## 27   27         22        ASSO         48101.48        49676.51      NO
## 106 106         22        ASSO         48101.48        49676.51      NO
## 112 112         22        ASSO         48101.48        49676.51      NO
## 198 198         22        ASSO         48101.48        49676.51      NO
## 88   88         20        ASSO         48343.62        49594.88      NO
## 157 157         20        ASSO         48408.62        49594.88      NO
## 92   92         12        ASSO         48502.00        49268.37      NO
## 44   44         19        ASSO         48988.62        49554.07      NO
## 191 191         22        ASSO         48988.62        49676.51      NO
## 6     6         20        ASSO         49568.62        49594.88      NO
## 9     9         10        ASSO         49983.00        49984.00      NO
## 96   96         20        ASSO         50494.62        50495.62      NO
## 3     3         23        ASSO         51102.62        51103.62      NO
## 161 161         20        ASSO         51102.62        51103.62      NO
## 39   39         18        ASSO         51289.62        51290.62      NO
## 168 168         22        ASSO         51380.00        51381.00      NO
## 42   42          8        ASSO         52264.00        52265.00      NO
## 59   59          7        ASSO         53502.00        53503.00      NO
## 1     1          7        ASSO         54002.00        54003.00      NO
## 170 170         21        ASSO         54206.62        54207.62      NO
## 135 135          5        ASSO         54502.00        54503.00      NO
## 16   16         31        ASSO         54684.98        62319.75     YES
## 162 162         23        ASSO         55000.62        55001.62      NO
## 136 136          7        ASSO         55002.00        55003.00      NO
## 2     2         22        ASSO         55538.62        55539.62      NO
## 127 127         23        ASSO         56838.00        56839.00      NO
## 43   43          8        ASSO         57172.00        57173.00      NO
## 63   75          4        ASSO         62503.00        62504.00      NO
##      Increment
## 53  1674.14226
## 78  1674.14226
## 154 1667.53453
## 158 1667.53453
## 32  1660.92680
## 55  1660.92680
## 56  1660.92680
## 68  1660.92680
## 104 1660.92680
## 144 1654.31907
## 122 1647.71134
## 141 1641.10361
## 153 1641.10361
## 187 1641.10361
## 113 1634.49588
## 117 1634.49588
## 73  1621.28042
## 131 1621.28042
## 196 1621.28042
## 22  1608.06495
## 90  1608.06495
## 123 1608.06495
## 13  1601.45722
## 178 1601.45722
## 28  1601.45722
## 126 1601.45722
## 130 1546.05971
## 64  1594.84949
## 77  1594.84949
## 192 1594.84949
## 61  1588.24176
## 11  1581.63403
## 109 1581.63403
## 169 1581.63403
## 200 1581.63403
## 27  1575.02630
## 106 1575.02630
## 112 1575.02630
## 198 1575.02630
## 88  1251.26418
## 157 1186.26418
## 92   766.37456
## 44   565.45062
## 191  687.89131
## 6     26.26418
## 9      1.00000
## 96     1.00000
## 3      1.00000
## 161    1.00000
## 39     1.00000
## 168    1.00000
## 42     1.00000
## 59     1.00000
## 1      1.00000
## 170    1.00000
## 135    1.00000
## 16  7634.76292
## 162    1.00000
## 136    1.00000
## 2      1.00000
## 127    1.00000
## 43     1.00000
## 63     1.00000

## 
## Call:
## lm(formula = Salary ~ Experience, data = df_proff)
## 
## Coefficients:
## (Intercept)   Experience  
##    60224.58        51.85  
## 
##      ID Experience Designation This_year_Salary New_year_salary Promote
## 167 167          8        PROF         57366.43        60587.55      NO
## 47   47         11        PROF         57600.41        60743.12      NO
## 33   33         11        PROF         57600.41        60743.12      NO
## 4     4         14        PROF         57834.39        60898.68      NO
## 172 172         15        PROF         57912.38        60950.53      NO
## 37   37         12        PROF         57958.00        60794.97      NO
## 105 105         17        PROF         58068.37        61054.24      NO
## 65   65         18        PROF         58146.36        61106.09      NO
## 5     5         18        PROF         58146.36        61106.09      NO
## 185 185         19        PROF         58224.36        61157.95      NO
## 85   85         19        PROF         58224.36        61157.95      NO
## 82   82         19        PROF         58224.36        61157.95      NO
## 133 133         20        PROF         58302.35        61209.80      NO
## 29   29         21        PROF         58380.34        61261.65      NO
## 87   87         22        PROF         58458.34        61313.51      NO
## 150 150         22        PROF         58458.34        61313.51      NO
## 54   54         22        PROF         58458.34        61313.51      NO
## 66   66         23        PROF         58536.33        61365.36      NO
## 101 101         23        PROF         58536.33        61365.36      NO
## 142 142         24        PROF         58614.32        61417.22      NO
## 166 166         24        PROF         58614.32        61417.22      NO
## 79   79         24        PROF         58614.32        61417.22      NO
## 58   58         24        PROF         58614.32        61417.22      NO
## 116 116         25        PROF         58692.32        61469.07      NO
## 176 176         25        PROF         58692.32        61469.07      NO
## 190 190         25        PROF         58692.32        61469.07      NO
## 143 143         25        PROF         58692.32        61469.07      NO
## 193 193         25        PROF         58692.32        61469.07      NO
## 203 203         25        PROF         58692.32        61469.07      NO
## 197 197         25        PROF         58692.32        61469.07      NO
## 7     7         26        PROF         58770.31        61520.92      NO
## 181 181         26        PROF         58770.31        61520.92      NO
## 171 171         26        PROF         58770.31        61520.92      NO
## 201 201         26        PROF         58770.31        61520.92      NO
## 23   23         26        PROF         58770.31        61520.92      NO
## 83   83         27        PROF         58848.30        61572.78      NO
## 99   99         27        PROF         58848.30        61572.78      NO
## 94   94         28        PROF         58926.30        61624.63      NO
## 46   46         12        PROF         58976.00        60794.97      NO
## 188 188         31        PROF         59160.28        61780.19      NO
## 202 202         24        PROF         59236.00        61417.22      NO
## 152 152         30        PROF         59329.00        61728.34      NO
## 62   62         23        PROF         59335.00        61365.36      NO
## 57   57         23        PROF         59757.00        61365.36      NO
## 12   12         22        PROF         60089.00        61313.51      NO
## 15   15         37        PROF         60578.00        62091.31      NO
## 35   35         26        PROF         60578.00        61520.92      NO
## 95   95         26        PROF         60802.00        61520.92      NO
## 24   24         24        PROF         61011.18        61417.22      NO
## 10   10         23        PROF         61072.18        61365.36      NO
## 165 165         24        PROF         61577.18        61578.18      NO
## 138 138         24        PROF         61683.18        61684.18      NO
## 40   40         25        PROF         62195.18        62196.18      NO
## 119 119         29        PROF         62372.00        62373.00      NO
## 25   25         23        PROF         62415.18        62416.18      NO
## 48   48         26        PROF         62744.00        62745.00      NO
## 45   45         26        PROF         63204.25        63205.25      NO
## 186 186         27        PROF         63238.18        63239.18      NO
## 52   52         49        PROF         64002.00        64003.00      NO
## 110 110         24        PROF         64680.18        64681.18      NO
## 18   18         25        PROF         64874.18        64875.18      NO
## 175 175         26        PROF         64874.18        64875.18      NO
## 204 204         29        PROF         65570.18        65571.18      NO
## 156 156          6        PROF         66502.00        66503.00      NO
## 180 180         24        PROF         66882.18        66883.18      NO
## 36   36         23        PROF         67449.18        67450.18      NO
## 159 159         25        PROF         68438.25        68439.25      NO
## 19   19         24        PROF         69502.18        69503.18      NO
## 103 103         27        PROF         69591.25        69592.25      NO
## 151 151          7        PROF         70502.00        70503.00      NO
## 120 120         23        PROF         70514.18        70515.18      NO
## 124 124         26        PROF         70514.18        70515.18      NO
## 49   49         42        PROF         70581.18        70582.18      NO
## 38   38         35        PROF         70737.18        70738.18      NO
## 100 100          5        PROF         78502.00        78503.00      NO
## 76  163          5        PROF         85503.00        85504.00      NO
##     Increment
## 167 3221.1268
## 47  3142.7080
## 33  3142.7080
## 4   3064.2893
## 172 3038.1497
## 37  2836.9700
## 105 2985.8705
## 65  2959.7310
## 5   2959.7310
## 185 2933.5914
## 85  2933.5914
## 82  2933.5914
## 133 2907.4518
## 29  2881.3122
## 87  2855.1726
## 150 2855.1726
## 54  2855.1726
## 66  2829.0331
## 101 2829.0331
## 142 2802.8935
## 166 2802.8935
## 79  2802.8935
## 58  2802.8935
## 116 2776.7539
## 176 2776.7539
## 190 2776.7539
## 143 2776.7539
## 193 2776.7539
## 203 2776.7539
## 197 2776.7539
## 7   2750.6143
## 181 2750.6143
## 171 2750.6143
## 201 2750.6143
## 23  2750.6143
## 83  2724.4747
## 99  2724.4747
## 94  2698.3351
## 46  1818.9700
## 188 2619.9164
## 202 2181.2153
## 152 2399.3380
## 62  2030.3615
## 57  1608.3615
## 12  1224.5078
## 15  1513.3144
## 35   942.9229
## 95   718.9229
## 24   406.0399
## 10   293.1862
## 165    1.0000
## 138    1.0000
## 40     1.0000
## 119    1.0000
## 25     1.0000
## 48     1.0000
## 45     1.0000
## 186    1.0000
## 52     1.0000
## 110    1.0000
## 18     1.0000
## 175    1.0000
## 204    1.0000
## 156    1.0000
## 180    1.0000
## 36     1.0000
## 159    1.0000
## 19     1.0000
## 103    1.0000
## 151    1.0000
## 120    1.0000
## 124    1.0000
## 49     1.0000
## 38     1.0000
## 100    1.0000
## 76     1.0000

Conclusion:

• As per the data displayed, we can observe that people who are already getting paid very high are ot given promotion (since giving promotion would increase their salary further).

• The university needs the following budget (in $) over the next 3 years to support the above displayed compensation system:

year	Budget
1	962432.1
2	453181.6
3	286815.1

• As the salaries are adjusted, as per the algorithm, the annual budget is decreasing almost by 50% every year.

• If the University does not have the needed budget or has more than the required budget, then the salaries can be proportionately increased by calculating the required percentage increase for each member of the faculty using the above table. This way the percentage can be applied to any available budget.