training for ccs

Operating Budget Preparation Material

Community Colleges

New Mexico State University nmsu.edu

1

Budget Dates to Remember

Operating Budget drive set-up Ready to go

Analysis Tools available in drive (see detailed list) Week of Feb. 24, 2020

Departmental Budget preparation (using February data) February 25 to March 27

Tuition & Fee Rates and Budget Guidelines (Present to Board of Regents for approval) March 19, 2020 (tentative)

Budgets due to Budget Office (Reminder: files will be copied over from the shared drive to budget office files)

March 27, 2020

Budget Office review and communication back to departments March 30 to April 30, 2020

Present final budget to Regents Financial Strategies, Performance & Budget Committee

TBD

Final Budget Due to HED May 1, 2020

Regents approve FY2020-21 Operating Budget

May 8, 2020 (tentative)

Legislative Session Dates

Session Begins January 21, 2020

Session Ends February 20, 2020

Deadline for Governor to sign bills March 11, 2020

10/15/2020

2

Operating Budget – Network Drives

By now, everyone should have verified that the correct people have access

Everyone has access to schedules in the root drive: “Operating_Budget”

Within this drive, you will see two folders: Information Folder (available to all) and your Community College Campus folder.

You will use schedules from these locations; depending upon the nature of schedule.

Supporting documentation and analysis tools will continue to be added to these drives as they become available; currently the drive includes or will include the following:

Information Folder:

o Budget Distribution List for Exhibits 15-22 – This spreadsheet provides you the exhibit budget pages that have to be prepared by your unit. This will also provide you with the name of your Budget Reviewer.

o Unrestricted Fund Hierarchy – This spreadsheet provides you with a list of all your unrestricted funds by Fund Level (FL) 1, FL 2 or FL 3 and is for your information and reference only.

o Budget by Fund Template – This spreadsheet is a tool intended to provide a more detailed analysis when preparing your budget when more than one fund rolls up to your exhibit page (especially designed for exhibits 15 through 20). Even if your budget exhibit page does not have more than one fund, you may wish to use this tool to provide you with more detail, especially as it relates to the “other supplies and expenses” line items.

o Folders by Fiscal Year – FY20-21 includes a Spreadsheet for State Appropriation, ERR, and Fringe Rates for the FY21 Budget. There are prior year folders still in the shared drive for your information and reference.

Your Community Campus Folder contains sub-folders by fiscal year, three workbooks, and two PDF’s:

o FY20-21 Budget folder contains:

10/15/2020

3

Folder for FY20-21 Budget contains all FY21 Operating Budget Exhibits that need to be prepared. This folder also includes the Average Salary for your campus, for your information and reference. The average salary amounts have been used in the formulas within the Operating Budget Templates to calculate the FTE numbers associated with salary budgets.

Workpapers folder contains:

o Budget by Fund Template – This spreadsheet is a tool intended to provide a more detailed analysis when preparing your budget when more than one fund rolls up to your exhibit page (designed for exhibits 15 through 20). Even if your budget exhibit page does not have more than one fund, you may wish to use this tool to provide you with more detail, especially as it relates to the “other supplies and expenses” line items.

o Exhibit 3-9 – This spreadsheet provides you with unrestricted revenue activity by account code for Exhibit 3-9. (BU-399 Budget Exhibit Reports)

o Exhibit I – This spreadsheet provides you with unexpended plant activity for revenues, expenditures, and transfers. (BU-399 Budget Exhibit Reports)

o Exhibit I Revenue Detail – This spreadsheet provides you with unexpended plant revenue activity at a more detailed level by account code. (BU-399 Budget Exhibit Reports)

o Exhibit II – This spreadsheet provides you with renewals & replacements activity for expenditures and transfers. (BU-399 Budget Exhibit Reports)

o I&G Restricted Revenue – This spreadsheet provides you with I&G restricted expenditures used to calculate revenue budgets. (BU-399 Budget Exhibit Reports)

o I&G Revenue by Fiscal Watch Line – This spreadsheet provides you with I&G unrestricted revenues by fiscal watch line. (BU-399 Budget Exhibit Reports)

o BU-403 Restricted Funds by FL3 Coding – This spreadsheet is for your information only and includes all of the restricted funds by fund level 3. You may use this spreadsheet to verify if you are budgeting an index to the correct budget exhibit page. It would also help us if

10/15/2020

4

you would review this list and let us know if you find any discrepancies.

The three workbook files and two PDF’s include:

o FY19 Report of Actuals (PDF), for your information and reference.

o FY20 Operating Budget (PDF), for your information and reference.

o Budget Actuals Analysis table for your information and reference. This workbook consist of Exhibit 10 through Exhibit 14 unrestricted and restricted indices. This spreadsheet has no amounts and you will only use this spreadsheet if you have a question on a specific index, and you want to see where this index rolls up to in the Operating Budget. Column “T” is the Exhibit Section and column “U” is the Exhibit Section Detail of the Operating Budget.

