User Guide for Defense Civilian Intelligence Personnel System

UNCLASSIFIED

ser Guide for

Defense Civilian Intelligence Personnel System (DCIPS)

Payout Analysis Tool (DPAT)

2018

UNCLASSIFIED DCIPS Payout Analysis Tool – 2018

Page i

DCIPS Payout Analysis Tool (DPAT) User Guide – 2018

About this Guide ................................................................................................................................ 1

What is the DCIPS Payout Analysis Tool? .............................................................................................................. 1

Who Should Use this Guide? ..................................................................................................................................... 1

How is the Guide Structured? .................................................................................................................................... 1

Relationship to Other Applications ................................................................................................. 2

When Do I Use the DPAT? ....................................................................................................................................... 2

Using the DPAT ................................................................................................................................. 3

Overview ....................................................................................................................................................................... 3

Where do I get the DPAT? ........................................................................................................................................ 7

Opening the DPAT and Enabling Macros (Excel 2007 and 2010) ...................................................................... 7

Compatibility Checker ............................................................................................................................................. 9

Instructions Worksheet ............................................................................................................................................. 10

Importing Data into the DPAT ............................................................................................................................... 11

Customize the Data (Optional) ................................................................................................................................ 13

Data Worksheet .......................................................................................................................................................... 15

Using the Wildcard Columns and Generating Custom Stats and Charts ...................................................... 15

Rating Statistics Worksheet ...................................................................................................................................... 18

Rating Charts Worksheet .......................................................................................................................................... 19

Rating Distribution by Group Worksheet.............................................................................................................. 20

Payout Statistics Worksheet ..................................................................................................................................... 21

Payout Charts Worksheet ......................................................................................................................................... 22

Salary Increase Statistics by Group Worksheet (NGA ONLY) .......................................................................... 23

Salary Increase Charts by Group Worksheet (NGA ONLY) ............................................................................. 24

Salary-Bonus Charts by Wildcard Worksheet ........................................................................................................ 25

Salary Increase Charts Worksheet (NGA ONLY) ................................................................................................ 26

Total Salary Increase Charts Worksheet (NGA ONLY) ..................................................................................... 27

Bonus Statistics by Group Worksheet .................................................................................................................... 28

Bonus Charts by Group Worksheet ........................................................................................................................ 29

Bonus Charts Worksheet .......................................................................................................................................... 30

Funding Statistics Worksheet ................................................................................................................................... 32

Funding Charts Worksheet ....................................................................................................................................... 33

Net Draw Analysis Worksheet (NGA ONLY) ..................................................................................................... 34

Net Draw Charts Worksheet (NGA ONLY) ........................................................................................................ 35

Capturing DPAT Charts and Exporting to PowerPoint or Excel ...................................................................... 36

Helpful Hints .................................................................................................................................... 37

Copying and Pasting Data from Worksheets......................................................................................................... 37

APPENDIX 1: Definitions ............................................................................................................. 38

APPENDIX 2: Using the DPAT with Excel 2003 ..................................................................... 39

Enabling Macros .................................................................................................................................................... 39

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page ii UNCLASSIFIED 2018

This page intentionally left blank.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 1

About this Guide

What is the DCIPS Payout Analysis Tool?

The DCIPS Payout Analysis Tool (DPAT) is a spreadsheet application that organizations may use to analyze

the results of their DCIPS performance evaluation and pay pool processes. The tool imports data directly

from your organization’s Human Resources Management System (HRMS) or from one or more

Compensation Workbench (CWB) spreadsheets and automatically generates a variety of statistics, including:

• Rating distributions;

• Salary increase (NGA Only) and bonus statistics and charts;

• Pay pool funding and allocations; and

• Net Draw (NGA Only)

Who Should Use this Guide?

This guide is for pay pool administrators, pay pool managers, pay pool panel members, and pay pool review

authorities who are responsible for compiling, reporting, or presenting results of their organization’s pay

pool process. It is also intended for HR practitioners who support DCIPS organizations.

How is the Guide Structured?

This guide is structured as follows:

• Relationship with Other Applications and Tools – this section describes how DPAT relates with

DCPDS/PeopleSoft and the Compensation Workbench (CWB).

• Using the DPAT – this section walks you through the features of the application and how to use the

tool.

• Using the DPAT in Excel 2003 – this section addresses some of the functionality and interface

differences the user encounters when using Excel 2003 versus Excel 2007 and later.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 2 UNCLASSIFIED 2018

Relationship to Other Applications

NEW: There are no enhancements in the relationships to other applications and tools in 2016.

Each Compensation Workbench (CWB) spreadsheet is populated with personnel data (e.g., names, pay

bands, base salary) that is extracted from your organization’s Human Resources Management System

