HR - CAT - Institutional Planning Spreadsheet

This document reviews the Institutional Planning Spreadsheet (IPS) used by campuses to implement pay plans or other salary increases.

Purpose and Overview

The Institutional Planning Spreadsheet supports institutions in the compensation administration process involved in implementing institutional pay plans or other pay increases.  Rate adjustment and funding data from this document can be mass uploaded to CAT for analysis, reporting, editing, and salary line reconciliation.  While changes can be made in both the Institutional Planning Spreadsheet (IPS) and the Compensation Administration Tool (CAT), the IPS is more useful for mass changes.

The IPS is constructed with one row per employee record.  Each row contains employee data from HRS job data, room for the user to enter up to five pay rate adjustments (five distinct action reasons), and room for up to five funding sources.



Compensation Administration Planning Tool

The CAT should be used after an Initialization Report (IR) has been created and filtered.  For information on how to generate an IR, please reference:  HR - CAT - Compensation Administration Tool Initialization Report.  If you choose to run multiple instances of the IR for different employee classes or effective dates, you can combine them into one IPS or maintain separate IPSs.

Procedure Steps:

1. Open the IPS template.

a.  Different browsers will have different opening processes.

i. For Internet Explorer, click "Open" in the pop-up window.  Navigate to Excel, and click "Enable Content" on the document.  Click "Yes" on the Trusted Document pop-up window.

ii. For Google Chrome, ensure that pop-ups are allowed for this page.  Open the downloaded file from the downloads toolbar at the bottom of the page.  Navigate to Excel, and click "Enable Content" on the document.

