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
  • Confirm for possible changes in the Directorates due to re-organization
  • Ensure that the Budget Category Definitions and other decisions are finalized before work on the template starts 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.
  • Based on the org chart, we would need to create a Personnel tab and a Non-Personnel tab for each group/manager.

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
  • Verify listing against last year's list before splitting out the CFOPAs in the template
  • 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:
  • Budget Template Creator 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 
    • Formula is similar to getting the amounts for each CFOPA and Title in the summary portion
  • Bridge Funding Requests from Group Personnel tabs - summarizes all other Bridge Funding requests from all group's Personnel tabs for the Directorate
    
    • Formula references the "CFOPA for Bridge Funding" cell in each group's tab
    • For example, =sum('ASD Personnel'!E20,'CIID Personnel'!E20,'CSD Personnel'!E20,'DMDD Personnel'!E20)
• 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
    • Budget Template Creator adds names of staff from prior year's file and their prior approved allocation manually
    • Budget Template Creator 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 - Budget Template Creator 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:
  • Budget Template Creator 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
  • For Directorate Requests only - summarizes details, will not populate correctly unless manager selects CFOPA and Title from below 
    • Formula is similar to getting the amounts for each CFOPA and Title in the summary portion
  • Strategic Travel Funding Requests from Group Personnel tabs - summarizes all other Strategic Travel Funding requests from all group's Non-Personnel tabs for the Directorate
    
    • Formula references the "CFOPA for Strategic Travel" cell in each group's tab
    • For example, =sum('ASD Non-Personnel'!C16,'CIID Non-Personnel'!C16,'CSD Non-Personnel'!C16,'DMDD Non-Personnel'!C16)
• 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
    • Budget Template Creator 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:
  • Budget Template Creator 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
    • Budget Template Creator adds names of staff from prior year's file and their prior approved allocation manually
    • Budget Template Creator 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 - Budget Template Creator 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:
  • Budget Template Creator 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
    • Budget Template Creator 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
• Confirm with Directorates for accuracy in case of ongoing and prospective re-organizations. Discuss with the Budget Committee before filling up the sheets.
• 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 (ABRRT) documents that each AD should complete

Data tabs

• These tabs are where data is pulled from using a formula to populate the Directorate and Group tabs in each Directorate's file
• Hidden from users before distributing

Account Spending Detail

• Create a copy of the tab in the AD Team State & ICR Budget Report 

New Account Detail

• Whenever new CFOPAs are requested in the Directorate sheets, Budget Template Creator copies them into this tab and lists the Org Directorate and Org Group based on where the request was made
• Usually seen immediately in the Pivot - Account Spending Detail tab since they would not have the same format as the usual CFOPA strings

Summary tabs

Non-Personnel Report

• Combines all of the information from each Directorate's Non-Personnel Approval tab into this report
• Utilizes the importrange function and links to each Directorate. The importrange function would look like it was typed in. But if someone tries to edit the information on the tab or override it, everything imported will disappear until the "disruption" is removed
• The Budget Committee compares this with the Summary of Accounts tab to ensure that everything ties out to a CFOPA for the Business Office to put into BA4
• This used to be where the ADs would go through each item line-by-line to decide if an expense request was approved or not. Since FY24, the process has changed and is mainly used to confirm the completeness of the Summary of Accounts and other report tabs

Personnel Report

• Combines all of the information from each Directorate's Personnel Approval tab into this report
• Utilizes the importrange function and links to each Directorate. The importrange function would look like it was typed in. But if someone tries to edit the information on the tab or override it, everything imported will disappear until the "disruption" is removed
• The Budget Committee compares this with the Summary of Accounts tab to ensure that everything ties out to a CFOPA for the Business Office to put into BA4
• This used to be where the ADs would go through each item line-by-line to decide if an expense request was approved or not. Since FY24, the process has changed and is mainly used to confirm the completeness of the Summary of Accounts and other report tabs

Summary of Accounts