(HRMS) – either DCPDS or PeopleSoft. The data is extracted based on each employee’s pay pool identifier,

which links employees to their pay pool. In addition to the personnel data, performance rating information

is also included in the data extract.

The CWB is used by individual pay pools to determine salary increase (NGA ONLY) and bonus amounts

for each employee in the pool. An additional tool, called the DCIPS Payout Analysis Tool (DPAT),

provides the capability to analyze results across multiple pay pools.

DCIPS Pay Pool Support Tools

When Do I Use the DPAT?

The DPAT primarily is intended to assist pay pools and organizations review and analyze performance

salary increase and bonus data calculated in the CWB. Because the tool accepts data in the format of actual

CWB spreadsheets (.xls files) or the upload export files generated by the CWB, the DPAT can be used both

during and after the pay pool process to analyze results. The DPAT can analyze the results of a single pay

pool or across multiple pay pools. For that reason, the DPAT is particularly useful during the Pay Pool PRA

review process.

Reviewing Officials and Performance Management PRAs can also use the DPAT to review ratings data,

including element and objective ratings, directly from the online Performance Management Support Tool

(e.g., PAA, ezHR, ePerformance, etc.). In this situation, the DPAT can accept one or more extract files from

the HRMS (either DCPDS or PeopleSoft), which is the same file format that the CWB uses. Note that when

the DPAT imports directly from the HRMS, the DPAT can only analyze rating data because payout data

has not yet been calculated and is not included in the extract.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 3

Using the DPAT

Overview

NEW: There are no new features in the custom toolbar in2018.

ALWAYS save the DPAT in compatibility 97-2003 (.xls) format. Simply clicking the Save icon in the

upper left corner of the screen will accomplish this. You also may save the tool as a .xlsm (Macro-Enabled

Worksheet) file; however, most DoD computer system configurations do not allow these files to be sent via

email or to be opened with macros enabled. Check with your local system administrator before attempting

to save the tool as a .xlsm file.

NEVER save the DPAT in 2007 or later (.xlsx) format. Doing so will cause irreparable harm to your

spreadsheet and you will have to start over with a blank DPAT.

The DPAT contains 19 worksheets and numerous macros that provide the application with advanced

functionality. Each worksheet is described in detail in this guide. A custom toolbar appears at the top of

each worksheet. The toolbar is made up of custom buttons that match the worksheet’s specific

functionality. To access the toolbar when using Excel 2007 or later, click the Add-Ins tab in the Ribbon.

This brings up the custom toolbar as in the image below. You may have to click the Right-facing arrow on

the right side of the toolbar to see all of the buttons.

Toolbar Item

Description

Import

Loads a data file from HRMS or one or more CWB files into the workbook.

This button is active on all worksheets.

Hide Column

Hides any column(s) from view. Single columns are selected from any cell in the

column. Multiple columns are selected by holding down the <Ctrl> key while

selecting any cells in the columns. Selecting and dragging across any row of cells in the

range of columns hide a range of columns. The first two columns (A and B) in the

Data worksheet cannot be hidden. This button is active on the Data worksheet only.

Unhide Column

Unhides columns you have just hidden as long as you have not moved the cursor. Also users

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 4 UNCLASSIFIED 2018

Toolbar Item

Description

can unhide a specific column or range of columns by highlighting cells in the columns

on either side of the hidden column or range of columns, and then selecting this

button.

This button is active on the Data worksheet only.

Unhide All Columns

Restores the view to show all columns.

This button is active on the Data worksheet only.

Hide Row

Users may hide rows from view by selecting any cell in the row or rows to be hidden

and then hitting this button. Select any cell in a single row and then select this button.

Multiple rows are selected by holding down the <Ctrl> key while highlighting any

cells in the rows. A range of rows is selected from any column of cells, then dragging

them up or down. Rows 1 through 3 in the Data worksheet cannot be hidden.

This button is active on the Data worksheet only.

Unhide Row

Unhides rows you have just hidden as long as you have not moved the cursor. Also users can

unhide a specific row or range of rows by highlighting cells in the rows on either side

of the hidden rows or range of rows, and then selecting this button.

This button is active on the Data worksheet only.

Unhide All Rows

Restores the view to show all hidden rows in the Data worksheet. Pressing Unhide

All causes a warning notice to pop up as a reminder that all rows will be revealed.

Press OK to proceed.

This button is active on the Data worksheet only.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 5

Toolbar Item

Description

Clear All Filters

Each column heading contains a filter arrow for the column. Clicking on the filter

arrow brings up a list of all of the values in the column, plus the following other

choices: Select All, Blanks, and Text or Number Filters (depending on whether the

column contains text or numbers). You can limit which rows are displayed by filtering

on specific values in one or more columns. For example, you may limit the display to

