Overview

The budget templates for each directorate have interconnected sheets that then are 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. 

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. 

Data Tabs: 

The data tabs should be updated in the template before each directorate sheet is created. 

• Account Spending Detail: In this tab is information out of BA4, this is a list of all the state and ICR budgeted accounts. The budget short name field was a field I created to remove the “FY21” from the name so when entering the new data for next year you will need to make sure that row is included. At the end of this sheet I also duplicated the CFOP and Activity code column, this is necessary because of a formula you will use in a summary sheet that will show the account name, CFOP and Activity code.
• Because of the way some of the formulas work that are in other sheets the Account Spending detail tab should be in alphabetical order based on the Budget Short name column
• Budget Categories: this tab is where the dropdowns are populated from. In the second year of the process the cabinet wanted different categories for each level in the organization that is why you see three grey boxes. In this most recent year they went back to uniform categories, so the only box that needs to be updated is the data under Group Level Budgets. The way these pull into each of the group sheets is through a formula and then the data validation. If you go into a group non-personnel sheet and click on cell L33 you will see that there is a formula up in the formula bar. This was the way to allow for the user to select a Category type and then for the correct sub-categories to appear in the drop down in column D. If the cabinet goes back to multiple categories for different levels of the organization you will need to update the three sets of boxes with the correct lists and then you will need to update the formulas on the group non-personnel sheets to look at the correct set of boxes. 

Personnel Tabs:

In these worksheets, information is collected about staffing requests for the state budget. Before these sheets are sent out to the managers the staff name, salary, prior year approved FTE should be filled out. The CFOPA column could also be filled out to assist managers in requesting under the same accounts as last year. 

Columns A, B, C reflect the approved budget amounts from the Cabinet, the process was changed mid-year last year so these currently reference the cabinet approval workbook. More on this below, but this would be better suited to flow into the approval tabs in the directorate workbooks and then down to the group sheets to maintain consistency and clarity of data. These three columns should be hidden from view when the sheet is sent to the managers. 

It is important to have the appropriate number of rows entered for each group before the sheet is sent off. You do not want managers making requests to add rows, as that will require updates across the importrange formulas you have throughout the workbooks, and can be very complicated when needing to update the cabinet workbook as well. If you have to enter more rows be sure to enter them in the middle of the set instead of at the end, that way the formulas will remain intact that summarize the total request. 

Under the CFOP section in group personnel request you will enter the appropriate CFOP names into column D. These names are the account names without the year associated to them. I removed the year from the name because when the CFOP was updated it was causing confusion, and breaking formulas when the CFOP was updated after budget approval. Columns G-J should pull in from the Account Spending Detail tab. 

Non-Personnel Tabs:

CFOP names are provided by the BWG in the appropriate group tabs; information in columns D, E and F will pull in from the Account Spending Detail tab when the names are entered correctly. Will want to check that the formulas in column D, E and F are inclusive enough to include the full list of accounts on the Account Spending Detail tab. 

The Center Level account section was added this last year, this section is to collect information about the group needs for Laptops and NCSA Swag accounts that are owned by the Business IT and Marketing and Communications teams. The laptop policy may change how the laptop information is collected - if it is collected at all in this process. Each of the groups Center Level account requests are rolled up into the “Summary Center Level Request” tab on each of the directorate sheets using the importrange formula. Each of the directorate level summaries are then copied into the bottom of the Business IT non-personnel sheet and the Marketing and Communications Non-personnel sheet. There is one line in each of the two groups non-personnel sheet that uses the sumif formula to the total the full request for the Center. 

Budget Summary Tabs

These tabs were originally created to summarize the group budget requests by different types. I think these could be removed and summaries could be created off the Personnel Approval and Non-Personnel Approval tabs. To do this you will need to pull in the group name like I have done in the Cabinet approval worksheet so that you can show totals for each category and group, etc. 

Detailed Directorate Summary

This sheet currently pulls totals from each of the groups Budget Summary Tabs. These formulas would need to be updated if you remove the budget summary tabs and use the Personnel Approval and Non-Personnel Approval tabs to pull data from instead. 

Red Text: This text is based on a formula that checks totals to make sure totals match across different cells, and tries to alert when something is not pulling in correctly. All of the red text should say “okay” 

Status:

This sheet is supposed to pull in information from each of the group budget sheet dropdowns so that you can quickly see the status of each group's request in one view. This sheet was not locked last year, and if it is used again, should be locked from all edit (as I know there were some groups  who came in and typed status into this sheet rather than updating the dropdown.