o Budget Checklist – Complete this checklist for each budget exhibit that

you prepare. Upon completion of the Operating Budget, please respond to the questions at the bottom of the checklist in your email transmittal to the Budget Office for each exhibit. Please submit all email transmittals to [email protected], instead of to your budget reviewer.

o Checklist For Review – for your information and reference. This checklist will assist you with verifying that the information that you completed is accurate and correct.

10/15/2020

5

Tools Available for Analysis & Budget Preparation

Institutional Support Charges (If applicable to your unit, this amount will be in your individual folder in a PDF named FY20-21 Additional Budget Information)

Debt Service Requirement schedule (If applicable to your unit, this amount will be in your individual folder in a PDF named FY20-21 Additional Budget Information)

Plant O&M Distribution schedule (If applicable to your unit, this amount will be in your individual folder in a PDF named FY20-21 Additional Budget Information)

IDC Transfers from I&G amounts (If applicable to your unit, this amount will be in your individual folder in a PDF named FY20-21 IDC Transfers). This will include information for both the FY20 Estimated Actuals and the FY21 Operating Budget.

Fee unit budgets (If applicable to your unit, this amount will be in your individual folder in a PDF named FY20-21 Additional Budget Information)

Fringe benefit rates (These amounts will be in the Information folder under FY20-21 folder named Fringe Rates for Budget FY21)

State Appropriations-Operating and Capital (These amounts will be in the Information folder under FY20-21 folder named FY2021 State Appropriations-Operating and FY2021 State Appropriations-Capital)

Building R&R Appropriation schedule (prior amount included in base) – Community Colleges (information folder)

Equipment R&R Appropriation schedule (amount included in base) – Community Colleges (information folder)

3% Scholarship information (amount included in base) – Community Colleges (information folder)

10/15/2020

6

List of Budget Exhibits

Exhibit 1 Summary of Current and Plant Funds (Linked)

Exhibit 1A Detail of Transfers (Linked, but needs review, modify)

Exhibit 2 Summary of Instruction and General (Linked)

Exhibit 3 – 9 – I & G Revenues (BU-399 Budget Exhibit Reports by Campus available to run in Cognos-P) (1)

Exhibit 3 Student Tuition and Miscellaneous Fees for I&G Exhibit 4 Governmental Appropriations for I&G Exhibit 5 Government Grants and Contracts for I&G Exhibit 6 Private Gifts, Grants, and Contracts for I&G

Exhibit 7 Endowment, Land and Permanent Fund Income for I&G Exhibit 8 Sales and Services of Educational Activities Exhibit 9 Other Sources of Revenue for I&G

Exhibit 10 - 14 I&G Expenditures (Use BU- IG Budget Exhibits 10-14 (Admin) Reports available to run in Cognos-P)

Exhibit 10 Instruction Exhibit 11 Academic Support Exhibit 12 Student Services Exhibit 13 Institutional Support

Exhibit 14 Operation and Maintenance of Plant

Non-I&G Exhibits 15-22 (Use BU-226 and BU-226A, reports received by email or available to run in Cognos-P)

Exhibit 15 Student Social and Cultural Development Activities Exhibit 16 Research (N/A for CC’s)

Exhibit 17 Public Service Exhibit 18 Internal Service Department Exhibit 19 Student Aid, Grants, and Stipends (1) Exhibit 20 Auxiliary Enterprises

Exhibit 21 Intercollegiate Athletics (N/A for CC’s) Exhibit 22 Independent Operations (N/A for CC’s)

10/15/2020

7

List of Budget Exhibits, continued

Plant Fund Exhibits (BU-399 Budget Exhibit Reports by Campus available to run in Cognos-P) (1)

• Exhibit I Capital Outlay • Exhibit II Renewals and Replacements • Exhibit III Retirement of Indebtedness

Exhibit a Summary of Current Funds Revenues by Source (Linked)

Exhibit b Summary of Salaries in All Current Funds (Linked)

Exhibit c Proposed Salary Increases (Need to complete)

Exhibit d Tuition, Summer Session, Required Fees, Revenues from Fees and Board Rates (Also complete HED Required Form)

Exhibit e Salaries of Principal Officers (Need to complete)

Exhibit f Perkins Student Loan Fund (NSDL) (N/A)

Exhibit g Investment in Plant (Not included in Operating Budget)

(1) You will need to use the reports provided in your Workpapers folder. BU-399 Budget Exhibit Reports by Campus - the Business Manager receives this report by email on a monthly basis after each close and is available to run in Cognos-P.

– Exhibit 3-9 - Reports will be provided in your Workpapers folder named BU-399 Budget Exhibit Reports (Exhibit 3-9, I&G Restricted Revenue, and I&G Revenue by Fiscal Watch Line).

– Exhibit 19 - Reports will be provided in your Workpapers folder named Exhibit 19 UR Query and Exhibit 19 Rest Query.

