ProVal’s Report Writer lets you create valuation reports, executive summaries, disclosure letters, etc., by merging ProVal results and other supplemental data into a Template Document prepared in Microsoft Word.  The steps below outline the basic process.

1. (Optional) If this is first time that you are using the Report Writer for this client and a sample Access database (*.mdb, *.accdb) has been prepared by someone in your organization, save a copy of this sample database into the same folder as the ProVal client files (*.sf).

2. Save the desired ProVal results to a Microsoft Access database (*.mdb, *.accdb).

   • In ProVal, view results for Valuation Sets Exhibits, Deterministic Forecasts Exhibits, Gain/loss Analysis, or Descriptive Statistics.

   • Click the File button

   • Type in a filename and choose Access database (*.mdb, *.accdb) as the file type

   • Click the Save button

   • It is recommended that you store the Access database (*.mdb, *.accdb) in the same folder as the ProVal client files (*.sf). All of the results for a client should be saved into a single database. This includes results for prior years, different scenarios, and even different plans (e.g., hourly pension, salaried pension, retiree medical). However, it is recommended that different clients’ results be saved in different databases.

3. Start the Report Writer and open the Microsoft Access Database you created in step 2.

   • From ProVal’s Tools menu, select Report Writer.

   • From the Report Writer’s File menu, select Open, select the database, and click Open.

4. (Optional) Manage the ProVal results contained in the Microsoft Access Database.

   • From the Report Writer’s Edit menu, select Database Maintenance. Alternatively, click the Database button on the toolbar. For more information, see Managing Results Stored in the Access Database below.

5. Edit a (or create a new) Report Definition.

   • For more information, see Setting up a Report Definition below.

6. Generate a report

   • Click the Populate button to check that all fields required by the Template Document exist in the ProVal Data Sets. After you click Populate, a message will be returned that either indicates all fields exist in the database or it will list the ProVal Data Sets missing as shown below.



• Click the Save & Publish button.

The end result is a new Microsoft Word document, generated by merging the data stored in the Microsoft Access Database into the Template Document (the original template remains untouched). If you are happy with the new document, you can save it, print it, etc. using Microsoft Word. You should refrain from editing the published document since changes will have to be redone if you publish the document again. Instead, changes should be made in ProVal, the Report Writer, or in some cases, the Template Document. (Template Documents are generally authored by a single person in your organization. If this is your responsibility, see Report Writer Templates.)

 

Setting up a Report Definition

A Report Definition lets you generate a report based on the inputs you define.

• General is the section where you define the basic elements required by the report writer.

  • Template Document is where you define the document(s) that will be merged with ProVal data.

  • Template Document is a document that has been prepared in Microsoft Word (*.doc, *.docx). Specify (or browse to) the Template Document you want to merge data into (a new document will be created, leaving the template untouched). The Template Document should contain placeholders (fields and bookmarks) that data will be merged into, rather than actual data. A previously generated report should not be used as a Template Document. (Template Documents are generally authored by a single person in your organization. If this is your responsibility, see Report Writer Templates.)

The buttons for Bookmarks, Form Fields, and Graphs are tools used in authoring a Template Document and can otherwise be ignored. (If this is your responsibility, see Report Writer Templates.)

• Sections to Include is a list of each section contained within the Template Document. Uncheck the corresponding box for any section you wish to omit from the final published report. A section is created in the Template Document by using Word Bookmarks that start with the prefix “RS_”.

• Text Substitutions let you incorporate content from other documents. For example, you might incorporate the entire contents of “plan provisions.doc”, replacing the Bookmark (and any content it encompasses) “TS_Plan_Provisions” in the template. Or, you might incorporate just the “Projected_Unit_Credit” portion (marked by a bookmark) of the document “cost methods.doc”, replacing the Bookmark “TS_CostMethod” in the template. (The Template Document should mark portions of the report which are eligible for text substitutions with Word Bookmarks starting with the prefix “TS_”.)

