Passer au contenu principal

logoCorrectSize.png

Adaptive Insights French

SUTA Calculation Based on Each State’s Max and Rate

This article provides steps to create a SUTA calculation based on each state’s rate and max.  A cube sheet was used to house the assumptions to make maintenance easier when adding new states.  If you have a small number of states and will not be adding more, you can use Global Assumptions.  
 

The first step is to create the State dimension and a value for each state:

Go to Modeling > Dimensions to create the dimension and a value for each state.


 

Create a Cube Sheet and add the State dimension:

Go to Modeling > User Assigned Sheets > and select New Sheet.  Fill in the applicable information and select Next.

Select Cube Accounts and create a new cube standard account. Create one for the max and another for the rate, which should be set to Percent.  Under Data Privacy for both accounts, make them public at top level only.

Go back to the Sheet Summary and select Dimensions, Attributes and Levels. Expand Dimensions and add the State for SUTA dimension to the sheet.  Select the dimension and deselect Uncategorized so it does not appear as an option in the sheet.  Also select Organization and select Top Level. An option is to select Time and remove Quarters.  Be sure to Save your changes.

Go back to Sheet Summary and select Accessibility and give the applicable users access to the sheet and Save:


 

Open the Cube sheet and add the assumptions for each state:

In the example below, the State Dimension and Accounts are in the rows to simplify data entry which is done at the Total Company (Only) level.

 

Add the State Dimension to the Personnel Sheet:

Go to Modeling > Level Assigned or User Assigned Sheets and select edit next to the sheet. Select Columns and Levels, find the dimension, and add it to the sheet and Save.

 

Create the account to calculate SUTA:

Go back to the Sheet Summary and select Modeled Accounts. Create a new calculated account.  The following formula can be copied and pasted into the formula section.  You will need to make adjustments based on your naming conventions.  The SUTA Max and Rate will be pulled from the cube sheet.  (Note: To check the formula use “if” and once the entire formula is working as expected, change the “if” to “iff”.

 (iff(ROW.CumulativeSalary< ACCT.State_Taxes.SUTA_Max[level=Total Company, State for SUTA=this],ROW.Salary* ACCT.State_Taxes.SUTA_Rate[level=Total Company, State for SUTA=this],iff(ROW.CumulativeSalary[time=this-1]< ACCT.State_Taxes.SUTA_Max[level=Total Company, State for SUTA=this] and ROW.CumulativeSalary[time=this-1]!=0,( ACCT.State_Taxes.SUTA_Max[level=Total Company, State for SUTA=this]-ROW.CumulativeSalary[time=this-1])* ACCT.State_Taxes.SUTA_Rate[level=Total Company, State for SUTA=this],0))+iff(ROW.CumulativeSalary> ACCT.State_Taxes.SUTA_Max[level=Total Company, State for SUTA=this], ACCT.State_Taxes.SUTA_Max[level=Total Company, State for SUTA=this]* ACCT.State_Taxes.SUTA_Rate[level=Total Company, State for SUTA=this],0)*iff(versionmonth(this)=0 and ROW.CumulativeSalary> ACCT.State_Taxes.SUTA_Max[level=Total Company, State for SUTA=this] or month(this)=1 and ROW.CumulativeSalary> ACCT.State_Taxes.SUTA_Max[level=Total Company, State for SUTA=this] or versionmonth(this)=versionmonth(ROW.StartDate) and ROW.CumulativeSalary> ACCT.State_Taxes.SUTA_Max[level=Total Company, State for SUTA=this],1,0))
 

In the Personnel Sheet, assign a State to each row:


 

Verify the calculation is working as expected: 

The final step is to test the formula to ensure it is working properly.  Go to the sheet, right click and select row details, find the SUTA formula and check the results for accuracy. If you have a Personnel Audit Sheet, I recommend using that sheet for your review:

If you already have a SUTA calculation that you referred to in a GL account, you will need to change the formula to pull the new SUTA account in the applicable version(s). 

 

 


  • Cet article vous a été utile ?