BIAS Help CenterFinancial Annual ReportingHow do I complete Excel tables for Schedule 09?

How do I complete Excel tables for Schedule 09?

  • The following steps can be used for either manually prepared reports, or accrual reports from BIAS.  The steps below were prepared using the Accrual Balances Report from Advanced Payroll.  Using your accrual balances spreadsheet from How do I start the Schedule 09?, add additional columns to complete your compensated absences:
    • Employer Costs
      • Medicare (1.45%) and Social Security (6.2%) as applicable
      • LEOFF (5.33%) and PERS (12.86%) for comp time only
      • PFML (Cost x employer rate). New This Year
    • Total Cost (Cost + Employer Costs)

  1. Copy the Excel Tables for Schedules and Notes.xlsx from BIAS Files to Preparer Files.  Use paste Special Values to Paste the spreadsheet of accruals/compensated balances into the Compensated Absences tab.  

If you are not needing the supplied notes in the form:

  • Click Enable Editing if needed.  Go to Review tab and unclick Show All Comments

  1. In the column for Social Security, enter the formula in row 2 by typing =.  
    • Then click in cell H2.  Then type *0.062.  The formula will appear in the cell as =[@Cost]*0.062.

  1. In the column for Medicare, enter the formula in row 2 by typing =.  
    • Then click in cell H2.  Then type *0.0145.  The formula will appear in the cell as =[@Cost]*0.0145.

  1. In the column for PERS, enter the formula in row 2 by typing =.  
    • Then click in cell H2.  Then type *0.1286.  The formula will appear in the cell as =[@Cost]*0.1286.  
  2. You only want the formula for PERS for the Comp Time accrual.  
    • Click the down arrow on column D and uncheck Comp Time.  
    • Then highlight all the cells in the PERS column and press delete on the keyboard.  
    • Go back and click the down arrow for column D and check Comp Time.  This will leave the formulas in PERS only for Comp Time.  
    • Check your employees and only calculate PERS on PERS eligible employees.

  1. In the column for LEOFF, enter the formula in row 2 by typing =.  
    • Then click in cell H2.  Then type *0.0533.  The formula will appear in the cell as =[@Cost]*0.0533.
  2. You only want the formula for LEOFF for the Comp Time accrual.  
    • Click the down arrow on column D and uncheck Comp Time.  
    • Then highlight all the cells in the LEOFF column and press delete on the keyboard.  
    • Go back and click the down arrow for column D and check Comp Time.  This will leave the formulas in LEOFF only for Comp Time.  
    • Check your employees and only calculate LEOFF on LEOFF eligible employees

PFMLA is only reported if the Employer contributes to PFMLA

  1. In the column for PFMLA, enter the formula in row 2 by typing =.  
    • Then click in cell H2.  Then type *0.00147.  The formula will appear in the cell as =[@Cost]*0.00147.  
    • Comp Time does not need to be removed from PFMLA.
    • Check your employees and only calculate PFMLA on PFMLA eligible employees.

  • Use the Pensions tab of the Excel Tables for Schedules & Notes.xlsx to calculate your net pension liabilities (NPL) and assets (NPA) with the files from the Pension Files Folder.
    • Look up your Employer Contributions & Allocation Percentage from the PEFI-2019.pdf or Unaudited-Contribution-Amounts-and-Allocation-Percentages.xlsx This is the easiest to use as it is grouped by ORG ID to calculate NPL for PERS plans & NPA for LEOFF plans
    • Verify the Employer Contributions in BIAS for 7/1/2018 to 6/30/2019
    • If you contribute to PERS 2/3 then you will have a PERS 1 UAAL liability
    • Look up your NPA for VFFRPF (Volunteer Fire Fighter & Reserve Officers Relief Pension Fund) from 2019 proportional share.pdf.  Due to rounding of proportionate share, use the allocation amount as published.
    • ONLY NPLs will be reported on the Schedule 09

Definitions

NPL - Net pension liabilities.  These have a positive balance and represent your entity's share of the State's pension deficit.

NPA - Net pension assets.  These have a negative balance and represent your entity's ownership of the State's fully funded pension.

Congratulations, you are done!

0 Comments

Add your comment

E-Mail me when someone replies to this comment