Attachment_3_-_ACT_Toolkit_Manual.pdf

Contents

Actuarial Calculation Toolkit Manual Homepage i

Introduction 7

About ACT Release Notes Technical Support

Installation Notes Problems and Workarounds

ValTool 11

Get Started The Icon Bar Open a Case Create New ACT Tables

Manage Participant Groups Define a Participant Group Create a User Defined Group for Runtime Use Modify a User-Defined Group Delete a Participant Group

Define Criteria Define Custom Criteria

Edit Data Edit Plan Data Edit User-Defined Field Data Enter and Edit Participant Data

Design the Spreadsheet Open the Spreadsheet Work in the Spreadsheet Select Fields for Use in the Spreadsheet Return to ValTool

Use MS Excel Retrieve Participant Data Calculate Participant Data

Actuarial Calculation Toolkit Manual Homepage

Update Participant Data Select Participant Data

Add and Empty Tables Command Reference

File Commands Valuation Commands Utilities Commands Admin Commands ValTool Commands

DOPT Report System (DRS) 49

Overview Select a Case

Open a Case Include Substantial Owner or Beneficiary Information in a Report

Select a Participant Group Open a Participant Group Create a Participant Group Modify a User-defined Group Delete a User-defined Group

Design the DOPT Report Choose the fields that will appear in the report Borrow formatting from another field Format the report fields Decode a field value Adjust the report's page settings Save the report as a different file type

Produce the DOPT Report Case Commands

Design Tool 77

Get Started Design the Statement and Worksheet Select Variables Select the Base Data Print the Design Layouts Functions, Fields, Operators and Connectors Command Reference

File Commands Edit Commands View Commands

Runtime Tool 113

Benefit Statement and Recalculation System (BSRS) Select a Case

Generate an Individual Statement Generate a Bulk Statement Perform Individual Recalculation Perform a Bulk Recalculation View All Benefit Statements Command Reference

Benefit Estimation Tool (BET) Select a Case Calculate a Benefit Recalculate a Bulk Statement Edit Participant Data Generate an Individual Statement Generate a Bulk Statement View All Benefit Statements Command Reference

Valuation Database 169

Introduction Valuation Database Contents

