{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["CALCULATION SHEET","voucher"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":1,"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":"CALCULATION SHEET","index":0,"n_rows":103,"n_cols":12,"rows_kept":103,"cut":false,"hidden":false,"merged":[[100,0,100,2],[103,0,104,9],[54,1,54,2],[56,1,56,2],[50,1,50,2],[55,1,55,2],[58,3,58,4],[59,3,59,4],[60,0,60,2],[60,3,60,4],[60,5,60,7],[100,3,100,4],[58,0,58,2],[58,5,58,7],[59,5,59,7],[100,5,100,7],[59,0,59,2],[0,0,0,9],[5,8,5,9],[16,5,16,7],[2,8,2,9],[3,8,3,9],[4,8,4,9],[6,8,6,9],[3,2,3,4],[4,2,4,4],[2,2,2,4],[7,8,7,9],[5,2,5,4],[6,2,6,4],[8,0,8,9],[10,5,10,7],[7,0,7,1],[7,2,7,4],[13,4,15,4],[30,0,30,1],[31,0,31,1],[30,3,30,4],[35,0,35,4],[23,1,23,2],[35,5,35,6],[36,4,36,6],[34,0,34,3],[53,1,53,2],[47,0,47,4],[49,1,49,4],[46,0,46,8],[45,0,45,8],[40,1,40,3],[41,1,41,3],[39,1,39,3],[38,1,38,3]]}
["AGREED SEPARATION CALCULATION SHEET for LOCAL FIXED TERM & PERMENANT STAFF:"]
[]
["Name:",null,null,null,null,null,"Entry on Duty:"]
["Index:",null,null,null,null,null,"Last Day of Active Service:"]
["Grade/Level:",null,null,null,null,null,"Separation Date (COB):"]
["Duty Station:",null,null,null,null,null,"Years of Service:",null,{"$f":"IF(OR(I3=\"\",I5=\"\"),\"\",K72&IF(D12>1,\" years, \",\" year, \")&J71&IF(J71>1,\" months\",\" month\"))","v":""}]
["Country:",null,null,null,null,null,"Date of Birth:",null,null,null,null,"1990-01-01"]
["Position number:",null,null,null,null,null,"normal retirement age",null,{"$f":"+IF(I3>L7,62,60)","v":60}]
["ESTIMATE OF TOTAL ENTITLEMENTS & TOTAL COSTING:"]
[]
["Months of TERMINATION INDEMNITY:",null,null,null,null,"Monthly Salary",null,null,"Organization costs","Staff member entitlement"]
[null,"Years of active service:",null,{"$f":"IF(OR(I3=\"\",I5=\"\"),0,+K76)","v":0},null,"Gross Salary:",null,null,{"$f":"H12*D16","v":0}]
[null,"Months of active service:",null,{"$f":"+J75","v":0},null,"Staff Assessment:",null,null,{"$f":"H13*D16","v":0}]
[null,"months of entitlement",null,{"$f":"+L77","v":0},{"$f":"+IF(+L80>L94,\"Max. allowed due to normal retirement age\",\"\")","v":""},"Lang. Allow.",null,null,{"$f":"H14*D16","v":0}]
[null,{"$f":"IF(B81=TRUE,\"Plus: 50% Indemnity (Type 1 only):\",\"\")","v":""},null,{"$f":"IF(B81=TRUE,D14*0.5,0)","v":0}]
[null,"Total Indemnnity:",null,{"$f":"+L94","v":0},null,"Net Salary:",null,{"$f":"+H12-H13+H14","v":0},{"$f":"+H16*D16","v":0},{"$f":"+H16*D16","v":0}]
[null,null,null,"Monthly ",{"$f":"+IF(B80>0,+IF(B84>0,B84,\"\")&IF(B84>1,\" months\",\" month\"))&IF(B84<3,\" (limited by retirement date)\",\"\")","v":"3 months"},"UNDP Contribution"]
[null,"Net Salary:",null,{"$f":"+IF(B80=2,H16,0)","v":0},{"$f":"+D18*B84","v":0},null,"Monthly ",{"$f":"+E17","v":"3 months"}]
[null,"Spouse allowance:",null,null,{"$f":"IF(B80=2,+D19*B84,0)","v":0}]
[null,"Dep. Child allowance:",null,null,{"$f":"IF(B80=2,+D20*B84,0)","v":0}]
[null,"Health Insurance:",null,null,{"$f":"IF(B80=2,+D21*B84,0)","v":0},null,null,{"$f":"IF(B80=2,+G21*B84,0)","v":0}]
[null,"Dental Insurance:",null,null,{"$f":"IF(B80=2,+D22*B84,0)","v":0},null,null,{"$f":"IF(B80=2,+G22*B84,0)","v":0}]
[null,"Life Insurance:",null,null,{"$f":"IF(B80=2,+D23*B84,0)","v":0},null,null,{"$f":"IF(B80=2,+G23*B84,0)","v":0}]
[null,"Net Pay:",null,{"$f":"IF(B80=2,+D18+D19+D20-D21-D22-D23,0)","v":0},{"$f":"+D24*B84","v":0},"total:",{"$f":"IF(B80=2,+SUM(G19:G23),0)","v":0},{"$f":"IF(B80=2,+SUM(H19:H23),0)","v":0},{"$f":"IF(B82=FALSE,+H24+E18+E19+E20,0)","v":0},{"$f":"+E24","v":0}]
["Commutation of  UNUSED ANNUAL LEAVE:",null,null,"(Must be payed through the payroll.  Do not pay via accounts payable voucher)"]
[null,"Net Monthly Salary",null,null,null,"Rate/Day",null,"Days"]
[null,{"$f":"+H16","v":0},null,null,null,{"$f":"+(B27+D27)/21.75","v":0},null,null,{"$f":"+F27*H27","v":0},{"$f":"+I27","v":0}]
[]
[]
["SPECIAL LEAVE WITHOUT PAY (SLWOP) DETAILS:"]
["SLWOP taken before:",null,null,null,null,{"$f":"+IF(L4>0,+L4,\"last day worked\")","v":"last day worked"},"Months taken:",0]
[]
[]
[]
["Total length of SLWOP taken after:",null,null,null,{"$f":"+IF(K4>0,+K4,\"last day worked:\")","v":"last day worked:"},null,"Years taken:",0]
["SLWOP taken after last day worked:",null,null,null,null,"Months taken:",null,0]
[null,null,null,null,"Workdays taken:",null,null,0]
[null,null,null,null,"Number of months used for calculation","Monthly amount",null,"subtotal"]
[null,"Pension contribution (Staff Member):",null,null,{"$f":"IF(F39=0,0,+H39/F39)","v":0},null,null,{"$f":"+F39*(H35*12)+(F39*H36)+F39*(H37/21.75)","v":0},null,{"$f":"IF(B96=TRUE,IF(H35+H36>0,H39*-1,-H39),0)","v":0}]
[null,"Monthly Pension (organizations share):",null,null,{"$f":"+E39","v":0},{"$f":"+F39*2","v":0},null,{"$f":"+H39*2","v":0},null,{"$f":"IF(B96=TRUE,IF(H35+H36>0,+H40*-1,-H40),0)","v":0}]
[null," Monthly Health Insurance \n(Staff Members + Organizations share):",null,null,null,{"$f":"+D21+G21","v":0},null,{"$f":"+F41*E41","v":0},null,{"$f":"IF(B97=TRUE,IF(H35+H36+H37>0,-H41,0),0)","v":0}]
[null,"Life Insurance Premium:",null,null,null,{"$f":"+D23+G23","v":0},null,{"$f":"+E42*F42","v":0},null,{"$f":"IF(B98=TRUE,-H42,0)","v":0}]
[null,null,null,null,null,null,null,"Total charges to the staff member for SLWOP period:",null,{"$f":"SUM(J39:J42)","v":0}]
["ANY OUTSTANDING AMOUNTS OWED BY THE STAFF MEMBER TO THE ORGANIZATION:",null,null,null,null,null,"Amount:",0,null,{"$f":"-H44","v":0}]
["TOTAL COST TO ORGANIZATION(s) IN LOCAL CURRENCY (excluding any unused annual leave):",null,null,null,null,null,null,null,{"$f":"+I16+I24","v":0}]
["TOTAL Agreed Separation Voucher Payment to staff member in local currency:",null,null,null,null,null,null,null,null,{"$f":"+J16+J24+J43+J44","v":0}]
["TOTAL amount for commutaion of unused annual leave(Payment via Atlas payroll):",null,null,null,null,null,null,null,null,{"$f":"+IF(H27>0,+J27,+J27)","v":0}]
[{"$f":"IF(+I4=\"\",\"UN EXCHHANGE RATE TO USD on the Last day of active service:\",+I4)","v":"UN EXCHHANGE RATE TO USD on the Last day of active service:"},null,null,null,null,null,1,null,"Total entitlement",{"$f":"+IF(H27>0,+J46+J47,+J46)","v":0}]
[]
[null,{"$f":"+IF(D12=0,\"FUNDING SOURCE OF ALL POSITIONS STAFF MEMBER HAS WORKED ON:\",+IF(D12>14,\"FUNDING SOURCE OF ALL POSITIONS STAFF MEMBER HAS WORKED ON DURING\",\"FUNDING SOURCE OF ALL POSITIONS STAFF MEMBER HAS WORKED ON SINCE:\"))","v":"FUNDING SOURCE OF ALL POSITIONS STAFF MEMBER HAS WORKED ON:"},null,null,null,{"$f":"+IF(I3=\"\",\"\",+IF(D12>14,\" PAST 15 YEARS OF CONTINUOUSE SERVICE\",+I3))","v":""}]
[null,"Agency worked for and position funding type:",null,"% of continuous service period worked on each funding type","Distribution of organizational costs\n(local currency):",{"$f":"+IF(B92=TRUE,\"80% advance on entitlement\",\"\")","v":""},{"$f":"+IF(B92=TRUE,\"20% balance due at end of SLWOP\",\"\")","v":""},"Distribution of organizational costs\n(local currency):","Distribution of organizational costs\n(USD):"]
[1,"UNDP on core funding (EB)",null,1,{"$f":"+IF(D53>0.19999,D52*I45,(D52+D53)*I45)","v":0},{"$f":"+E52*0.8","v":0},{"$f":"+E52*0.2","v":0},{"$f":"+G52+F52","v":0},{"$f":"+IF(D52=\"\",\"\",+H52/G$48)","v":0}]
[2,"UNDP on non-core funding (EX)",null,null,{"$f":"+IF(D53>0.199999,D53*I45,0)","v":0},{"$f":"+E53*0.8","v":0},{"$f":"+E53*0.2","v":0},0,{"$f":"+IF(D53=\"\",\"\",+H53/G$48)","v":""}]
[3,"Non UNDP organization (if applicable)",null,null,{"$f":"+D54*I45","v":0},{"$f":"+E54*0.8","v":0},{"$f":"+E54*0.2","v":0},0,{"$f":"+IF(D54=\"\",\"\",+H54/G$48)","v":""}]
[4,null,null,null,{"$f":"+D55*I45","v":0},{"$f":"+E55*0.8","v":0},{"$f":"+E55*0.2","v":0},0,{"$f":"+IF(D55=\"\",\"\",+H55/G$48)","v":""}]
[5,null,null,null,{"$f":"+D56*I45","v":0},{"$f":"+E56*0.8","v":0},{"$f":"+E56*0.2","v":0},0,{"$f":"+IF(D56=\"\",\"\",+H56/G$48)","v":""}]
[null,"Totals",null,{"$f":"+SUM(D52:D56)","v":1},{"$f":"+E56+E55+E54+E53+E52","v":0},{"$f":"+SUM(F52:F56)","v":0},{"$f":"+SUM(G52:G56)","v":0},0,{"$f":"+IF(D57=0,\"\",+H57/G$48)","v":0}]
[]
["VERIFICATION and APPROVAL:",null,null,"Signature",null,"Print Name",null,null,"Date"]
["Data verified & approval by DRR(O):"]
[]
[]
[]
[null,null,null,null,null,null,"last day of service",{"$f":"+I5","v":"00:00:00"}]
[null,null,null,null,null,null,"last day to use",{"$f":"+I5+1","v":"1900-01-01"}]
[null,"Agreed/early seperations",null,null,null,null,null,null,"number of months indemnity based on length of service"]
[null,"years of service","months entitlement",null,null,null,null,null,"days","months"]
[null,5,5,null,null,null,null,null,{"$f":"+DAY(H65)-DAY(I3)","v":1},{"$f":"+MONTH(H65)-MONTH(I3)+IF(MONTH(H65)-MONTH(I3)>MONTH(H65)-MONTH(I3),1,0)","v":0},"years"]
[null,6,6,null,null,null,null,null,null,{"$f":"+IF(I68<0,J68-1,J68)","v":0},{"$f":"+YEAR(H65)-YEAR(I3)","v":0}]
[null,7,7,null,null,null,null,null,null,{"$f":"+IF(J69<0,12+J69,J69)","v":0},{"$f":"INT(+K69+(J69/12))","v":0}]
[null,8,8,null,null,null,null,null,"for service",{"$f":"+J70","v":0}]
[null,9,9,null,null,null,null,null,"SLWOP now",{"$f":"-H36","v":0},{"$f":"+K70","v":0}]
[null,10,9.5,null,null,null,null,null,"subtotal",{"$f":"IF(J71=12,0,IF(J72+J71>0,J72+J71,IF(J72+J71=0,0,12+J72+J71)))","v":0},{"$f":"-H35","v":0}]
[null,11,10,null,null,null,null,null,"SLWOP b4",{"$f":"IF(H31>11,-(H31-INT(H31/12)*12),-H31)","v":0},{"$f":"IF(J72+J71<0,-1+K73+K72,IF(J72+J71<0,0,+K73+K72))","v":0}]
[null,12,10.5,null,null,null,null,null,"subtotal",{"$f":"IF(J73=12,0,IF(J74+J73>0,J74+J73,IF(J74+J73=0,0,12+J74+J73)))","v":0},{"$f":"-INT(H31/12)","v":0}]
[null,13,11,null,null,null,null,null,"entitlement",{"$f":"IF(K76>4,+IF(AND(J75>0,K76<15),(((-VLOOKUP(K76,B68:C78,2)+VLOOKUP(K76+1,B68:C78,2))/12)*J75),0),0)","v":0},{"$f":"IF(J74+J73<0,-1+K75+K74,IF(J74+J73<0,0,+K75+K74))","v":0}]
[null,14,11.5,null,null,null,null,null,null,null,{"$f":"+IF(AND(K76>4,K76<16),+VLOOKUP(K76,B68:C78,2),IF(K76<5,0,C78))","v":0},{"$f":"+K77+J76","v":0}]
[null,15,12,null,null,null,null,null,"additional 50% indemnity"]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(B81=TRUE,D14*0.5,0)","v":0}]
[null,{"$f":"+IF(B83=2,B83,1)","v":1},{"$f":"IF(B80=2,\"cash in lieu of notice ok with retirement(2)\", \"no cash in lieu of notice, due to retirement age(1)\")","v":"no cash in lieu of notice, due to retirement age(1)"},null,null,null,null,null,null,null,"total:",{"$f":"+L77+L79","v":0}]
[null,false,"with additional 50% indemnity",null,null,null,null,null,"number of months indemnity based on time until retirement"]
[1,false,"Staff member serving notice period",null,null,null,null,null,"retirment age date:"]
[1,{"$f":"+A83","v":1},{"$f":"IF(B83=2,\"cash in lieu of notice box checked\", \"cash in lieu of notice box NOT selected\")","v":"cash in lieu of notice box NOT selected"},null,null,null,null,null,"days","months",{"$f":"+DATE(YEAR(I7)+I8,MONTH(I7),DAY(I7))","v":21915}]
[null,{"$f":"+IF(L92<4,L92,3)","v":3},"maximum number of full months of in lieu of notice due to retirement age",null,null,null,null,null,{"$f":"+DAY(K83)-DAY(I4)","v":31},{"$f":"+MONTH(K83)-MONTH(I4)","v":11},"years"]
[null,null,null,null,null,null,null,null,null,{"$f":"+IF(I84<-1,J84-1,IF(I84>0,+J84+1,J84))","v":12},{"$f":"+YEAR(K83)-YEAR(I4)","v":59}]
[null,null,null,null,null,null,null,null,null,{"$f":"+IF(J85<0,12+J85,J85)","v":12},{"$f":"INT(+K85+(J85/12))","v":60}]
[null,{"$f":"+IF(B83=2,B84,0)","v":0},"number months cash in liue of notice",null,null,null,null,null,"total time until retirement",{"$f":"+J86","v":12}]
[null,null,null,null,null,null,null,null,null,null,{"$f":"+K86","v":60}]
[]
[]
[null,"SLWOP:",null,null,null,null,null,null,"maximum months due to retirement age"]
[null,{"$f":"+IF(H35>0,TRUE,IF(H36>0,TRUE,IF(H37>0,TRUE,FALSE)))","v":false},"SM taking SLWOP after last working day",null,null,null,null,null,"indemnity reduced by cash in lieu of notice and retirement",null,null,{"$f":"+J87+K88*12","v":732}]
[null,null,null,null,null,null,null,null,"number of months indemnity",null,null,{"$f":"+IF(L80+B87>L92,L92-B87,+L80)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"+IF(L93>0,L93,0)","v":0}]
[null,{"$f":"+IF(I3>E95,62,60)","v":60},"normal retirement date default",null,"1989-12-31",{"$f":"+I3","v":"00:00:00"}]
[null,false,"remaining in pension fund"]
[null,false,"remaining in medical benefit"]
[null,false,"remaining in life insurance"]
[]
[]
[{"$f":"+IF(D52>0,\"Verified & Approved by GPS focal point:\",\"Verified by GPS focal point:\")","v":"Verified & Approved by GPS focal point:"}]
[]
["NOTES:"]

{"sheet":"voucher","index":1,"n_rows":29,"n_cols":8,"rows_kept":29,"cut":false,"hidden":true,"merged":[]}
["FINAL VOUCHER DETAILS, when no advance has been paid:",null,null,null,null,null,"TABLE OFACCOUNTS RECIEVABLE ACCOUNTS:"]
[null,null,null,null,null,null,14001,"Accounts Receivable "]
["Account",null,"description","Amount",null,null,14002,"Accounts Receivables - Reimbur "]
[{"$f":"+IF(D4>0,\"To be provided by GPS\",\"\")","v":""},null,{"$f":"IF(D4=0,\"\",+'CALCULATION SHEET'!B52)","v":""},{"$f":"+'CALCULATION SHEET'!H52","v":0},null,null,14005,"Salary Overpay to Separtd Stf "]
[{"$f":"+IF(D5>0,\"Detemined by CO\",\"\")","v":""},null,{"$f":"IF(D5=0,\"\",+'CALCULATION SHEET'!B53)","v":""},{"$f":"+'CALCULATION SHEET'!H53","v":0},null,null,14010,"Accrued Interest Receivable "]
[{"$f":"+IF(D6=0,\"\",IF(D6=\"\",\"\",\"Detemined by \"&C6))","v":""},null,{"$f":"IF(D6=\"\",\"\",IF(D6=0,\"\",+'CALCULATION SHEET'!B54))","v":""},{"$f":"IF(+'CALCULATION SHEET'!H54=\"\",\"\",+IF(+'CALCULATION SHEET'!H54=0,\"\",+'CALCULATION SHEET'!H54))","v":""},null,null,14015,"Contributions Receivable "]
[{"$f":"+IF(D7=\"\",\"\",+IF(D7>0,\"Detemined by \"&C7,\"\"))","v":""},null,{"$f":"IF(D7=\"\",\"\",+'CALCULATION SHEET'!B55)","v":""},{"$f":"IF(+'CALCULATION SHEET'!H55>0,+'CALCULATION SHEET'!H55,\"\")","v":""},null,null,14020,"Staff Receivable - Salary Adv "]
[{"$f":"IF(D8=\"\",\"\",+IF(D8>0,\"Detemined by \"&C8,\"\"))","v":""},null,{"$f":"IF(+D8=\"\",\"\",+'CALCULATION SHEET'!B56)","v":""},{"$f":"IF(+'CALCULATION SHEET'!H56>0,+'CALCULATION SHEET'!H56,\"\")","v":""},null,null,14022,"Salary Adv- Intl & HQ Sup Saff  "]
[{"$f":"+IF(D9=\"\",\"\",IF(D9<0,23010,\"\"))","v":""},null,{"$f":"+IF(D9=\"\",\"\",\"MIP\")","v":""},{"$f":"IF(+'CALCULATION SHEET'!J41-'CALCULATION SHEET'!H24-'CALCULATION SHEET'!E21=0,\"\",+'CALCULATION SHEET'!J41-'CALCULATION SHEET'!H24-'CALCULATION SHEET'!E21)","v":""},{"$f":"+IF(D9<0,\"*\",\"\")","v":""},null,14023,"Mid month adv - HQ Sup Staff "]
[{"$f":"+IF(D10=\"\",\"\",+IF(D10<0,\"23050\",\"\"))","v":""},null,{"$f":"+IF(D10=\"\",\"\",\"PENS\")","v":""},{"$f":"IF(+'CALCULATION SHEET'!J39+'CALCULATION SHEET'!J40=0,\"\",+'CALCULATION SHEET'!J39+'CALCULATION SHEET'!J40)","v":""},{"$f":"+IF(D10<0,\"*\",\"\")","v":""},null,14025,"Staff Receivables - Car Adv "]
[{"$f":"+IF(D11=\"\",\"\",IF(D11=0,\"\",\"23025\"))","v":""},null,{"$f":"+IF(D11=\"\",\"\",\"LIFE\")","v":""},{"$f":"IF(+'CALCULATION SHEET'!J42-'CALCULATION SHEET'!E23=0,\"\",+'CALCULATION SHEET'!J42-'CALCULATION SHEET'!E23)","v":""},{"$f":"+IF(D11<0,\"*\",\"\")","v":""},null,14030,"Staff Receivable - Rental Adv A "]
["to be determined",null,{"$f":"IF(D12<0,+VLOOKUP(A12,G2:H29,2,1),\"\")","v":""},{"$f":"IF(+'CALCULATION SHEET'!J44=0,\"\",+'CALCULATION SHEET'!J44)","v":""},{"$f":"+IF(D12<0,\"*\",\"\")","v":""},null,14035,"Staff Receivable - Emergcy Adv A "]
[null,null,"Total net pay",{"$f":"+'CALCULATION SHEET'!J46","v":0},null,null,14040,"Staff Receivable - Educ Grant A "]
[{"$f":"+IF(D14>0,\"to be advised\",\"\")","v":""},null,{"$f":"+IF(D14>0,\"to be advised\",\"\")","v":""},{"$f":"+D13-'CALCULATION SHEET'!J46","v":0},null,null,14042,"Educ Grant Intl & HQs Sup Stf A "]
[null,null,null,null,null,null,14045,"Staff Receivable - Travel Adv A "]
[null,null,null,null,null,null,14047,"Pension Under/Over Contri Loca L "]
["* Do not include implementing agent, project or donor for these charges.",null,null,null,null,null,14050,"Staff Receivable - Other A "]
[null,null,null,null,null,null,14051,"UNDP ARL (STAFF ARL) BRAZIL A "]
[null,null,null,null,null,null,14055,"Non-Staff Receivable - Travel A "]
[null,null,null,null,null,null,14056,"VAT/Sales Tax A "]
[null,null,null,null,null,null,14057,"Petrol Advance A "]
[null,null,null,null,null,null,14060,"Receivables Due in One Year A "]
[null,null,null,null,null,null,14065,"Long Term Receivables A "]
[null,null,null,null,null,null,14070,"Unapplied Deposits-Receivables A "]
[null,null,null,null,null,null,14075,"Unapplied Deps-Non-Receivables A "]
[null,null,null,null,null,null,14080,"Misc Accounts Receivable A "]
[null,null,null,null,null,null,14085,"Staff Recv - Telephone Charges A "]
[null,null,null,null,null,null,14090,"Rounding Suspense A "]
[null,null,null,null,null,null,"to be determined","Not yet determined"]