{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Instructions","Leave Payout","Lookups and Calculations","Revision Notes","Adjusted Emp Date Calc"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":29,"comments":1,"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":"Instructions","index":0,"n_rows":6,"n_cols":1,"rows_kept":6,"cut":false,"hidden":false,"merged":[]}
[]
["Enter only the information requested in the BLUE shaded areas. Everything else is automatically calculated."]
[]
["Click on any field in the spreadsheet for further instructions or to determine the source of the information requested."]
[]
["If you receive an error message highlighted in YELLOW please verify all required information is entered and is correct to ensure that the calculations are correct."]

{"sheet":"Leave Payout","index":1,"n_rows":57,"n_cols":10,"rows_kept":57,"cut":false,"hidden":false,"merged":[[50,0,50,9],[56,0,56,1],[0,0,0,9],[7,5,7,7],[9,5,9,7],[10,5,10,7],[2,8,2,9],[53,0,53,9],[32,1,32,2],[31,1,31,2],[3,3,3,4],[7,2,7,4],[51,3,51,7],[7,0,7,1],[49,0,49,1],[4,0,4,9],[23,4,23,8],[15,0,15,1],[23,2,23,3],[16,0,16,1],[20,0,20,2],[19,0,19,2],[22,0,22,9],[14,0,14,1],[17,0,17,2],[12,5,12,8],[13,0,13,1],[9,3,10,4],[12,0,12,1],[11,0,11,1],[6,0,6,9],[8,0,8,1],[5,0,5,9],[11,5,11,7],[9,0,9,1],[10,0,10,1],[8,5,8,8]]}
["Leave Payout Calculations"]
[]
["Agency Number:",null,null,null,null,null,null,null,"Revised: 10/25/2016"]
["Check Date to Be Paid:",null,null,"2016-07-01"]
[{"$f":"IF(AND(A18<>\"\",D18=\"\"),\"PLEASE ENTER DISPOSITION OF DIS CREDITS IN CELL E14\",IF(AND(A21<>\"\",D21=\"\"),\"PLEASE ENTER DISPOSITION OF TSL SICK BALANCE IN CELL H14\",IF(OR($C$10=\"\",$C$17=\"\",$D$4=\"\",$C$14=\"\",$I$10=\"\"),\"INCOMPLETE\",IF(ISERROR(I53),\"TOTAL VALUE IS IN ERROR\",\"\"))))","v":""}]
["This spreadsheet is policy driven.  If you have an error above this line, please correct it to ensure that the calculations are correct."]
["You must select or fill in all information between rows 8 and 18."]
["Employee's Name:",null,null,null,null,"Emp ID #"]
["Contract Length:",null,12,null,null,{"$f":"IF(AND($I$10<>\"\",$C$9=\"\"),\"Contract Length required to calculate Hourly Rate\",IF(ISBLANK(I12),\"FTE Equivalent Required to Calculate Hourly Rate\",\"\"))","v":""}]
["VSDP Employee:",null,"y",{"$f":"IF(AND(C11=\"Y\",OR(C10=\"N\",C10=\"\")),\"If TSL Bank Employee is 'Y' VSDP Employee must be 'Y'\",IF(AND(C10=\"Y\",C11=\"\"),\"please Enter TSL Bank Employee status\",\"\"))","v":""},null,"Current Annual Salary:",null,null,51448.08]
["TSL Bank Employee:",null,"n",null,null,"Current Hourly Rate:",null,null,{"$f":"IFERROR(ROUND($I$10/'Lookups and Calculations'!$D$4,2),0)","v":24.73}]
["PMIS Next Anniversary Date:",null,"2021-06-25",null,null,"FTE Equivalent",null,null,1]
["PMIS Last Anniversary Number:",null,5,null,null,"Prior Salary History for 3 Years Prior to Separation:"]
["Adjusted Emp. Date:",null,{"$f":"IF(C12=\"\",\"\",'Adjusted Emp Date Calc'!B16)","v":"2011-06-25"},null,null,"eff. date:",null,"prev. salary:"]
["Separation Date:",null,"2016-09-08",null,null,"eff. date:",null,"prev. salary:"]
["YRS of Service:",null,{"$f":"IF(C12=\"\",\"\",IF(DATE(YEAR(C15),MONTH(C14),DAY(C14))<=C15,YEAR(C15)-YEAR(C14),(YEAR(C15)-1)-YEAR(C14)))","v":5},null,null,"eff. date:",null,"prev. salary:"]
["Separation Status:",null,"TERM",null,null,"eff. date:",null,"prev. salary:"]
[{"$f":"IF(C17=\"\",\"\",IF(D25>0,IF(C17<>\"RETIRED\",\"\",(LEFT(C25,LEN(C25)-1)&\" used to Purchase Service?\")),\"\"))","v":""},null,null,"N",null,"eff. date:",null,"prev. salary:"]
[null,null,null,null,null,"eff. date:",null,"prev. salary:"]
[{"$f":"IF(C17=\"INVSEP\",\"SEV BENEFIT PD. ENDED?\",\"\")","v":""},null,null,{"$f":"IF(AND(C17=\"INVSEP\",(IF(DAY(D4)>=DAY(C15),0,-1)+(YEAR(D4)-YEAR(C15))\n*12+MONTH(D4)-MONTH(C15))<12),\"N\",\"Y\")","v":"Y"}]
[{"$f":"IF(AND(C17=\"INVSEP\",OR(C10=\"N\",AND(C10=\"Y\",C11=\"Y\"))),\"Retain TSL Sick Balance?\",\"\")","v":""},null,null,""]
[]
["EMPLOYEE 'S ACCRUED UNUSED LEAVE HOURS:"]
[null,null,{"$f":"IF(ISERROR(I36),\"Cannot Pay This Now\",\"\")","v":""},null,{"$f":" IF(OR(A42=\"DUPTYPE\",A45=\"DUPTYPE\",A48=\"DUPTYPE\"),\"<---------      SELECT DIFFERENT LEAVE TYPES    ---------->\",\"\")","v":""}]
["Annual: ",258,{"$f":"IF(AND(C10=\"y\",C11<>\"y\"),\"Dis. Credits: \",\"TSL Sick: \")","v":"Dis. Credits: "},null,"Comp: ",34.200000000000003,"Recog: ",null,"Overtime: "]
[]
[null,null,"Hours",null,"Rate",null,null,"HUE01 Special Pay No","GROSS AMT. DUE"]
[]
[{"$f":"A25","v":"Annual: "},null,{"$f":"IF(C16<5,IF(B25>192,192,B25),IF(C16<10,IF(B25>240,240,B25),IF(C16<20,IF(B25>288,288,B25),IF(C16>19,IF(B25>336,336,B25)))))","v":240},"x",{"$f":"I11","v":24.73},null,null,{"$f":" IF(OR(C17=\"INVSEP\",C17=\"ENHCD RET\"),\"066\",\"009\")","v":"009"},{"$f":"IF($C$17=\"\",\"ERROR\",IF(C17=\"transfer\",\"TRF\",C29*E29))","v":5935.2}]
[]
[null,"*Max. Payment Hours allowed Depending on Length of Service:"]
[null,"Less than 5 yrs.",null,192,null,"10-19 yrs.",288]
[null,"5-9 yrs",null,240,null,"20 or more yrs",336]
[]
[null,null,"Hours",null,null,null,"Rate"]
[{"$f":"C25","v":"Dis. Credits: "},null,{"$f":"IF(AND(C10=\"Y\",C11=\"N\",C17=\"LTD\"),D25-D38,D25)","v":0},"x",0.25,"x",{"$f":"I11","v":24.73},{"$f":"IF(OR(C17=\"INVSEP\",C17=\"ENHCD RET\"),\"067\",\"010\")","v":"010"},{"$f":"IF(D18=\"Y\",0,IF(OR(AND(C10=\"Y\",C11=\"N\",C17=\"INVSEP\",D20=\"N\",D25>0),AND(C10=\"Y\",C11=\"Y\",C17=\"INVSEP\",D20=\"N\",D21=\"Y\",D25>0),AND(C10=\"N\",C11=\"N\",C17=\"INVSEP\",D20=\"N\",D21=\"Y\",D25>0)),\"ERROR\",IF($C$17=\"\",\"ERROR\",IF(C17=\"TRANSFER\",\"TRF\",IF(C17=\"LTD\",E37*G36,IF(E37*G36<5000,E37*G36,5000))))))","v":0}]
[null,null,null,null,{"$f":"IF(AND(C10=\"Y\",C11=\"N\",C17=\"LTD\"),D39*E36,C36*E36)","v":0},"Payout Hours (25%)"]
[null,{"$f":"IF(C17=\"LTD\",\"LTD BAL TRF TO UNUM:\",\"Non LTD\")","v":"Non LTD"},null,{"$f":"IF(AND(C10=\"Y\",C11=\"N\",C17=\"LTD\"),((ROUNDDOWN(D25/8,0)*8)),\"\")","v":""},null,{"$f":"IF(AND(C10=\"Y\",C11=\"N\",C17=\"InvSep\"),\"Disability Credits must be held 12 months for Invol. Sep. EES\",IF(OR(AND(C10=\"Y\",C11=\"Y\",C17=\"INVSEP\",D20=\"N\",D21=\"Y\"),AND(C10=\"N\",C11=\"N\",C17=\"INVSEP\",D20=\"N\",D21=\"Y\")),\"TSL Sick Balance must be held 12 months for Invol. Sep. EEs\",\"Leave may be paid\"))","v":"Leave may be paid"}]
[null,{"$f":"IF(C17=\"LTD\",\"Remaining DC Bal:\",\"\")","v":""},null,{"$f":"IF(AND(C10=\"Y\",C11=\"N\",C17=\"LTD\"),D25-D38,\"\")","v":""}]
[]
[null,null,"Hours",null,"Rate"]
[{"$f":"IF(OR(E25=G25,E25=I25),\"DUPLICATE LV TYPE\",E25)","v":"Comp: "},null,{"$f":"F25","v":34.200000000000003},"x",{"$f":"E29","v":24.73},null,null,{"$f":"VLOOKUP(A42,spcpay,IF(OR($C$17=\"INVSEP\",$C$17=\"ENHCD RET\"),3,2),FALSE)","v":11},{"$f":"IFERROR(IF(OR($C$17=\"\",$H$42=\"\"),\"ERROR\",C42*E42),\"ERROR\")","v":845.76600000000008}]
[null,null,null,null,{"$f":"IF(AND($A$42='Lookups and Calculations'!$A$10,E42<>0),\"The larger of the last hourly pay rate, or average hourly pay rate over past 3 years\",\"\")","v":""}]
[]
[{"$f":"IF(OR(G25=E25,G25=I25),\"DUPLICATE LV TYPE\",G25)","v":"Recog: "},null,{"$f":"H25","v":0},"x",{"$f":"E29","v":24.73},null,null,{"$f":"VLOOKUP(A45,spcpay,IF(OR($C$17=\"INVSEP\",$C$17=\"ENHCD RET\"),3,2),FALSE)","v":11},{"$f":"IFERROR(IF(OR($C$17=\"\",$H$45=\"\"),\"ERROR\",C45*E45),\"ERROR\")","v":0}]
[null,null,null,null,{"$f":"IF(AND($A$45='Lookups and Calculations'!$A$10,E45<>0),\"The larger of the last hourly pay rate, or average hourly pay rate over past 3 years\",\"\")","v":""}]
[]
[{"$f":"IF(OR(I25=E25,I25=G25),\"DUPLICATE LV TYPE\",I25)","v":"Overtime: "},null,{"$f":"IF(J25>240,240,J25)","v":0},"x",{"$f":"IF(AND($A$48='Lookups and Calculations'!$A$10,OTLvAvgHrlyRate>$I$11),OTLvAvgHrlyRate,'Leave Payout'!$I$11)","v":24.73},null,null,{"$f":"VLOOKUP(A48,spcpay,IF(OR($C$17=\"INVSEP\",$C$17=\"ENHCD RET\"),3,2),FALSE)","v":27},{"$f":"IFERROR(IF(OR($C$17=\"\",$H$48=\"\"),\"ERROR\",C48*E48),\"ERROR\")","v":0}]
["Excess Overtime Leave Balance:",null,{"$f":"IF(J25>240,J25-240,\"\")","v":""},"x",{"$f":"IF(C50=\"Y\",IF(AND($A$48='Lookups and Calculations'!$A$10,OTLvAvgHrlyRate>$I$11),OTLvAvgHrlyRate,'Leave Payout'!$I$11),0)","v":0},null,null,{"$f":"VLOOKUP(A48,spcpay,IF(OR($C$17=\"INVSEP\",$C$17=\"ENHCD RET\"),3,2),FALSE)","v":27},{"$f":"IF(C49=\"\",0,C49)*E49","v":0}]
["Public Safety Employee?",null,"N"]
[{"$f":"IF(AND(C49<>\"\",C50=\"N\"),\"OVERTIME LEAVE OVER 240 FOR NON PUBLIC SAFETY EMPLOYEE MUST BE PROCESSED AS OVERTIME PAY\",\"\")","v":""}]
[null,null,null,"Total Combined Leave Payout:",null,null,null,null,{"$f":"SUM(IF(I29=\"trf\",0,I29)+IF(I36=\"trf\",0,I36)+I42+I45+I48+I49)","v":6780.9660000000003}]
[]
["If sending all or part of leave payout to Deferred Comp, use Deferred Compensation Payout Spreadsheet to determine amount to enter on H0ZDC. The employee should provide you with a \"Payroll Authorization Form - One-Time Deferral\". This form is provided by the Deferred Compensation vendor. Access the vendor's website through the Virginia Retirement System (VRS) website at: www.varetire.org."]
[]
[]
["Prepared By :",null,null,null,null,null,null,null,"Date",{"$f":"NOW()","v":"2016-10-25T16:27:08.490"}]