CASE Specific Tables (########.ACCDB) ACT Application Tables (ACT.ACCDB)

Add-in Documentation 189

General Information Pension Actuarial Add-ins Functions

List of Functions and their Formats Actuarial Present Value Factors

Add-in Functions Input Error Detection Error Code Programmers Guide Syntax of Exported Functions

Social Security Functions Covered Compensation Function PIA Functions SSA Functions

Complex Parameters Common to Multiple Functions AnnType (CalcMethod, SAnnType, and AnnTp) BenForm (From_Code, To_Code, and Form) Method (IntMethod and SMethod) BRD (XBRD and ObjBRD) MMortality and FMortality (TheMNameHandle & The FNameHandle, SMMortName &

SFMortName, and SMortName) MGen and FGen (SMGen & SFGen) PHSorPY Mapping Functions

Benefit Administration Web Applications Launcher Troubleshooting

VERSION 380

ADT Utility 383

Install Operate

Case Processing Create Reports Create Charts View Participant Data

Contact Information

Archive Access 403

Select a Case Select Group or Version Run the Benefit Program for a Case View Documents Add and Replace Files Access to Available Archived Files View and Copy Files Archive Access Support Notes

Archive Utility 413

ACT User Group Meetings 415

2016 User Group Meetings January 2016 Meeting

2015 User Group Meetings January 2015 Meeting February 2015 Meeting March 2015 Meeting April 2015 Meeting May 2015 Meeting June 2015 Meeting July 2015 Meeting August 2015 Meeting October 2015 Meeting November 2015 Meeting

2014 User Group Meetings January 2014 Meeting February 2014 Meeting

March 2014 Meeting April 2014 Meeting May 2014 Meeting June 2014 Meeting July 2014 Meeting August 2014 Meeting October 2014 Meeting December 2014 Meeting

2013 User Group Meetings January 2013 Meeting February 2013 Meeting March 2013 Meeting April 2013 Meeting May 2013 Meeting June 2013 Meeting July 2013 Meeting August 2013 Meeting October 2013 Meeting November 2013 Meeting

2012 User Group Meetings January 2012 Meeting February 2012 Meeting March 2012 Meeting April 2012 Meeting May 2012 Meeting June 2012 Meeting July 2012 Meeting August 2012 Meeting October 2012 Meeting November 2012 Meeting

2011 User Group Meetings January 2011 Meeting February 2011 Meeting March 2011 Meeting April 2011 Meeting May 2011 Meeting June 2011 Meeting July 2011 Meeting August 2011 Meeting October 2011 Meeting November 2011 Meeting December 2011 Meeting

2010 User Group Meetings January 2010 Meeting March 2010 Meeting April 2010 Meeting June 2010 Meeting July 2010 Meeting

August 2010 Meeting November 2010 Meeting

2009 User Group Meetings October 2009 Meeting December 2009 Meeting

Help Using the BAPD Operations Manual 968

Access Access the BAPD Operations Manual from the Intranet Access the BAPD Operations Manual from the Portal

Navigation Common Icon Bar Table Left Navigation Pane Table Searching Using the Index

Functionality Printing Save Documents from the Resources Page Submit Feedback Bookmarking E-mail a Manual Page Link

Index 997

About ACT 06/27/2014

Actuarial Calculation Toolkit (ACT), is used by actuaries to define how a plan must be valued and how benefit statements for that plan should be generated. ACT also allows the actuary to define how a plan’s benefit estimation calculation is to be conducted, prior to final valuation.

Actuaries use ACT to take basic data from the Participant Data Audit and perform the valuation.

Once the valuation is complete –

ACT data is provided to other systems such as ADT (Automated Data Transfer) and Datahub, to load the data into the paying database – Genesis.

Benefit statements for each customer are loaded into ALG (Automated Letter Generation) system, so that Benefit Determination letters can be sent to customers.

All files related to that particular plan are archived and are made available through the Archive system.

ACT includes the following tools:

ValTool - The Valuation Spreadsheet Tool

DRS - The DOPT Report System

Design Tool - BET -Benefit Estimation Tool and BSRS - Benefit Statement and Recalculation System

Runtime Tool - BET - Benefit Estimation Tool and BSRS - Benefit Statement and Recalculation System

Chapter 1

Introduction

Release Notes 02/26/2016

Click a link to read the ACT release notes.

Release Notes Table

Release Date Release Notes Link

February 26, 2016 BCV Legacy Suite 1.6 Release Notes PDF

August 13, 2015 BCV Legacy Suite 1.5 Release Notes PDF

December 11, 2014 BCV Legacy Suite 1.4 Release Notes PDF

August 22, 2014 BCV Legacy Suite 1.3 Release Notes PDF

May 29, 2014 BCV Legacy Suite 1.2 Release Notes PDF

January 21, 2014 BCV Legacy Suite 1.1 Release Notes PDF

August 26, 2013 BCV Legacy Suite 1.0 Release Notes PDF

November 19, 2012 End of Service Life Release Notes PDF

March 23, 2012 ADT 6.2 Release Notes PDF

January 13, 2012 ACT Archive 6.2.1 Release Notes PDF

December 5, 2011 ACT Archive 6.2 Release Notes PDF

March 25, 2011 ACT Archive 6.1.1 Release Notes PDF

January 21, 2011 ACT Archive 6.1 Release Notes PDF

June 25, 2010 ACT Archive 6.0 Release Notes PDF

June 26, 2009 ACT Archive 5.1 Release Notes PDF

July 27, 2000 ACT Version 5 Release Notes PDF

Technical Support

Installation Notes

09/29/2014

Include the following in the ACT installation steps:

1. Add ATPBGC2007.XLAS to the MS Excel add-ins and check the box next to ATPBGC2007.

2. Verify that the correct version number is displayed when the About option is selected under the Help menu of ACT components, ADT, or Archive.

3. Call VERSION() function from Excel to verify the version of the ATPBGC Add-in.

Problems and Workarounds

08/22/2014

Problem and Workaround Documents Table

Date Document Link

March 25, 2011 Problems and Workarounds PDF

The primary processes performed by the Valuation Spreadsheet Tool (ValTool) are:

Retrieving input data from the ACT tables into the nnnnnnnnV1.XLSX or nnnnnnnnVE.XLSX sheets as input data to the Excel formulae used for performing the valuation calculations.

Applying the formulae in the Excel spreadsheet to the input data for each participant in the ACT tables to perform the valuation calculations.

Writing the results of the calculations to the ACT tables to be used by various modules of ACT and ADT.

Updates and changes to the ACT tables are made through ValTool only. This guarantees the user will always have the same participants included in each customer level table for the case.

Getting Access to ValTool

For users on the network, ACT, with its module ValTool, is available for download from Altiris. Users who are not connected to the network will be provided with thumb drive access to ACT.

Get Started

To learn more on how to get started with ValTool, click any of the available links.

Note If ValTool is opened while MS Excel is running, the system will display the error message: "Another process of Excel is already running. Please close before reopening ValTool." Click the OK button and ValTool will close.

Chapter 2

ValTool

The Icon Bar

Icon Bar Definitions Table

Icon Icon Definition

Displays the Select Working Folder dialog box. See Open a Case.

Displays the Define New Case Number dialog box. See Create New ACT Tables.

Launches the PBGC Intranet and opens the ACT online help site.

Closes ValTool.

Open a Case

1. Launch the Valuation Spreadsheet Tool (ValTool). The system will display the Select Working Folder dialog box to select the file location.

2. Click the Browse button to search for the required folders.

3. Click the Continue button.

The system will display the Select Case dialog box with all the plan names and case numbers of the cases that have ACT tables in the location selected in step 2.

a) To select a case, click on the Plan Name or type a case number in the Case Number box.

b) To select a spreadsheet, select the Valuation/BSRS or Benefit Estimation radio button.

Selection Availability Graph

Selections Radio Button Availability

BSRS-Valuation

BET-Benefit Estimation

V1 Spreadsheet ONLY yes no

VE Spreadsheet ONLY no yes

Database ONLY no no

V1 and VE Spreadsheets yes yes

Note Some circumstances will alter spreadsheet availability.

4. Click the OK button.

The case number, plan name and DOPT will appear in the title bar at the top of the window.

Create New ACT Tables

1. Choose File | Create ACT Tables.

2. In the Define New Case Number window, enter the 8-digit case number and the plan name.

Note There is no need to select any of the boxes under Choose Tables, including the Select All box. All tables will be created

3. Click the OK button.

The following will be created: a database (nnnnnnnn.accdb), with all tables, a valuation spreadsheet (nnnnnnnnv1.xlsx), and a benefit estimation spreadsheet (nnnnnnnnve.xlsx). Also created are two configuration files, nnnnnnnns1.cfg and nnnnnnnnse.cfg.

Note After selecting a case, the following warning may appear: "Warning: There are no groups defined in the group. You must define groups in the group table before performing valuation options." To clear this warning, click the OK button. However, the Valuation pull down menu will remain inactive until a participant group is defined.

