Pre-Template Work Steps

Coordinate with Budget Committee leads to identify any changes to the budget Guidance (e.g., changes to categories, travel rollup, bridge funding).
- Co-lead updates Budget Guidance document with changes identified
- Review by CoS/ADs
- Ensure that the Budget Category Definitions and other decisions are finalized before work is started on the template to avoid duplication of effort.
- Present updated template to the committee to review changes and finalize template.

New Fiscal Year Budget Template Setup

Create a new SFY folder on the Drive - NCSA State and ICR Budget Planning Folder

Create new folders for each Directorate within the new fiscal year folder, and update directorate names as needed.

Create a copy of the previous year's directorate with the largest number of groups' budgets, and name the file "SFYxx Budget Template" ... Historically, the Engagements directorate file was utilized. Save as new budget year's template with naming convention SFYxx Budget Template
Remove all the data and restore the tab names to the default naming conventions:
- XXX Directorate Personnel
- XXX Directorate Non-Personnel
- GroupN Personnel
- GroupN Non-Personnel

Once approval from the Budget Committee is received, utilize the new SFYxx Budget Template to fill in the data and create a new file for each directorate using the naming convention SFYxx Directorate Name Budget and save it inside each directorate's folder.
- Would need to create a Personnel tab and a Non-Personnel tab for each group/manager based on the org chart.

Directorate Budget File

Data tabs

These tabs are where data is pulled from using a formula to populate the Directorate and Group Tabs.
Hidden from users before distributing.

Budget Categories

Update based on Budget Guidance
Will show a drop-down on each tab accordingly using a formula

Account Spending Detail

Referenced by each tab to get previous year's approved amounts
Data from BA4 - Activity Listing with Sub Type report
- Ensure only Activity Code Type - Budgeted is included
- Remove CDDRs, Faculty Fellows, and MOUs from the list
Override the previous year's data with the current report on Google Sheet
Manually add the following columns:
- Budget Short Name - removing fiscal year from Name column - use the RIGHT formula like such: =right(C2,len(C2)-5)
- CFOPA + Title - combines Budget Short Name, Activity Code, and CFOP columns using this formula: =D2&" "&B2&" "&A2
- Approved Budget per previous file - manual check of last year's Account Spending Detail tab
- Change - calculated as difference between Approved Budget per previous file and Updated BA4 budget as of Date

Pivot - Account Spending Detail

Pivot table of the Account Spending Detail tab
Easily shows the different directorates and groups within a directorate and the current CFOPAs each tab should have
- ICR - Non-Personnel tabs
- State - Personnel tabs
If the Directorate and Group names are the same, then those belong to the Directorate level tabs
Pivot table editor:
- Rows:
  - Org Directorate, Ascending
  - Org Group, Ascending
  - CFOP Type, Ascending
  - CFOPA + Title, Ascending
- Values:
  - Approved Budget per previous file, Summarize by SUM

NCSA Staff

Referenced by Personnel tabs for Salary with 3% merit increase amount data
Data from MIS Savannah - Personnel Tool report
- Remove 0% FTE and GS Type
Manually add the following columns:
- Org will originally be formatted as such: CONSULTING/BusIT/INNOVATION/ITSM
- From Data menu > Split text to columns function > choose Custom separator "/"
- Insert 4 columns and label them as follows:
  - Directorate
  - Group
  - Subgroup
  - Specialty
- FTE is usually a whole number like 100 ... Convert into decimal amount on FTE Amount column
- Salary with 3% merit increase
  - Calculate current salary x 1.03.
  - We are unsure what the actual merit increase will be while creating the spreadsheets.
  - 3% has been a standard amount and will stay the same for budget planning purposes even if the actual increase turns out differently.

Directorate tabs

Directorate Personnel

Sample Directorate Personnel tab

Important Notes for Directorate Personnel tab