only work level / work category 3 Professional employees by filtering on “3” in

Column Q (work level / work category at end of Evaluation Period) and

“Professional” in column Y (Work Category). When

a filter is active, the filter arrow turns blue (in Excel 2003) or changes from to

(in Excel 2007 and later). A filter may be de-activated by selecting Select All under the

filter choices. Blanks also may be used for filtering. For example, to identify

employees with no Wildcard 2 value assigned, select Blanks in the filter for the

Wildcard 2 column (Column AP). The Custom (Excel 2003) or Text or Number Filters

(Excel 2007/later) choice allows the user to design more complex filter criteria.

The Clear All Filters button on the toolbar removes all filters you have set on the

Data worksheet.

This button is active on the Data worksheet only.

Sort

Allows users to sort the rows in the worksheet by any combination of up to three

columns. Sorts may be in either ascending or descending order.

Note: The first options you may see when you click on a filter button show “Sort Z to

A” and “Sort A to Z”. Sorting has been disabled here and under the “Data” tab in the

menu bar in the CWB and is ONLY accessible through the Sort button on the

toolbar.

This button is active on the Data worksheet only.

Main Menu

Returns users to the Instructions worksheet.

This button is active on all worksheets.

Wildcard Stats

Generates statistics and charts on ANY one column in the Data worksheet. Pressing

the Wildcard Stats button causes the Wildcard interface to display, which allows you

to select which column’s stats and charts you want to generate. For more information,

see “Using Wildcards in the DPAT”.

This button is active on all worksheets.

Customize

Allows users to re-sort pay pools and wildcard statistics. Users can also use the

Customize pop-up interface to delete or rename pay pools in the DPAT.

This button is active on all worksheets.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 6 UNCLASSIFIED 2018

Toolbar Item

Description

Capture Chart Images

Selects and automatically exports to PowerPoint or Excel any or all of the 22 charts

generated by the CWB. Pressing the Capture Chart Images button causes the Charts

interface to display, which allows you to select which charts from which worksheets

you want to export.

This button is active on all worksheets.

Highlight

Changes the background color of any selected cell or range of cells. To remove the

highlighting, select the cell or range of cells, select the highlight button, and choose

the large “No Fill” option. This item is active on the Pay Pool Panel, Bonus

Adjustment Summary, and Summary worksheets.

Note: When using a projector to display the CWB during a meeting, data in some

rows still may show through due to projector’s resolution, even if you use black

highlighting. Test your projected image before displaying to an audience.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 7

Where do I get the DPAT?

The OUSD(I) HCMO DCIPS Working Group will make the DPAT available to POCs in each component.

Component POCs may wish to further disseminate the tool for local use within their organization.

Opening the DPAT and Enabling Macros (Excel 2007 and later)

NEW: The 2018 DPAT includes enhanced compatibility with Excel 2010; however, there are no new

features relevant to opening the tool or enabling macros. For instructions on opening the spreadsheet in

Excel 2003, please see the Excel 2003 section at the end of this guide.

The first time you open the DPAT spreadsheet, the tool may open in Protected View. Click the Enable

Editing button in the yellow security warning banner under the tabs in the Menu bar to enable normal

viewing and use, as shown in the figure below.

Opening in Protected View

Each time you open the DPAT, the macros must be enabled for the spreadsheet to operate properly. In

most cases, when you open the spreadsheet you receive a security warning as shown below. In Excel 2007,

users must click the Options button in the Security Warning dialogue.

Security Warning Banner in Excel 2007 upon Opening the Tool

Macros are enabled by clicking the option Enable this Content option and then clicking the OK button.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 8 UNCLASSIFIED 2018

Enabling Macros in Excel 2007

Enabling Macros in Excel 2010 or later

In Excel 2010 or later, it is necessary to click the Enable Content button in the Security Warning dialogue.

Security Warning Banner in Excel 2010 or later upon Opening the Tool

Unlike in Excel 2007, there is no other pop-up box to click through once you click Enable Content.

Depending on your local security settings, you may see a pop-up box asking if you would like to make this a

trusted document. Clicking “Yes” means that you will not have to click Enable Content every time you open

the tool. Follow your local security procedures regarding whether to make the DPAT a trusted document.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 9

Compatibility Checker

When the DPAT is saved, Excel 2007 may generate an alert stating that the DPAT is not compatible with

earlier versions of Excel. This is inaccurate since the DPAT was developed in an earlier version of Excel.

Ignore this error message and click the Continue button.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 10 UNCLASSIFIED 2018

Instructions Worksheet

NEW: There are no new features on the Instructions worksheet in 2018.

The Instructions Worksheet contains a listing of the analysis worksheets contained in the tool. Beginning

in Row 38, users can see a list of the most-recently analyzed Wildcard field, as well as a list of all the pay