Manage Participant Groups

Define a Participant Group

1. Choose Utilities | Manage Groups.

2. Click on a group in the Groups Available for Use box, and then click the Assign for Use button.

The selected group will then be available in the Groups Assigned for Use box.

3. Enter the Start and End Sheets for each group.

Entering the Start and End Sheets helps orient group data.

Note

The Start Sheet can never be after the End Sheet.

Sheets must exist in the nnnnnnnnV*.XLSX file before assignment.

A sheet cannot have more than one (1) group assigned to it.

4. Click the Check Sheet Sequence button to ensure Excel sheet numbers are sequential.

Note To remove a group assigned for use, select the group and click the Retire From Use button. The group will be returned to the Groups Available for Use box.

Create a User Defined Group for Runtime Use

08/13/2015

1. In the Manage Groups and Sheets window, click the Add UD Group button.

The system displays the Groups window:

2. Enter a group description in the Group Desc box.

3. Select the type of user-defined group:

A Valuation Group is a custom-defined group that can be assigned for use on the Managed Group and Sheets window.

A Statement Group is a custom-defined group that can be used for processing bulk estimate/statements in Runtime.

A Recalculation Group is a custom-defined group that can be used for processing bulk recalculation (automated testing) in Runtime.

4. Click the Change SQL button to begin entering the SQL criteria.

This SQL criteria will allow ValTool to select the participants that meet the description and load their data into the spreadsheet. ValTool initiates the SQL Where statement.

5. Select criteria from the Connector, Field Expression, Operator, and Value Expression boxes. (For help on constructing the SQL Where field, see Defining Criteria.)

6. Click the Add button to move the SQL expression to the SQL construction box.

The selections in the Connector, Field Expression, Operator, and Value Expression boxes are moved into the SQL construction box.

7. Click the Update SQL button to move the constructed SQL expression from the construction box to the Group SQL location, where it cannot be edited.

Clicking the Change SQL button moves it back into the construction box for further edits.

8. Click the OK button.

Note Default option will be Valuation Group (if valuation case selected) or Estimation Group (if estimation case selected). When creating Statement Group or Recalculation Group, the Assign for Use option will be disabled.

Modify a User-Defined Group

08/13/2015

1. Choose Utilities | Manage Groups.

2. In the Manage Groups and Sheets window, select the user-defined group from Groups Available for Use section that requires edits.

3. Click the Modify UD Group buttons to open the Groups window.

4. Edit the Group Desc (Group Description) and the Group SQL.

Group Desc: Type the new description in the box.

SQL statement: Use either action.

Click the Change SQL button; then either select criteria from the Connector, Field Expression, Operator, and Value Expression drop-down lists.

Enter the new statement into the SQL box.

Click the Update SQL button.

5. Click the OK button to save the changes and return to the Manage Groups and Sheets window.

Delete a Participant Group

Note Only user-defined groups can be deleted.

1. Select the group in the Manage Groups and Sheets window.

2. Click the Delete UD Group button.

3. Click the Yes button in the Delete Group message box.

Define Criteria

ValTool can customize the set of participants to work on. This is useful in order to:

Define a valuation group other than one of the standard groups.

Modify participant data for a single participant, or for a group of participants other than a defined valuation group.

Retrieve, value, or run the valuation for a single participant, or for a group of participants other than a defined valuation group.

Define Custom Criteria

The Valuation: Custom window is used to describe participant records that are to be worked with.

1. To open the Valuation: Custom window, click or select Utilities | Data | Participant Data | Custom.

If the necessary syntax for defining the criteria is known, the SQL expression can be entered directly in the Define Custom Criteria box.

If the necessary syntax is not known, use the drop-down menus (Connector, Field Expression, Operator, and Value Expression) at the bottom of the window to construct the SQL expression.

2. After the Field Expression, Operator, and Value Expression have been selected, click the Add button.

The SQL expression will appear in the Define Custom Criteria box. Additional changes can be made to the SQL expression directly in the box.

Note If a complex expression is required, select a Connector to combine the separate SQL expressions.

Example:

Select all participant records of female participants who are not Active Non-vested, that is, which do not have C1.

RETSTAT = ‘4' and which have C1.PSEX = ‘F'.

1. Use the drop-down menus to choose:

Custom Criteria Example Table

Drop-down Menu Option

Field Expression = C1.RETSTAT

Operator = <

Value Expression = ‘4'

Note If the required single quote marks are not included, the user will be prompted for them.

2. Click the Add button and use the drop-down menu to choose:

Custom Criteria Example Table

Drop-down Menu Option

Connector = AND

Field Expression = C1.PSEX

Operator = =

Value Expression = 'F'

3. Click the Add button. The SQL expression in the Define Custom Criteria box should read: C1.RETSTAT < ‘4' AND

C1.PSEX = ‘F'.

Clicking the OK button selects all female participants that are not Active Non-vested.

Note For more complex statements that require the use of parentheses, for example, (criterion 1 AND criterion

2) OR criterion 3, enter the parentheses directly into the Define Custom Criteria box. If, after selecting a Field Expression and an Operator, no selections appear in the drop-down menu for Value Expression, type the desired value in the Value Expression box.

The following table lists the valid connectors and operators that can be used to construct SQL expressions in the Define Custom Criteria box.

Valid Connectors and Operations Table

Valid Connectors Valid Operations

AND =

AND NOT <

AND OR >

NOT >=

OR <=

- IN

- NOT IN

- BETWEEN

- NOT BETWEEN

- IS NULL

- IS NOT NULL

Edit Data

Edit Plan Data

1. Choose Utilities | Data | Plan Level Data to open the Data Entry: Plan Level (PL Table) window.