Update header for Directorate Name
Approved columns A, B, and C are hidden until after Director Gropp has approved the budget
Summary portion (in orange)
- Update to appropriate fiscal years
- All formulas are summaries of Current CFOPA and New CFOPA portion
Current CFOPA portion:
- Richelle adds CFOPA and Title information for the Directorate based on Pivot - Account Spending Detail tab
- Sum of Budget Request and Sum of FTE Request - summarizes details, will not populate correctly unless manager selects CFOPA and Title from below
- Approved prior year Budget - pulled from Account Spending Detail tab using formula =sumif('Account Spending Detail'!$E$2:$E$143,D6,'Account Spending Detail'!$K$2:$K$143)
- Check to close account - manager should check if they want to close the current CFOPA moving forward
- Comment on Purpose of this Account - manager provides more details on why this CFOPA should exist
New CFOPA portion:
- Managers fill in name of new CFOPA being requested
- Sum of Budget Request and Sum of FTE Request - summarizes details, will not populate correctly unless manager selects CFOPA and Title from below
- Comment on Purpose of this Account - manager provides more details on why this CFOPA should be created
CFOPA for Bridge Funding portion:
- For Directorate Requests only - summarizes details, will not populate correctly unless manager selects CFOPA and Title from below
- Bridge Funding Requests from Group Personnel tabs - summarizes all other Bridge Funding requests from all group's Personnel tabs for the Directorate
Manager Status:
- Drop-down that managers choose and reflects on Status tab to update the Associate Director
  - In Progress
  - Ready for Review
Details - color-coded cells:
- Gray - Manager can edit and override information on these cells
  - Richelle adds names of staff from prior year's file and their prior approved allocation manually
  - Richelle has a formula for the Annual Salary (with 3% increase if not TBD position) column that pulls from the NCSA Staff column, can be overridden by manager as necessary
  - Staff name should match the format and name on Personnel Tool so that the formula works accurately
- White - Richelle updates, not editable
- Blue - Formula, not editable, summarized in above portions
- Red - only shows up when Dr. Gropp approves the final budget

Directorate Non-Personnel

Sample Directorate Non-Personnel tab

Important Notes for Directorate Non-Personnel tab

There is a formula for the Directorate Name to populate automatically
Approved column A is hidden until after Director Gropp has approved the budget
Current CFOPA portion:
- Richelle adds CFOPA and Title information for the Directorate based on Pivot - Account Spending Detail tab
- Current SFY Budget request - summarizes details, will not populate correctly unless manager selects CFOPA and Title from below
- Approved prior year Budget - pulled from Account Spending Detail tab using formula =sumif('Account Spending Detail'!$E$2:$E$143,D6,'Account Spending Detail'!$K$2:$K$143)
- Check to close account - manager should check if they want to close the current CFOPA moving forward
- Budget Justification - manager provides more details on why this CFOPA should exist
New CFOPA portion:
- Managers fill in name of new CFOPA being requested
- Current SFY Budget Request (include requested title) - summarizes details, will not populate correctly unless manager selects CFOPA and Title from below
- Budget Justification - manager provides more details on why this CFOPA should be created
Strategic Travel Funding Requests from Group Non-Personnel tab - summarizes all other Strategic Travel Funding requests from all group's Non-Personnel tabs for the Directorate
Manager Status:
- Drop-down that managers choose and reflects on Status tab to update the Associate Director
  - In Progress
  - Ready for Review
Details - color-coded cells:
- Gray - Manager can edit and override information on these cells
  - Richelle copies all prior year requests that has a value of "Recurring Expense" under Frequency of Expense column
- Red - only shows up when Dr. Gropp approves the final budget

Group tabs

Group Personnel

Sample Group Personnel tab

Important Notes for Group Personnel tab

