L&S Budget Build: Departmental Planning Spreadsheets (DPS)

This page contains detailed instructions for the Departmental Planning Spreadsheets (DPS) which are used by L&S departments/programs during the annual budget build. This page has been updated for fiscal year 2025.

The deadline for fiscal year 2025 has passed. The instructions are archived below.

Fiscal year 2025 instructions

Documents & deadlines

  • L&S Budget Office Staff will distribute the FY25 DPS on Monday, March 4, via Box.
  • Download from Box and save locally before making changes.
  • Upload completed DPS via Box by March 18, 2024.
  • Box locations:
    • Download: Budget Office Folder / Annual Budget Process / FY25
    • Upload: Budget Office Folder / Annual Budget Process / FY25 / Return Documents

Background: Departmental Planning Spreadsheet

The Departmental Planning Spreadsheet (DPS) is a tool used by L&S during the annual budget build. The DPS shows personnel information and funding information for the upcoming fiscal year. It will include future-dated changes that were known to the L&S Budget Office as of late February. Departments/Programs should review the DPS for accuracy and make updates as needed.

The DPS spreadsheet contains two worksheets/tabs:

  • DPS: This is the main spreadsheet to review and update. It contains current appointments that are expected to continue into FY25. Review for accuracy, anticipated rate/title changes, departures, and funding changes.
  • New Hires_Position Changes: New appointments that are not listed on the DPS may be listed on this worksheet.

The DPS does not include short-term staff, teaching assistants or other graduate assistants. See L&S Budget Build: Overview & Timeline for links and templates to related Budget Build exercises.

Reviewing the 'DPS' worksheet

The DPS includes employees who are expected to be funded by the department/program in the upcoming fiscal year. It's sorted by the employee classification column, and then the employee's last name.

There are eleven rows for each appointment. Do not insert or delete rows; this will disrupt the formulas.

  • The first row is a “summary” row which displays each employee’s job and salary data.
  • The summary row is followed by ten “detail” rows to accommodate multiple funding rows.
Reviewing the DPS worksheet
Changes to Report Instructions
Rate & title changes
Funding
  • Budget information is entered for the entire fiscal year; the DPS does not allow for budgeting by semester. Employees with mid-year changes will need manual updates in HRS.
  • Funding fields are defined below.
  • Funding distribution:
    • 'Dist %': this field shows the percent that a specific funding source is contributing to appointment.
    • 'Dist % check' column totals the values from the Dist % rows. This value must equal 100% for all appointments.
  • To change distribution % for an existing funding source, modify Dist %.
  • To remove funding source, modify Dist % to zero. Do not update other values on the row.
  • To add a new funding source, use a blank row to add funding string. Some projects may be unknown at this time; add as much of the funding string as possible. The employee's funding will need to be updated manually in HRS.
Departures

Appointment Percentage Changes

  • Unpaid leaves
  • Sabbaticals
  • Adjust the Bdgt FTE field and add comments with additional info.
  • Reminder: the funding distribution ('dist % check') will still equal 100% for part-time appointments.

'DPS' Worksheet fields

  • Do not update the gray shaded fields; these fields contain information or formulas that cannot be modified.
  • Fields with white shading under orange headers may be updated, and their shading will change to light green as changes are made.
S/D rows
Column Field Name Description
A Sum or Det Row

Indicates if the row is a 'Summary' or 'Detail' type row.

  • S rows: Details and updates related to the appointment (base rate, appointment %)
  • D rows: Changes to funding
Employee Data From Hrs/Cat - No Action By User - Informational
Column Field Name Description
B Home Dept Job Data Deptid (UDDS) UDDS of their appointment home department; see L&S Department ID/UDDS
D Empl Class

Employee title classifications; see Employee Classifications

  • AS: Academic Staff
  • FA: Faculty
  • CP & CJ: University Staff
  • LI: Limited
  • ET1 & ET3: Post degree training
  • LI: Limited
E Job Code Job Code from HRS
F Planned Jobcode Description (Title) Job Title
G Last Name Employee last name
H First Name Employee first name
I Empl ID Employee/Person ID from HRS
J Empl Rec # Appointment's Empl Record from HRS
K Position Number Appointment's Position Number from HRS
L CAT Pay Basis Pay-basis
  • A = Annual / 12-month
  • C = Academic / 9-month
  • H = Hourly
O Comp Rate (1 FTE)

Current full-time salary rate.

If changes are needed, use the 'Cat Pre-Merit Rate (1 FTE)' field.
Data from HRS/CAT - Update Salary Rate
Column Field Description
P Cat Pre-Merit Rate (1 FTE)

Employee's full-time salary rate; update on 'S' rows only.

  • For most employees, this value will match the 'Comp Rate (1 FTE)' field.
  • Compensation changes that were already processed in HRS will be reflected in the DPS.
  • Add notes to the 'Comments' column if changing.

Funding updates

Adjust funding on the 'D' rows for an appointment, with one funding string per row.

Funding Fields
Column Field KB link
V
 Dept Department ID / UDDS: L&S Fund Management: Department ID
W
 Fund Fund ID: L&S Fund Management: Fund Numbers
X
 Prog Program Code: L&S Fund Management: Program Codes
Y
 Proj ID

Project Number / ID: L&S Fund Management: Project Numbers

If the project is unknown at the time of reviewing the DPS, please leave this field blank. The employee's funding will need to be updated manually in HRS.
Z
 Dist %