pools currently in the DPAT.

Instructions Worksheet

Bottom of the Instructions Worksheet

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 11

Importing Data into the DPAT

NEW: There are no updates to the import routines in the 2018 DPAT.

Users can import data into the DPAT from the HRMS extract file (the CWB import file) as well as from the

actual CWB spreadsheets.

The import process is conducted as follows:

1. Begin with a new (empty) copy of the DPAT spreadsheet and rename it (e.g., paypoolxyx11Nov2016.xls).

It is a good idea to keep a blank copy of the spreadsheet available in case you make a mistake and want

to start over.

2. Open the spreadsheet and select

Enable Macros

(or

Enable Content

in Excel 2007 or later).

3. Select the

Import

button on the custom toolbar.

4. Select one of the options:

NGA

CWB Spreadsheet or CWB Export File

Non-N GA

CWB

Spreadsheet or CWB Export File

, or

CWB Extract Files from DCPDS/PeopleSoft

then click

Select File(s)

. Choose either of the first two options (dependent on whether your organization is a part

of NGA or not) to look at summary statistics and charts on ratings AND payouts from one or more pay

pools AFTER your pay pool panel has convened. Choose the third option to look at summary statistics

and charts on ratings ONLY, typically during the Performance Management Performance Review

Authority (PM PRA) process.

Select the type of file and click “Select File(s)”

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 12 UNCLASSIFIED 2018

5. Select the files you wish to import. To select the import files, double click the file name or highlight the

file name and click the double-right arrows in the center of the form. This action will move the files to

the right-hand box for import. The files you want to import must be in the same folder on your computer as the

DPAT.

Select the file to import and use the arrows to move them to the right side of the screen

6. Order the files as you want the pay pools listed in the tool (and displayed on charts) by using the

Move

Move Down

buttons.

7. Select whether the imported data replaces any existing data in the DPAT or appends to it by using the

radial buttons,

Replace All Existing Data with this file(s)

Append this file(s) to Existing Data

8. Select the

Open

button (the tool will import the selected pay pool data.)

9. The spreadsheet then imports the selected data files.

10. Select

Yes

when you receive a confirmation reading:

Data successfully imported. Would you like to

save the spreadsheet?

The spreadsheet is saved.

Save the spreadsheet once data is imported

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 13

Customize the Data (Optional)

NEW: There are no new features in the Customize function in 2018.

Organizations with multiple pay pools may choose to apply the features of the Customize button to

rename and re-arrange pay pools within the DPAT’s database and displays.

1. Click on the Customize button on the toolbar.

2. The imported pay pool files will be listed in the pop-up interface in the order they were arranged during

import into the DPAT.

3. To rename the pay pools, select the

Rename

button in the lower right-hand side of the form, type in a

new name for the pay pool, and click Rename (in the smaller Rename pop-up box). Once the file is

renamed, the Rename pop-up form will include the original name in the pop-up reference title. The

renamed files can revert to their original names by selecting the Revert button on the Customize form.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 14 UNCLASSIFIED 2018

Renaming a pay pool

4. To re-arrange the import files, select the file in the text box. Once highlighted in blue, select the

Move

and

Move Down

buttons until the files in the text box are arranged in the preferred order, then

click

Sort

. This will change the order in which the pay pools display in all charts and statistics.

5. Users can also delete one or more pay pools with the Customize pop-up interface. Highlighting one pay

pool’s name and then clicking

Delete

, or checking the “

Delete all

” box and then clicking

Delete

removes all of the employees from the selected pay pool(s) from the tool.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 15

Data Worksheet

NEW: There are no new features on the Data worksheet in

The Data worksheet displays 103 (if NGA) or 66 (if non-NGA) visible columns of data when importing

data from CWB spreadsheets, and 54 visible columns of data when importing from HRMS extract files. The

Data worksheet stores information such as personnel info, rating (including objective and element rating

when importing from HRMS extract), as well as payout, salary increase, and bonus that were calculated in

the CWB. The only columns that can be edited are the 14 Wildcard columns spaced throughout the

worksheet. The data can be sorted by any visible column and each of the columns has a filter. The number

of pay pools and employees in the DPAT are displayed in cells B1 and B2 respectively.

When data is imported from an HRMS extract file, no payout information is visible on the Data worksheet

(because this information is not contained in the extract file). The payout columns (columns AR through

CW) are permanently hidden. Instead, the objective and element rating columns (columns EG through EV)

are visible at the far right side of the worksheet so that Reviewing Officials or Performance Management

PRAs may view individual element and objective ratings in a spreadsheet format. The Data worksheet

contains space for 10 objectives for each employee; however since most employees have five or fewer

objectives, an administrator may wish to hide the extra objective rating columns. Additionally, the user may