{"sheet":"Lookups and Calculations","index":2,"n_rows":39,"n_cols":6,"rows_kept":39,"cut":false,"hidden":false,"merged":[[3,0,3,2],[36,0,36,1],[38,0,38,1],[38,4,46,8],[15,4,16,8],[33,4,34,8],[23,0,23,4],[21,0,21,2]]}
[]
["Contract Length (months):",null,null,{"$f":"'Leave Payout'!$C$9","v":12}]
["FTE:",null,null,{"$f":"'Leave Payout'!I12","v":1}]
["Applicable No. of Work Hours in Year: (based on contract length and FTE)",null,null,{"$f":"(((2080/12)*$D$2)*'Leave Payout'!I12)","v":2080}]
[]
[]
["Leave Types","Reg SP","Inv Sep SP","Separation Status",null,"Yes/No"]
["Annual: ","09",66,"LTD",null,{"$f":"IF(OR($C$25=\"TRANSFER\",$C$25=\"LTD\"),\"\",\"\")","v":""}]
["Comp: ",11,68,"TERM",null,"Y"]
["Overtime: ",27,68,"TRANSFER",null,"N"]
["Recog: ",11,68,"RETIRED"]
["OnCall: ",28,68,"INVSEP"]
[{"$f":"IF($D$16=\"Y\",\"Bonus: \",\"\")","v":"Bonus: "},{"$f":"IF($D$16=\"Y\",25,\"\")","v":25},{"$f":"IF($D$16=\"Y\",68,\"\")","v":68}]
["Sick: ",10,67]
[]
["Is Bonus Leave valid at this time?",null,null,"Y","<--- SPO: update this field when Bonus Leave is awarded/activated by the Governor or DHRM."]
[]
[]
[]
["Calculating average hourly pay rate over the last three continuous years of service"]
[]
["Current Annual Salary:",null,null,{"$f":"'Leave Payout'!$I$10","v":51448.08}]
[]
["Prior Salary History for 3 Years Prior to Separation:"]
["eff. date:",{"$f":"IF('Leave Payout'!$G$14>$C$39,'Leave Payout'!$G$14,\"\")","v":""},"prev. salary:",{"$f":"IF('Leave Payout'!$G$14>$C$39,'Leave Payout'!$I$14,0)","v":0}]
["eff. date:",{"$f":"IF('Leave Payout'!$G$15>=$C$39,'Leave Payout'!$G$15,\"\")","v":""},"prev. salary:",{"$f":"IF('Leave Payout'!$G$15>=$C$39,'Leave Payout'!$I$15,0)","v":0}]
["eff. date:",{"$f":"IF('Leave Payout'!$G$16>$C$39,'Leave Payout'!$G$16,\"\")","v":""},"prev. salary:",{"$f":"IF('Leave Payout'!$G$16>$C$39,'Leave Payout'!$I$16,0)","v":0}]
["eff. date:",{"$f":"IF('Leave Payout'!$G$17>$C$39,'Leave Payout'!$G$17,\"\")","v":""},"prev. salary:",{"$f":"IF('Leave Payout'!$G$17>$C$39,'Leave Payout'!$I$17,0)","v":0}]
["eff. date:",{"$f":"IF('Leave Payout'!$G$18>$C$39,'Leave Payout'!$G$18,\"\")","v":""},"prev. salary:",{"$f":"IF('Leave Payout'!$G$18>$C$39,'Leave Payout'!$I$18,0)","v":0}]
["eff. date:",{"$f":"IF('Leave Payout'!$G$19>$C$39,'Leave Payout'!$G$19,\"\")","v":""},"prev. salary:",{"$f":"IF('Leave Payout'!$G$19>$C$39,'Leave Payout'!$I$19,0)","v":0}]
[null,null,null,{"$f":"SUM($D$22,$D$25:$D$30)","v":51448.08},"Total of Eligible Previous Annual Salaries"]
[null,null,null,{"$f":"1+(COUNTIF(B25:B30,\">0\"))","v":1},"Number of Eligible Annual Salaries"]
[null,null,null,{"$f":"D31/D32","v":51448.08},"Average Annual Rate"]
[null,null,null,{"$f":"IFERROR(ROUND($D$33/$D$4,2),0)","v":24.73},"Hourly rate from Average Annual Rate (OTLvAvgHrlyRate)"]
[]
[]
["Separation Date:",null,{"$f":"'Leave Payout'!$C$15","v":"2016-09-08"}]
[]
["Lookback Start Date:",null,{"$f":"DATE(YEAR(C37)-3,MONTH(C37),DAY(C37))","v":"2013-09-08"},null,"The Lookback Start Date is the earliest date for determining average hourly rate for Overtime Leave payout.  Since overtime leave is paid by the current agency when transferring, we will only use the previous 3 years at the current agency.  If the employee has not completed 3 years at the current agency, only the salary earned during current agency tenure is necessary."]