2. Enter or edit plan level data in the PL table.

3. Click on the Check PBGC Required Fields button.

For each required field that is empty, a dialog box will appear in which the user can confirm that the field is permitted to remain empty.

Note A "Yes" response will continue the verification process; a "No" response will move the insertion point to that field.

4. After entering the necessary information, click on the Check PBGC Required Fields button to resume the verification process.

5. Click the Save button to save the modifications, or click the Cancel button to ignore any modifications and return to the Valuation Spreadsheet Tool screen.

Edit User-Defined Field Data

1. Choose Utilities | Data | User Var Description to open the Data Entry: User Variable Labels and Descriptions (UD Table) window.

2. Enter or edit user-defined variable labels and descriptions in the UD table.

Note The default value for the User Variable Label Name and User Variable Description columns is "UNUSED." If the field is used in the worksheet, then the User Variable Description column cannot be blank and must contain a value other than "UNUSED."

3. Click the Validate Description button to find all UD fields used in the spreadsheet that have rows in the UD table with no values or with the value "UNUSED" in their User Variable Description column.

These rows will be highlighted. Update the descriptions as appropriate.

4. Click the Save button to save the modifications, or click the Cancel button to ignore any modifications and return to the Valuation Spreadsheet Tool's main screen.

Enter and Edit Participant Data

1. Choose Utilities | Data | Participant Data.

2. Choose the appropriate type of records to edit:

All to edit all participant records.

Group to edit a pre-defined group of participant records.

The Select Group window appears.

a. Select a group to edit in the Select Group window.

b. Click the OK button.

Custom to edit a customized group of participant records.

The Valuation: Custom window appears.

a. Define the criterion needed to select the custom group

Note For additional help see Defining Criteria.

b. Click the OK button.

3. Select a table to edit in the Data Entry: Participant Data window.

To insert a record:

a. Click the Insert button

b. Enter data.

c. Click the OK button to save changes.

To delete a record:

a. Click the Delete button.

b. Click the Yes button to delete the record from all records.

c. Click the Yes button to confirm the deletion.

To modify key fields (CASE, RETSTAT, ID and CUSNUM) for a selected participant:

a. Click the Modify Key Fields button.

b. Edit the data.

c. Click the OK button to save the changes.

4. Click the Save button to save all changes or click the Cancel button to ignore modifications.

Design the Spreadsheet

Open the Spreadsheet

08/22/2014

1. In the menu bar, select Valuation | Design.

2. MS Excel is launched, and either nnnnnnnnV1.XLSX (if Valuation/BSRS when opening the case is chosen) or nnnnnnnnVE.XLSX (if Benefit Estimation is chosen) is opened.

3. Edit and enter information in the spreadsheet.

4. Find ValTool menu under the Add-Ins tab. Select desired action.

Work in the Spreadsheet

The V1 and VE spreadsheets are basic Excel spreadsheets used to perform any standard Excel operation. Certain features of ValTool require that the format and layout of the spreadsheet are designed in a specific manner.

ValTool Spreadsheet Required Rows Table

Required Row Description

Fields Row This row represents fields which to be read, calculated, or updated. All field names must be listed in the row for which the cell in column A contains the word "Fields." For example, if row 1 contains field names, cell A1 must contain the entry "Fields."

Note Field names may not be used more than once. Fields not listed in the standard tables must have the entry "N" in the IOB row. Key field names (CASE, RETSTAT, ID, and CUSNUM) must be preceded by "C1." (or "E1."), e.g., C1.RETSTAT.

Formulas Row This row represents which formulae will be applied to the data. Column A in this row must contain the word "Formulas". For example, if row 2 contains formulae, cell A2 must contain the entry ‘Formulas'. These formulae will be copied down during a Calculate, Update or Value command for columns labeled O, B, P, or N (see the I/O/B Row section). This row should be below the Fields row in the worksheet.

I/O/B Row This row identifies for ValTool whether to treat the field for that column as an input item or as an output item, or whether it should be ignored entirely. The cell in column A of this row should contain the entry ‘I/O/B'. The values that can be entered in this row are:

I: Input field. Participant data for these fields are read into the spreadsheet from the ACT tables. These cells are then write-protected so that no changes will be made to them while a user working within the spreadsheet. When an Update or Value command is entered, data in these cells are not updated to the ACT tables. Changes to data labeled "I" should be made through the Utilities menu in the ValTool interface.

O: Output field. Participant data for these fields are not read into the spreadsheet from the ACT tables. Rather, they are calculated from the formulae in the Formulas Row being copied down during a Calculate or Value command. Therefore, when data is retrieved from the tables, cells in columns marked with an O will remain empty.

B: Benefit Recalculation field. This column contains data that are treated as output during the valuation process, but during any subsequent benefit recalculation in RunTime will be treated as both input and a value to be calculated. For example, for purposes of the valuation, the field XRD may be treated as a calculated field based upon a variety of factors; however, in RunTime it is treated as an input item by the user.

P: This is the equivalent of a B field for OFA (Optional Forms of Annuity) calculations. OFA calculations are used to determine and store OFA benefits.

Note It is advisable that any column labeled as an O, B or P must have a formula or value in the Formulas row that can be used with the Update or Value commands. If the cell in the Formulas row is left empty, an error may result in your program.

Note To empty the contents of a field through an Update or Value command, a formula may be constructed that sets the value to "NULL", e.g., ="NULL".

N: Null field. Any column that the user wishes to devote to calculations that need not be stored in the ACT tables should be identified as a Null field. Any formula in the Formulas Row of an N column will be copied down during a Calculate command but will not be updated to the ACT tables during an Update or Value command. No field name is required but may be given in the field name row.