wish to sort or filter the data in the DPAT by Rating Official (column AG), Reviewing Official (column

AH), or others when looking at element and/or objective ratings.

Using the Wildcard Columns and Generating Custom Stats and Charts

Wildcard columns in the Data worksheet are open columns that are used for a variety of purposes. If

wildcard data exists in the imported files, it will be available in the DPAT. These columns can be used to

define sub-sets of the data (e.g., geographic locations, divisions, teams, or departments) in four of the

generated statistics spreadsheets and three of the generated charts worksheets within the tool.

To change the wildcard grouping used throughout the application:

1. Select the

Wildcard Stats

button.

2. Select the wildcard column for which you want to generate stats and charts.

3. Select

Run Statistics

You can also remove any previous statistics generated by the Wildcard columns.

Example: Consider a Pay Pool PRA that oversees two pay pools that have employees working in three areas

in the US – San Antonio, Washington DC, and San Diego. The application can generate a variety of

statistics comparing employees from each of those areas.

To accomplish this, the user first enters the appropriate data into a Wildcard column (as seen in the example

below). In this example, the user has also renamed the Wildcard 1 column by typing “Area” into the green

header cell at the top of the Wildcard 1 column.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 16 UNCLASSIFIED 2018

Example of Data Entry in Wildcard 1

Next, the user selects the

Wildcard Stats

button from the custom toolbar, chooses the appropriate wildcard

column (in this example, Wildcard 1), and selects

Run Statistics

. The DPAT will automatically generate the

statistics by the groupings identified in Wildcard 1 (example below). Note that the new name for Wildcard 1,

“Area”, appears in the list of Wildcard columns in the Wildcard Selection interface.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 17

Using Wildcard Stats Button to Select Wildcard 1

The example below, a rating distribution by wildcard, is only one of the many statistical graphs generated by

the tool. Other statistics that capture wildcard data include: ratings statistics; salary increase statistics; bonus

statistics; net draw.

Sample Graph Generated from Wildcard Data

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 18 UNCLASSIFIED 2018

Rating Statistics Worksheet

NEW: There are no new features in the Rating Statistics

worksheet in 2018.

The Rating Statistics worksheet includes breakout by pay plan/grade combination.

The Rating Statistics worksheet is available regardless of whether data is imported into the DPAT from an

HRMS extract file or a CWB spreadsheet/export file, and for both NGA and non-NGA pay pools.

The Rating Statistics worksheet displays the mean objective rating, mean element rating, mean overall

rating, standard deviation, number of employees, number of employees rated, and the rating distributions by

group. The statistics review data by pay pool, work level / work category, work category/work level, work

role, occ series, rating official, reviewing official, organization, and wildcard.

To simplify the list of rating group breakouts displayed on the Rating Statistics worksheet, click the

Display Options

button in the top left corner of the page and choose which breakouts you want to see in

the pop-up interface. You can also modify the sorting of these groupings using the same interface (statistics

can be sorted in ascending or descending order). Click

Apply

once you have set which breakouts and sorts

you would like to view. Note that the

Display Options

button only affects the Rating Statistics page.

Display Options on the Rating Statistics worksheet

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 19

Rating Charts Worksheet

NEW: There are no new features on the Rating Charts worksheet in 2018.

The Rating Charts worksheet is available regardless of whether data is imported into the DPAT from an

HRMS extract file or a CWB spreadsheet/export file, and for both NGA and non-NGA pay pools.

The Rating Charts worksheet provides bar charts showing the rating distribution of all the employees in

the Data worksheet with a rating. Charts on this page are aggregated across pay pools. The top left

chart shows the percentage distribution by Evaluation of Record, and the top right chart show the

percentage distribution by Overall Rating. The bottom left chart shows the count of employees by

Evaluation of Record, and the bottom right chart shows the count of employees by the Overall Rating.

Rating Charts Worksheet

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 20 UNCLASSIFIED 2018

Rating Distribution by Group Worksheet

NEW: There are no new features on the Rating Distribution by Group worksheet in 2018.

The Rating Distribution by Group worksheet is available regardless of whether data is imported into the

DPAT from an HRMS extract file or a CWB spreadsheet/export file, and for both NGA and non-NGA

pay pools.

The Rating Distribution by Group worksheet provides two bar charts showing the rating distribution by

pay pool and Wildcard grouping. The top chart shows the rating distribution by pay pool and the bottom

chart shows the distribution by the selected Wildcard grouping (empty if no grouping is selected from the

Wildcard Stats

button). A trend line shows the average across the groupings in both charts.

Rating Distributions by Group

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 21

Payout Statistics Worksheet

This worksheet is only available when data is imported from CWB spreadsheets or CWB export

files.

NEW: There are no new features on the Payout Statistics worksheet in 2018.