Funding distribution: Amount the given funding string will contribute towards the appointment. Formatted as a percentage.

The values from the 'D' rows must equal 100%, for both full-time and part-time employees (calculated in 'Dist % Check).

Example: an employee with a 50% appointment funded by one funding source:
- Appointment percentage (Bdgt FTE) would be .5
- Dist % = 100%
Summary fields
Column Field Description
AA Dist % Check

No entry; displays on 'S' rows only

Sum of 'Dist %' values for the employee on the 'D' rows.

This value should equal 100% for all employees. Adjust the values in the 'Dist %' rows if needed.
AB Bdgt FTE

Appointment level in FTE format.

Examples:
- 1.0 for a 100% appointment
- 0.5 for a 50% appointment
AH Base Salary By Funding
(Adj for Bud FTE)

Salary estimate, rounded to the nearest dollar (no entry).

  • S rows: total salary for the appointment.
    • Salary basis * Bdgt FTE
    • If Hourly basis, also multiplied by 2080
  • D rows: total salary for the given funding source
    • Above calculation multiplied by the funding distribution %
AL Comments

Add comments on the 'D' rows

  • Anticipated rate/title changes
  • Departures (e.g., 'resignation eff. 6/30/24')

Add new appointments on 'New Hires_Position Changes'

If new appointments have been added to HRS, but are missing from the DPS worksheet, they may be added to the 'New Hires_Position Changes' worksheet. Departments/programs may err on the side of including these positions in the 'New Hires' tab, and the L&S Budget Office staff will review them for possible entry.

Reminder: Short-term Staff and student appointments are not included in the DPS.

'New Hires_Position Changes' Worksheet fields

Similar to the DPS worksheet, there are eleven rows per appointment.

Employee and appointment data may be entered on the top row of each set (columns A-K, with white shading). Funding data may be entered in the ten rows that follow, in columns M-Q.

Data may be entered in white shaded fields only; gray shaded fields contain formulas and may not be modified. Do not add or delete rows.

Enter Basic Employee Data
Column Field Description
A Job Data Deptid (UDDS) UDDS of their appointment home department
B Last Name Employee last name
C First Name Employee first name
D Empl ID Employee/Person ID from HRS
E Employee Record Number Appointment's Empl Record from HRS
F Empl Class

Employee title classifications; see Employee Classifications

  • AS: Academic Staff
  • FA: Faculty
  • CP & CJ: University Staff
  • LI: Limited
  • ET1 & ET3: Post degree training
  • LI: Limited
G Position Number Appointment's Position Number from HRS
H Title or Job Code Job Code from HRS
I Total HRS FTE Appointment level in FTE format.

Examples:
- 1.0 for a 100% appointment
- 0.5 for a 50% appointment
J Pay Basis

Select pay-basis

  • A = Annual / 12-month
  • C = Academic / 9-month
  • H = Hourly
K Comp Rate (1 FTE) Current full-time salary rate
L Annualized Base Rate (Adj for FTE)

Salary calculation field, no data entry.

This field calculates the employee's salary for the year. For hourly appointments, this field will first multiply the hourly rate by 2080.

For part-time appointments, this field will multiply the full-time rate by the appointment level ('Total HRS FTE' field).

Enter Funding Data

Add funding information to the white shaded rows.
Enter Funding Data
Column Field Description
M Dept

Department ID / UDDS: L&S Fund Management: Department ID
N Fund

Fund ID: L&S Fund Management: Fund Numbers
O Prog

Program Code: L&S Fund Management: Program Codes
P Proj ID

Project Number / ID: L&S Fund Management: Project Numbers

If the project is unknown at the time of reviewing the DPS, please leave this field blank. The employee's funding will need to be updated manually in HRS.
Q Dist %

Funding distribution: The amount the given funding string will contribute towards the appointment. Formatted as a percentage.

  • Update the white shaded rows only.
  • The funding % from the white rows are totaled in top, gray-shaded row for an employee. This value must equal 100%, for both full-time and part-time employees.

Example: an employee with a 50% appointment funded by one funding source.

  • Appointment percentage ('Total HRS FTE', column I) = .5
  • Dist % = 100%
R Budgeted Amount

Salary calculations, no entry.

The top row for an employee will show the salary estimate for the appointment.

The other ten rows will show the salary estimate for each funding string.
S Comments

Add additional information if needed.

Next Steps

The L&S Budget Office will review your documents and reach out if there are any questions. If you need to make changes after uploading your file to Box, please email Mary Beth Roberts. Do not upload additional copies of the spreadsheet after uploading.

L&S Budget Office Contacts

See Also


KeywordsKeywordX email to department admin, Appointment changes, Appointment details, archive emails, Bdgt FTE, Box distribution, Budget allocations, Budget build, Budget management, Budget Office, Budget preparation, Budget process, Budget review, budget worksheet, Budgeted Amount, department planning spreadsheet, DPS, email to chairs, Annual Budget Process, finance, Financial instructions, Financial planning, fiscal year 2025, Fiscal year planning, funding, Funding distribution, funding splits, FY25, FY25 DPS, How-to guide, Instructions, main overview, process, Spreadsheet template, Upload instructions, Worksheet tabs   Doc ID135272
OwnerAmanda M.GroupL&S KB
Created2024-02-07 09:17:54Updated2024-04-03 15:27:05
SitesL&S KB
CleanURLhttps://kb.wisc.edu/ghi/dps
Feedback  0   0