{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Instructions","SCO Required Fields","Calculator"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":52,"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":"Instructions","index":0,"n_rows":58,"n_cols":2,"rows_kept":58,"cut":false,"hidden":false,"merged":[[32,0,32,1]]}
["INSTRUCTIONS FOR USING THE LEAVE OFFSET CALCULATOR"]
["Payroll Letters Reference:",{"$f":"HYPERLINK(\"https://www.sco.ca.gov/Files-PPSD-Letters/Payr/2012_p12-005.pdf\", \"Payroll Letter #12-005\")","v":"Payroll Letter #12-005"}]
[null,{"$f":"HYPERLINK(\"https://www.sco.ca.gov/Files-PPSD-Letters/Clas/l11-010.pdf\", \"Payroll Letter #11-010\")","v":"Payroll Letter #11-010"}]
[]
["This Excel workbook contains macros. If prompted, select \"Enable Editing\" and \"Enable Content\"."]
[]
["All fields must be completely filled for a final calculation to populate. If a field is N/A, then enter 0.00."]
[]
["Current Salary Details Instructions"]
["Salary Full Rate: ","Enter the employee's full current salary rate (unreduced by PLP)."]
[]
[null,"      Do not enter salary rate from the AR's pay period. It must be current salary rate."]
[]
[null,"      Do not reduce salary rate by Voluntary Personal Leave. You must consider those premiums,"]
[null,"      locked in or not, that should be considered when cashing out the specific employee's leave time.  "]
[null,"      Refer to the California Human Resources (CalHR) Pay scale for applicable pay differential information. "]
[]
[null,"      If Pay Differential indicates ‘Lump Sum Vacation – YES’, then include it in the Salary Full Rate. "]
[null,"      If it indicates ‘Lump Sum Vacation – NO’, then do not include it in the Salary Full Rate.  "]
[]
[null,"      It is important to consider any supplemental pay that should be included in the calculation to determine"]
[null,"      the hourly rate as this will also impact the number of hours needed to offset the Account Receivable.  "]
[]
["Salary Rate:","If employee is paid hourly, select Hourly. Otherwise, select the employee's salary rate."]
["Calc. Hourly Rate:","This will be calculated for you as: Salary Full Rate / Salary Rate"]
["Federal Tax Exempt","Select whether or not the employee is exempt from federal income tax withholding."]
["State Tax Exempt","Select whether or not the employee is exempt from state income tax withholding."]
["State","If the employee is not tax exempt, select the state that will be withholding state taxes."]
["Social Security","Select whether or not the employee is subject to Social Security withholdings."]
["Medicare","Select whether or not the employee is subject to Medicare withholdings."]
["SDI","Select whether or not the employee is subject to SDI withholdings."]
[]
["AR Details Instructions"]
["AR Gross:","Enter the gross of the AR. If not applicable, enter 0.00."]
["AR Net:","Enter the net of the account receivable (AR). If not applicable, enter 0.00."]
["AR Retirement Deductions:","Retirement:"]
[null,"      Enter the Retirement Withholding (W/H) amount from the AR, if any. If not applicable, enter 0.00."]
[null,"OPEB:"]
[null,"      Enter the OPEB Withholding (W/H) amount from the AR, if any. If not applicable, enter 0.00"]
[null,"003/005/006/009:"]
[null,"      If applicable, this will be found on page 2 of the AR (Misc. Deductions)."]
[null,"      If present, enter the total amount for deduction codes 003, 005, 006, or 009. If not applicable, enter 0.00"]
[null,"      Other deduction codes do not affect the taxable gross of the AR and can be ignored."]
[null,"      Credits (negative amounts on the AR) are entered in the calculator as positive numbers."]
[null,"      Debits (positive amounts on the AR) are entered in the calculator as negative numbers."]
[null,"      Multiple amounts for these deduction codes can be added together to find the total."]
[null,"            Example:"]
[null,"         DED ORG   AMOUNT"]
[null,"         003       100.00-*"]
[null,"         003        75.00 *"]
[null,"                    (100.00-) + (75.00) = (25.00-) Enter 25.00 in the calculator."]
[]
["Calculator End-Result Definitions:"]
["Leave Offset","The calculator provides you with the number of hours required for the leave offset."]
["Gross Cash Out","The calculator provides you with the gross cash out, calculated as: Leave Offset * Salary Rate"]
[]
[]
[null,"instructions revised: 10/13/2023"]

