L&S Budget Build: Departmental Planning Spreadsheets (DPS)
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.
Changes to Report | Instructions |
---|---|
Rate & title changes |
|
Funding |
|
Departures |
|
Appointment Percentage Changes
|
|
'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.
Column | Field Name | Description |
---|---|---|
A | Sum or Det Row |
Indicates if the row is a 'Summary' or 'Detail' type row.
|
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
|
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
|
O | Comp Rate (1 FTE) |
Current full-time salary rate. If changes are needed, use the 'Cat Pre-Merit Rate (1 FTE)' field. |
Column | Field | Description |
---|---|---|
P | Cat Pre-Merit Rate (1 FTE) |
Employee's full-time salary rate; update on 'S' rows only.
|
Funding updates
Adjust funding on the 'D' rows for an appointment, with one funding string per row.
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% |
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).
|
AL | Comments |
Add comments on the 'D' rows
|
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.
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
|
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
|
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.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.
Example: an employee with a 50% appointment funded by one funding source.
|
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
- Mary Beth Roberts: marybeth.roberts@wisc.edu or Teams chat
- James Hovland: james.hovland@wisc.edu or Teams chat
- Kesha Weber: kesha.weber@wisc.edu or Teams chat