– Exhibit I - Reports will be provided in your Workpapers folder named BU-399 Budget Exhibit Reports (Exhibit I and Exhibit I Revenue Detail).

– Exhibit II - Reports will be provided in your Workpapers folder named BU-399 Budget Exhibit Reports (Exhibit II).

10/15/2020

8

Reports to be Used in Budget Preparation Community College VP’s for Business and Finance receive the following five reports by email after each close on a monthly basis for your information, review, and use. These reports contain YTD actual activity for your units and are the reports that you will use for your operating budget preparation. The reports are in Excel format, therefore you may create a pivot table for easy review and analysis of your numbers.

1. BU-226 Exhibit 15-22 Unrestricted 2. BU-226 Exhibit 15-22 Unrestricted (detail drill-down) 3. BU-226A Exhibit 15-22 Restricted 4. BU-226A Exhibit 15-22 Restricted (detail drill-down) 5. BU-399 Budget Exhibit Reports by Campus

You can log into Cognos-P and run FI-SUM-Fund Balance – Month End to look at your fund balances. (See page 28 for instructions). This report provides information by fund level 3 and by fund level 5; it lists all individual fund balances that roll up to your fund level 3 fund balance. You may also run BU-IG-Budget Exhibits 10-14 (Admin) to assist you in gathering information on your exhibit 10-14 actuals for FY2020 and preparing your “Estimated Actuals”. (See screen print below)

Cognos Portal: Cognos-P; Administrative Tab; Finance Portlet

10/15/2020

9

BU-226 Unrestricted and Restricted Fund Detail

Purpose: Provides a detail listing of all activity in the Unrestricted and Restricted Fund rolling up to an Exhibit page. Report provides information by Program, Exhibit Page, Account Level, Account Category, Fiscal Watch Line, and YTD Actual Activity. The business managers for each area are receiving these reports on a monthly basis by email after each close. If these reports are run on any given day; the activity posted will be through the prior working day.

Original Budget: Represents the budget Adopted by the Board of Regents and approved by the NM Higher Education Department (HED) and the Department of Finance and Administration (DFA).

Adjusted Budget: Represents the Original Budget adjusted for any permanent (BD02 transaction codes) or temporary (BDC or BD04/B4C/B4D transaction codes) budget adjustments.

Budget Actuals: Represents the YTD activity booked from July 1 through the closed period for Month-End.

10/15/2020

10

Cognos Portal – Administrative Tab

Tab has “Finances Portlet” that contains pre-created reports to assist you in gathering information on your exhibits 10-14 and exhibits 15-20 actuals for FY2020 and preparing your “Estimated Actuals”. Use these for Unrestricted and Restricted Expenses. Use the BU-399 Budget Exhibit Reports by Campus to gather information for Exhibit I, II, and Exhibit 3 through 9 Revenue actuals.

10/15/2020

11

BU-IG-Budget Exhibit 10-14 Prompt Page (Administrative Tab) – report choices

To run these reports: Select the fiscal year and fiscal period, choose your campus, and then choose the program. You may select one program or you may select them all by holding the Ctrl key down while selecting. Press “Finish” when button is active. This will bring up the Summary of Expenditures and Expenditures – Detail of Individual Units for each exhibit that was selected.

Select fiscal year and fiscal period

Select campus

Select program

10/15/2020

12

BU-IG-Budget Exhibits 10-14 – Summary of Expenditures

Report provides expense activity by budget exhibit, exhibit section and exhibit detail section. This report includes both Unrestricted and Restricted expenditures and also includes the adopted budget for unrestricted expenditures.

Notes:

1. The fiscal period relates to the month within the fiscal year with July being Period 1 and June being Period 12 (Period 14 for final close). For preparing estimated actuals, you will use the February data (FY2020, 08).

2. Data is shown as debits and credits. The normal balance for revenues is a credit and the normal balance for expenses and transfers is a debit.

3. The Original Budget column shown for the unrestricted fund will tie back to the budget approved by the Regents, HED, and DFA. No budget is provided in the restricted fund, because the budget in Banner for Restricted is not an annual budget as is the operating budget submitted to the State.

The fiscal period relates to the month within the fiscal year. July being period 01 and June being Period 12 (Period 14 for final close)

Original Budget will tie back to the budget approved by the Regents, HED and DFA.

10/15/2020

13

BU-IG-Budget Exhibits 10-14 – Expenditures –Detail

of Individual Unit

Report provides the same information as the previous slide, but breaks the expenditures down by fiscal watch line as used in the operating budget to the State. Notes:



3. The Original Budget column shown for the unrestricted fund will tie back to the budget approved by the Regents, HED, and DFA. No budget is provided in the restricted fund, because the budget in Banner for Restricted is not an annual budget as is the operating budget submitted to the State.