{"sheet":"SCO Required Fields","index":1,"n_rows":9,"n_cols":14,"rows_kept":9,"cut":false,"hidden":false,"merged":[[0,0,0,13],[1,0,2,0],[1,1,1,8],[1,9,1,13]]}
["Required Fields per SCO PPOB Civil Service Program Area"]
["Civil Service Program Area","Current Salary Details",null,null,null,null,null,null,null,"AR Details"]
[null,"Salary Full","Salary Rate","Federal Tax Exempt","State Tax Exempt","State","Social Security","Medicare","SDI","AR Gross","AR Net","Retirement","OPEB","003/005/\n006/009"]
["Benefits","x","x","x","x","x","x","x","x",null,"x"]
["Disability","x","x","x","x","x","x","x","x","x","x","x","x","x"]
["Payroll","x","x","x","x","x","x","x","x","x","x","x","x","x"]
["Premium Pay","x","x","x","x","x","x","x","x","x","x","x","x","x"]
["Retirement","x","x","x","x","x","x","x","x","x"]
[null,"X is a required field per the Program Area. On the calculator, if field is N/A - enter 0.00."]

{"sheet":"Calculator","index":2,"n_rows":77,"n_cols":15,"rows_kept":77,"cut":false,"hidden":false,"merged":[[8,2,8,4]]}
[]
[]
[null,"Leave Offset AR Calculator - 2025 (eff. 1/1)"]
[]
[null,"Current Salary Details",null,null,null,null,"AR Details"]
[]
[null,"Salary Full Rate",null,{"$f":"IF(Validation_SalaryFull_Blank,\"*\",\"\")","v":"*"},null,null,null,"AR Gross",null,{"$f":"IF(Validation_ARGross_Blank,\"*\",\"\")","v":"*"}]
[]
[null,"Salary Rate",null,null,null,{"$f":"IF(Validation_SalaryRate_Blank,\"*\",\"\")","v":"*"},null,"AR Net",null,{"$f":"IF(Validation_ARNet_Blank,\"*\",\"\")","v":"*"}]
[]
[null,"Calc. Hourly Rate",{"$f":"IFERROR(ROUND(Salary_Full/SalaryRate,2),\"-\")","v":"-"},"/hr"]
[]
[null,"Federal Tax Exempt","No",{"$f":"IF(Validation_FedTax_Blank,\"*\",\"\")","v":""},null,null,null,"AR Retirement Deductions"]
[]
[null,"State Tax Exempt","No",{"$f":"IF(Validation_StateTax_Blank,\"*\",\"\")","v":""},null,null,null,"Deduction","Amount"]
[]
[null,"State","California",{"$f":"IF(Validation_State_Blank,\"*\",\"\")","v":""},null,null,null,"Retirement",null,{"$f":"IF(Validation_ARDed1_Blank,\"*\",\"\")","v":"*"}]
[]
[null,"Social Security","No",{"$f":"IF(Validation_SS_Blank,\"*\",\"\")","v":""},null,null,null,"OPEB",null,{"$f":"IF(Validation_ARDed2_Blank,\"*\",\"\")","v":"*"}]
[]
[null,"Medicare","No",{"$f":"IF(Validation_Medicare_Blank,\"*\",\"\")","v":""},null,null,null,"003/005/006/009",null,{"$f":"IF(Validation_ARDed3_Blank,\"*\",\"\")","v":"*"}]
[]
[null,"SDI","No",{"$f":"IF(Validation_SDI_Blank,\"*\",\"\")","v":""}]
[]
[]
[null,null,"Leave Offset:",{"$f":"IFERROR(Leave_Offset,\"\")","v":""}]
[null,null,null,null,null,{"$f":"IF(Validation_AnyBlank,\"* Complete All Fields - if N/A, enter 0.00\",\"\")","v":"* Complete All Fields - if N/A, enter 0.00"}]
[null,null,"Gross Cash Out:",{"$f":"IF(Leave_Offset=\"\",\"\",IFERROR(Gross_CashOut,\"\"))","v":""}]
[]
[null,"See the \"Instructions\" tab for more information.",null,null,null,null,null,null,null,"Revised 12/20/2024"]
[]
[]
[null,"Salary Rate:",{"$f":"IFERROR(VLOOKUP(C9,Table_SalaryRates,2,FALSE),\"n/a\")","v":"n/a"},null,null,null,null,null,null,null,null,"Salary Rates:"]
[null,null,null,null,null,null,null,null,null,null,null,"Hourly",1]
[null,"1. Calculate AR Taxable Gross",null,null,null,null,null,null,null,null,null,173.33,173.33]
[null,"AR Gross:",null,{"$f":"AR_Gross","v":0},null,null,null,null,null,null,null,"184.17 (BU5)",184.17]
[null,"Retirement:","-",{"$f":"AR_RetDed1","v":0},null,null,null,null,null,null,null,"177.67 (BU6 7k, not CHP)",177.67]
[null,"OPEB:","-",{"$f":"AR_RetDed2","v":0},null,null,null,null,null,null,null,"184.16 (S06 7k)",184.16]
[null,"003/005/006/009:","-",{"$f":"AR_RetDed3","v":0},null,null,null,null,null,null,null,"168.00 (BU17 21 day pay period)",168]
[null,"Taxable Gross:",null,{"$f":"AR_Gross-IF((AR_RetDed1+AR_RetDed2+AR_RetDed3)<0,0,AR_RetDed1+AR_RetDed2+AR_RetDed3)","v":0},null,null,null,null,null,null,null,"176.00 (BU17 22 day pay period)",176]
[]
[null,"2. Get applicable withholding rates, then calculate exact payment gross",null,null,null,null,null,null,null,null,null,"State Tax Rates:"]
[null,"Fed:",{"$f":"IF(Federal_Tax_Exempt=\"Yes\",0,Rate_Fed)","v":0.22},{"$f":"(Exact_PaymentGross - AR_Taxable_Gross) * Rate_Fed_Exact_Pmt","v":0},null,null,null,null,null,null,null,"California",6.6000000000000003E-2]
[null,"State:",{"$f":"IF(State_Tax_Exempt=\"Yes\",0,Rate_State)","v":6.6000000000000003E-2},{"$f":"(Exact_PaymentGross - AR_Taxable_Gross) * Rate_State_Exact_Pmt","v":0},null,null,null,null,null,null,null,"Illinois",3.7499999999999999E-2]
[null,"SS:",{"$f":"IF(SS=\"Yes\",Rate_SS,0)","v":0},{"$f":"Rate_SS_Exact_Pmt*Exact_PaymentGross","v":0},null,null,null,null,null,null,null,"New York",9.6199999999999994E-2]
[null,"Med:",{"$f":"IF(Medicare=\"Yes\",Rate_Med,0)","v":0},{"$f":"Rate_Med_Exact_Pmt*Exact_PaymentGross","v":0},null,null,null,null,null,null,null,"Other",0]
[null,"SDI:",{"$f":"IF(SDI=\"Yes\",Rate_SDI,0)","v":0},{"$f":"Rate_SDI_Exact_Pmt*Exact_PaymentGross","v":0}]
[null,"Exact payment gross:",null,{"$f":"(AR_Net-AR_Taxable_Gross*(Rate_Fed_Exact_Pmt+Rate_State_Exact_Pmt))/(1-Rate_Fed_Exact_Pmt-Rate_State_Exact_Pmt-Rate_SS_Exact_Pmt-Rate_Med_Exact_Pmt-Rate_SDI_Exact_Pmt)","v":0},null,null,null,null,null,null,null,"Withholding Rates:"]
[null,null,null,null,null,null,null,null,null,null,null,"Fed",0.22,"[PPM H102], [IRS Notice 1036]"]
[null,"3. Get minimum payment gross as the larger of exact payment gross or taxable gross",null,null,null,null,null,null,null,null,null,"State",{"$f":"IFERROR(VLOOKUP(State,Table_StateTaxRates,2,FALSE),0)","v":6.6000000000000003E-2}]
[null,"Minimum payment gross:",null,{"$f":"IF(Exact_PaymentGross>=AR_Taxable_Gross,Exact_PaymentGross,AR_Taxable_Gross)","v":0},null,null,null,null,null,null,null,"SS",6.2E-2,"[PPM H256]"]
[null,null,null,null,null,null,null,null,null,null,null,"Med",1.4500000000000001E-2,"[PPM H275]"]
[null,"4. Calculate equivalent hours of pay for the minimum payment gross",null,null,null,null,null,null,null,null,null,"SDI",1.2E-2,"[PPM E803]","eff. 1/1/25"]
[null,"Minimum hours:",null,{"$f":"IFERROR(Minimum_PaymentGross/HourlySalary_Calculated,\"\")","v":""}]
[]
[null,"5.Round up to next quarter hour increment"]
[null,"Leave offset hours:",null,{"$f":"IF(Validation_AnyBlank,\"\",IFERROR(ROUNDUP(D54*4,0)/4,\"\"))","v":""},null,null,null,null,null,null,null,"Active"]
[]
[null,"6. Calculate gross cash out",null,null,null,null,null,null,null,null,null,"Inactive"]
[null,"Gross cash out:",null,{"$f":"IFERROR(Leave_Offset*HourlySalary_Calculated,0)","v":0}]
[]
[null,"(Optional) Calculate payment withholdings, gross, and net"]
[null,"Fed:",{"$f":"IF(Federal_Tax_Exempt=\"Yes\",0,Rate_Fed)","v":0.22},{"$f":"ROUND((Actual_PaymentGross - AR_Taxable_Gross) * Rate_Fed_Exact_Pmt,2)","v":0},null,null,null,null,null,null,null,"Validation","Blank?","Not Number?"]
[null,"State:",{"$f":"IF(State_Tax_Exempt=\"Yes\",0,Rate_State)","v":6.6000000000000003E-2},{"$f":"ROUND((Actual_PaymentGross - AR_Taxable_Gross) * Rate_State_Exact_Pmt, 2)","v":0},null,null,null,null,null,null,null,"Salary Full Rate",{"$f":"IF(TRIM(Salary_Full) = \"\", TRUE, FALSE)","v":true},{"$f":"NOT(ISNUMBER(Salary_Full))","v":true}]
[null,"SS:",{"$f":"IF(SS=\"Yes\",Rate_SS,0)","v":0},{"$f":"ROUND(Rate_SS_Exact_Pmt*Actual_PaymentGross,2)","v":0},null,null,null,null,null,null,null,"Salary Rate",{"$f":"IF(Salary_Rate = \"\", TRUE, FALSE)","v":true},{"$f":"NOT(ISNUMBER(Salary_Rate))","v":true}]
[null,"Med:",{"$f":"IF(Medicare=\"Yes\",Rate_Med,0)","v":0},{"$f":"ROUND(Rate_Med_Exact_Pmt*Actual_PaymentGross,2)","v":0},null,null,null,null,null,null,null,"Federal Tax Exempt",{"$f":"IF(Federal_Tax_Exempt = \"\", TRUE, FALSE)","v":false}]
[null,"SDI:",{"$f":"IF(SDI=\"Yes\",Rate_SDI,0)","v":0},{"$f":"ROUND(Rate_SDI_Exact_Pmt*Actual_PaymentGross,2)","v":0},null,null,null,null,null,null,null,"State Tax Exempt",{"$f":"IF(State_Tax_Exempt = \"\", TRUE, FALSE)","v":false}]
[null,"Payment gross:",null,{"$f":"Gross_CashOut","v":0},null,null,null,null,null,null,null,"State",{"$f":"IF(State = \"\", TRUE, FALSE)","v":false}]
[null,"Payment net:",null,{"$f":"Actual_PaymentGross-AR_Net-SUM(D63:D67)","v":0},null,null,null,null,null,null,null,"Social Security",{"$f":"IF(SS = \"\", TRUE, FALSE)","v":false}]
[null,null,null,null,null,null,null,null,null,null,null,"Medicare",{"$f":"IF(Medicare = \"\", TRUE, FALSE)","v":false}]
[null,null,null,null,null,null,null,null,null,null,null,"SDI",{"$f":"IF(SDI = \"\", TRUE, FALSE)","v":false}]
[null,null,null,null,null,null,null,null,null,null,null,"AR Gross",{"$f":"IF(AR_Gross = \"\", TRUE, FALSE)","v":true},{"$f":"NOT(ISNUMBER(AR_Gross))","v":true}]
[null,null,null,null,null,null,null,null,null,null,null,"AR Net",{"$f":"IF(AR_Net = \"\", TRUE, FALSE)","v":true},{"$f":"NOT(ISNUMBER(AR_Net))","v":true}]
[null,null,null,null,null,null,null,null,null,null,null,"Retirement",{"$f":"IF(AR_RetDed1 = \"\", TRUE, FALSE)","v":true},{"$f":"NOT(ISNUMBER(AR_RetDed1))","v":true}]
[null,null,null,null,null,null,null,null,null,null,null,"OPEB",{"$f":"IF(AR_RetDed2 = \"\", TRUE, FALSE)","v":true},{"$f":"NOT(ISNUMBER(AR_RetDed2))","v":true}]
[null,null,null,null,null,null,null,null,null,null,null,"003/005/006/009",{"$f":"IF(AR_RetDed3 = \"\", TRUE, FALSE)","v":true},{"$f":"NOT(ISNUMBER(AR_RetDed3))","v":true}]
[null,null,null,null,null,null,null,null,null,null,null,"Any:",{"$f":"OR(M64,M65,M66,M67,M68,M69,M70,M71,M72,M73,M74,M75,M76)","v":true},{"$f":"OR(N64,N65,N66,N67,N68,N69,N70,N71,N72,N73,N74,N75,N76)","v":true}]