Update header for Group Name
Approved columns A, B, and C are hidden until after Director Gropp has approved the budget
Summary portion (in orange)
- Update to appropriate fiscal years
- All formulas are summaries of Current CFOPA and New CFOPA portion
Current CFOPA portion:
- Richelle adds CFOPA and Title information for the Directorate based on Pivot - Account Spending Detail tab
- Sum of Budget Request and Sum of FTE Request - summarizes details, will not populate correctly unless manager selects CFOPA and Title from below
- Approved prior year Budget - pulled from Account Spending Detail tab using formula =sumif('Account Spending Detail'!$E$2:$E$143,D6,'Account Spending Detail'!$K$2:$K$143)
- Check to close account - manager should check if they want to close the current CFOPA moving forward
- Comment on Purpose of this Account - manager provides more details on why this CFOPA should exist
New CFOPA portion:
- Managers fill in name of new CFOPA being requested
- Sum of Budget Request and Sum of FTE Request - summarizes details, will not populate correctly unless manager selects CFOPA and Title from below
- Comment on Purpose of this Account - manager provides more details on why this CFOPA should be created
CFOPA for Bridge Funding portion - summarizes all Bridge Funding requests from details when manager selects CFOPA for Bridge Funding under CFOPA and Title drop-down
Manager Status:
- Drop-down that managers choose and reflects on Status tab to update the Associate Director
  - In Progress
  - Ready for Review
Details - color-coded cells:
- Gray - Manager can edit and override information on these cells
  - Richelle adds names of staff from prior year's file and their prior approved allocation manually
  - Richelle has a formula for the Annual Salary (with 3% increase if not TBD position) column that pulls from the NCSA Staff column, can be overridden by manager as necessary
  - Staff name should match the format and name on Personnel Tool so that the formula works accurately
- White - Richelle updates, not editable
- Blue - Formula, not editable, summarized in above portions
- Red - only shows up when Dr. Gropp approves the final budget

Group Non-Personnel

Sample Group Non-Personnel tab

Important Notes for Group Non-Personnel tab

There is a formula for the Group Name to populate automatically
Approved column A is hidden until after Director Gropp has approved the budget
Several groups usually do not utilize this tab as they have MOU or Grants or other non-budgeted ICR they can use
Current CFOPA portion:
- Richelle adds CFOPA and Title information for the Directorate based on Pivot - Account Spending Detail tab
- Current SFY Budget request - summarizes details, will not populate correctly unless manager selects CFOPA and Title from below
- Approved prior year Budget - pulled from Account Spending Detail tab using formula =sumif('Account Spending Detail'!$E$2:$E$143,D6,'Account Spending Detail'!$K$2:$K$143)
- Check to close account - manager should check if they want to close the current CFOPA moving forward
- Budget Justification - manager provides more details on why this CFOPA should exist
New CFOPA portion:
- Managers fill in name of new CFOPA being requested
- Current SFY Budget Request (include requested title) - summarizes details, will not populate correctly unless manager selects CFOPA and Title from below
- Budget Justification - manager provides more details on why this CFOPA should be created
Strategic Travel Funding Requests from Group Non-Personnel tab - summarizes all other Strategic Travel Funding requests from all group's Non-Personnel tabs for the Directorate
Manager Status:
- Drop-down that managers choose and reflects on Status tab to update the Associate Director
  - In Progress
  - Ready for Review
Details - color-coded cells:
- Gray - Manager can edit and override information on these cells
  - Richelle copies all prior year requests that has a value of "Recurring Expense" under Frequency of Expense column
- Red - only shows up when Dr. Gropp approves the final budget

Overall Summary tabs

Pulls all data from tabs within the Directorate Budget File
Flows into SFYxx AD Team State & ICR Budget Report
ADs usually use these tabs when reviewing the data for their Directorate.

Personnel Approval