The fiscal period relates to the month within the fiscal year… July being period 01 and June being Period 12 (Period 14 for final close)

10/15/2020

14

BU-IG-Budget Exhibits 10-14 – Expenditures –Detail of Individual Unit (continued)

Summary by Expenditure Category NMSU Fiscal Watch Line ADOPTED BUDGET

- UNRESTRICTED Summary

BUDGET ACTUALS - UNRESTRICTED

Summary

BUDGET ACTUALS - RESTRICTED

Summary

31 Faculty Salaries 2,699,797.00 1,324,762.26 114,761.25

31 Part-time Faculty Salaries 0.00 385,451.71 13,000.00

32 Professional Salaries 42,321.00 43,152.14 126,241.47

33 Support Staff Salaries 313,405.00 64,337.62 63,608.46

34 GA/TA Salaries 6,162.00 0.00 0.00

35 Student Salaries 0.00 548.63 0.00

36 Federal Work Student Salaries 0.00 0.00 0.00

37 State Work Study Salaries 0.00 2,524.25 0.00

38 Other Salaries 92,243.00 82,575.47 5,000.00

41 Fringes - Other 21,058.00 10,048.92 91,378.49

42 Travel 170,750.00 51,427.13 4,199.86

43 Utilities 0.00 11.81 0.00

47 Student Account Pool 0.00 2,600.00 0.00

50 Equipment 0.00 0.00 9,680.45

53 Other Supplies and Expenses 904,412.00 327,571.19 86,103.14

Summary 4,250,148.00 2,295,011.13 513,973.12

At the bottom of this report is a Summary by Expenditure Category. This summary provides the same information as the Summary by Expenditure Category page in the Operating Budget submitted to the State. Also consistent with the Operating Budget, this summary does not include amounts from “Other Items Not Included in A’s”. (Fringes, O&M Plant Charges, Federal/State Work-Study allocation).

10/15/2020

15

BU-IG-Budget Exhibits 10-14 Drill-down (Admin) Prompt Page

Use the drill-down report, to find out what indices make up the total actual amount for the Exhibit Detail Sections.

Select all - if you what all sections for Carlsbad Campus (Exhibit 10)

10/15/2020

16

BU-IG-Budget Exhibits 10-14

Report BU-IG-Budget Exhibit 10-14 also has drill through capability to FOAPAL detail based on the Exhibit Detail Section column if you run in PDF or HTML format (default is Excel). To receive an Excel spreadsheet with detail by FOAPAL, click on any title that is Blue in the Exhibit Detail Section. Instructions to run reports in PDF format are found on the following two pages.

10/15/2020

17

Instructions to run reports in PDF or HTML format

1. Maximize Finances Portal.

2. Click on the arrow under ACTIONS to run with options to a PDF format.

10/15/2020

18

Instructions to run reports in PDF or HTML format, Continued

3. Under Format: select PDF then press the run button

4. Choose your parameters-then hit the finish button.

10/15/2020

19

BU-226 Exhibit 15-22 (Unrestricted)

The Unrestricted reports provide information by program, then exhibit page. Within each exhibit page the report includes: Account Level, Account Category, and Fiscal Watch Line and dollar fields for the Adopted Budget, Adjusted Budget, and YTD Actual Activity. Notes:



3. The Original Budget column will tie back to the budget approved by the Regents, HED, and DFA.

ACCOUNT LEVEL A

ACCTCAT FISCAL WATCH LINE ADOPTED BUDGET -

UNRESTRICTED

ADJUSTED BUDGET -

UNRESTRICTED

BUDGET ACTUALS -

UNRESTRICTED

01 Tuition and Fees ($55,000.00) ($55,000.00) $0.0013 Other Sources $0.00 $0.00 ($45.30)

($55,000.00) ($55,000.00) ($45.30)($55,000.00) ($55,000.00) ($45.30)

34 GA/TA Salaries $8,088.00 $8,088.00 $0.0035 Student Salaries $0.00 $0.00 $280.5036 Federal Work Student Salaries $0.00 $0.00 $1,492.0137 State Work Study Salaries $0.00 $0.00 $2,259.50

$8,088.00 $8,088.00 $4,032.0141 Fringes - Other $2,508.00 $2,508.00 $44.3442 Travel $8,500.00 $8,500.00 $2,702.9853 Other Supplies and Expenses $38,412.00 $38,412.00 $32,790.86

$49,420.00 $49,420.00 $35,538.18$57,508.00 $57,508.00 $39,570.19$2,508.00 $2,508.00 $39,524.89$2,508.00 $2,508.00 $39,524.89

E-ExpenseSTACCA-STUDENT ACTIVITIES CARLS15-STUDENT SOCIAL CULTURAL 1

R-RevenueE-Expense 2 SALARIES

2 SALARIES3 OTHER EXPENDITURES

3 OTHER EXPENDITURES