iii. For Firefox, click "OK" on the pop-up that defaults to opening Excel.  Click on the file from the downloads box in the upper right-hand corner of the page.  (If it doesn't automatically open, click the down arrow icon to open the downloads box.)  Excel will automatically open. Click the "Enable Content" on the document.

b.  The IPS is a Macro Enabled file (.xlsm).  If asked to enable Macros upon opening the file, please do so.

i.  To ensure the file functions correctly, navigate to 'Macro Settings' in Excel.

1.  File > Options > Trust Center > Trust Center Settings > Macro Settings


Picture 1


2.  "Enable All Macros (not recommended potentially dangerous code can run)" should be checked.

3.  "Trust access to the VBA project object model" should be checked.

2.  Save a new copy of the IPS as an Excel Macro-Enabled Workbook (.xlsm) file with a descriptive title.

3.  Copy and paste HR data from the Initialization Report into the IPS.

a. Begin by using the IR report without "addl_splits" in the title.

b. Employee data:  In the Initialization Report, start in cell A13 and select all the data from Column 1 (Business Unit) to Column 33 (Maximum Annual as of 7/1).

i. This should select all the employee data in those columns, not including headers (start with row 13).

ii. The images below indicate the range of data to select.


Picture 2


c.  Copy the selected data.

d.  In the IPS "Compensation Administration Planning Tool" tab, paste the data starting in cell C16 (immediately under "Business Unit").

i.  In order to maintain the formatting in the IPS, the data must be pasted as values.

1.  Right-click in cell C16 of the IPS on the Comp Admin Planning Tool tab.

2.  Under "Paste Options" in the menu, select the option that looks like "123" on a clipboard (image below).  This will paste the data as values.

Picture 3


e.  Funding Data:  Return to the Initialization Report.  Starting in cell AH13, select all of the data from Column 77 (GL Business Unit 1) to Column 116 (Budgeted FTE 5).

i.  This should select all the employee data in those columns, not including headers.  Some selected cells may be blank.

ii.  The images below indicate the range of data to select.


Picture 4


f.  Copy the selected data.

g.  In the IPS "Comp Admin Planning Tool" tab, paste the data starting in cell CA16 (immediately under "GL Business Unit 1").

i.  Paste the data as values as described in Step 3d.

4.  Determine how to enter employee records with 6 funding sources or more.

a.  Option 1:  Enter first 5 sources in the IPS; enter the remaining sources manually.

i.  After completing Steps 1 - 3 and entering the data for employees with 5 sources or fewer as described in Step 4, open the "init_report_addl_splits" file.

ii.  Repeat Step 3 using this document.  Be sure to only select up to Column 116 (Budgeted FTE 5) when copying and pasting in funding data.

iii.  Enter the remaining funding sources (#6 and above) manually in the CAT.

b.  Option 2:  Enter all information manually.

i.  Enter all the employee records in the "init_report_addl_splits" Excel document manually in the CAT.

c.  Option 3:  Perform multiple uploads.

i.  After completing Steps 1 - 3 and entering the data for employees with 5 sources or fewer as described in Step 3, open the "init_reports_addl_splits" file.

ii. Repeat Step 3 using this document.  Be sure to only select up to Column 116 (Budgeted FTE 5) when copying and pasting in funding data.  Paste the new data (the first 5 sources) directly below the data already pasted into the IPS.

iii.  Perform an upload (Steps 5 - 10)

iv.  Unlock the spreadsheet by clicking the "Review" menu at the top of the Excel window and clicking "Unprotect Sheet."


Picture 5


v.  Remove the funding data (Columns 77 through 106) from the first upload, and replace with the next five funding sources found in the "init_report_addl_splits" file (Columns 117 through 156).  Leave all demographic information and compensation information the same.  Only the unique funding resources will load.

1.  If any compensation data has been changed between the initial load and any subsequent loads, that data will add to those job records in the CAT.  In order to only add funding to a record, the compensation data must be exactly the same as it was during the initial load.

vi.  Perform an upload (Steps 5 - 10, skipping steps 7 and 9).

vii.  Repeat as necessary until all additional funding sources have been uploaded.

5.  Check numbers stored as text.

a.  Due to the nature of the Initialization Report, all the data will be formatted as text.  For the IPS to work correctly, some of the data must be changed to a number format.

b.  Select the data in Column 20 (Row V).


Picture 6


c.  To the left of the selected data, an error box, which looks like an exclamation point in a diamond, should appear.


Picture 7


d.  Click on the box, revealing a menu.  Click "Convert to Number."


Picture 8


e.  It may take a few minutes for the data to convert.  When the cursor stops spinning, the conversion is complete.

6.  Lock the data in the IPS.

a.  To prevent data from accidentally being edited, certain cells in the IPS are locked.  To activate this protection, the "Comp Admin Planning Tool" tab must be locked.

b.  Ensure that the "Comp Admin Planning Tool" tab is open.

c.  Click the "Review" menu at the top of the Excel window.

d.  Click "Protect Sheet."


Picture 9


e.  In the window that appears, make sure the following options are selected:

i.  Check "Protect worksheet and contents of locked cells."

ii.  Do not require a password to unprotect the sheet.


Picture 10


f.  Click "OK."

7.  If UW-Madison, please reference the additional Continuing Staff Bases instructions.

8.  Use the IPS to make compensation rate adjustments.

a.  Background:  Following the institution's pay plan or other rate increase guidelines, an institutional representative is able to make compensation rate adjustments en masse.  Changes to rates for large groups of employee records can be made by manual entry, or by dragging down a single value.

b.  Note:  Columns containing values that will be passed on to the CAT (which therefore cannot be deleted) are indicated by an asterisk (*) in the column title.

c.  Note:  For more information on the columns, you can select the Review tab on the top toolbar and click "Show All Comments."  This will reveal descriptions for most of the column headers.

d.  Step 1:  Choose the appropriate action reason for the adjustment in the Other Pay Rate Adjustment Action Reason column.

i.  Examples of such columns include 34, 41, 48, and 55 for Cumulative and 41, 48, 55, 62 for Off-Base.

1.  The Action Reason in Column 62 for the Cumulative IPS and 34 for the Base IPS is locked as "Merit Increase."

2.  Note that you must copy and paste the "019 - Merit" Action Reason down for each cell in your data set.  This cell cannot be dragged down or auto filled.

e.  Step 2:  Enter the desired pay rate increase percent in the Pay Rate Adjustment Amount (%) column, or the Pay Rate Adjustment Amount ($).

i.  Please note that only one of these values may be entered.  If both are entered, the $ amount will override the %.

ii.  Examples of such columns include 35/36, 42/43, 49/50, 56/57, and 63/64.

iii.  To make one identical change across a large group of employee records:

1.  Enter the Pay Rate Action Reason and Pay Rate Adjustment Amount (either $ or %) in the top row.

2.  The $ amount increase that is entered will always be an increase to that employee's Comprate (an increase to their annualized salary if A/C basis, or an increase in their hourly rate if H basis).  The % increase that is entered will always be calculated off their Comprate as well.

3.  Select those cells in the row and hover over the black box on the lower right corner of the selection.  Your cursor should become a black cross.


Picture 11


4.  Click and drag the edge of the selection down over the cells beneath that should have the identical adjustment.


Picture 12


5.  After the selection is released, a small box, pictured below, should appear at the bottom right of the selection.  Click the box and select the "Copy Cells" option.

Picture 13   Picture 14

6.  The selected cells should now all have the same rate adjustments.

7.  Alternatively, double click on the black cursor to automatically copy the formula down to the bottom of your data set.

f.  Step 3:  Repeat steps 1 and 2 until rate adjustments have been distributed to all employees in accordance with the institution's pay plan.

i.  Up to 5 different pay rate adjustments (unique Action Reasons) can be assigned to each job record within the IPS.

ii.  If more pay rate adjustments need to be assigned to a particular job, they can be added in the PeopleSoft CAT page.

g.  Step 4:  Once all changes are made, filter the Proposed Annualized Base Salary (1 FTE) column (Column 76) by color for cells with a light red conditional formatting.  There may be no cells with light red formatting, in which case this step may be skipped.

i.  If any cells appear, it indicates that the current Proposed Annualized Base Salary (1 FTE) is out of the range for that salary plan.  The minimum and maximum salaries for the employee's salary plan are in Columns 31 and 33.

ii.  For employee records who have been approved for over max, and UW-Madison employee records who may be eligible for extraordinary salary ranges (Column 23), these cells may continue to be red.

9.  Use the IPS to make funding changes.

a.  The data entered from the Initialization Report contained the funding information from commitment accounting in HRS as of the date entered on the Initialization Report Run Control.  This offers a basis for current FY funding set up.

b.  Edit the information as necessary for the upcoming FY.

i.  If you wish to change a Fund, Department, Program, or Project for a particular Empl_Rcd, simply enter the new values within those columns (Columns 78 through 106).

ii.  Allocate, by percent, how the employee's salary will be paid in the Distribution Percentage columns.  The sum of the columns must equal 100%.

1.  Examples of such columns include 82, 90, 98, 106, and 114.

2.  Note that the percentages should be entered as whole numbers, rather than decimals.  For example, a distribution of 80% should be entered as "80" rather than "0.80."

iii.  To change an employee record's budgeted FTE, overwrite the value found in Column 16.  This will change the budgeted amount sent back to Budget Summary, but will not change an employee record's FTE in HRS.

iv.  Funding information can also be left as-is, reflecting the same funding information and distribution from the previous FY.

v.  Employees with more than 5 funding sources must have funding sources 6 and above manually entered in the CAT (see options in Step 1).

10.  Use Column 120 on the Comp Admin Planning Tab to make any comments concerning the employee record or changes you've made.

a.  It is best practice for users to enter their name, initials, or an Empl_ID followed by "///".  This will be useful for separating and organizing comments that are made for the same employee entry.

11.  Use the IPS to make changes to the Additional Adjustments Page (AAP)

a.  The third tab on the IPS template, Additional Adjustments, allows for users to enter Vacant Position information or Budget Adjustment position information (formerly group, lump sum, deduct, negative entries.)

b.  If entering these types of positions manually, it is best to use the AAP directly within PeopleSoft.  This tab is most useful for institutions that carry over many of their Vacant or Budget Adjustment positions from the previous fiscal year.

c.  If Vacant or Budget Adjustments positions are being carried over from the previous fiscal year, first construct a query (from EPM) to pull last year's AAP in the format found on this tab.

d.  Copy and paste the results from the query into the AAP tab, starting in cell A6.

e.  Make any necessary additions, deletions, or adjustments to existing data.

f.  Regulations:

i.  The entries in this tab and in the AAP itself must be done by funding source (if a vacant position is source funded, it will appear as two separate rows, with the same position number but different funding information.)

ii.  You must enter an Adj Type for all positions.  This is required for budget summary information.  The possible adjustment types are:

1.  Univ. Staff

2.  LI/AS/FA

3.  Grad Assistants

4.  Student Help

5.  LTE

iii.  Job Code and Pay Basis are required for updating a Vacant Position.

iv.  If you select an Adjustment Type of Student Help or LTE, FTE will gray out.

v.  If the Position Type is Bud. Adj., Job Code, Pay Basis, and Position Number will gray out.

vi.  If entering GL Business Unit in Column 10, it must match the Business Unit in Column 1.

12.  After all changes have been made in the IPS, it should be saved and loaded to the CAT.

13.  If Option 3 was selected for entering employees with more than 5 funding sources (perform multiple uploads), perform the upload process again, as instructed in Step 3.

Reference Column/Cell Guide below for a table with column descriptions.


Additional Adjustments Tab

All changes to Additional Adjustments are entered manually in this tab. See the chart below for information on entering data in each column.

ADDITIONAL ADJUSTMENTS TAB
COLUMN DATA
COLUMN FIELD NAME ENTRY
METHOD
DESCRIPTION
1 BUSINESS UNIT

Entered Field Enter the BU as you would like it to appear in the Additional Adjustments Page, including UW (e.g. UWMSN, UWLAC, etc)
2 FUNDING
DEPARTMENT (DEPTID)
Entered Field Enter the funding department as you would like it to appear in the Additional Adjustments Page
3 FISCAL YEAR Entered Field Enter the fiscal year in which this Additional Adjustment will take place
4 POSITION TYPE Entered Field Enter the position type, 'Budget Adjustment' or 'Vacant'
5 ADJUSTMENT TYPE Entered Field Enter the adjustment type from the drop down list
6 JOB CODE Entered Field

Job Code is only required for 'Vacant' position types
7 PAY BASIS Entered Field Enter a pay basis from the drop down list
8 POSITION # Entered Field Position number is optional for 'Vacant' position types
9 FTE Entered Field Enter FTE as you would like it to appear on the Additional Adjustments Page.  FTE should not be entered for adjustment types LTE and Student Help
10 GL BUSINESS
UNIT 1

Entered Field

 Enter GL BU as your would like it to appear on the Additional Adjustments Page.

11 FUND CODE

Entered Field

 Enter Fund Code as you would like it to appear on the Additional Adjustments Page.

 12DEPARTMENT

Entered Field

 Enter Department as you would like it to appear on the Additional Adjustments Page.

 13PROGRAM

Entered Field

 Enter Program number as you would like it to appear on the Additional Adjustments Page.

 14PROJECTID

Entered Field

 Enter Project ID as you would like it to appear on the Additional Adjustments Page.

 15DESCRIPTION


Entered Field

 Enter Description as you would like it to appear on the Additional Adjustments Page.


 16BUDGETED
AMOUNT

Entered Field

 Enter Budgeted Amount as you would like it to appear on the Additional Adjustments Page.



COMP PLAN SUMMARY TABLE

The pivot table on this tab provides summary data at a variety of different levels.  This provides preliminary benchmarks on how much has been spent in each Department and Business Unit to reference against the amount budgeted towards adjustment.  The pivot table allows the user to filter based on Pay Rate Action Reasons, Fund Codes, and Employee Class.  It then displays the totals for Total Annual Adjustment, Proposed Annualized Base Salary, and Job Data FTE broken down by Business Unit and Department ID.

While users may add filters or values that correspond with any of the columns from the Comp Admin Tool tab, all data and filters are best kept at a very high level.  Otherwise, the table has too many rows and may get challenging to read.

IPS → CAT

This tab concatenates the fields in the Comp Admin Planning Tool tab in a format that can be uploaded to the CAT.

This is the page that should be saved as a .CSV when preparing to upload data to the CAT.  Reference the following for information on uploading to the CAT:  HR - CAT - 1.1 New Hires Business Process

IPS → AAP

This tab concatenates the fields in the Additional Adjustments tab in a format that can be uploaded to the AAP.

This is the page that should be saved as a .CSV when preparing to upload data to the CAT.  For more information on uploading to AAP, please reference:  HR - CAT - IPS Upload to AAP

COLUMN / CELL GUIDE AND EXPLANATION

GUIDE AND EXPLANATION
ADJUSTMENT SUMMARY TABLE
 CELL(S)  FIELD NAME
 ENTRY METHOD
 -  DESCRIPTION
 D2




Continuing Staff Base




Auto-calculated




Explanation




This value is the sum of the base salaries adjusted
for FTE (before compensation rate increases are entered)
of the employee records that are both continuing their
employment in the next FY and are eligible for pay rate
adjustments (Yes to Questions 1 and 2).
F2

Board of Regents
Plan Adjustment (%)

Entered Field

Explanation

Set (known) value, applies to both Merit based and
Chancellor's Discretionary rate adjustments.
 H2





Dollars Available for Merit\
and Chancellor's Discretionary
Compensation




Auto-calculated





Explanation




Equation
The value represents the dollar amount available for Merit based
and Chancellor's Discretionary pay rate adjustments.  This dollar
amount is determined by using the institution's Continuing Staff
Base (E2) multiplied by a given percent (G2).

=SUM of Annualized Base Salaries Adjusted for FTE * %
E & F6
G6






% Allocated







Auto-calculated







Explanation






Equation
E, F - The percentage of the Dollars Available for Compensation
Plan allocated in that round of pay rate increases.
G - The sum of these percentages.  Cannot be greater than 100%,
otherwise the institution has spent more than it had allocated
for compensation.

=Merit based [or Chancellor's Discretionary] Adjustments /
Dollars Available for Compensation Plan.
E & F7
G7






Totals Available







Auto-calculated







Explanation




Equation


E, F - A running value of the unallocated (still available) amount
of Dollars Available for Compensation.
G - Remaining dollars available.  Will equal the value of the last
Total Available calculation in Columns B or C.

=Dollars Available for Compensation Plan - Total Awarded
The Totals Available reflects both the Total Awarded in that
phase and every phase before it.
E & F8
G8








Totals Awarded









Auto-calculated









Explanation




Equation


Note

E, F - A running value of the unallocated (still available) amount
of Dollars Available for Compensation Plan.
G - Remaining dollars available.  Will equal the value of the last
Total Available calculation in Columns B or C.

= Dollars Available for Compensation Merit and Chancellor's
Discretionary Compensation - Total Awarded.

The Totals Available in Column C subtracts both sum of Merit
based adjustments as well as Chancellor's Discretionary Adjustments.
H 6-8






All Other Rate
Adjustments






Auto-calculated






Explanation




Equation

This is a running total of the value of all other pay rate adjustments
distributed (non-Merit and non-Chancellor's Discretionary).  There
is no cap on the amount that can be spent on these types of
adjustments, therefore there is only a Total Awarded.

= Sum of All Other Adjustments pay rate adjustments
(Other 1 + 2 + 3 + 4)
I6





Pay Plan Status





Auto-calculated





Explanation





- Pay Plan Within Limit - G8 < 12.  Institution has
additional funds to allocate.
- Pay Plan Limit Reached - G8 = 12.  All funds for Merit and
Chancellor's Discretionary pay plan adjustments have been allocated.
- Pay Plan Limit Exceeded - G8 > 12.  Merit Based + Chancellor's
Discretionary adjustments exceeds amount set aside for adjustments.

EMPLOYEE DATA
COLUMN DATA
COLUMN(S) FIELD NAME
ENTRY METHOD
 - DESCRIPTION
Question 1








Load Employee
to CAT?*








Entered Field








Explanation





Note


Select from drop down or drag down answers for each individual.
   Yes - This job record will be budgeted for in the next Fiscal Year.
   No - This job record will NOT be budgeted for in the next Fiscal
Year (Example:  employee will be leaving UWS due to Termination,
Retirement, etc.)

If the answer is "No" the cell will turn blue.  You can delete this
employee record from the Comp Admin Tool tab by right clicking
the row and selecting delete, as described in the Overview and instructions.
Question 2





















Base Salary Adds
to Continuing Staff
Base?*




















Entered Field





















Explanation








Note












Select from drop down or drag down answers for each individual.
Yes - Employee record's salary adds to the continuing staff base
(according to each institution's own guidelines) and is eligible for
pay rate adjustments.
No - Employee record's salary does NOT add to the continuing
staff base (according to each institution's own guidelines) and
employee may not be eligible for pay rate adjustments (Example:
Duration of employment, poor performance.)

If you have answered "No" for Question 1, Question 2 will appear blue.
Otherwise, if the answer is "No" the cell will turn yellow.  These
individuals' Base Salaries will be considered for funding purposes
but will not add to the Continuing Staff Base.  You can leave these
individuals on the Planning Tool or filter them out (advised) by
unchecking "No" from the filter drop down menu, which can be
selected from in Cell B15.

If an employee has a "No" response for Question 1 but a "Yes"
response for Question 1 but a "Yes" for Question 2, the cell will
highlight in Red and have a strike through.  This signifies an error,
an employee who is not being budgeted for in the next FY cannot
add to the Continuing Staff Base.
 1

BUSINESS UNIT*

Loaded from HRS

Explanation

 This pulls the Business Unit from the HRS as of the effective date
entered on the Initialization Report Run Control.
 2



JOB DATA DEPTID
(UDDS)*



Loaded from HRS



Explanation



HR home department.

May be different than funding DeptID.

 3

EMPLOYEE ID*

Loaded from HRS

Explanation

This pulls the Employee ID from the HRS as of the effective date
entered on the Initialization Report Run Control.
 4


EMPLOYEE
RECORD
NUMBER

Loaded from HRS


Explanation


This pulls the Employee Record Number from the HRS as of the
effective date entered on the Initialization Report Run Control.

 5 LAST NAME

Loaded from HRS

Explanation

This pulls the Last Name from HRS as of the effective date entered
on the Initialization Report Run Control.
 6

FIRST NAME

Loaded from HRS

Explanation

This pulls the First Name from HRS as of the effective date entered
on the Initialization Report Run Control.
 7

EMPLOYEE CLASS*

Loaded from HRS

Explanation

This pulls the Employee Class from HRS as of the effective date
entered on the Initialization Report Run Control.
 8

POSITION NUMBER*

Loaded from HRS

Explanation

This pulls the Position Number from HRS as of the effective date on
the Initialization Report Run Control.
 9

JOBCODE*

Loaded from HRS

Explanation

This pulls the Job Code from HRS as of the effective date on
the Initialization Report Run Control.
 10

JOBCODE
DESCRIPTION

Loaded from HRS

Explanation

This pulls the Job Code Description from HRS as of the effective date
entered on the Initialization Report Run Control.
 11


ORIGINAL
START DATE


Loaded from HRS


Explanation


This pulls the Original Start Date (first date of hire at UW System across
all jobs) from the HRS as of the effective date entered on the Initialization
Report Run Control.
 12


LAST ASSIGNMENT
START DATE


Loaded from HRS


Explanation


This pulls the Last Assignment Start Date (most recent date of hire of a
particular job) from HRS as of the effective date entered on the
Initialization Report Run Control.
 13

REHIRED ANNUITANT

Loaded from HRS

Explanation

This pulls the Rehired Annuitant Flag from HRS as of the effective date
entered on the Initialization Report Run Control.
 14

CONTINUITY CODE

Loaded from HRS

Explanation

This pulls the Continuity Code from HRS as of the effective date entered
on the Initialization Report Run Control.
 15



CURRENT HRS FTE*



Loaded from HRS



Explanation

Note

The FTE for this employee record as it exists in HRS.

Changing the FTE here WILL NOT change the FTE in HRS.  It may
cause an error in the load.
 16





PLANNED FTE (FOR
REDBOOK)*





Defaults to HRS FTE
(editable)




Explanation


Note


The FTE for this employee record as you would like it to exist in the
budget system.

This will default load as the FTE from HRS.  If you edit this FTE, it
will change the total budgeted amount that interfaces back to the
budget system.
 17

PAY BASIS*

Loaded from HRS

Explanation

This pulls the Pay Basis from HRS as of the effective date entered on
the Initialization Report Run Control.
 18


COMPRATE
(1 FTE)*


Loaded from HRS


Explanation


For Hourly employee records, this is the hourly rate.  For salaried employee
records, Comprate will be equal to Annual Full Time Pay Rate regardless
of if they are Calendar or Annual pay basis.  Assumes 1 FTE.
 19







ANNUALIZED
FULL TIME RATE
(1 FTE)






Loaded from HRS







Explanation

Equation


Note


A job's annual salary for the employee record if it is 1 FTE.  Assumes 1 FTE.

[
For Hourly Employee Records only]
= Comprate * 2080

The equations in this spreadsheet use 2080 as the total hours worked annually.
While HRS is set up to use 2088, all CAT and budgeting processes will continue
to use 2080.
 20





ANNUALIZED BASE
SALARY (ADJUSTED
FOR FTE)




Loaded from HRS





Explanation

Equation

Note

Annual salary adjusted for FTE.

= Annualized Full Time Rate * FTE

Value will equal Annualized Full Time Rate for 1 FTE employee records, but will
not equal for <1 FTE employee records.
 21

SALARY PLAN

Loaded from HRS

Explanation

This pulls the Salary Plan from HRS as of the effective date entered on the
Initialization Report Run Control.
 22

SALARY GRADE

Loaded from HRS

Explanation

This pulls the Salary Grade from HRS as of the effective date entered on the
Initialization Report Run Control.
 23

APPROVED FOR
OVER MAX

Loaded from HRS

Explanation

This pulls the Approved for Over Max flag from HRS as of the effective date
entered on the Initialization Report Run Control.
 24

UNION CODE

Loaded from HRS

Explanation

This pulls the Union Code from HRS as of the effective date entered on the
Initialization Report Run Control.
 25

PROBATION TYPE

Loaded from HRS

Explanation

This pulls the Probation Type from HRS as of the effective date entered on the
Initialization Report Run Control.
 26

EXPECTED JOB
END DATE*

Loaded from HRS

Explanation

This pulls the Expected Job End Date from HRS as of the effective date entered
on the Initialization Report Run Control.
 27



EFFECTIVE
DATE OF
RATE/FUNDING
CHANGE

Loaded from HRS



Explanation



The date as of which the new base salary and funding information entered will
be effective in HRS.  Should default as the first of the following fiscal/academic
year depending on the employee class.

 28




MINIMUM HOURLY
AS OF 7/1




Loaded from HRS




Explanation


Note

The salary minimum that exists for hourly employee records in HRS as of 7/1
of the next Fiscal Year.

This value will be the current minimum in the first year of the biennium, and
the future minimum for the second year of the biennium.
 29




MIDPOINT HOURLY
AS OF 7/1




Loaded from HRS




Explanation


Note

The salary midpoint that exists for hourly employee records in HRS as of 7/1
of the next Fiscal Year.

This value will be the current midpoint in the first year of the biennium, and the
future midpoint for the second year of the biennium.
 30

MAXIMUM
HOURLY AS OF 7/1

Loaded from HRS

Explanation

The salary maximum that exists for hourly employee records in HRS as of 7/1
of the next Fiscal Year.
 31




MINIMUM ANNUAL
AS OF 7/1




Loaded from HRS




Explanation


Note

The salary minimum that exists for salaried employee records in HRS as of 7/1
of the next Fiscal Year.

This value will be the current minimum in the first year of the biennium, and
the future minimum for the second year of the biennium.
 32




MIDPOINT ANNUAL
AS OF 7/1




Loaded from HRS




Explanation


Note

The salary midpoint that exists for salaried employee records in HRS as of 7/1
of the next Fiscal Year.

This value will be the current midpoint in the first year of the biennium, and the
future midpoint for the second year of the biennium.
 33




MAXIMUM ANNUAL
AS OF 7/1



Loaded from HRS




Explanation


Note

The salary maximum that exists for salaried employee records in HRS as of 7/1
of the next Fiscal Year.

This value will be the current maximum in the first year of the biennium, and the
future maximum for the second year of the biennium.
 34



MERIT PAY RATE
ADJUSTMENTS*



Auto-populated



Explanation



All merit based adjustments should be entered in one of the two columns after this.

The Action Reason of "Merit" will be locked.  If the employee record is not
receiving this adjustment, leave this section blank.
 35




MERIT INCREASE
AMOUNT ($)




Entered Field




Explanation


Note

The dollar amount increase for merit.  A user should enter an adjustment to an
hourly employee's hourly rate or a salaried employee's full time rate.

You can only enter EITHER a $ increase OR a % increase.  If you enter both,
only the $ increase will register.
 36



MERIT INCREASE
AMOUNT (%)



Entered Field



Explanation

Note

The percentage increase for merit.

You can only enter EITHER a $ increase OR a % increase.  If you enter both,
only teh $ increase will register.
 37



MERIT INCREASE
AMOUNT ($)
(ANNUALIZED
& ADJ. FOR FTE)

Auto-calculated



Explanation

Note

The annual dollar amount given to this employee record for merit, adjusted for FTE.

The total at the top sums all of the values in the row beneath it.

 38





MERIT INCREASE
AMOUNT ($)
ANNUALIZED
AS IF 1 FTE



Auto-calculated





Explanation



Note

The dollar amount given to this employee record for merit, as if 1 FTE.   This will
equal column 35 if you entered in a dollar amount, or calculate it if you entered
a percentage.

The total at the top sums all of the values in the row beneath it.  This value will differ
from column 37 if the employee is <1 FTE.
 39


MERIT INCREASE TO
COMPRATE ($)
(1 FTE)*

Auto-calculated


Explanation


The dollar amount that the employee's comprate will increase.  For an hourly
employee this is the amount their hourly rate will increase, for a salaried
employee this is the increase to their annual rate.
 40


UPDATED ANNUALIZED
BASE SALARY (1)
(ADJ. FOR FTE)

Auto-calculated


Explanation


The new annual salary for the employee, taking the merit increase into account.
This number is calculated s if the employee is 1 FTE.

 41,48, 55, 62



OTHER PAY RATE
ADJUSTMENT #1 (OR
#2 OR #3 OR #4) ACTION
REASON*

Entered Field



Explanation

Note

Drop down of PAY action reactions to enter additional compensation changes.

Excludes merit action reason as it should be entered in Columns 35/36.
Can add up to 4 Other Pay Rate Adjustments.
 42, 49, 50, 63




OTHER INCREASE
AMOUNT ($)




Entered Field




Explanation


Note

The dollar amount increase for any adjustment other than merit.  This number
should be annualized and as if 1 FTE.

You can only enter EITHER a $ increase OR a % increase.  If you enter both,
only the $ increase will register.
 43, 50, 57, 64



OTHER INCREASE
AMOUNT (%)



Entered Field



Explanation

Note

The percentage increase for an adjustment other than merit.

You can only enter EITHER a $ increase OR a % increase.  If you enter both,
only the $ increase will register.
 44, 51, 58, 65



OTHER INCREASE
AMOUNT ($)
(ANNUALIZED & ADJ.
FOR FTE)

Auto-calculated



Explanation


Note
The annual dollar amount given to this employee record for other increases.  Adjusted
for FTE.

The boxed total at the top of this row sums all of the values in the row beneath it.
 45, 52, 59, 66



OTHER INCREASE
AMOUNT ($)
ANNUALIZED
AS IF 1 FTE

Auto-calculated



Explanation

Note

The dollar amount given to this employee record for other increases, as if 1 FTE.

The boxed total at the top of this row sums all of the values in the row beneath it.

 46, 53, 60, 67


OTHER INCREASE
TO COMPRATE ($)
(1 FTE)*

Auto-calculated


Explanation


The dollar amount that the employee record's comprate will increase.  For an hourly
employee record this is the amount their hourly rate will increase, for a salaried
employee record this is the increase to their annual rate.
 47, 54, 61, 68




UPDATED
ANNUALIZED
BASE SALARY
(2, 3, 4, or 5)
(ADJ. FOR FTE)

Auto-calculated




Explanation




The new annual salary for the employee record, taking all prior rate increases into
account.  Please note this is a running total of all increases given.  Adjusted for FTE.



 69


TOTAL ANNUAL
ADJUSTMENT
(ADJ. FOR FTE)

Auto-calculated


Explanation


The total increase being given to this employee record.  This number is adjusted
for an employee record's FTE.

 70


TOTAL
PERCENTAGE
INCREASE

Auto-calculated


Explanation


The total percentage increase to their base salary that an employee record will receive.


 71



TOTAL
COMPRATE
ADJUSTMENT
(ADJ. FOR FTE)

Auto-calculated



Explanation



The value of the increase in the employee record's comprate, as compared to his
or her original comprate, due to the allocated adjustments.  Reflects actual FTE.


 72


PROPOSED
COMPRATE
(ADJ. FOR FTE)

Auto-calculated


Explanation


New employee record total comprate after adjustments.  Reflects actual FTE.


 73




PROPOSED
ANNUALIZED
BASE SALARY



Auto-calculated




Explanation




This value reflects an employee record's total annual compensation after each
adjustment (running total).  Reflects actual FTE.

The Updated Base Salary value reflects teh adjustments allocated in that set of
increases and every adjustment before it.
 74





TOTAL
COMPRATE
ADJUSTMENT
(1 FTE)



Auto-calculated





Explanation





Increase in comprate based on increases from adjustments IF employee record
were a 1 FTE.  Assumes 1 FTE.

Pay Rate Adjustment Amounts ($) were recalculated assuming 1 FTE.  Adjustment
will not change among employee records with 1 FTE, but will be greater than Total
Comprate Adjustment w/FTE for employee records with <1 FTE.
 75



PROPOSED
ADJUSTED
COMPRATE
(1 FTE)

Auto-calculated



Explanation



The new comprate of a 1 FTE employee record which includes all base adjustments.
Assumes 1 FTE.


 76




PROPOSED
ANNUALIZED
BASE SALARY
(1 FTE)


Auto-calculated




Explanation

Note


Updated base salary for a 1 FTE employee record.  Assumes 1 FTE.

If the cell becomes highlighted in red, the Proposed Annual Rate is outside the
min/max set for the salary grade and plan.  See the Minimum Annual (1 FTE)
or Maximum Annual (1 FTE) as well as Over Max or Under Min for more details.

FUNDING INFORMATION
COLUMN DATA
 COLUMNS  FIELD NAME
 ENTRY METHOD
 -  DESCRIPTION
 77 - 116









FUNDING STRING
INFORMATION*









Loaded from HRS (editable)









Explanation









The funding string information that exists for each employee record in HRS
(also known as account code).  Can be manually changed.  Maintain the following
formatting for each subsection:
  • GL Bus. Unit: UW___ (example: UWRVF)
  • Fund Code: 3 digits
  • Dept ID: 6 digits
  • Program ID: 1 digit
  • Project ID: Unique
 82, 90, 98, 106, 114


DISTRIBUTION
PERCENTAGE
FROM (#)*†
Loaded from HRS
(editable)

Explanation


Percentage of employee record's salary that is paid from the associated funding
string/account code.  Employees whose salaries are source among more than 5
sources must have their distribution percentages entered in the CAT directly.
 108, 10, 112, 114, 116

BUDGETED
AMOUNT (#)*†
Loaded from HRS
(editable)
Explanation

This is the amount of an employee record's total updated salary (adjusted for FTE)
that will be charged to a particular funding source.
 109, 111, 113, 115,
 117, 119

BUDGETED FTE
(#)*†, TOTAL
BUDGETED FTE

Loaded from HRS
(editable)

Explanation


Total Planned FTE* Distribution % (shows FTE by funding source).


   †



Explanation


Indicates that these three fields are based off of one another.  If a formula in one
of these fields is overwritten, the rest of the fields should be checked to ensure
that the values are correct.
 107



FUNDING OK
TO LOAD?  (NO:
WILL NOT PASS
TO HRS)*

Loaded from HRS
(editable)


Explanation



A yes/no drop down to indicate whether or not the funding information should
load into HRS.  Select No if you would like to budget different funding than
what should exist in HRS for the next Fiscal Year.  This data will load to the CAT
no matter what is selected.
 118

TOTAL
PERCENTAGE

Auto-calculated

Explanation

Sum of percentage distributions.  Must = 100% otherwise cell will automatically
highlight in red.
 119

TOTAL
BUDGETED FTE

Auto-calculated

Explanation

Sum of all budgeted FTE's.  Should equal the total FTE to be passed to the budget
system for the next fiscal year.


HIDDEN COLUMNS
COLUMN DATA
 TAB  COLUMNS  ENTRY METHOD
 -  DESCRIPTION
Comp Admin
Tool Tab
CG, CH, CO, CP,
CW, CX, DE, DF,
DM, DN
Auto-populated Explantion Hidden cells, located in the columns listed, are
used for calculations in the spreadsheet, but never
need to be edited.  If you see a column with a bright
blue header, hide it by right-clicking the column and
selecting "Hide."



ADDITIONAL RESOURCES:

Related KBs: Related Links:
  • N/A




Keywords:Compensation Administration Tool IPS IR Initialization Report Excel planning budget splits split fund funding base salary FTE merit increase hourly lump A Basis C Basis H Basis project program vacant position AAP discretionary UDDS redbook adjust adjustment comprate pay rate distribution percentage sum   Doc ID:60506
Owner:Chris L.Group:Human Resource System (HRS)
Created:2016-02-05 09:59 CSTUpdated:2017-12-11 12:31 CST
Sites:Human Resource System (HRS)
Feedback:  0   0