Note The cell in column A of this row should contain the entry ‘I/O/B'.

Example:

In this simple example row 1 is designated as the Fields Row - thus, cell A1 contains the entry "Fields". Row 2 is selected to be the Formulas Row, and so cell A2 contains the entry "Formulas." Rows 3 through 7 may be left blank for user documentation, and so Row 8 is chosen to be the I/O/B Row. This requires cell A8 to contain the entry "I/O/B".

When a Retrieve data command is entered, the input data will be read into the spreadsheet beginning with row 10 (two rows below row 8). Row 9 must have "Field Name" in the first column. Initially, the other columns may be left blank in this row.

Select Fields for Use in the Spreadsheet

All participant data must be erased from the worksheet prior to inserting or deleting a column. If there is participant data in the worksheet and a field in the spreadsheet must be added or deleted,

1. Select the Add-Ins tab.

2. Erase the data using ValTool | Erase Data.

Note If inserting a column, make certain that the field name is entered correctly in the Fields Row and the appropriate code is entered in the I/O/B Row.

Note Do not use any range names identical to Excel or ATPBGC functions or those which could be confused with a cell address (for example, a range name N001 will be interpreted as referring to the cell N1).

Return to ValTool

To exit the spreadsheet, go to the Add-Ins tab and select ValTool | Return. This will prevent any errors from being generated when exiting the Excel spreadsheet.

Use MS Excel

Retrieving, Calculating, Updating and Selecting Participant Groups

Note While working within MS Excel, use the ValTool pulldown menu under the Add-ins tab to retrieve, calculate, update, or select participant data.

Retrieve Participant Data

08/22/2014

To retrieve participant data from the ACT tables in the database into the Excel spreadsheet:

Select ValTool | Retrieve Group to retrieve the input data for the selected group sheet or sheets.

The data in the columns marked with an I will be retrieved from the ACT tables and imported into the spreadsheet.

Select ValTool | Retrieve Custom to retrieve a single participant's data or a customized participant group's data and import it into the current sheet.

The Retrieve Custom command opens the Valuation: Custom window, to define the criterion to be used to select the custom group. For more information about defining criteria, see Defining Criteria.

Calculate Participant Data

1. If the input data are not already retrieved into the spreadsheet, retrieve the data into the columns marked with an "I" through the Retrieve command.

2. Use the Calculate command to have the Excel formulae in the Formulas row applied to columns marked with a B, P, N, or O.

The calculated values will then be copied down into the appropriate cells.

Update Participant Data

08/22/2014

To update participant data from the Excel spreadsheet to the ACT tables, use one of the Update or Value commands.

Note The Update commands retrieve, calculate and update participant data as a group.

The Valuation commands retrieve, calculate and update participant data participant by participant.

Choose ValTool | Update to update values for all participants in the current group.

If the active sheet is XRD-enabled (that is, if the sheet accepts XRD data calculations), the Update Custom command opens the XRD Update selection box (see the XRD Update image in the table at the bottom of the topic).

Choose ValTool | Update Custom to update values for a single participant's data or a customized participant group's data.

If the active sheet is XRD-enabled, the Update Custom command opens the XRD Update selection box (see the XRD Update image in the table at the bottom of the topic). Select the fields to be updated and click the OK button to open the Valuation: Custom window. For more about defining criteria, see Defining Criteria.

Choose ValTool | Value Group to update values for all participants in the current group.

If the selected sheet is XRD-enabled (that is, if the sheet accepts XRD data calculations), the command will open the XRD Retrieve selection box.

Choose ValTool | Value Custom to perform valuations for a single participant's data or a customized participant group's data within the current group.

This command opens the Valuation: Custom window. For more about defining criteria, see Defining Criteria. If the selected sheet is XRD-enabled, clicking the OK button in the Valuation: Custom window opens the XRD Retrieve selection box.

XRD Update and Retrieve Menus Table

XRD Update XRD Retrieve

If the group's sheet is XRD-enabled, selecting the Update or Update Custom command will open the XRD Update selection box.

If the group's sheet is XRD-enabled, selecting the Value Group or Value Custom command will open the XRD Retrieve selection box.

The Update or Value commands overwrite any data below the I/O/B row.

Using the valuation formulae in the Formulas row of the spreadsheet, the data for columns marked with a B, P, O, or N are calculated and copied down into the appropriate cells in the spreadsheet.

The fields with columns marked with a B, a P, or an O have the data in the ACT tables updated with the calculated data from the spreadsheet.

Select Participant Data

08/22/2014

1. Select Valuation|Run.

2. Select desired records (For more information, see Enter and Edit Participant Data).

All: All participant records.

Group: A pre-defined group of participant records.

Custom: A customized group of participant records.

Add and Empty Tables

Click a link to find more information about adding C tables or emptying E tables.

Adding C Tables

The command to add C tables reached by choosing Admin |Add Tables is no longer functional.

Emptying E Tables

To delete information from the Estimation tables:

1. In the menu bar on ValTool's main screen, choose Admin | Empty E tables.

A prompt will ask "Are you sure you want to delete all E tables?"

2. Click the Yes button to empty the E tables of their content. (The tables themselves will not be deleted.)

Command Reference

File Commands

08/22/2014

Click a link to view more information about a File command topic. File commands are used to:

Open a case that has ACT tables

The File | Select Case command is used for opening a case for which ACT tables have been previously created.

Procedure

1. Choose File | Select Case. The Select Working Folder dialog box opens.

2. Click the Browse button to select the folder where the case files are stored and click the Continue button.