BU-226 Exhibit 15-22 UnrestrictedEXHIBIT 15-STUDENT SOCIAL CULTURAL 1 3 CarlsbadFY 2014, 08 STACCA-STUDENT ACTIVITIES CARLS

R-Revenue 1 REVENUE

1 REVENUE

10/15/2020

20

BU-226 Exhibit 15-22 (Unrestricted Summary)

At the end of the report, is a summary by Program (Exhibit). In this case the totals are the same information as the previous page. However, if the exhibit (program) has more than one “A” page, it would provide a rolled-up summary by program.

PROGRAM LEVEL A ACCOUNT LEVEL A FISCAL WATCH LINE ADOPTED BUDGET -

UNRESTRICTED

ADJUSTED BUDGET -

UNRESTRICTED

BUDGET ACTUALS -

UNRESTRICTED

01 Tuition and Fees ($55,000.00) ($55,000.00) $0.0013 Other Sources $0.00 $0.00 ($45.30)

($55,000.00) ($55,000.00) ($45.30)34 GA/TA Salaries $8,088.00 $8,088.00 $0.0035 Student Salaries $0.00 $0.00 $280.5036 Federal Work Student Salaries $0.00 $0.00 $1,492.0137 State Work Study Salaries $0.00 $0.00 $2,259.5041 Fringes - Other $2,508.00 $2,508.00 $44.3442 Travel $8,500.00 $8,500.00 $2,702.9853 Other Supplies and Expenses $38,412.00 $38,412.00 $32,790.86

$57,508.00 $57,508.00 $39,570.19$2,508.00 $2,508.00 $39,524.8915-STUDENT SOCIAL CULTURAL 1

BU-226 Exhibit 15-22 UnrestrictedEXHIBIT SUMMARY 3 CarlsbadFY 2014, 08

15-STUDENT SOCIAL CULTURAL 1

R-Revenue

R-RevenueE-Expense

E-Expense

10/15/2020

21

BU-226 Exhibit 15-22 (Unrestricted detail drill-down)

The Unrestricted detail drill-down reports provide the same information by program and exhibit page, but is further broken down to the Index, Fund, Org, and Account code level. The Unrestricted detail drill-down report with index description is further broken down to the Index Description, Encumbrances, and Balance Available. These reports can be run through cognos at any time during the month.

FUND ORG ACCOUNT PROGRAM ACTIVITY LOCATIO


53 Other Supplies and Expenses

300185 STUDENT ACTIVITIES

310011-Student Activities Carlsbad

930260-CACC COUNSEL AND STU SVCS CTR

730200-FUEL AND LUBRICANTS

1500-STUDENT SOCIAL CULTURAL

No Activity

No Location

$0.00 $0.00 $104.37 $0.00






731000-OFFICE SUPPLIES


No Activity

No Location

$0.00 $0.00 $130.19 $0.00






731020-OFFICE SUPPLY RECRUITING


No Activity

No Location

$0.00 $0.00 $389.68 $0.00






732001-NON OFFICE SUPPLIES


No Activity

No Location

$0.00 $0.00 $884.69 $13.50






732990-NONOFFICE SUPPLIES FEDERAL EXCLUDED


No Activity

No Location

$0.00 $0.00 $2,535.00 $0.00

BU-226 Exhibit 15-22 Unrestricted (detail drill-down) with index descriptionEXHIBIT 15-STUDENT SOCIAL CULTURAL 1 3 CarlsbadFY 2014, 08 STACCA-STUDENT ACTIVITIES CARLS

ACCTCAT

FISCAL WATCH

LINE INDEXINDEX DESC

ADOPTED BUDGET -

UNRESTRICTE

ADJUSTED BUDGET -

UNRESTRICTE

BUDGET ACTUALS -

UNRESTRICTE

UNRESTRICTED

ENCUMBRANC

10/15/2020

22

Pivot Table for BU-226 Exhibit 15-22 (detail drill down)

This report can be dropped into Excel for analysis using various methodologies. Creating Pivot Tables with this information provides you with a way to group items in a field in a custom way to help you isolate a subset of data that satisfies your own individual needs.

BU-226 Exhibit 15-22 Unrestricted (Pivot Table for detail drill down)

EXHIBIT 15-STUDENT SOCIAL CULTURAL 1

FY 2014, 08Sum of BUDGET ACTUALS - UNRESTRICTED ACCTCAT

FUND FISCAL WATCH LINE 1 REVENUE 2 SALARIES3 OTHER EXPENDITURES Grand Total

310011-Student Activities Carlsbad 01 Tuition and Fees - - 13 Other Sources (45.30) (45.30) 34 GA/TA Salaries - - 35 Student Salaries 280.50 280.50 36 Federal Work Student Salaries 1,492.01 1,492.01 37 State Work Study Salaries 2,259.50 2,259.50 41 Fringes - Other 44.34 44.34 42 Travel 2,702.98 2,702.98 53 Other Supplies and Expenses 32,790.86 32,790.86