{"sheet":"Revision Notes","index":3,"n_rows":7,"n_cols":3,"rows_kept":7,"cut":false,"hidden":true,"merged":[]}
["Revision Date","Updates and Changes Made","Revised by"]
[]
["2015-05-21","* Added error message, \"Contract Length is required to calculate hourly rate\".","rer"]
["2015-02-05","* Added 3 blank rows at bottom for agencies to insert additional signature lines.","rer"]
["2014-11-05","* Added Contract Length (Employment Months from PSE305) field.\n* Corrected hourly rate calculation to take into account varying contract lengths.\n* Added \"Revisions\" tab.","rer"]
["2014-10-15","* Added variable hourly rate calculation for Overtime Leave payout.\n* Added \"Bonus Leave\" valid indicator. Maintained by SPO, this field prevents use of Bonus Leave when no hours have been awarded by Governor.\n* Added \"Lookups and Calculations\" tab to simplify making updates to background calculations.\n* Added option to turn off field validation messages.","rer"]
["2014-08-27","Original version posted to DOA website features include:\n* Form drafted in accordance with DHRM leave policies, including banked Traditional Sick Leave effective 7/1/2014.\n* Correct/standardized special pay code for paying Recognition Leave balances.\n* Automatic calculation of severance period for determining when disability credits and banked traditional sick leave may be paid.","rer"]