The Payout Statistics worksheet provides the following data for each individual pay pool as well as

averages across all the pools in the DPAT. The salary increase portions of this worksheet are hidden when

the user imports non-NGA pay pool data. The payout values include only employees that are rated.

 Analyzed Population Counts

 Mean Rating

 Share Value for Salary and Bonus

 Salary Increase ($) – Non-zero minimum, Mean, Median, and Maximum

 Salary Increase (% of Base Pay) – Non-zero minimum, Mean, Median, and Maximum

 Bonus ($) – Non-zero minimum, Mean, Median, and Maximum

 Bonus (% of Midpoint) – Non-zero minimum, Mean, Median, and Maximum

Payout Statistics Worksheet – non-NGA pay pool view

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 22 UNCLASSIFIED 2018

Payout Charts Worksheet

This worksheet is only available when data is imported from CWB spreadsheets or CWB export

files.

NEW: There are no new features on the Payout Charts worksheet in 2018.

The Payout Charts worksheet provides bar charts showing the salary increase share value by pay pool,

bonus share value by pay pool, mean salary increase percentage by pay pool, mean salary increase dollar

amount by pay pool, mean bonus increase percentage by pay pool, and mean bonus increase dollar amount

by pay pool. The salary increase portions of this worksheet are hidden when the user imports non-NGA pay

pool data.

Payout Charts Worksheet – NGA pay pool view

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 23

Salary Increase Statistics by Group Worksheet (NGA ONLY)

This worksheet is only available when data is imported from CWB spreadsheets or CWB export

files.

NEW: There are no new features on the Salary Increase Statistics by Group worksheet in 2018.

The Salary Increase Stats by Group worksheet includes a

Display Options

button with the same

functionality as the one on the Rating Statistics worksheet.

The Salary Increase Stats by Group worksheet provides salary increase statistics by pay pool, work

level / work category, rating, work category/work level, work role, occ series, rating official, reviewing

official, organization and wildcard.

Salary Increase Stats by Group Worksheet

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 24 UNCLASSIFIED 2018

Salary Increase Charts by Group Worksheet (NGA ONLY)

This worksheet is only available when data is imported from CWB spreadsheets or CWB export

files.

NEW: There are no new features on the Salary Increase Charts by Group worksheet in 2018.

The Salary Increase Charts by Group worksheet provides charts of the data provided in the Salary

Increase Stats by Group worksheet. Note: not all available charts on this worksheet are shown here.

Salary Increase Charts by Group Worksheet

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 25

Salary-Bonus Charts by Wildcard Worksheet

This worksheet is only available when data is imported from CWB spreadsheets or CWB export

files.

NEW: There are no new features on the Salary-Bonus Charts by Wildcard worksheet in 2018.

The Salary-Bonus Charts by Wildcard worksheet provides charts of the wildcard groupings data provided

in all relevant worksheets. Charts by wildcard include: mean salary increase percentage; mean salary increase

in dollar amount; percent receiving bonus; mean bonus percentage; and mean bonus in dollar amount. The

salary increase portions of this worksheet are hidden when the user imports non-NGA pay pool data.

Salary-Bonus Charts by Wildcard Worksheet – NGA pay pool view

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 26 UNCLASSIFIED 2018

Salary Increase Charts Worksheet (NGA ONLY)

This worksheet is only available when data is imported from CWB spreadsheets or CWB export

files.

NEW: There are no new features on the Salary Increase Charts worksheet in 2018.

The Salary Increase Charts worksheet displays the performance salary increase distribution as a percentage

of base pay, the salary increase as a percentage of base pay, and the correlation between performance rating

and the performance salary increase in dollar amount.

The scatter plots can be filtered by filtering in the Data worksheet.

Salary Increase Charts Worksheet

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 27

Total Salary Increase Charts Worksheet (NGA ONLY)

This worksheet is only available when data is imported from CWB spreadsheets or CWB export

files.

NEW: There are no new features on the Total Salary Increase Charts worksheet in 2018.

The Total Salary Increase Charts worksheet displays the total salary increase distribution as a percentage

of base pay, the salary increase as a percentage of base pay, and the correlation between performance rating

and the total salary increase in dollar amount. The charts on this worksheet differ from those on the Salary

Increase Charts worksheet in that the charts on this worksheet take the floor increase and increases to

LMS into account.

The scatter plots can be filtered by filtering in the Data worksheet.

Total Salary Increase Charts Worksheet

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 28 UNCLASSIFIED 2018

Bonus Statistics by Group Worksheet

This worksheet is only available when data is imported from CWB spreadsheets or CWB export

files.

NEW: There are no new features on the Bonus Statistics by Group worksheet in 2018.

The Bonus Stats by Group worksheet includes a