3. Choose the case by clicking on its plan name or entering its case number in the Case Number box.

4. Indicate whether the work will be done in the Valuation/BSRS spreadsheet or in the Benefit Estimation spreadsheet.

5. Click the OK button to select the case, or the Cancel button to return to the ValTool interface.

Keyboard Shortcuts

CTRL + O

ALT + F, ENTER

Close a case

The File | Close Case command is used for closing the case that is currently being worked on without exiting ValTool.

Procedure

Choose File | Close Case.

Note A prompt will appear asking whether or not to save the changes made in the Excel spreadsheet. After responding, the case is closed within ValTool and the spreadsheet is closed within Excel. However, both ValTool and Excel remain open within Windows.

Keyboard Shortcuts

CTRL + C

ALT + F, C

Create new ACT tables and spreadsheets for a case

The File | Create ACT Tables command is used for creating all the necessary tables and spreadsheets to perform the valuation.

Procedure

1. Choose File | Create ACT Tables to open the Define New Case Number window.

2. Enter the 8-digit case number and the plan name.

By default, all C tables are created.

3. Click the OK button

The system will create a subdirectory that contains the tables, along with an empty valuation spreadsheet (nnnnnnnnV1.XLSX), an empty benefit estimation spreadsheet (nnnnnnnnVE.XLSX), and associated files.

If, after creating the ACT tables, the case is selected, a warning box will appear. It will state, "There are no groups defined in the group table. You must define groups in the group table before performing valuation options."

4. Clicking the OK button clears the warning, but the Valuation drop-down menu will remain inactive until a participant group is defined.

Keyboard Shortcut

ALT + F, A

Remove internal files stored in the Compact Database

The File | Compact Database command is used for compacting the database of the selected group.

Procedure

1. Choose File | Compact Database.

2. In the Compact Database window, select a case from the Select a Case to Compact list.

3. Click the Compact button.

Edit the appearance of the Toolbar

The File | Toolbar command hides, displays, or relocates the toolbar within the ValTool interface.

Procedure

1. Select File | Toolbars.

2. In the Move box, select a location for the toolbar.

3. Click the Hide or Show button to hide or display the toolbar.

4. Check the Show Tips checkbox to display the name of each icon as the cursor moves over it.

5. Click the Done button when finished customizing the toolbar.

ALT + F, T

Exit from ValTool

The File | Exit command is used to close the active case and exits ValTool.

Procedure

Choose File | Exit.

Note If making changes to the Excel spreadsheet, a DRS prompt to save changes appears. After responding, both Excel and ValTool will be exited.

Keyboard Shortcut

CTRL + X

ALT + F, X

Valuation Commands

Click a link to view more information about a Valuation command topic. Valuation commands are used to:

Go to the MS Excel worksheet to design the valuation and benefit estimation worksheets

The Valuation | Design command is used to open MS Excel and allows for work to be performed in the selected (valuation or benefit estimation) worksheet.

Procedure

Choose Valuation | Design.

MS Excel is launched, and either nnnnnnnnV1.XLSX (if Valuation/BSRS is chosen when opening the case) or nnnnnnnnVE.XLSX (if Benefit Estimation is chosen) is opened. Now it is possible to edit and enter information in the spreadsheet.

ALT + V, D

Automatically retrieve, calculate, and update data for a selected group of participants

Valuation | Run | All

The Valuation | Run | All command is used to retrieve and recalculate the data for all participants in the plan, and to write the recalculation results in the ACT tables.

Procedure

Choose Valuation | Run | All.

Data for the input fields will be retrieved into the MS Excel spreadsheet, output fields calculated, and the ACT tables updated on a participant by participant basis.

Keyboard Shortcut

ALT + V, R, A

Valuation | Run | Group

The Valuation | Run | Group command is used to retrieve and recalculate the data for the participants in a specified valuation group, and to update the data in the ACT tables.

Procedure

1. Choose Valuation | Run | Group. The Select Group window opens, displaying the list of valuation groups that were defined for the case.

2. Highlight the group needed.

3. Click the OK button.

Data for the input fields will be retrieved into the Excel spreadsheet, output fields calculated, and the ACT tables updated on a participant by participant basis.

Keyboard Shortcut

ALT + V, R, G

Valuation | Run | Custom

The Valuation | Run | Custom command is used to conduct a valuation of a single participant or a customized group of participants.

Procedure

1. Choose Valuation | Run | Custom.

2. In the Valuation: Custom window, define the selection criterion for the custom group.

This is a logical string that will allow ValTool to select the participants that meet the description and load their data into the spreadsheet.

3. Use the drop-down lists at the bottom of the Valuation: Custom window to build the string, or to enter the string directly into the Define Custom Criteria box.

4. Click the OK button.

Data for the input fields will be retrieved into the MS Excel spreadsheet, the output fields will be calculated, and the ACT tables updated on a participant-by-participant basis.

ALT + V, R, C

Utilities Commands

Click a link to view more information about a Utilities command topic. Utilities commands are used to:

Define a participant group for valuation

The Utilities | Manage Groups command is used to select a pre-defined participant group to value, or define a new participant group.

Procedure

1. Choose Utilities | Manage Groups. The Manage Groups and Sheets window will display the list of available groups and the groups that have been selected for use.

Select a group in the Groups Available for Use box and clicking the Assign for Use button. The group will move to the Groups Assigned for Use box.

Enter the Start Sheet and End Sheet for each group. Click the Check Sheet Sequence button to make sure that the Excel sheet numbers are in the correct sequence

To remove a group from the Groups Assigned for Use list, select it and click the Retire from Use button.

The group will be returned to the Groups Available for Use box.