310011-Student Activities Carlsbad Total (45.30) 4,032.01 35,538.18 39,524.89 Grand Total (45.30) 4,032.01 35,538.18 39,524.89

10/15/2020

23

BU-226A Exhibit 15-22 (Restricted)

BU-226A Exhibit 15-22 Restricted EXHIBIT 17-PUBLIC SERVICE 1 3 Carlsbad FY 2014, 08 SBDCCA (17-SMALL BUS DEV CTR CARLS) ACCOUNT LEVEL

A ACCTCAT FISCAL WATCH LINE BUDGET

ACTUALS - RESTRICTED

R-Revenue 1 REVENUE 05 Federal Govt Grants and Contracts ($11,921.00)06 State Govt Grants and Contracts ($81,326.90)

1 REVENUE ($93,247.90)R-Revenue ($93,247.90)E-Expense 2 SALARIES 32 Professional Salaries $37,582.04

33 Support Staff Salaries $16,256.18

2 SALARIES $53,838.223 OTHER EXPENDITURES

41 Fringes - Other $16,616.3142 Travel $4,410.1553 Other Supplies and Expenses $18,383.22

3 OTHER EXPENDITURES $39,409.68E-Expense $93,247.90SBDCCA ($0.00)17-PUBLIC SERVICE 1 ($0.00)3 Carlsbad ($0.00)

The Restricted reports provide information by program, then exhibit page. Within each exhibit page the report includes: Account Level, Account Category, and Fiscal Watch Line and dollar fields for the YTD Actual Activity. In the restricted fund, revenue is always reported equal to expense. No budget is provided in the restricted fund, because the budget in Banner for restricted is not on an annual budget as the operating budget submitted to the State.

Notes:



10/15/2020

24

BU-226A Exhibit 15-22 (Restricted Summary)

BU-226A Exhibit 15-22 Restricted EXHIBIT SUMMARY 3 Carlsbad FY 2014, 08

PROGRAM LEVEL A ACCOUNT LEVEL A FISCAL WATCH LINE BUDGET ACTUALS -

RESTRICTED 17-PUBLIC SERVICE 1 R-Revenue 05 Federal Govt Grants and Contracts ($11,921.00)

06 State Govt Grants and Contracts ($81,326.90)

R-Revenue ($93,247.90)E-Expense 32 Professional Salaries $37,582.04

33 Support Staff Salaries $16,256.1841 Fringes - Other $16,616.3142 Travel $4,410.1553 Other Supplies and Expenses $18,383.22

E-Expense $93,247.9017-PUBLIC SERVICE 1 ($0.00)Summary ($0.00)

At the end of the report, is a summary by Program (Exhibit). In this case the totals are the same information as the previous page. However, if the exhibit (program) has more than one “A” page, it would provide a rolled-up summary by program.

10/15/2020

25

BU-226A Exhibit 15-22 (Restricted detail drill-down)

The Restricted detail drill-down reports provide the same information by program and exhibit page, but is further broken down to the Index, Fund, Org, and Account code level. The drill down information for restricted funds is for expenses only. The Restricted detail drill-down report with index description is further broken down to the Index Description, Budget Period Start Date, Budget Period End Date, IDC Rate, CS Rate, Net Rate, Encumbrances, and includes Revenues. These reports can be dropped in Excel to do analysis using various methodologies. These reports can be run through cognos at any time during the month.

FUND ORG ACCOUNT PROGRAM ACTIVITY LOCATION



300589 SMALL BUSINESS DEV CENT STATE 12-13

340206-Small Business Dev Cent State 12-13

939016-CACC GRANT FUNDED PGMS

750300-TELEPHONE TELEGRAPH

1700-PUBLIC SERVICE

No Activity

No Location

$52.50 $0.00






750701-ADVERTISING

1700-PUBLIC SERVICE

No Activity

No Location

$878.11 $0.00






760900-SALES TAX

1700-PUBLIC SERVICE

No Activity

No Location

($100.87) $0.00






798100-INDIRECT COSTS GENERAL

1700-PUBLIC SERVICE

No Activity

No Location

$1,223.49 $0.00






798210-CS IDC VOL NO CAP OR NO REQ MATCH

1700-PUBLIC SERVICE

No Activity

No Location

($1,223.49) $0.00

BU-226A Exhibit 15-22 Restricted (detail drill-down) with index descriptionEXHIBIT 17-PUBLIC SERVICE 1 3 CarlsbadFY 2014, 08 SBDCCA (17-SMALL BUS DEV CTR CARLS)

ENCUMBRANCES -

RESTRICTEDACCTCAT

FISCAL WATCH

LINE INDEXINDEX DESC

BUDGET ACTUALS -

RESTRICTED

10/15/2020

26

Analysis Notes

The reports discussed on pages 9 through 25 will be important tools to use when preparing both your Estimated Actuals and your Operating Budgets. They can be dropped into Excel to do analysis using various methodologies.