To define how a TS_xxx bookmark should be handled, select a row in the list and then click the Edit button (or just double-click the row). Your choices are:

1. Delete the bookmark and whatever content it encompassed;

2. Leave the bookmark and whatever content it encompassed alone;

3. Replace the content with content from another document. Here you may choose to use the entire contents of the document, or just the content encompassed by a bookmark within that document.

• ProVal Data Sets is where you specify the ProVal results that will be merged into the Template Document.

• Labels and Data Sets. Provide a label for each set of ProVal results (e.g., Valuation Sets, Deterministic Forecasts, Gain/loss Analysis and Descriptive Statistics) saved in the access database that will be used in your report, e.g., “Current Year”, “Prior Year 1”, “Prior Year 2”, “Baseline”, “Salary Distribution”, etc. These labels must be consistent with those found in Form Fields in the Template Document. As an example, the name of your Valuation Set with the final results that you wish to publish might have been “2011 Valuation with plan amendment”. The template document may have been set to call the final results from the valuation set with the label “Current Year”. Therefore, you would assign the label “Current Year” to the Valuation Set “2011 Valuation with plan amendment”.

• The Group button specifies the relationship between current year and prior year valuation sets (so the report writer can derive additional values such as change in minimum contribution). If you have multiple valuation sets for a single year (e.g., for Hourly and Salaried plans), then you’ll need to define Groups. For example, you might have group 1 for Salaried and group 2 for Hourly, each with a current year valuation set and a prior year valuation set.

• User-Defined Data lets you enter supplemental data that isn’t found in ProVal Data Sets or Asset Data. Enter Values for each data item. (Setting up the user defined data items is generally the responsibility of the Template Document author. If this is your responsibility, see Report Writer Templates.)

• Asset Data has 3 separate sections that allow you to enter detailed asset information to be incorporated into the final published report. (Setting up the asset data items is generally the responsibility of the Template Document author. If this is your responsibility, see Report Writer Templates.)

  • Statement of Assets lets you enter a breakdown of the plan’s assets by asset class.

  • Reconciliation allows you to enter the cash flow to summarize the change in assets from the prior valuation date to the current valuation date.

  • Returns allows you to enter historical returns for the plan’s market value of assets, actuarial value of assets, and any number of asset classes (e.g., “Equities”, “Fixed Income”, “Other”, etc.).

• Valuation Sets allows you to review the results of the Valuation Sets available to be merged into the report. Only results for Valuation Sets that you have selected as part of the report definition are displayed (i.e., Valuation Sets that were assigned a label under the Template Document > ProVal Data Sets section).

  • Exhibits allow you to view the valuation set exhibits.

  • Output Variables allows you to view the information available in ProVal under Output > Valuation Sets. The variables are sorted alphabetically. You can also compare valuation set results from the most recent two years. This is a powerful way to catch mistakes before they get into the report. You will also see derived values that are not found in ProVal (e.g., contribution amount divided by headcount).

  • Headcount & Benefits allows you to view the headcount and projected benefits for the selected basis (e.g., PVB, PBO, etc.).

  • Active Age/Service allows you to view the active age/service scatter generated by the valuation set.

  • Inactive Count/Benefits allows you to view the headcount and benefits inforce for participants inactive on the valuation date.

  • Assumptions allows you to view all of the assumptions underlying each valuation set.

  • Assumption Tables allows you to view any table referred to in Assumptions (e.g., the retirement decrement table, salary merit scale table, cost of living adjustments table, etc.).

• Deterministic Forecasts allows you to review the results of the Deterministic Forecasts available to be merged into the report. Only results for Deterministic Forecasts you’ve selected as part of the Report Definition are displayed (e.g., Deterministic Forecasts that were assigned a label under the Template Document > ProVal Data Sets section).

  • Exhibits allow you to view the deterministic forecast exhibits.

  • Output Variables allows you to view the information available in ProVal under Output > Deterministic Forecast. The variables are sorted alphabetically. You can also compare a Deterministic Forecast to a corresponding Valuation Set, e.g., to verify that the first year of the forecast matches the valuation set.