Personnel Approval:

In this tab all the personnel requests from each group budget sheet are pulled in using the importrange formula. You pull in all appropriate personnel columns starting in column G of this sheet. 

The original thought behind these sheets was that the directorate leads would review and approve the line item requests and then present a full budget to the cabinet for approval and that the cabinet would not review detailed information. But, last year, the cabinet still did a line item review and approval of each budget line item. IF the cabinet wants to do this again the personnel approval sheet will need to be updated to include 3 extra columns FY2X Cabinet Months Approved, FY2X Cabinet Allocation Approved, FY2X Cabinet Total Salary on State Approved. I would add these between columns A&B. New order of the columns would be Cabinet Comment, FY2X Cabinet Months Approved, FY2X Cabinet Allocation Approved, FY2X Cabinet Total Salary on State Approved, Directorate Comment, Does this line…, etc. In this scenario the current columns B-E should be unlocked for edit by the directorate lead. I had column D and E set up to pull in the requested amount from the manager so if the directorate lead approved they didn’t need to enter any information. You might want to change coloring a bit to follow the normal coloring I listed above, in this case red is currently editable by the directorate lead. 

Special consideration needs to be given on the permissions of the approval worksheets. These are set up so that only the directorate lead, Angela, and whoever else was designated by the directorate lead could edit. We do not want managers to be able to edit the information in the approved columns since this is supposed to be driven by the directorate lead. 

Non-Personnel Approval

Same concept here as in the personnel approval sheet. Information is pulled in from each of the group request sheets, and the extra columns are added for the directorate review and approval. If the cabinet wants to have line item approval over what the directorate has approved then the additional columns will need to be added here. 

Approved Summary

This is a high level summary of the approved budgets by group and then by CFOP, this was the sheet that was intended to help the business office enter the new year budgets and be able to update the CFOP  numbers for the managers so if they needed to reference where their budgets came from they could come here to see. The CFOP names are pulled in by using the unique formula and pulling the unique list of CFOP names from the Personnel Approval tab; this same formula is used for the Non-labor list. Total approved budgets are calculated using the sumif function, these are currently looking at the directorate level approval totals, but if the cabinet does line item approval again these total columns should reference those cells. The CFOP and Activity Code columns are pulled in from the Account Spending Detail tab. I mentioned above that in the account spending detail tab you needed to add the CFOP and Activity code column to the end of the table, this is why! The vlookup formula requires that the range you are referencing be in alphabetical order, and that the information you are pulling is to the right of the first column that has the matching data. 

Other things to remember:

Approval columns in each of the group workbooks will be hidden when the sheet is sent out to the managers for completion. You will not add the formulas to pull the approved budget information into these cells until after you have completed the cabinet workbook. Waiting till that point will save you time if someone decides to push a change through. 

Budget Report Workbooks

The intention of the budget report workbooks was to give managers a place to go see their approved budget and also be able to see where else their staff were requested across the organization. Without this sheet that view is not available anywhere else. When the cabinet decided to do line item approval these workbooks became outdated as they reference the directorate level approval columns.

Rather than try to explain how this sheet works I think it may be easier, if there is time and if managers request them for you to recreate however it makes sense to you!

Something to keep in mind, whatever you use to create these workbooks the data needs to be able to update based on what the cabinet enters, that is why what I originally put together was not a copy and paste of the information for each group. You want these to be linked and to be updated based on data from other sheets so you don’t have to worry about making multiple updates to the report. 

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

1. Bill Gropp Sends out Budget guidance and charge email February 1st Done - 2/3/21
2. BWG sent manager email and invite - Done - 2/3/21
3. Scheduled training making sure majority of leadership can attend.- Done 2/10 
4. 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
5. 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
1. 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
2. Cabinet to review April 19 monday and Friday 11-1 through May 15th.

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

Initiating the new fiscal year budget planning process:

1. Create a new SFY folder on the Drive - NCSA State and ICR Budget Planning Folder 
2. Create new folders for each Directorate within the new fiscal year folder:

   

   
   

3. 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.
4. Remove all the data and restore the tab names to the default naming conventions:
   1. Directorate Personnel
   2. Directorate Non-Personnel
   3. GroupX Personnel
   4. GroupX Non-Personnel
5. Ensure that the Budget Category Definitions and other decisions are finalized before work is started on the templates.
6. Utilize the new SFYxx Budget Template to fill in the data and create a new file for each directorate.