Manage Variable Benefit Rates in the Integrated Workbook

You can create and upload variable rates by defining variable rate profiles and associating them with standard rates using the integrated Microsoft Excel workbook.

The basic process for managing variable rates using the workbook is:

Generate the workbook.
Create and edit variable rate profiles.
Upload edits.
Add variable rate profiles to standard rates.
Upload edits.
Resolve errors.

Repeat these steps as many times as required.

Prerequisites

Before you upload new or edited variable rate profiles, confirm that the following already exist in the application database:

Any associated benefit eligibility profiles, plans, and options
Any referenced compensation user-defined factors where Calculation Method is set to Multiple of compensation
Any referenced fast formulas associated with variable rate profiles where Calculation Method is set to Calculate for enrollment formula

Generating the Workbook

In the Plan Configuration work area:

In the Tasks panel drawer, click Manage Benefit Rates to open the Manage Benefit Rates page.
On the Search Results section toolbar of the Variable Rate Profiles tab, click Prepare in Workbook.

Creating and Editing Variable Rate Profiles and Adding them to Standard Rates

Use the two worksheets in the workbook to:

Create and edit your variable rate profiles in the variable rate profiles worksheet.
Associate existing and newly uploaded variable rate profiles with standard rates in the standard Rates worksheet.

On both worksheets:

Select a calculation method. The calculation method for all rows must match the calculation method for the workbook, which you selected in the Search section. Mismatches result in errors when you upload your data.
Optionally, enter an effective as-of date. The download process uses the date as a constraint when downloading either the variable rate profiles or the standard rates. The upload process uses it to set the effective date for the new and edited profiles and edited standard rates. If you leave this field blank, the upload process sets the current date, also known as the system date, as the effective date.
Search for either the variable rate profiles or standard rates that match your criteria. The Search button and other integrated workbook buttons, such as Upload, Delete, and End-Date are available in the Upload Variable Rate Profiles tab.
Depending on the worksheet, either:
- Create and edit the profiles, as required.
- Associate the profiles with the relevant standard rates.
Upload your edits to the application database.

The variable rate profiles must already exist in the application database before you can associate them with standard rates in the workbook. Upload any new or edited variable rate profiles and associate them with standard rates.

Some of the cells in the spreadsheet are purposefully locked. You shouldn’t unprotect the spreadsheet and load values in such cells. The application doesn't save such values.

Note:

You must associate future-dated profiles with standard rates in the application; you can't do it in the workbook.

Uploading Edits

After you complete your edits for one of the worksheets, in the Upload Variable Rate Profiles tab, click Upload to load into the application tables those rows that are marked as Changed.

Randomly test that the upload worked as you expected by searching for one or more of the following in the application:

New or edited variable rate profiles
Standard rates with which you associated a variable rate profile

The upload process updates the worksheet Status field only if the server or database becomes inaccessible during upload.

Resolving Errors

The upload process automatically updates the Status field in each workbook row. If there are errors that require review, the process:

Rolls back the change in the application database
Sets the workbook row status to Upload Failed
Continues to the next workbook row

To view and resolve an error:

Double-click Update Failed in the Status field.
Fix any data issues in the workbook.
Upload the latest changes.