The Add UD Group button opens the Groups window to enter a new group description and create the SQL statement that ValTool will use to select the appropriate participant data.

Use the Modify UD Group button to modify the description of a user-defined group or to update the SQL statement that appears in the Group SQL box.

Use the Delete UD Group button to delete a user-defined group.

2. Click the Save button to save the changes or click the Close button to exit without saving the changes and return to the Valuation Spreadsheet Tool screen.

ALT + U, G

Edit a defined participant group's characteristics

The Utilities | Data | Plan Level Data command is used to edit the plan-level data contained in the PL table in the case database (nnnnnnnn.ACCDB).

Procedure

1. Choose Utilities | Data | Plan Level Data.

2. Enter or edit plan-level data in the PL table.

3. Verify that all required PBGC fields have been populated by clicking on the Check PBGC Required Fields button.

For each required field that is empty a dialog box will appear in which to confirm that it is appropriate for that field to remain empty.

A Yes response will continue the verification process.

A No response will move the insertion point to that field.

4. After entering the necessary information, click on the Check PBGC Required Fields button to resume the verification process.

5. Click the Save button to save the modifications, or click the Cancel button to ignore any modifications and return to the Valuation Spreadsheet Tool screen.

Keyboard Shortcut

ALT + U, D, L

Add or delete a participant record to the ACT tables

The Utilities | Data | User Var Description command is used to edit the labels for the used-defined variables.

Procedure

1. Choose Utilities | Data | User Var Description.

2. Enter or edit user-defined variable labels and descriptions in the UD table.

The default value for the User Variable Label Names and the User Variable Descriptions columns is "UNUSED."

3. Click the Validate Descriptions button to find all UD fields used in the worksheet that have rows in the UD table with no values or with the value "UNUSED" in their User Variable Description column.

4. Update the descriptions as appropriate.

5. Click the Save button to save the modifications, or click the Cancel button to exit without saving the modifications and return to the Valuation Spreadsheet Tool screen.

Keyboard Shortcut

ALT + U, D, U

Edit a participant's data within the ACT tables

Utilities | Data | Participant Data | All

The Utilities | Data | Participant Data | All command is used to edit data in all participant records in the plan.

Procedure

1. Choose Utilities | Data | Participant Data | All.

2. Select the table that requires edits by clicking on the appropriate tab at the top of the screen.

Data can be entered/edited directly within the table:

To insert a record, click on the Insert button in the lower left of the window.

A prompt will appear requesting data for the key fields.

To delete a record from all tables, click on the Delete button in the lower left of the window.

To modify one or more of the key fields in the record (CASE, RETSTAT, ID, or CUSNUM), click the Modify Key Fields button at the bottom of the window.

The Modify Key Fields window will give edit access to these fields.

3. Click the Save button to save the modifications, or click the Cancel button to ignore any modifications and return to the Valuation Spreadsheet Tool screen.

Keyboard Shortcut

ALT + U, D, P, A

Utilities | Data | Participant Data | Group

The Utilities | Data | Participant Data | Group command is used to edit data in the records for a pre-defined group of participants.

Procedure

1. Choose Utilities | Data | Participant Data | Group.

2. Select the group that requires edits, and click the OK button.

3. In the Data Entry: Participant Data (C1-C7, C9-C12 Tables) window, select the table that requires edits by clicking on the appropriate tab at the top of the window.

Data can be entered/edited directly within the table.

To insert a record, click on the Insert button in the lower left of the window.

A prompt will appear requesting the data for the key fields.

To delete a record from all tables, click on the Delete button in the lower left of the window.

To modify one or more of the key fields in the record (CASE, RETSTAT, ID, or CUSNUM), click the Modify Key Fields button at the bottom of the window.

The Modify Key Fields window will give edit access to these fields.

4. Click the Save button to save the modifications, or click the Cancel button to ignore any modifications and return to the Valuation Spreadsheet Tool screen.

ALT + U, D, P, G

Utilities | Data | Participant Data | Custom

The Utilities | Data | Participant Data | Custom command is used to edit the records of a customized group of participants different from the defined participant groups.

Procedure

1. Choose Utilities | Data | Participant Data | Custom.

2. Define the criterion for the selected custom group. (For additional help, see Defining Criteria).

3. Click the OK button.

4. Select the table that requires edits by clicking on the appropriate tab at the top of the screen.

Data can be entered/edited directly within the table.

To insert a record, click on the Insert button in the lower left of the window.

A prompt will ask for the key fields.

To delete a record from all tables, click on the Delete button in the lower left of the window.

To modify one or more of the key fields in the record (CASE, RETSTAT, ID, or CUSNUM), click the Modify Key Fields button at the bottom of the window.

The Modify Key Fields window will give edit access to these fields.

5. Click the Save button to save the modifications, or click the Cancel button to ignore any modifications and return to the Valuation Spreadsheet Tool screen.

ALT + U, D, P, C

Admin Commands

Click a link to view more information about an Admin command topic. Admin commands are used to:

Empty a case's E tables

The Admin | Empty E Tables command is used to delete participant data from the E tables of the selected case.

Procedure

1. Select Admin | Empty E Tables.

2. A prompt will ask "Are you sure you want to delete all E tables?"

3. Click the Yes button to empty the E tables of their content. (The tables themselves will not be deleted.)

ValTool Commands

Click a link to view more information about a ValTool command topic. ValTool commands are used to:

Retrieve input data for groups of participants

The ValTool | Retrieve Group command is used to retrieve the input data from the ACT tables for the group that belongs to the current sheet.

In the Add-Ins tab, choose ValTool | Retrieve Group.