{"sheet":"Adjusted Emp Date Calc","index":4,"n_rows":25,"n_cols":2,"rows_kept":25,"cut":false,"hidden":false,"merged":[[2,0,2,2],[17,0,23,1]]}
[]
[]
["Convert PMIS Anniversary Date to CIPPS Adjusted Employment Date"]
[]
["Employee Name:",{"$f":"'Leave Payout'!C8","v":0}]
["EIN:",{"$f":"'Leave Payout'!I8","v":0}]
["PMIS Next Anniversary Date (Nxt-Ann on PSE305)",{"$f":"'Leave Payout'!C12","v":44372}]
["PMIS Last Anniversary Number (Anniv on PSE305)",{"$f":"'Leave Payout'!C13","v":5}]
[]
["PMIS Next Anniversary Date",{"$f":"pmisanndate","v":"2021-06-25"}]
["Last Anniversary Months",{"$f":"(pmisannnum+5)*12","v":120}]
["Next Anniversary Date Minus Last Ann Months",{"$f":"EDATE(cpmisanndate,-lstannmos)","v":"2011-06-25"}]
[]
["SDP Begin Date for HPIUS:",{"$f":"DATE(YEAR(pmisannnew),MONTH(pmisannnew),1)","v":"2011-06-01"}]
[]
["ADJ EMP DATE for HPIUS:",{"$f":"pmisannnew","v":"2011-06-25"}]
[]
["NOTE: The employee’s leave accrual rate calculation includes all cumulative periods of salaried state service. Periods of Leave Without Pay of more than 14 consecutive calendar days normally do NOT count as service. Adjustment of the leave anniversary date is required in PMIS to record such LWOP periods.  This spreadsheet assumes that the PMIS Next Anniversary Date has been adjusted for periods of LWOP."]
[]
[]
[]
[]
[]
[]
["Revised 10/4/2012"]