Display Options

button with the same functionality as

the one on the Rating Statistics worksheet.

The Bonus Stats by Group worksheet provides bonus statistics by pay pool, work level / work category,

rating, work category/work level, work role, occ series, rating official, reviewing official, organization, work

location, and wildcard.

Bonus Stats by Group Worksheet – NGA pay pool view

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 29

Bonus Charts by Group Worksheet

This worksheet is only available when data is imported from CWB spreadsheets or CWB export

files.

NEW: There are no new features on the Bonus Charts by Group worksheet in 2018.

The Bonus Charts by Group worksheet provides charts of the data provided in the Bonus Stats by

Group worksheet. Note: not all available charts on this worksheet are shown here.

Bonus Charts by Group Worksheet

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 30 UNCLASSIFIED 2018

Bonus Charts Worksheet

This

worksheet is only available when data is imported from CWB spreadsheets or CWB

export files.

NEW: There are no new features on the Bonus Charts worksheet in

The Bonus Charts worksheet provides charts of pay pool data. Charts display the following: the

bonus distribution as a percentage of base pay; the bonus distribution as a percentage of midpoint;

the bonus amount as a percentage of base pay; the bonus amount as a percentage of midpoint; the

correlation between performance rating and the bonus in dollar amount, and mean bonus $ by

overall rating (not shown here). The final chart on the worksheet is a bonus distribution chart that

breaks out bonus dollar amounts into

$500 groups. The number of categories on the x-axis of this chart will change in response to the

actual bonus amounts in the data; for example, if the largest bonus on the Data worksheet is

$4,372, the x-axis of this chart will only go up to $4,500. If there are bonuses over $7,000, they will

all be included in the last category on the chart, which will read “$6,500 – [highest increment]” as in

the example on the next page.

The scatter plots can be filtered by filtering in the Data worksheet.

Bonus Charts Worksheet (top)

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 31

Bonus Charts Worksheet (bottom)

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 32 UNCLASSIFIED 2018

Funding Statistics Worksheet

This worksheet is only available when data is imported from CWB spreadsheets or CWB export

files.

NEW: There are no new features on the Funding Statistics worksheet in 2018.

The Funding Statistics worksheet provides statistics on pay pool funding, including both the salary and

the bonus budgets, the funds allocated for each, and the difference between the funds budgeted and the

funds actually allocated. Funding statistics note the total number of employees, the number contributing to

salary and bonus funds, the number of employees rated, and the number of employees receiving a payout

and/or a bonus. The salary increase portions of this worksheet are hidden when the user imports non-NGA

pay pool data. Note: entire worksheet is not shown here.

Funding Statistics Worksheet – non-NGA pay pool view

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 33

Funding Charts Worksheet

This worksheet is only available when data is imported from CWB spreadsheets or CWB export

files.

NEW: There are no new features on the Funding Charts worksheet in 2018.

The Funding Charts worksheet provides bar charts showing the salary increase and bonus funding

percentages for each pay pool. A trend line shows the average funding across the pay pools. The salary

increase portions of this worksheet are hidden when the user imports non-NGA pay pool data.

Funding Charts Worksheet – NGA pay pool view

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 34 UNCLASSIFIED 2018

Net Draw Analysis Worksheet (NGA ONLY)

This worksheet is only available when data is imported from CWB spreadsheets or CWB export

files.

NEW: There are no new features on the Net Draw Analysis worksheet in

The Net Draw Analysis worksheet includes a

Display Options

button with the same functionality as the

one on the Rating Statistics worksheet. The Net Draw Analysis worksheet shows net draw results by

career group, pay schedule, work level / work category, Wildcard, and for the entire population. Net Draw

is the difference between the dollar amount an employee is awarded from the pay pool minus the dollar

amount contributed to the pay pool based on the employee’s base pay all divided by the employee’s base

pay.

Net Draw % = (Payout – Contribution) / Base Salary

For example, if a pay pool’s total funding percentage is 5%, then each employee’s contribution is 5% of

their base salary. The amount they take out is their payout amount. So, an employee with a base salary of

$50,000 contributes $2,500 into the pay pool ($50,000 x 5%). If the employee receives a payout of $3,000,

then their net draw is positive. If the employee receives a payout of $2,000, then their net draw is negative.

Net draw is only useful when looking for patterns across groups of employees, and should not be assessed

at the individual employee level.

Net Draw Analysis Worksheet

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 35

Net Draw Charts Worksheet (NGA ONLY)

This worksheet is only available when data is imported from CWB spreadsheets or CWB export

files.

NEW: There are no new features on the Net Draw Charts worksheet in

The Net Draw Charts worksheet provides visual representation of the net draw results by work level /

work category, rating, work category, work role, occupational series, rating official, reviewing official,