Apply valuation formulae to the input data

The ValTool | Calculate command is used to retrieve values for the input fields and calculate values for the output fields in the current worksheet, using the valuation formulae in the Formulas line of the spreadsheet. This command does NOT update the ACT tables.

In the Add-Ins tab, choose ValTool | Calculate.

Update the output fields in the ACT tables

ValTool | Update

The ValTool | Update command is used to update the ACT tables with the data in the spreadsheet. Data for the input fields for all participants in the current group is automatically retrieved from the ACT tables and the output fields calculated. Then, the ACT tables are updated with the values in the output fields.

1. In the Add-Ins tab, choose ValTool | Update.

2. If the active spreadsheet is XRD-enabled, select the fields to be updated from the XRD Update selection box.

ValTool | Retrieve Custom

The ValTool | Retrieve Custom command is used to retrieve a single participant's data or a customized participant subgroup's data from the ACT tables into the current sheet.

Procedure

1. In the Add-Ins tab, choose ValTool | Retrieve Custom.

2. Use the Valuation: Custom window to define the criterion for the custom group. (For additional help, see Defining Criteria.)

ValTool | Update Custom

The ValTool | Update Custom command is used to retrieve, calculate, and update a customized participant subgroup's data.

Procedure

1. In the Add-Ins tab, choose ValTool | Update Custom.

2. If the active spreadsheet is XRD-enabled.

3. Select the fields to be updated from the XRD Update selection box.

4. In the Valuation: Custom window, define the criterion for the custom group. (For additional help, see Defining Criteria.)

ValTool | Value Group

The ValTool | Value Group command is used to update the data for the current group of participants. Data for the input fields for all participants in the current group is automatically retrieved from the ACT tables and the output fields calculated. Then, the ACT tables are updated with the values in the output fields. This process is performed participant by participant.

Procedure

1. In the Add-Ins tab, choose ValTool | Value Group.

2. If the active spreadsheet is XRD-enabled, select the fields to be updated from the XRD Retrieve selection box.

ValTool | Value Custom

The ValTool | Value Custom command is used to update the data for a single participant or for a customized group of participants. Data for the input fields for the selected participants is automatically retrieved from the ACT tables and the output fields calculated. Then, the ACT tables are updated with the values in the output fields. This process is performed participant by participant.

Procedure

1. In the Add-Ins tab, choose ValTool | Value Custom.

2. If the active spreadsheet is XRD-enabled

3. Select the fields to be updated from the XRD Retrieve selection box.

4. In the Valuation: Custom window, define the criterion for the custom group. (For additional help, see Defining Criteria.)

Retrieve a single participant's data or a customized participant group's data

The ValTool | Retrieve Custom command is used to retrieve a single participant's data or a customized participant subgroup's data from the ACT tables into the current sheet.

Procedure

1. In the Add-Ins tab, choose ValTool | Retrieve Custom.

2. Define the criterion for the selected the custom group (For additional help, see Defining Criteria).

3. Click the OK button.

Erase all participant data within the MS Excel spreadsheet

The ValTool | Erase Data command is used to erase participant data from the spreadsheet.

Procedure

In the Add-Ins tab, choose ValTool | Erase Data.

This action clears all participant data (both input and calculated) from the current worksheet. The Erase Data command does not erase data from the ACT tables - it only erases participant data from the current group within the nnnnnnnnV1.XLSX or nnnnnnnnVE.XLSX spreadsheets.

Return to the ValTool interface

The ValTool | Return command is used to leave the MS Excel spreadsheet and return to the Valuation Spreadsheet Tool interface.

Procedure

In the Add-Ins tab, choose ValTool | Return.

This command reopens the Valuation Spreadsheet Tool interface. It does not close the spreadsheet or MS

Excel.

Overview

The DOPT Report System (DRS) customizes valuation data reports, including:

Selecting a valuation participant group or other customized group.

Specifying reporting fields.

Formatting the data's appearance.

Updating the UD table's user-defined labels.

Generating reports listing all participants.

The wizard-style interface prompts a series of choices to determine the report's case, participant group(s), and the included fields - as well as their format and labeling.

The application allows viewing and printing reports directly while storing the report settings in the case's SI and RI tables. DRS also allows saving the report in several file formats, including CSV, Text, Excel (XLS), and PDF.

Select a Case

Open a Case

06/27/2014

1. As the program launches, DRS displays the Select Working Folder dialog box to input or navigate to the appropriate file location.

2. The ACTWork folder is the default working folder; for another folder, click the Browse button to search for that location.

Chapter 3

DOPT Report System (DRS)

3. Click the Continue button. The system will display the Case Selection window with all the plan names for the cases that have ACT tables in the location selected in Step 2.

4. Click on the plan name for the desired case; or type the case number in the Case Number box.

5. Click the Next button to select the report's participant group.

Click the Case Settings button to change the following options:

Include Substantial Owners in Non-SO Reports.

Automatically include spouse name fields for beneficiary groups.

Include Substantial Owner or Beneficiary Information in a Report

1. Click the Case Settings button in the Case Selection window.

2. In the Select Fields to Include box, check or uncheck the Substantial Owners in Non-SO Reports and Spouse Name Fields for Beneficiary Groups checkboxes to enable or disable these settings.

3. Select (check or uncheck) either box to enable the Next button; click Next to proceed with changes or the Back button to continue without changing the options.

Select a Participant Group

Open a Participant Group

06/27/2014

In the Case Selection window, click the Next button to open the Group Choice window. The top half of the window displays all the standard participant groups while the bottom half displays any user-defined groups created within the DOPT Report System (DRS).

The Select a Group list of standard…