• Gain/Loss Analysis allows you to view the results of the Gain/Loss Analysis Tool available to be merged into the report. Only results for Gain/Loss Analysis you’ve selected as part of the Report Definition are displayed (e.g., Gain/Loss Analysis that were assigned a label under the Template Document > ProVal Data Sets section).

  • Exhibits allow you to view the exhibits for each Gain/Loss Analysis.

• Descriptive Statistics allows you to view the results of the Descriptive Statistics available to be merged into the report. Only Descriptive Statistics you’ve selected as part of the Report Definition are displayed (e.g., Descriptive Statistics that were assigned a label under the Template Document > ProVal Data Sets section).

  • Exhibits allow you to view the results of the Descriptive Statistics.

 

Managing Results Stored in the Access Database

To manage results, from the Edit menu, select Database Maintenance. Alternatively, click the Database button on the toolbar. This allows you to:

• Create a New (blank) Valuation Set data set for which you can hand-enter results. This is useful for entering historical results where ProVal runs are not available (e.g., results from a prior actuary).

• Edit a Data Set to specify whether Overrides should be allowed for this data set. If Overrides are allowed this screen will also display the current number of overrides. By unchecking the allow overrides box, you will be disabling the override feature for this data set and deleting any existing overrides, essentially resetting the data set to its initial state. The Override feature is not allowed for Descriptive Statistics data sets.

Caution: Overrides are only recommended for new (blank) data sets (e.g., a prior year’s results that were not run in ProVal). Overriding ProVal results should only be used as a last resort. Instead of overriding ProVal results in the Report Writer, you should go back to ProVal, calculate the desired results (e.g., with Scaling Factors), and save them into the Microsoft Access Database. If, for some reason, you must override ProVal results, use caution as dependent values that ProVal calculated will not be recalculated by the Report Writer. Any result that has an override present will have a little red triangle in the cell to denote that an override is present. (Note that the Report Writer’s derived values which are not found in ProVal will be updated based upon overrides but only after the overrides are Saved to the database.) If you place your cursor over the cell with the override, the original value will be displayed in a text box. To restore the original value, right click on the cell with the override and select the option to restore original value from the pop-up menu.

• Rename a Data Set before using it in a Report Definition. This is useful if you want to improve or fine tune names of previously defined objects (e.g., “Val Results 1/1/2008” instead of “Val Results”).

• Erase a Data Set.

• Import results from another Microsoft Access Database with ProVal results. You might use this, for example, to import a previous year’s results from another database. Once you have selected the database file to be copied from, check the Data Set(s) you wish to copy and hit the Import button. If the data set to be imported has a duplicate name to one in the current database, you will be prompted for a new name for the Data Set.

 

Derived Data Fields Dependent on User Defined Data

ProVal will derive certain values based on data input into User Defined Data fields. Each user defined data field must be named exactly as shown below and be contained within a category “Derived Data Inputs”. If a field is missing, then any calculations dependent on that field will not appear. Note that some of the calculations require a current and prior year Valuation Set. In this case, if one of the required Valuation Sets is missing, the derived field will not appear.

Calculation Details

The formulas for each of the derived data fields dependent on User Defined Data are specified below. For values in the formula generated by ProVal, the exhibit and field name are shown. User defined data items are in italics.

Fields shown on the Development of Actuarial Assets exhibit:

• Expected actuarial value of assets =

Actuarial Assets, Prior Year 1 value (Val Set Data)
+ Total ContAmt excluding CB, Prior Year 1 value (Schedule of Employer Contribution)
+ Expected Return on Funding Assets (in dollars)
+ Actual employee contributions for prior year (funding)
+ Actual asset transfers for prior year (funding)
- Actual benefit payments for prior year (funding)
- Actual administrative expenses for prior year (funding)