Pulls from all Directorate and Group Personnel tabs
New columns for this tab only:
- Directorate Comment
- SFYxx Months Approved - no longer used since the FY24 process. When ADs require changes to a specific line item, they go to the source data and revise it there. No record of original amounts and allocations proposed by managers is kept in this process.
- SFYxx Allocation Approved - no longer used since the FY24 process. When ADs require changes to a specific line item, they go to the source data and revise it there. No record of original amounts and allocations proposed by managers is kept in this process.
- Total Salary on State Approved - no longer used since the FY24 process. When ADs require changes to a specific line item, they go to the source data and revise it there. No record of original amounts and allocations proposed by managers is kept in this process.
- Proposed / Updated by ADs → eventually becomes Approved amount.
- Group - vlookup formula referencing the Account Spending Detail tab, =iferror(vlookup(J3,'Account Spending Detail'!$E$2:$G$152,3,false),"")
Starting with CFOPA and Title column, use importrange formula to include all details on each tab by using the following formula: =importrange("https://docs.google.com/spreadsheets/d/1Bi7bGryfms1AcGXfhJLuWnAChq9C21csgDtIhJ6UVIM/edit#gid=2050415953","ENGAGE Directorate Personnel!D27:O50")

Non-Personnel Approval

Pulls from all Directorate and Group Non-Personnel tabs
New columns for this tab only:
- Directorate Comment
- Approved Amount - no longer used since the FY24 process. When ADs require changes to a specific line item, they go to the source data and revise it there. No record of original amounts and allocations proposed by managers is kept in this process.
- Group - vlookup formula referencing the Account Spending Detail tab, =iferror(vlookup(J3,'Account Spending Detail'!$E$2:$G$152,3,false),"")
Starting with CFOPA and Title column, use importrange formula to include all details on each tab by using the following formula: =importrange("https://docs.google.com/spreadsheets/d/1Bi7bGryfms1AcGXfhJLuWnAChq9C21csgDtIhJ6UVIM/edit#gid=220118304","ENGAGE Directorate Non-Personnel!B32:K50")

Status

Lists Directorate Name and Groups within the Directorate
Starting in FY24, included tabs that did not require State or ICR budget requests for whole-picture purposes only
Includes high-level summary that flows into the ABRRT tab of the SFYxx AD Team State & ICR Budget Report
Sample:

SFYxx AD Team State & ICR Budget Report

Compilation report for final steps of the Budget Planning Process
Basis for the NCSA FYxx State & ICR Budget Report Google Doc and the Annual Budget Report and Request Template

--------------------------------------------

Transition from Kaylyn to Richelle:

Overview

The budget templates for each directorate have interconnected sheets that are then interconnected to the cabinet-level summary and group reports. Careful consideration should be made when the template is updated to ensure that formulas are not broken.

The formula most used to share data across sheets is importrange. This formula allows you to insert a spreadsheet link and specify what range you want to pull into the sheet you are inserting into. This allows for the data to be viewed but not edited. If you type in anything on the sheet, it will remove all the imported data imported unless the typed characters are removed.

When the new templates are created, it is imperative that locks be placed on cells that have formulas and should not be edited. Without these locks managers/PMs/etc. will overwrite data that should not be edited.

Coloring of cells:

Grey = cells that we are requesting that managers fill in information

Pink/Red = cells where approved budget information will show (these cells should always be locked)

Blue = cells where formulas are calculated (these cells should always be locked)

White = cells where we have provided information (these cells should always be locked)

Timeline to get templates done