• Summarizes the Non-Personnel Report and Personnel Report tabs to facilitate the budget input of the Business Office into BA4, which is usually done in August/September timeframe
• Use the unique function to determine all the CFOPAs in the budget request. This functions similar to the importrange formula where if someone tries to edit the information on the tab or override it, everything imported will disappear until the "disruption" is removed
• There is a formula to determine the Directorate and Group, and where the New Account Detaill is referenced
• Business Office staff name is assigned manually upon filtering the sheet by the group

Reporting tabs

SFYxxxx Budget Allowances

• Included Excel file started by Jay Roloff to determine the budget limits for each directorate based on historical financials
• Provides the Budget Limit for the fiscal year for each directorate and compares it with the Current Budget Request by each AD
• Includes the Admin Allocations since during the FY24 budget process, NCSA leadership has determined that the ASCO will be disbanded and that Administrative staff will be assigned accordingly

ABRRT

• Provides the financial tables for the ABRRT Google Doc for each directorate
• The first part focuses on the current fiscal year, listed first by Directorate, followed by Groups:

• • SFYxx Approved Budget - link to previous year's Directorate budget file Status tab using the importrange function
  • Actual Expenses up to March 31, 20xx including encumbrances up to June 30, 20xx - links to ABRRT Exp+Enc Pivot tab with a SUMIF formula to filter by Group name
  • Uncommitted Balance as of March 31, 20xx Surplus/(Deficit) - difference between the SFYxx Approved Budget and Actual Expenses columns
• The second part focuses on the upcoming budget year

• • SFYxx State and ICR Budget Requests - link to current year's Directorate budget file Status tab using the importrange function
  • % Change From Previous Year - uses IFERROR formula, compared to the prior year's amounts above to show how much the budget amounts changed from previous request

ABRRT Exp+Enc

• Import from Activity Listing with Sub Type Report from BA4 system to show cumulative expenses and encumbrances as of a given date, in this case, March 31 of the current fiscal year
• Keep only Activity Code Type = Budgeted

ABRRT Exp+Enc Pivot

• Pivot table from the ABRRT Exp + Enc tab to add information to the ABRRT tab's Actual Expenses up to March 31, 20xx including encumbrances up to June 30, 20xx column 

Other Funding Summary

• Import from Activity Code Listing under the account tab at the top from BA4
• Create a Pivot table within the tab at the rightmost part to show the totals by Activity Code Type as seen below, which flows to the Report Charts tab:

Summary by Directorate

• Summarizes each Directorate's Labor and Non-Labor Requests and compares it to prior years
• Links to the Summary of Accounts tab
• Provides two pie charts and a stacked column chart based on the tables in the tab that flow into the NCSA FYxx State & ICR Budget Report Google Doc shared with NCSA leadership

Report Charts

• Shows different tables and graphs that flow into the NCSA FYxx State & ICR Budget Report Google Doc shared with NCSA leadership
• Tables updated from numbers in the Multi-Year Comparison By Account Type Report found in the Tableau system
• Provides two combo-type charts and a stacked column chart

Other Notes

Protect Range

• We need to ensure that ranges in tabs are protected to prevent unauthorized changes to the tab
• Follow the current spreadsheets' protections and apply consistently to all tabs
  • Usually these are the gray cells in each tab
    • Current configuration for Directorate Personnel tabs:

• • • Current configuration for Directorate Non-Personnel tabs:

• • • Current configuration for Group Personnel tabs:

• • • Current configuration for Group Non-Personnel tabs:

• Other tabs such as Personnel Approval, Non-Personnel Approval, Status, and other Data tabs only restrict the editing to the sheet owner, so there is no specific configuration
• Right-click on any cell in the workbook, and select Protect Range from the list. Follow the prompts on the sidebar, which will be to select the range to protect and then add any members that should have access to edit that range. 

Permissions

• Managers are given Edit access after the Budget Training occurs
• Associate Directors are given View access to everyone else's budget sheets, and Edit access to their own Directorate
• Budget Committee members have Edit access to the sheets 
• Lockdown of sheets
  • Occurs nearer to the end of the budget process, after the NCSA Director shares the email that the budget has been finalized

Helpful Links

NCSA Org Chart 

NCSA Personnel Tool

NCSA State and ICR Budget Planning Folder