• Expected market (funding) value of assets =

Market Assets (funding), Prior Year (Val Set Data)
+ Total ContAmt excluding CB, Prior Year 1 value (Schedule of Employer Contribution)
+ Expected Return on Funding Assets (in dollars)
+ Employee contributions for prior year (funding)
+ Actual asset transfers for prior year (funding)
- Actual benefit payments for prior year (funding)
- Actual administrative expenses for prior year (funding)

Fields shown on the Development of Market-Related Assets exhibit:

• Sum of Prior Year Actual Benefit Payments & Admin Expenses (accounting) =

Actual administrative expenses for prior year (accounting)
+ Benefits Paid (Reconciliation of Funded Status / Balance Sheet)

• Expected Market Related Value Based on Act. Cashflow =

Market Related Assets, last year (Val Set Data)
+ Prior year contributions (Reconciliation of Funded Status/Balance Sheet, )
+ Plan participant contributions (Reconciliation of Funded Status/Balance Sheet, )
+ Expected return on assets, Prior Year 1(Development of Expense)
+ Actual asset transfers for prior year (accounting)
- Benefits paid (Reconciliation of Funded Status/Balance Sheet)
- Actual administrative expenses for prior year (accounting)

• Expected Market Value Based on Act. Cashflow =

Market Assets (acctg), last year (Val Set Data)
+ Prior year contributions (Reconciliation of Funded Status/Balance Sheet, )
+ Plan participant contributions (Reconciliation of Funded Status/Balance Sheet, )
+ Expected return on assets, Prior Year (Development of Expense)
+ Actual asset transfers for prior year (accounting)
- Benefits paid (Reconciliation of Funded Status/Balance Sheet)
- Actual administrative expenses for prior year (accounting)

• Total Prior Year Asset Income incl. Contribs =

+ Plan participant contributions (Reconciliation of Funded Status/Balance Sheet)
+ Investment earnings for prior year (accounting)
+ Prior year contributions (Reconciliation of Funded Status/Balance Sheet)

Fields shown on the Summary of Minimum Contribution Limits exhibit:

• Minimum Contribution with Interest to Payment Date =

Minimum contribution at beginning of year (Summary of Minimum Contribution Limits)
x (1 + Effective Interest Rate)^[(Funding Date - Valuation Date) / 365]
Effective Interest Rate & Valuation Date are from ProVal Val Set Data

Fields shown on the Development of PBGC Premium exhibit:

• Total PBGC premium - Amt Paid with Form 1-ES =

PBGC premium (Development of PBGC Premium)
- Amount Paid on PBGC Form 1-ES for current year

• Total PBGC Premium - (Amt Paid with Form 1-ES + Other Credits) =

PBGC premium (Development of PBGC Premium)
- Amount Paid on PBGC Form 1-ES for current year
- Other PBGC credits for current year

Fields shown on the Development of Credit Balances exhibit:

• Investment Return on Prior Year Prefund. Balance =

PFB before investment return, Prior Year 1 (Development of Credit Balance)
x (1 + Actual rate of return on assets (funding))

• Investment Return on Prior Year Carryover. Balance =

FSCB before investment return, Prior Year 1 (Development of Credit Balance)
x (1 + Actual rate of return on assets (funding))

Fields shown on the Schedule of Employer Contributions exhibit for the prior year:

• Tot of ER Contribs for Plan Year Made During Plan Year + Actual EE contribs=

Contribs for plan year made during plan year, Prior Year 1 (Schedule of Employer Contributions)
+ Actual employee contributions for prior year (funding)

Fields shown in Valuation Set Data:

• Market Value of Assets Less Discounted Contribution Receivables=

Market Value (Funding) (Val Set Data)
- Discounted contribution receivables included in MVA (funding)