organization, and wildcard. Groups that plot below the axis have contributed more than their payout, while

the opposite is true of those groups that plot above the axis.

Net Draw Charts Worksheet

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 36 UNCLASSIFIED 2018

Capturing DPAT Charts and Exporting to PowerPoint or Excel

The

Capture Chart Images

button on the toolbar allows you to select any or all of the charts within the

DPAT and automatically export them into Excel or PowerPoint. Select the chart(s) you wish to export,

select either Excel or PowerPoint in bottom left-hand corner, and select the

Generate Charts

button.

Capture Chart Images

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 37

Helpful Hints

Copying and Pasting Data from Worksheets

To copy data from the DPAT into another Excel spreadsheet, first select the cells you want to copy. Next,

select Copy from the Edit menu. Then open the spreadsheet you want to copy the data into, and select Paste

Special from the Edit menu. Finally, choose Paste Values and select OK. You may also select Paste Special a

second time at the same location and choose Paste Formats. This second step will keep the data looking just

as it does in the DPAT.

Pasting Data into another Excel Spreadsheet

DCIPS Payout Analysis Tool –

UNCLASSIFIED

Page 38 UNCLASSIFIED 2018

APPENDIX 1: Definitions

Mean – is the value obtained by dividing the sum of a set of quantities by the number of quantities in the

set. For example, if there are three DCIPS ratings of 3, 3, and 5, the arithmetic mean of the ratings is their

sum (11) divided by the number of ratings (3), or 3.66.

Median – is the middle number in a given sequence of numbers, or the point at which half of the values are

above and half below. For example, in the following set of DCIPS ratings – 3, 3, 3, 3, 3, 4, 4, 5, 5, 5, 5 – the

median value is 4.

Net Draw – is the ratio of the amount of money an employee contributes to the pay pool versus how much

they take out.

Net Draw % = (Payout – Contribution) / Base Salary

For example, if a pay pool’s total funding percentage is 5%, then each employee’s contribution is 5% of

their base salary. The amount they take out is their payout amount. So, an employee with a base salary of

$50,000 contributes $2,500 into the pay pool ($50,000 x 5%). If the employee receives a payout of $3,000,

then their net draw is positive. If the employee receives a payout of $2,000, then their net draw is negative.

Wildcard – open, unprotected columns in the DPAT that are used to hold data or equations. Wildcard

columns in the Data worksheet can be used to define sub-sets of the data (e.g., geographic locations,

divisions, teams or departments) to generate statistics and charts for those groups in numerous worksheets

in the application.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

2018 UNCLASSIFIED Page 39

APPENDIX 2: Using the DPAT with Excel 2003

Enabling Macros

The DPAT is currently developed in Excel 2003 to maintain compatibly with users of previous versions of

Excel. Excel 2007 and later have quite a few differences compared with previous versions. One of the most

significant is enabling macros. The main part of this user guide discussed enabling macros in Excel 2007 and

later. This appendix discusses enabling macros and using security settings in Excel 2003.

Each time you open the CWB spreadsheet, the macros must be enabled for the spreadsheet to operate

properly. In most cases, when you open the spreadsheet you receive a security warning as shown below. To

enable the macros, select Enable Macros, and the spreadsheet opens and operates normally.

If the security setting in Excel is set to either High or Very High, Excel automatically disables the macros in

the spreadsheet. You can recognize this because 1) you are not prompted to enable the macros in the

spreadsheet, and 2) the spreadsheet is not operating properly (e.g., links are non-responsive, the tool-bar

does not appear). If this occurs, do the following (Excel 2003 only – for instructions in Excel 2007, please

see the Opening the Spreadsheet and Enabling Macros in Excel 2007and later section on page 9 of this

guide).

• Open Excel

• From the Tools Menu, select Macro



Security

• Change the security level to Medium

• Close Excel

• Reopen the spreadsheet

• Select Enable Macros, when prompted

If you do not have the administrative rights to set the security level on your computer, contact your IT

department for assistance.

DCIPS Payout Analysis Tool – 2018 UNCLASSIFIED

Page 40 UNCLASSIFIED 2018

Select Tools



Macro



Security

Select the Medium security level

Saving in Excel 2003 format

ALWAYS save the DPAT in compatibility 97-2003 (.xls) format. Simply clicking the Save icon in the

upper left corner of the screen will accomplish this. You also may save the tool as a .xlsm (Macro-Enabled

Worksheet) file; however, most DoD computer system configurations do not allow these files to be sent via

email or to be opened with macros enabled. Check with your local system administrator before attempting

to save the tool as a .xlsm file.

NEVER save the DPAT in 2007 or later (.xlsx) format. Doing so will cause irreparable harm to your

spreadsheet and you will have to start over with a blank DPAT.