Remember that you are budgeting for spending authority: expenditures exceeding the approved budget are subject to an audit finding. Our “estimated actuals” become the Revised Budget for FY2020. Therefore, it is better to budget high but the budget should still be REALISTIC and SUPPORTABLE; if you have no revenue source – you should not be budgeting to spend! A reasonable contingency is acceptable and encouraged.

You will be using February YTD data to estimate your actuals through the end of the fiscal year. One method of estimation is to annualize the February data on a straight-line basis – that is, by dividing by the number of months elapsed (8) and multiplying by the number of months in the fiscal year (12). However, straight-line annualization is not always recommended – see below:

When using Year-to-Date data to prepare your Estimated Actuals, it is not always a good idea to annualize:

For some expenditures, annualizing is appropriate, but you must determine which expenses in general to annualize (typically, salaries and fringe are appropriately annualized).

Keep in mind one-time expenses versus ongoing expenses.

Analyze the transfers to determine if they are complete for the year or partially made. Budget for transfers where appropriate; use overhead transfers schedule if your unit gets IDC.

For your Unrestricted Operating Budget, if you are using the current year information as a basis you must:

Adjust for new State Appropriations.

Adjust for Plant O&M and Institutional Support changes as provided.

Adjust for changes in fringe rate, if applicable.

Adjust for changes in required transfers that may change from year to year. (community colleges only)

If unit is receiving reductions in revenue, appropriate reductions should be made to the expense budgets. This may require detailed analysis and reduction in staffing or operating budgets. Any budgeted reductions of regular faculty or staff should be described in a separate memo so that we may consider if HR involvement is required.

10/15/2020

27

Expense budget should generally be less or equal to the revenue budget, unless you are planning to use beginning fund balance. Remember that the fund balance represents a reserve and can be used only for one-time expenditure (non-recurring).

Ending fund balances budgeted should be reasonable – see recommended balances below. You should be prepared to explain your plans if you appear to be keeping too small or too large of a fund balance.

Restricted Funds are more difficult and may require more detail analysis of the actual grants and contracts that you have or may be receiving in the future.

You want to have enough spending authority to allow for new grants that may be awarded during the fiscal year, but you still want to be realistic.

Remember that you are budgeting for a fiscal year of activity, not an entire grant period, should the grant run over fiscal years; still, it is better to budget high.

Budget your revenues to equal expense in accordance with reporting requirements for Restricted.

We suggest that your grant programs be reviewed and budgeted for individually using the “Budgeting by Fund” spreadsheet which can also be used to budget by grant or by program if more realistic.

As Approved by the Regents Budget Committee

Ending Fund Balance as a Percentage of Expenditures Target

Instruction and General (Exh 2) (Target is 15% for CC’s) 3%-6%

Student Soc & Cultural Dev Activities (Exh 15) 15% - 25%

Research (Exh 16) 15% - 25%

Public Service (Exh 17) 15% - 25%

Internal Service Depts (Exh 18) N/A

Student Aid Grants & Stipends (Exh 19) N/A

Auxiliary Enterprises (Exh 20) 6% - 10%

Intercollegiate Athletics (Exh 21) 6% - 10%

Independent Operations (Exh 22) 6% - 10%

Ending Fund Balances: Ending fund balances of both Instruction & General and Total Unrestricted Current Funds must be at least 3% of respective expenditures, but a higher percentage is generally better, especially for institutions that experience unpredictable fluctuations in mil levy and land & permanent fund revenue, for example. Higher fund balances are also recommended in periods of uncertain economic conditions and forecasts.

10/15/2020

28

Using Cognos Reports LOGGING IN:

• Open Cognos-P

https://cognos-p.nmsu.edu

• Administration and Finance tab:

– Go to the Administrative tab

– Under the Finances portlet, you will see the following report on the next page; entries 16-24

• FI-SUM-Fund Balance (Admin)

10/15/2020

29

Using Cognos Reports, Continued

• As mentioned before the following cognos reports are sent by email to the Business Manager on a monthly basis.

– BU-226 Exhibit 15-22 Unrestricted

– BU-226 Exhibit 15-22 Unrestricted (detail drill-down)

– BU-226A Exhibit 15-22 Restricted

– BU-226A Exhibit 15-22 Restricted (detail drill-down)

– BU-399 Budget Exhibit Reports by Campus

• These reports can be run through cognos at any time during the month under the Finances portlet, entries 1-15.

10/15/2020

30

Using Excel for Analysis Purposes

To export the Cognos reports:

Go to drop-down box in upper right hand corner of screen -

Click View in Excel Options (or PDF or other as appropriate)

Click on View in Excel 2007 Format

You will see a pop-up screen

Click on Open File

Go to Save As

Make sure the file “Save as Type” is Excel Workbook (*.xlsx)

Choose your drive, folder, and create filename

Use your new report in Excel

