QUESTION: How can I compute a monthly benefit using rates based on the eligible location and eligible unions where employees work. Not all location/union combinations are eligible to participate in the plan. Benefit service is only earned at eligible locations with eligible union. Any employee can change jobs and locations at any time. The collective bargaining agreement is made at locations individually; so unions may have different benefit rates at different locations. The rates are used prospectively. Below are three charts which show:
- the company's location, codes and effective date the location elected (voted) to participate in the plan.
- employment events
- location, union and basic monthly benefit per year of service
| The company's locations and dates joining the plan. | |||
| Location; | Code | Eligible? | Effective Date |
| Minneapolis, MN | 700 | yes | 3/1/1953 |
| St Paul, MN | 701 | yes | 3/1/1965 |
| Minnetonka, MN | 702 | no | |
| Bloomington, MN | 703 | yes | 3/1/1965 |
| Maple Grove, MN | 704 | no | |
| Woodbury, MN | 705 | no | |
| Annandale, MN | 706 | yes | 5/1/1990 |
| Waconia, MN | 707 | no | |
| Hastings, MN | 900 | yes | 1/1/2018 |
| Code | Employment Event |
| 1 | Hire |
| 2 | Termination |
| 3 | Rehire |
| 4 | Leave of absence - personal |
| 5 | Return from leave |
| 6 | Leave of absence - medical |
| 7 | Leave of absence - paternal/maternal |
| 8 | Layoff |
| 9 | Return from Layoff |
The unions in the eligible locations are: AFL-CIO, IAM, IBEW, TWU, UAW and UGW. Eligible unions, for each location, are shown with their effective dates and benefit rates.
| Location | Effective date | Union | Benefit rate | Union | Benefit rate |
| Hastings, MN | 1/1/2018 | UGW | 25.00 |
| Location | Effective date | Union | Benefit rate | Union | Benefit rate |
| Annandale, MN | 5/1/1990 | IAM | 23.50 | ||
| 1/1/1995 | IAM | 25.00 | IBEW | 24.00 | |
| TWU | 25.00 | ||||
| 1/1/2000 | AFL_CIO | 25.00 | IAM | 27.00 | |
| IBEW | 25.00 | TWU | 27.00 | ||
| UAW | 25.00 | UGW | 28.00 | ||
| 1/1/2006 | AFL_CIO | 26.50 | IAM | 27.50 | |
| IBEW | 29.00 | TWU | 29.00 | ||
| UAW | 25.00 | UGM | 27.00 | ||
| 1/1/2012 | AFL_CIO | 29.50 | IAM | 28.00 | |
| IBEW | 31.00 | TWU | 31.50 | ||
| UAW | 25.00 | UGW | 30.25 | ||
| 1/1/2018 | AFL-CIO | 31.50 | IAM | 29.50 | |
| IBEW | 33.00 | TWU | 33.50 | ||
| UAW | 31.50 | UGW | 33.00 |
| Location | Effective date | Union | Benefit rate | Union | Benefit rate |
| Bloomington, MN | 3/1/1965 | IAM | 23.50 | ||
| 1/1/1969 | IAM | 25.00 | IBEW | 24.00 | |
| TWU | 25.00 | ||||
| 3/1/1975 | IAM | 28.00 | IBEW | 24.50 | |
| TWU | 26.75 | ||||
| 4/1/1981 | IAM | 29.25 | IBEW | 25.75 | |
| TWU | 27.00 | UAW | 24.00 | ||
| UGW | 25.00 | ||||
| 1/1/1989 | IAM | 31.25 | IBEW | 26.75 | |
| TWU | 29.75 | UAW | 25.75 | ||
| UGW | 26.25 | ||||
| 1/1/1995 | IAM | 33.00 | IBEW | 29.00 | |
| TWU | 31.25 | UAW | 27.25 | ||
| UGW | 29.00 | ||||
| 1/1/2000 | AFL_CIO | 25.00 | IAM | 34.00 | |
| IBEW | 31.25 | TWU | 32.25 | ||
| UAW | 29.00 | UGW | 29.50 | ||
| 1/1/2006 | AFL_CIO | 26.50 | IAM | 35.75 | |
| IBEW | 32.00 | TWU | 34.00 | ||
| UAW | 31.25 | UGW | 30.00 | ||
| 1/1/2012 | AFL_CIO | 29.50 | IAM | 36.00 | |
| IBEW | 33.50 | TWU | 34.50 | ||
| UAW | 32.00 | UGW | 31.50 | ||
| 1/1/2018 | AFL-CIO | 31.50 | IAM | 37.50 | |
| IBEW | 34.75 | TWU | 35.50 | ||
| UAW | 33.50 | UGW | 33.00 |
| Location | Effective date | Union | Benefit rate | Union | Benefit rate |
| Minneapolis, MN | 3/1/1965 | TWU | 10.00 | ||
| 1/1/1969 | TWU | 12.75 | |||
| 1/1/1976 | TWU | 14.00 | UAW | 14.00 | |
| 4/1/1981 | TWU | 15.75 | UAW | 17.00 | |
| 1/1/1989 | TWU | 17.00 | UAW | 19.50 | |
| 1/1/1995 | TWU | 20.00 | UAW | 20.00 | |
| 1/1/2000 | TWU | 21.75 | UAW | 20.00 | |
| 1/1/2006 | TWU | 24.75 | UAW | 25.00 | |
| 1/1/2012 | TWU | 27.00 | UAW | 27.50 | |
| 1/1/2018 | TWU | 30.00 | UAW | 30.00 |
The employee data contains histories of the member's employment events (Events), location (Location) and union (Union) in three separate fields.
ANSWER: Develop an Accrual Definition that uses an array of benefit values representing the member's "history" of benefit values to compute the total benefit. The history of values will include zero (0) for the period of non-eligible service. The steps are:
- Ensure that the data can be easily translated. If Events, Locations or unions are in character format, transform the characters to numerical codes. Events and location already have numerical code which we will use. We will set the union codes from AFL-CIO to 1, IAM to 2, IBEW to 3, TWU to 4, UAW to 5 and UGW to 6.
- Define a benefit Service Definition Set, for example: Benefit_Service.
- This set should use an Event Definition using the company's employment events so periods of non-employment are not included in the benefit service.
- This set should include period of employment even if the employee is working at an ineligible location or union. When the member is not working at eligible location/union combination, the value of the associated benefit rate will be set to is zero (0).
- Define an effective dated date array in the Data Dictionary. For example: Members_relevant_dt. This will hold relevant dates for each particular member. Relevant dates occur when
- the member changes location and or unions
- the benefit rate increases (changes).
- Develop an Excel text spreadsheet, or csv file to use with ProAdmin's special operator #GETTABVAL, to produce an array of monthly benefit values for each member's calculation. The csv file has three main elements:
- The first row contains column headings: Location , Location code, Union, Union code, Effective date, Monthly Benefit Amount
- The second row contains #GETTABVAL parameters:
- Location - this is text, and were using a numerical code, so we can ignore it (I)
- Location code - this has to be an exact match (E)
- Union - this is text, and were using a numerical code, so we can ignore it (I)
- Union code - this has to be an exact match (E)
- Effective date - for a particular location-union combination, the values at the effective date remain effective until the rate changes. This is a range (R)
- Monthly benefit Amount - this is the value we are seeking (V)
- The third row and beyond contains the data.
Your csv file may look like:
- Default the field Members_relevant_dt in Data Defaults.
L_dt := #START Locations &
U_dt := #START Unions &
B_dt := "rates?" #GETTABVAL (3) &
; build the array:
12 #ARRAY (L_dt, L_dt, U_dt U_dt, B_dt, B_dt)
Check the box to replace all values, whether or not missing.
- In Data Defaults redefault Members_relevant_dt to remove duplicate rows and to add the day before each relevant date to provide an ending for the previous period
d1_dt := #START all_Relevant_Rate_dt &
d2_dt := 1 #START all_Relevant_Rate_dt & ; the next relevant date
da_dt := (d1_dt <> d2_dt) * d1_dt & ; disregard dates that are the same
db_dt := da_dt #dateminus 1d & ; get the previous day
; build the array:
12 #ARRAY (da_dt, da_dt, db_dt, db_dt)
Check the box to replace all values, whether or not missing.
- Adapt the Plan's Definition to include the field Members_relevant_dt as part of the set of calculation dates. In the topic plan attributes of the plan's Definition, set the last parameter of the miscellaneous dialog to Members_relevant_dt.
- Define three Accrual Basis Components of type Database field, to return all values from Unions, Locations and Members_relevant_dt. They can have the same name as their field.
- Define another Accrual Basis Component - subformula to get the rates. For example Members_rates
- Define an Accrual Definition as a Benefit Formula Component, such as Members_benefit, where the format type is Career average [basis x (sum of rates)]. The definition includes:
- Basis Formula - Members_rates
- Accrual Rates -
- Service Definition Set - Benefit_Service
- Rate Type - Constant 12.
- Accrued Benefit & Rounding - select: Expected value
- Members_benefit can be used as a Benefit Definition.
d_dt := All_elevant_dt &
L := d_dt #GETASOF Locations &
U := d_dt #GETASOF Unions &
"rates" #GETTABVAL (l, U, d_dt)