November 1st: Finish defining and updating Budget Category Definitions, etc before work is started on the templates. What information did they look at/not on the reports.
February 1st: Directorate templates ready for manager completion
March 1st: Cabinet sheet ready (before starting this sheet you need to make sure that no additional lines will be added to any of the group sheets. Once you start on this sheet adding lines or changing categories will require you to update almost every formula in the cabinet sheet!
April 1st: Group Budget Reports Ready

Helpful Links

NCSA Org Chart: https://internal.ncsa.illinois.edu/mis/orgChart/ - first two columns, all employees

NCSA State and ICR Budget Planning Folder

FY23 State Budget Folder

Directorate Budget Sheet Overview

Link to FY22 Engagement Request, this is the sheet I would use to start the new template.

For the directorate sheets every year I would update one template and then duplicate that template when all updates were ready. This reduced the amount of duplicative updates across sheets. I always tried to use the directorate that had the most groups, and most rows in the personnel tabs to start the template. This way you have the least amount of row cleanup to do. Engagements has the most number of groups so that is why I linked their sheet above. You will create a copy of this sheet in a new folder for FY23 budget process and then clear out all the specific Engagement data, Change the name tabs back to generic names (Group1 Personnel, etc.) update the data tabs as I describe below. Double check all the formulas, and then create copies of this template for each group. Once you do that then go into each sheet and add the group information.

Cabinet State & ICR Budget Report

Link to Cabinet State & ICR Budget Report

In this report is where all the information is pulled together. I am going to start on the tabs where the requested information is pulled in and then work our way through the tabs and where the data flows.

For this next year you should be able to make a copy of this template and then update the links to reference the new budget templates. I would spend time looking at where the information flows through this sheet just so you become familiar with how it works before making those updates.

Personnel Report

This tab functions much like the Personnel approval tabs in the directorate sheets, in these sheets the information from Column G-X are pulled off the Personnel approval Directorate tabs using the importrange formula. All of these cells should be locked from edit

Column E and F were added to this sheet mid budget cycle last year, if you remove the budget group summaries from the directorate sheets I would recommend adding these columns at the directorate Personnel and Non-Personnel Approval sheets. These columns are used to summarize the data in reports that will follow

Column A-D are the cabinet approval columns, if you remember these columns should be viewable in the directorate approval tabs, since they were added mid budget cycle they are not currently there. You should have column A-C unlocked for cabinet edit, but I would lock column D. Again for this I would pull the directorate approval totals so that if the cabinet agrees, they do not need to change anything.

Each directorate is listed on this sheet, I have included one free row between each directorate just to help the spreadsheet developer see where the delineation between directorates is.

Starting in Column Y there are new columns added at this level that I created to try and help the cabinet see the long term impacts of today’s decisions. In column Y-AN was my initial categorization and analysis. The cabinet wanted to add different categories so starting in column AO we re-did this analysis. Information from my initial analysis flows onto the Analysis tab. This is the data we presented to the cabinet in the state and ICR budget report to show how we would spend down the carryforward in future years if we approved the budget at the levels requested.

The cabinet analysis with the new categories was pushed onto the Cabinet Analysis Tab. Same concept as the Analysis tab, but with their updated categories.

Non-Personnel Report

This worksheet is again very similar to the Personnel Report worksheet, everything from column C-N should be locked from edit. Column A and B should flow back into the directorate sheets. Columns O-S were used to populate the Analysis and the Cabinet Analysis tabs, again these were used to give the cabinet an idea of how the costs would impact NCSA going forward if they continued at the same level. When I was updating these tabs I would read the details of the line item request in column N to determine what numbers I should enter in future years. If they said it was a one time request then it did not extend, some comments said things like we reduced this because of COVID, we typically ask for $35k but this year we only asked for $15k so over the next three years I would re-increase that budget back to pre-covid budget request amount.

Approved Budget Summary

This sheet I used to pull all the information onto one sheet, the colored cells with the same color should all match. If you use this sheet to summarize all information, right now it does not include much of the non-personnel detail, you could use this as the way to create all the individual tables and charts that the cabinet likes to see.

Special Considerations

This tab was created for the cabinet to be able to determine if they could afford to hire deployable capacity. Here they are able to enter by directorate what positions they want to evaluate and then in column I and J run two scenarios and see the cost of those scenarios in rows 27-33. If they choose to implement a scenario into the projections you can use a drop down in row 37 to select which scenario costs to use. Make sure to lock any cells that cabinet members should not be able to edit.

Center Wide Requests

This tab is a view of the detailed request that came in for laptops and Swag, this is the same information that is found on the bottom of the non-personnel tabs in business IT and marketing and communications. This is an informative tab for the cabinet and should be locked.

Summary By Directorate

This data is information from the Approved Budget Summary Tab reorganized into tables and charts that the cabinet is more familiar with for the overall report. This sheet should be locked from all edit access, except for you! You can click through the tables to see where the data is pulling from specifically, and what formulas I used to pull the data.

TBD Review

This was a manually created tab to pull together the TBD information. It would be useful if in this next year all new and vacancy position requests had TBD before the role name that they were wanting to hire, that way you could filter this information instead! The one thing that we were doing on this sheet outside of viewing the data was trying to see what positions were requested the year before, and if they were being re-requested for a full year again. If the directorates were requesting a full year of support for an FTE, did they have an HRT open already or not? Was the position open yet? Etc. Trying to do an analysis of actual expected impact of new hires on the budget and if what was being requested was reasonable, or not.

Summary of Accounts

This tab is for the Business Office to be able to see the approved numbers for each approved CFOP. you can click through the list to see where all these formulas are pulling from in the workbook.

Approved Account Summary

Pretty sure I had forgotten that there was the summary of accounts tab, I believe the data across these two tabs is the same.

NCSA Approved Budget

This tab is the final roll up of data for the cabinet, I think it will be simple for you to see where this information is pulled from to recreate next year.

Other Funding Summary

Data from this tab is provided by Angela, I believe I copied in exactly what she sent me this last year so you should be able to track with Angela where that information was pulled from! The first set of data is summarized starting on row 144, the two charts were provided to the cabinet in the State & ICR Budget Report. The second set of data is summarized over in column P starting in row 206, this was another chart provided in the cabinet report. This information is important to give the cabinet a picture of what we have done in the past.

New Account Detail

We had added the two new columns in the personnel report and non-personnel report tabs that show the directorate and group information. Those formulas that pull in the name were referencing the Account Detail tab where those groups were pulled out of BA4, but for the new accounts that data is not listed in BA4. So on this tab I have first used the importrange formula over in cell E2 to pull in the new account name requests off each of the group sheets. As you make your way down column E you will see all the individual import range formulas. Again, this was not a copy and paste because I created this before all the groups were done, and you don’t want to have to try to catch add ons after you have finished things! Columns F and G I hand keyed in as I created the import range formulas for each group. I did a copy and paste of data into columns A-C from columns E-G. You cannot sort on the columns using the import range formulas, so copying this info over and pasting at the end allows you to filter this data alphabetically as required to do the vlookup in the personnel report and non-personnel report tab where the directorate information is pulled in.

Formula Errors:

When using the importrange formula where we are pulling in lists from multiple sources into one sheet, if the range extends beyond the available number of rows you will get a reference error, you can correct this by adding additional rows between the formulas.

Protect Range:

Right click on any cell in the workbook, select protect range from list. Follow prompts on sidebar, which will be to select the range you want to protect and to then add any members you want to still have access to edit that range.

State & ICR Budget Report

This report is mostly handled by Angela. I would update all the tables and charts from the Cabinet State & ICR Budget Report workbook. I think as you look over this report you will be able to see where these tables were pulled from.

Link to FY22 State & ICR Budget Report

FY22 Original timeline

Bill Gropp Sends out Budget guidance and charge email February 1st Done - 2/3/21
BWG sent manager email and invite - Done - 2/3/21
Scheduled training making sure majority of leadership can attend.- Done 2/10
Managers prepare budgets February 1st - March 30th (if training sessions need to be held these should be scheduled between February 1st and February 15th) OK
Directorate Leads review budgets and make initial pass of approvals and recommendations for any money for improvements/special projects - began as they can through the Feb-March budget prep meeting but needs completed by April 15th. OK - March 30th

When do we want the information from AD to compile the information for the cabinet. April 12th 1 week to prepare or the 9th? - March 30th
Cabinet to review April 19 monday and Friday 11-1 through May 15th.

-----------------

Page tree

Budget Template Summary