Instructions

1. Open the IPS template.

a.  Different browsers will have different opening processes.

i. For Microsoft Edge, 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

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 (Column A) (Business Unit) to Column 33 (Excel Column AG) (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.

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" or "Values (V)" on a clipboard (image below).  This will paste the data as values.

e.  Funding Data:  Return to the Initialization Report.  Starting in cell AH13, select all of the data from Column 77 (Excel Column AH) (GL Business Unit 1) to Column 116 (Excel Column BU)  (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.

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")(Excel Column CA, Column 77).

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 (Excel Column BU) (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 (Excel Column BU) (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."

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 (Excel Column V).

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

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

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."

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.

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.

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

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.

  

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.

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:  Compensation Administration Tool 1.1 New Hires Business Process in CAT

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:  Running the IPS Upload to Additional Adjustments Pages (AAP) for CAT

Column / Cell Guide and Explanation

To the top

• UW Shared Services
• Office of Technology Services (OTS) Helpdesk