Once the data has been saved into Excel you can open it and add your own formatting and analysis

You may save the reports after running them - choose a location that you’ll remember --- you can save files to the Operating_Budget drives, however,

REMEMBER THAT THESE DRIVES ARE TEMPORARY; once the Budget Process is complete you should re-save these files on your own computer in order to keep them!

10/15/2020

31

Budgeting By Fund – Snapshot of Template

Use this Excel template to prepare budgets and estimated actuals by fund --- totals will feed to Exhibit Page template

We have placed a Budget by Fund Template in your workpapers folder. Use these workbooks as a tool in completing your budgets at a more detail level. Information from this tool is then keyed into the correct Operating Budget template tab.

See detailed instructions for utilizing this template on the following page.

10/15/2020

32

Spreadsheet by Fund Tool After pulling information using Cognos reports, you can use this tool

Tool is intended to provide a more detailed analysis when preparing your budget when more than one fund rolls up to your exhibit page. Even if your budget exhibit page does not have more than one fund, you may wish to use this tool to provide you with more detail, especially as it relates to the “other supplies and expenses” line item.

A copy of the template has been placed into your workpapers folder.

You will recognize the Exhibit Page sheet. This page is protected, so that the only changes you can make are to the Unit Name. All other fields are formulas. If you should need to unprotect the sheet, go to the Review Tab, and select “Unprotect Sheet”. This should only be done when absolutely necessary; under most circumstances, you should not need to change any of the protected items.

The other sheets in the notebook feed into the Exhibit Page sheet. Use these sheets to develop your budget.

The tabs indicate into which columns on the Exhibit Page sheet the numbers will flow.

Change the name at the top of the sheet to the name of the exhibit page. Enter the fund numbers into the heading in columns b through h; you can also

enter the fund name. If you need additional columns, you will have to “unprotect” the sheet (they have been protected in order to preserve inadvertent changing of formulas). If you do add columns, you may need to copy formulas to total lines.

Using your Cognos reports, you can plug the numbers by fund into this sheet as they appear on the report and

then annualize totals annualize the numbers as appropriate for each fund adjust for seasonal revenue or expense as appropriate enter new information of which you are aware – example, a new grant or

contract, or perhaps the loss of a revenue source. Feel free to modify the “other supplies and expense” line items to meet your

needs. If you add a row, you will need to unprotect the sheet; be careful that the formulas don’t need to be modified due to the added line. You can also hide the rows you don’t need. Recommend not deleting rows so as to not affect formulas.

When your budgets are complete, you can print the Exhibit Page sheet and then key into the correct Operating Budget template tab.

10/15/2020

33

Other Items for Community Colleges

Tuition and Fee Recommendation/ Sources and Uses Template

Each CC was provided with a Sources and Uses Template to complete and submit with their Tuition and Fee Recommendation

Preliminary is due on February 25 and final is due on or around March 2 this year (to be confirmed soon)

HED Required Forms – at this point in time, we have not received any instructions from the HED for the budget submission, but typically the following reports are required.

Budget Approval Form General Fund Appropriations Schedule Tuition, Required Fees, Room and Board Rates (must be signed by an

institution’s Chief Financial Officer) Tuition Waivers Budgeting of Building Renewal & Replacement Transfer Budgeting of Equipment Budgeting of Sources and Uses of Indirect Cost Revenue Calculation of 3% Scholarship Required Transfer

You will be asked to complete these forms as a part of your budget submission (the Tuition form requires SVP’s signature). As a reminder, the form completion is included in the Budget Checklist. The forms will be saved in the 20-21 Budget folder for your campus once we receive them from the HED.

Budget Adjustment Requests (BAR) Completion – A final BAR for FY2020 is submitted to the State (HED) along with the FY2021 Operating Budget.

The BAR provides the authorization for final spending authority for the current fiscal year. As such, we recommend that you include an expenditure contingency amount in your budget for FY2020 to allow for any unanticipated expenditures that might occur before the end of the fiscal year. Any expenditure over the expenditure budget amount (by exhibit) can become an audit finding for

the University.

Budget Office prepares the BAR for each campus based on the Estimated Actuals. In other words, the Estimated Actuals become the Final Revised Budget against which actual expenditures will be audited.

10/15/2020

34

HED Website and Contact Information

http://hed.state.nm.us

If you would like a copy of the most current NMHED Financial Reporting Manual, you may contact any of us below and we will send you a PDF.

Budget Office Contact Information

Kim Rumford 646-2431 krumford @nmsu.edu

Debbie Bravo 646-7915 [email protected]

Lorraine Cisneros 646-2310 [email protected]

Araceli Hernandez 646-5865 [email protected]

Vicky Vasquez-Rangel

646-6725 [email protected]

Valeria Olsson 646-2775 [email protected]

10/15/2020

35

Good Luck and Happy Budgeting!

10/15/2020

training for ccs

Documents