{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["UConn Salary Calculator","AY-Accrual Data","Definitions"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":17,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"UConn Salary Calculator","index":0,"n_rows":70,"n_cols":22,"rows_kept":70,"cut":false,"hidden":false,"merged":[[35,5,35,6],[35,7,35,8],[0,0,0,11],[21,0,21,1],[22,0,22,1],[10,8,10,9],[1,0,1,11],[8,8,8,9],[9,8,9,9],[4,0,4,1],[5,0,5,1],[8,0,8,1],[11,0,11,1],[20,0,20,1],[7,8,7,10],[9,3,10,6],[35,11,36,11],[35,2,35,3],[35,4,36,4],[69,1,69,11],[12,3,13,6],[58,1,60,10],[34,0,34,3],[11,8,13,10],[17,4,17,5],[23,0,23,1],[11,3,11,5],[65,1,65,11],[67,1,67,11],[35,1,36,1],[63,1,63,11],[35,9,35,10]]}
["UConn Salary Calculator",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(AcadYr=\"2017-18\",IF(G8=\"No\",AcadYr&\"-NF\",AcadYr),AcadYr)","v":"2020-21"}]
["A companion to the Core-CT Smart HR Payroll Distribution"]
[null,null,null,null,null,null,null,null,null,null,"v. 3.4",null,null,null,"Lookup","Accrual Rate Amt","Accrual Rate","# of PPs","Start Date","Earnings End","Pay End","# of Earn PPs"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,9,{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,16,FALSE)","v":0.33333299999999999},{"$f":"+P4+1","v":1.3333330000000001},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,9,FALSE)/10","v":26},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,2,FALSE)","v":"2020-08-23"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,4,FALSE)","v":"2021-05-22"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,6,FALSE)","v":"2021-08-22"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,14,FALSE)/10","v":19.5}]
["Employee Name:",null,null,null,null,null,null,null,null,null,null,null,null,null,"Fall Only",{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,16,FALSE)","v":0.33333299999999999},{"$f":"+P5+1","v":1.3333330000000001},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,11,FALSE)/10","v":13.1},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,2,FALSE)","v":"2020-08-23"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,7,FALSE)","v":"2021-01-06"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,8,FALSE)","v":"2021-02-22"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,10,FALSE)/10","v":9.8000000000000007}]
["Employee/Payroll ID:",null,null,null,null,null,null,null,null,null,null,null,null,null,"Spring Only",{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,18,FALSE)","v":0.64356400000000002},1.643564,{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,12,FALSE)/10","v":16.600000000000001},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,3,FALSE)","v":"2021-01-01"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,4,FALSE)","v":"2021-05-22"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,6,FALSE)","v":"2021-08-22"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,13,FALSE)/10","v":10.1}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,10,{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,17,FALSE)","v":0.198157},1.1981569999999999,{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,9,FALSE)/10","v":26},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,2,FALSE)","v":"2020-08-23"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,5,FALSE)","v":"2021-06-22"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,6,FALSE)","v":"2021-08-22"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,15,FALSE)/10","v":21.7}]
[null,"Academic Year:","2020-21",null,null,"Do Furloughs Apply? ",{"$f":"+P23","v":"No"},null,{"$f":"\"Academic Year \"&AcadYr","v":"Academic Year 2020-21"},null,null,null,null,null,11,0,1,{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,9,FALSE)/10","v":26},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,2,FALSE)","v":"2020-08-23"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,6,FALSE)","v":"2021-08-22"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,6,FALSE)","v":"2021-08-22"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,9,FALSE)/10","v":26}]
["Months in Appointment:",null,null,{"$f":"+IF(C11=\"Non-Faculty\",12,IF(C10=\"Full Year\",C9, C10))","v":0},null,null,null,null,"Pay/Earnings Start Date:",null,{"$f":"IF(Appt=0,\"\",VLOOKUP(Appt,$O$4:$U$9,5,FALSE))","v":""},null,null,null,12,0,1,{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,9,FALSE)/10","v":26},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,2,FALSE)","v":"2020-08-23"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,6,FALSE)","v":"2021-08-22"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,6,FALSE)","v":"2021-08-22"},{"$f":"VLOOKUP(O1,'AY-Accrual Data'!$A$3:$R$16,9,FALSE)/10","v":26}]
[null,"Semesters Worked:",{"$f":"+P21","v":"Full Year"},{"$f":"IF(C10=\"Fall Only\", \"This employee is only paid in the Fall Semester and has left UConn or is on an unpaid Sabbatical in the Spring semester.\",IF(C10=\"Spring Only\", \"This employee is only paid in the Spring Semester and is a January 1 start at UConn or is on an unpaid Sabbatical in the Fall semester.\",\"\"))","v":""},null,null,null,null,"Earnings End Date:",null,{"$f":"IF(Appt=0,\"\",VLOOKUP(Appt,$O$4:$U$9,6,FALSE))","v":""},{"$f":"VLOOKUP(Appt,$O$4:$V$9,8,FALSE)","v":{"$error":"#N/A"}}]
[null,"Employee Type:",{"$f":"+P22","v":"Faculty"},null,null,null,null,null,"Pay End Date:",null,{"$f":"IF(Appt=0,\"\",VLOOKUP(Appt,$O$4:$U$9,7,FALSE))","v":""},{"$f":"VLOOKUP(Appt,$O$4:$V$9,4,FALSE)","v":{"$error":"#N/A"}}]
["Full-Time \nAppointment Salary:",null,null,"Adjusted Full-Time \nAppointment Salary (26 PP):",null,null,{"$f":"IF(FTE=\"\",\"\",IF(AcadYr<>\"2017-18\",C12,IF(G8=\"No\",C12,(ROUND(C12/26,2)*20.7-ROUND(C12/IF(Appt=9,195,IF(Appt=10,218,260))*3/21*FTE,2)*21+(ROUND(C12/26,2)*5.3)))))","v":""},null,{"$f":"IF(C9=\"\",\"\",IF(C9<11,IF(C11=\"Faculty\",\"All salary charges posted between \"&MONTH(K10+1)&\"/\"&DAY(K10+1)&\"/\"&YEAR(K10+1)&\" and \"&MONTH(K11)&\"/\"&DAY(K11)&\"/\"&YEAR(K11)&\" will be reversed to offset the accrual entries posted throughout the academic year.\",\"\"),\"\"))","v":""},null,null,null,null,null,"Default Data"]
[null,"FTE:",null,{"$f":"+B27","v":""}]
["For FY2017-2018 ONLY"]
[null,{"$f":"IF(C10=\"Spring Only\",\"Bi-weekly Pay (8/23/17-12/31/17):\",\"Bi-weekly Pay (8/23/17-8/31/17):\")","v":"Bi-weekly Pay (8/23/17-8/31/17):"},{"$f":"IF(FTE=\"\",\"\",IF(AcadYr<>\"2017-18\", \"N/A\",IF(C10=\"Spring Only\",0,IF(G8=\"No\",C12/26*FTE,(ROUND(C12/26*FTE,2)*0.7-ROUND(C12/IF(Appt=9,195,IF(Appt=10,218,260))*3/21*FTE,2))/0.7))))","v":""}]
[null,{"$f":"IF(C10=\"Spring Only\",\"Bi-weekly Pay (1/1/18-6/7/18):\",\"Bi-weekly Pay (9/1/17-6/7/18):\")","v":"Bi-weekly Pay (9/1/17-6/7/18):"},{"$f":"IF(FTE=\"\",\"\",IF(AcadYr<>\"2017-18\",\"N/A\",IF(G8=\"No\",C12/26*FTE,(ROUND(C12/26*FTE,2)-ROUND(C12/IF(Appt=9,195,IF(Appt=10,218,IF(Appt=\"Spring Only\",114,260)))*3/21*IF(FTE=0.78,0.5,FTE),2)))))","v":""}]
[null,"Bi-weekly Pay (6/8/18-8/22/18):",{"$f":"IF(FTE=\"\",\"\",IF(AcadYr<>\"2017-18\",\"N/A\",C12/26*FTE))","v":""}]
[null,null,null,null,"Bi-weekly Pay Rate:",null,{"$f":"IF(AcadYr<>\"2017-18\", C12/26*FTE,C16)","v":0}]
["HIDE","Annualized Salary:",{"$f":"IF(ISBLANK(C9),\"\",FullTimeSal/IF(C9 = 11,12,C9)*12)","v":""}]
["HIDE",null,null,null,null,null,null,null,null,null,null,null,null,null,"Academic Year","2017-18"]
["Funding Effective Date:",null,{"$f":"+K9","v":""},{"$f":"+IF(StartDt<K9,\"Academic Year Start Date Used (\"&MONTH(K9)&\"/\"&DAY(K9)&\"/\"&YEAR(K9)&\")\",\"\")","v":""},null,"Actual Paid Salary:",{"$f":"+ActualSalary","v":0},null,null,null,null,null,null,null,"Pay Period","Full Year"]
["Funding End Date:",null,{"$f":"+K11","v":""},{"$f":"+IF(EndDt>K10,\"Academic Year End Date Used (\"&MONTH(K10)&\"/\"&DAY(K10)&\"/\"&YEAR(K10)&\")\",\"\")","v":""},null,"Actual Earned Salary:",{"$f":"+EarnedSal","v":0},null,null,null,null,null,null,null,"Employee Type","Faculty"]
["# of Pay Periods Worked:",null,{"$f":"IF(StartDt>K10,0,IF(C21=\"\",0,IF(NETWORKDAYS(IF(C21<K9,K9,C21),IF(C22>K10,K10,IF(C22=0,K10,C22)))/10>L10,L10,NETWORKDAYS(IF(C21<K9,K9,C21),IF(C22>K10,K10,IF(C22=0,K10,C22)))/10)))","v":0},{"$f":"IF(PayPeriods=0,\"\",IF(NETWORKDAYS(C21,C22)/10>L10,\"Max Pay Periods: Number of Academic Year (Earning) Pay Periods\",\"\"))","v":""},null,null,null,null,null,null,null,null,null,null,"Furlough",{"$f":"IF(AcadYr=\"2017-18\",\"Yes\",\"No\")","v":"No"}]
["# of Pay Periods:",null,{"$f":"IF(C21=\"\",0,IF(NETWORKDAYS(IF(C21<K9,K9,C21),IF(C22=0,K11,C22))/10>L11,L11,NETWORKDAYS(IF(C21<K9,K9,C21),IF(C22=0,K11,C22)))/10)","v":0},null,null,null,null,null,null,{"$f":"IF(ISBLANK(C28),IF(ISBLANK(D28),\"\",\"AMT\"),\"EFF\")","v":"EFF"}]
[]
["HIDE"]
["HIDE",{"$f":"IF(PercTime=0.78,IF(Appt=\"Spring Only\", \"\", \"NOTE: 78% should only be listed in the % of time field if the individual is a 9 month faculty paid only in the Spring.  Please change the Semesters Worked to *Spring Only*.\"),IF(C10=\"Spring Only\",IF(FTE=0.78,\"\",\"Percent of Time must equal 78% for *Spring Only* Faculty.\"),\"\"))","v":""},null,null,null,null,null,null,null,{"$f":"IF(ISBLANK(Appt),\"Incomplete\",IF(FTE=0,\"Incomplete\",IF(ISBLANK(FullTimeSal),\"Incomplete\",IF(ISBLANK(StartDt),\"Incomplete\",IF(D13=\"\",\"Complete\",\"Incomplete\")))))","v":"Incomplete"}]
["HIDE",null,"Start Date","End Date","Bi-Weekly Salary","Full Time Salary #","% of \ntime","Actual  \n(Paid) \nSalary","Months of Service \nper year","Actual Earned Salary"]
["HIDE",{"$f":"IF(J29=0,0,IF(C29=\"\",0,NETWORKDAYS(C29,D29)))","v":0},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(AcadYr<>\"2017-18\",IF(StartDt<K9,K9,StartDt),IF(StartDt>D29,\"\",StartDt)))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(AcadYr=\"2017-18\",IF(AcadYr=\"2017-18\",DATE(2017,8,31),K10),IF(EndDt=\"\",\"\",IF(EndDt>K10,K10,EndDt))))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",ROUND(IF(AcadYr=\"2017-18\",C15,$G$18),2))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",E29*26)","v":""},{"$f":"+FTE","v":0},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(C29>EndDt,0,F29/26*IF(IFERROR(NETWORKDAYS(C29,D29),0)<0,0,IFERROR(NETWORKDAYS(C29,D29),0))/10))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",C9)","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",ROUND(IF(C29>$K$10,0,VLOOKUP(Appt,$O$4:$R$9,3,FALSE))*H29,2))","v":""}]
["HIDE",{"$f":"IF(J30=0,0,IF(C30=\"\",0,NETWORKDAYS(C30,D30)))","v":0},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(D29=\"\",\"\",IF(D29+1>EndDt,\"\",IF(StartDt>D29+1,StartDt,D29+1))))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(AcadYr=\"2017-18\",IF($C$9=9,IF(EndDt<K10,EndDt,K10),IF(EndDt<DATE(2018,6,7),EndDt,DATE(2018,6,7))),IF(C30=\"\",\"\",IF(EndDt<K11,EndDt,K11))))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",ROUND(IF(AcadYr=\"2017-18\",C16,$G$18),2))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",E30*26)","v":""},{"$f":"+G29","v":0},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(C30>EndDt,0,F30/26*IF(IFERROR(NETWORKDAYS(C30,D30),0)<0,0,IFERROR(NETWORKDAYS(C30,D30),0))/10))","v":""},{"$f":"+I29","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",ROUND(IF(C30>$K$10,0,VLOOKUP(Appt,$O$4:$R$9,3,FALSE))*H30,2))","v":""}]
["HIDE",0,{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(D30=\"\",\"\",IF(D30+1>EndDt,\"\",IF(StartDt>D30+1,StartDt,D30+1))))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(AcadYr=\"2017-18\",IF(C31=\"\",\"\",IF(EndDt<IF($C$9=9,DATE(2018,6,7),K10),EndDt,IF($C$9=9,DATE(2018,6,7),K10))),\"\"))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",ROUND(IF(AcadYr=\"2017-18\",IF($C$9=9,C16,C17),$G$18),2))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",E31*26)","v":""},{"$f":"+G30","v":0},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(C31>EndDt,0,F31/26*IF(IFERROR(NETWORKDAYS(C31,D31),0)<0,0,IFERROR(NETWORKDAYS(C31,D31),0))/10))","v":""},{"$f":"+I30","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",ROUND(IF(C31>$K$10,0,VLOOKUP(Appt,$O$4:$R$9,3,FALSE))*H31,2))","v":""}]
["HIDE",{"$f":"IF(J32=0,0,IF(C32=\"\",0,NETWORKDAYS(C32,D32)))","v":0},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(D31=\"\",\"\",IF(D31+1>EndDt,\"\",D31+1)))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(AcadYr=\"2017-18\",IF(C32=\"\",\"\",IF(EndDt<K11,EndDt,K11)),\"\"))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",ROUND(IF(AcadYr=\"2017-18\",C17,$G$18),2))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",E32*26)","v":""},0,{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(C32>EndDt,0,F32/26*IF(IFERROR(NETWORKDAYS(C32,D32),0)<0,0,IFERROR(NETWORKDAYS(C32,D32),0))/10))","v":""},"",{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",ROUND(IF(C32>$K$10,0,VLOOKUP(Appt,$O$4:$R$9,3,FALSE))*H32,2))","v":""}]
["HIDE",{"$f":"+SUM(B29:B32)","v":0},null,null,{"$f":"IF(B33=0,0,(E29*B29+E30*B30+E31*B31+E32*B32)/B33)","v":0},null,0,{"$f":"+SUM(H29:H32)","v":0},"",{"$f":"+SUM(J29:J32)","v":0}]
["HIDE",null,null,null,null,null,null,null,null,{"$f":"IF(ISBLANK(C38),IF(ISBLANK(D38),\"\",\"AMT\"),\"EFF\")","v":""}]
[{"$f":"IF(AllocType=\"\",\"Please enter % Effort or Goal $ Amounts\",IF(AllocType=\"EFF\",\"Allocated by Effort\",\"Allocated by Amount\"))","v":"Please enter % Effort or Goal $ Amounts"}]
[null,"KFS Account","CHOOSE ONE",null,"Distribution Percentage\n(on Smart HR Template)","Actual Pay \nExpense",null,"Accrual Entries\n(if applicable)",null,"Total Salary \nExpense",null,"Comments"]
[null,null,"% Effort","Amount",null,"Biweekly","Total","Biweekly","Total","Biweekly","Total"]
[null,null,null,null,{"$f":"ROUND(IF(AllocType=\"\",0,IF(AllocType=\"EFF\",C38,D38/EarnedSal)),8)","v":0},{"$f":"IF(StartDt>$K$10,0,IF(HeaderStatus=\"Incomplete\",\"\",ROUND($G$18*E38,2)))","v":""},{"$f":"IF(ActualSalary=0,\"\",$E$33*PayPeriods*E38)","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(VLOOKUP(Appt,$O$4:$R$9,2,FALSE)=0,\"\",ROUND(F38*VLOOKUP(Appt,$O$4:$R$9,2,FALSE),2)))","v":""},{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",ROUND(G38*VLOOKUP(Appt,$O$4:$R$9,2,FALSE),2)))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(H38=\"\",0,H38)+F38)","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(I38=\"\",0,I38)+G38)","v":""}]
[null,null,null,null,0,"","","",{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H39*PayPeriods))","v":""},"",""]
[null,null,null,null,{"$f":"ROUND(IF(AllocType=\"\",0,IF(AllocType=\"EFF\",C40,D40/EarnedSal)),8)","v":0},{"$f":"IF(StartDt>$K$10,0,IF(HeaderStatus=\"Incomplete\",\"\",ROUND($G$18*E40,2)))","v":""},{"$f":"IF(ActualSalary=0,\"\",$E$33*PayPeriods*E40)","v":""},"",{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H40*PayPeriods))","v":""},"",""]
[null,null,null,null,0,"","","",{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H41*PayPeriods))","v":""},"",""]
[null,null,null,null,0,"","","",{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H42*PayPeriods))","v":""},"",""]
[null,null,null,null,0,"","","",{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H43*PayPeriods))","v":""},"",""]
[null,null,null,null,0,"","","",{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H44*PayPeriods))","v":""},"",""]
[null,null,null,null,0,"","","",{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H45*PayPeriods))","v":""},"",""]
[null,null,null,null,0,"","","",{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H46*PayPeriods))","v":""},"",""]
[null,null,null,null,0,"","","",{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H47*PayPeriods))","v":""},"",""]
[null,null,null,null,0,"","","",{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H48*PayPeriods))","v":""},"",""]
[null,null,null,null,0,"","","",{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H49*PayPeriods))","v":""},"",""]
[null,null,null,null,0,"","","",{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H50*PayPeriods))","v":""},"",""]
[null,null,null,null,0,"","","",{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H51*PayPeriods))","v":""},"",""]
[]
[null,"Remainder to be allocated",{"$f":"IF(AllocType=\"EFF\",C55-SUM(C38:C51),\"\")","v":""},{"$f":"IF(AllocType=\"\",\"\",IF(ActualSalary=\"\",\"\",IF(AllocType=\"EFF\",\"\",EarnedSal-SUM(D38:D51))))","v":""},0,{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",ROUND(FullTimeSal/26*FTE*E53,2))","v":""},{"$f":"IF(ActualSalary=0,\"\",F53*PayPeriods)","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(VLOOKUP(Appt,$O$4:$R$9,2,FALSE)=0,\"\",ROUND(F53*VLOOKUP(Appt,$O$4:$R$9,2,FALSE),2)))","v":""},{"$f":"IF(ActualSalary=0,\"\",IF(VLOOKUP(Appt,$O$4:P$9,2,FALSE)=0,\"\",H53*PayPeriods))","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(H53=\"\",0,H53)+F53)","v":""},{"$f":"IF(HeaderStatus=\"Incomplete\",\"\",IF(I53=\"\",0,I53)+G53)","v":""}]
[]
[null,"TOTAL",{"$f":"IF(AllocType=\"EFF\",100%,\"\")","v":""},{"$f":"IF(AllocType=\"\",\"\",IF(ActualSalary=\"\",\"\",IF(AllocType=\"EFF\",\"\",SUM(D38:D53))))","v":""},{"$f":"IF(AllocType=\"\",\"\",IF(ActualSalary=\"\",\"\",SUM(E38:E53)))","v":""},{"$f":"IF(AllocType=\"\",\"\",IF(ActualSalary=\"\",\"\",SUM(F38:F53)))","v":""},{"$f":"IF(AllocType=\"\",\"\",IF(ActualSalary=\"\",\"\",SUM(G38:G53)))","v":""},{"$f":"IF(AllocType=\"\",\"\",IF(ActualSalary=\"\",\"\",IF(SUM(H38:H53)=0,\"\",SUM(H38:H53))))","v":""},{"$f":"IF(AllocType=\"\",\"\",IF(ActualSalary=\"\",\"\",IF(SUM(I38:I53)=0,\"\",SUM(I38:I53))))","v":""},{"$f":"IF(AllocType=\"\",\"\",IF(ActualSalary=\"\",\"\",SUM(J38:J53)))","v":""},{"$f":"IF(AllocType=\"\",\"\",IF(ActualSalary=\"\",\"\",SUM(K38:K53)))","v":""}]
[]
[]
["Comments:"]
[]
[]
[]
[]
["Notes:"]
["*","This form is intended to be used for one person for one funding distribution in one Academic Year.  Payroll Distributions with end dates that cross Academic Years can remain on one Core-CT Smart HR Template Payroll Distribution, but will need to be split into multiple UConn Salary Calculators - one for each Academic Year.  "]
[]
["*","9 & 10 month faculty do not earn academic salary in the summer months. The Core-CT Smart HR Template Payroll Distribution in place at the end of the academic year should be extended through the end of the summer or beyond; however, all salary posting in the summer period will be reversed as part of the automated accrual process."]
[]
["*","The exact number of pay periods varies year to year and may exceed 26 pay periods resulting in the Actual Paid & Earned Salary for the entire year exceeding the Full-Time Appointment Salary, which is based on 26 pay periods."]
[]
["*","For Academic Year 2017-18, most employees are subject to 3 days of furloughs.  Please note that for these people, the rate of pay changes three times within the academic year.  The dollar amounts listed above are a best approximation for the actual expense you will see on your accounts; if you are concerned about only charging a specific amount of salary, please email facultyaccrual@uconn.edu for assistance."]

{"sheet":"AY-Accrual Data","index":1,"n_rows":16,"n_cols":19,"rows_kept":16,"cut":false,"hidden":true,"merged":[]}
[]
["Academic Year","Start","SpringOnly-Start","9M-End","10M-End","Pay End","FallOnly Earn End","FallOnly Pay End","PAY-days","FallOnly-days","FallOnly-Paydays","SpringOnly-Paydays","SpringOnly-days","9M-days","10M-days","9M-Accrual","10M-Accrual","Spring Only","Comments"]
["2015-16","2015-08-23","2016-01-01","2016-05-22","2016-06-22","2016-08-22","2016-01-06","2016-02-22",{"$f":"NETWORKDAYS(B3,F3)","v":261},{"$f":"ROUND(K3/(1+P3),0)","v":98},{"$f":"+NETWORKDAYS(B3,H3)","v":131},{"$f":"NETWORKDAYS(C3,F3)","v":167},{"$f":"+NETWORKDAYS(C3,D3)","v":101},{"$f":"+NETWORKDAYS(B3,D3)","v":195},{"$f":"NETWORKDAYS(B3,E3)","v":218},{"$f":"ROUND((I3/N3)-1,6)","v":0.33846199999999999},{"$f":"ROUND((I3/O3)-1,6)","v":0.19724800000000001},{"$f":"ROUND((L3/M3)-1,6)","v":0.65346499999999996}]
["2016-17","2016-08-23","2017-01-01","2017-05-22","2017-06-22","2017-08-22","2017-01-06","2017-02-22",{"$f":"NETWORKDAYS(B4,F4)","v":261},99,{"$f":"+NETWORKDAYS(B4,H4)","v":132},{"$f":"NETWORKDAYS(C4,F4)","v":167},{"$f":"+NETWORKDAYS(C4,D4)","v":101},{"$f":"+NETWORKDAYS(B4,D4)","v":195},{"$f":"NETWORKDAYS(B4,E4)","v":218},{"$f":"ROUND((I4/N4)-1,6)","v":0.33846199999999999},{"$f":"ROUND((I4/O4)-1,6)","v":0.19724800000000001},{"$f":"ROUND((L4/M4)-1,6)","v":0.65346499999999996}]
["2017-18","2017-08-23","2018-01-01","2018-05-22","2018-06-22","2018-08-22","2018-01-08","2018-02-22",261,98,132,168,102,195,218,0.34394200000000003,0.200539,0.65835200000000005,"Manual override to rates for AY17-18 due to furlough days; Spring only rate is preliminary"]
["2017-18-NF","2017-08-23","2018-01-01","2018-05-22","2018-06-22","2018-08-22","2018-01-08","2018-02-22",{"$f":"NETWORKDAYS(B6,F6)","v":261},{"$f":"ROUND(K6/(1+P6),0)","v":99},{"$f":"+NETWORKDAYS(B6,H6)","v":132},{"$f":"NETWORKDAYS(C6,F6)","v":168},{"$f":"+NETWORKDAYS(C6,D6)","v":102},{"$f":"+NETWORKDAYS(B6,D6)","v":195},{"$f":"NETWORKDAYS(B6,E6)","v":218},{"$f":"ROUND((I6/N6)-1,6)","v":0.33846199999999999},{"$f":"ROUND((I6/O6)-1,6)","v":0.19724800000000001},{"$f":"ROUND((L6/M6)-1,6)","v":0.64705900000000005},"Rates for ppl with no furlough"]
["2018-19","2018-08-23","2019-01-01","2019-05-22","2019-06-22","2019-08-22","2019-01-08","2019-02-22",261,99,132,168,102,195,217,0.33846199999999999,0.202765,0.64705900000000005]
["2019-20","2019-08-23","2020-01-01","2020-05-22","2020-06-22","2020-08-22","2020-01-07","2020-02-22",{"$f":"NETWORKDAYS(B8,F8)","v":261},98,131,168,103,{"$f":"+NETWORKDAYS(B8,D8)","v":196},{"$f":"NETWORKDAYS(B8,E8)","v":217},0.33163300000000001,0.202765,0.63106799999999996]
["2020-21","2020-08-23","2021-01-01","2021-05-22","2021-06-22","2021-08-22","2021-01-06","2021-02-22",260,98,131,166,101,195,217,0.33333299999999999,0.198157,0.64356400000000002]
["2021-22","2021-08-23","2022-01-01","2022-05-22","2022-06-22","2022-08-22","2022-01-06","2022-02-22",261,99,132,166,100,195,218,0.33846199999999999,0.19724800000000001,0.66]
["2022-23","2022-08-23","2023-01-01","2023-05-22","2023-06-22","2023-08-22","2023-01-06","2023-02-22",261,99,132,167,101,195,218,0.33846199999999999,0.19724800000000001,0.65346499999999996]
["2023-24","2023-08-23","2024-01-01","2024-05-22","2024-06-22","2024-08-22","2024-01-08","2024-02-22",262,99,132,169,103,196,218,0.33673500000000001,0.20183499999999999,0.64077700000000004]
["2024-25","2024-08-23","2025-01-01","2025-05-22","2025-06-22","2025-08-22","2025-01-07","2025-02-22",261,98,131,168,102,195,216,0.33846199999999999,0.20833299999999999,0.64705900000000005]
["2025-26","2025-08-23","2026-01-01","2026-05-22","2026-06-22","2026-08-22","2026-01-07","2026-02-22",260,98,130,167,102,195,216,0.33333299999999999,0.203704,0.63725500000000002]
["2026-27","2026-08-23","2027-01-01","2027-05-22","2027-06-22","2027-08-22","2027-01-06","2027-02-22",260,98,131,166,101,195,217,0.33333299999999999,0.198157,0.64356400000000002]
["2027-28","2027-08-23","2028-01-01","2028-05-22","2028-06-22","2028-08-22","2028-01-06","2028-02-22",262,99,132,167,101,196,219,0.33673500000000001,0.19634699999999999,0.65346499999999996]

{"sheet":"Definitions","index":2,"n_rows":37,"n_cols":3,"rows_kept":37,"cut":false,"hidden":true,"merged":[[36,0,36,2],[0,0,0,2]]}
["UConn OVPR Sponsored Program Services Salary Calculator Definitions"]
[null,null,{"$f":"+'UConn Salary Calculator'!K3","v":"v. 3.4"}]
["Field Name","Payroll Authorization  (PA) Field Name","Definition"]
["Months in Appointment",null,"List the number of months in the individual's appointment (i.e. 9, 10, 11 or 12)."]
["Full-Time Appointment Salary","Full Time Salary #","This is total salary the individual receives if they work full time for their entire appointment (i.e. a 9 month employee working 100% for the (9 month) academic year takes home $100,000 - this is the amount to list here.  Do not annualize.)"]
[]
["Period Start Date","Start Date","Start Date of PA"]
["Period End Date","End Date","End Date of PA (optional)"]
["# of Pay Periods",null,"Number of pay periods authorized by PA (maximum: 26)"]
[]
["Start Date","Start Date","See: Full-Time Appointment Salary"]
["End Date","End Date","See: Period Start Date"]
["Full Time Salary #","Full Time Salary #","See: Period End Date"]
["% of \ntime","% of Time","The percentage of a full-time work week that the individual works during their appointment term."]
["Actual \nSalary","Actual Salary","The actual salary paid during the Period listed."]
["Months of Service \nper year","Months of Service \nper year","See: Months in Appointment"]
["Actual Earned Salary",null,"The actual salary earned during the period listed.  For 9- and 10-month employees, this reflects the salary they would be paid if they were only paid during their 9 or 10 month appointment."]
[]
["Total Grant Effort",null,"The total effort devoted to grant and grant-related match accounts in the period listed.  For any 9 or 10 month employees with over 75%, please email Jennifer.Pelletier@uconn.edu for additional guidance."]
[]
["Type of Account",null,"Calculated field: Grant (5 or 6 ledger account); Match account (designated by Match Account column); or Non-Grant (any other account)."]
["KFS Account","KFS Account Number","List the KFS account number - funding source."]
["Match Account",null,"Identify whether this account is a designated grant match account."]
["% Effort",null,"List the % of effort committed to the account for the period listed.\nNote: Choose % Effort or Goal $ Amount.  One option must be applied for all accounts listed on the calculator."]
["Goal $ \nAmount",null,"List the $ to charge to the account in the period listed.\nNote: Choose % Effort or Goal $ Amount.  One option must be applied for all accounts listed on the calculator."]
["Effective Effort",null,"Calculated field: Lists the effort associated with the account for the period listed."]
["Goal Actual Salary",null,"Calculated field: Lists the intended dollar amount to be charged the account for the period listed."]
["% of Activity","% of Activity","Calculated field: Lists the % of Activity to list on the PA.  This reflects the new procedure for 9 and 10 month employees to charge grants at their Earned Salary rate (rather than the rate at which they are paid)."]
["Biweekly Salary","Biweekly Salary","Calculated field: Lists the biweekly salary to be charged each account based on the % of Activity listed above."]
["Actual Salary","Actual Salary","Calculated field: Lists the actual salary to be charged each account based on the % of Activity listed above for the period listed."]
[]
["Dept Offset Acct",null,"This account must be populated for any 9 or 10 month employee on grants.  It should not also be listed above as that will make transfering the information to the PA difficult.\n\nThis identifies the departmental or institutional (non-grant) account that will be reduced during the academic year and charged 100% in the summer.  This account will be reduced by difference in the period listed between the dollars earned and the dollars paid for the % Effort committed to grants. (See Example below.)"]
[]
["Remainder to be allocated",null,"This line will always show the remaining % or $ to be allocated (depending on the method chosen.)"]
[]
["Example:"]
["If a 9-month faculty has a Full-time Appointment Salary of $120,000 and works 50% Effort on a grant throughout the academic year, then they earn $120,000 during the academic year (50% = $60,000) but they are only paid $90,000 (50% = $45,000).  In this case, during the academic year, the dept account pay would be reduced by $15,000 (50% pay of $45,000 - $15,000 = $30,000 during the academic year) and the dept should be charged 100% during the summer (3 months = $30,000).  The total dollars for the year remain the same: $60,000 to the grant and $60,000 to the department.  Only the timing has changed and now the grants are charged only when the work is being performed."]