BUDGET APPLICATION INSTRUCTIONS

FISCAL YEAR 2027

General Information

The use of the provided workbook is required for submission of your agency budget application in order to eliminate mathematical errors and to assist the Mental Health, Addiction & Recovery Services Board of Lorain County (MHARS Board) in the analysis of the budgets received. You must provide the completed budget in electronic format via e-mail to bhabony@mharslc.org.

Please note that this package is primarily unchanged from last year. Services and Non-Personnel types have been updated to better reflect those line items on your previous year(s) budgets. Formulas have been included in the workbook to calculate row and column totals and double check related data. In addition, the worksheets are linked to eliminate duplicative data entry. The worksheets are protected by password (2382) so that none of the formulas or links can be altered or erased in error. If you should try to enter data into one of these protected cells, you will be notified that you cannot. The workbook is in Excel 97-2003 Workbook format and should not be converted to any other version to assure compatibility.

Unallowable costs. The US Office of Management and Budget (OMB) has issued guidance referred to as the “Uniform Guidance”. This guidance can be located at www.ecfr.gov. Specific cost principles are located under Title 2 – Grants and Agreements: Subtitle A – Office of Management and Budget Guidance for Grants and Agreements: Chapter II – Office of Management and Budget Guidance: Part 200 – Uniform Administrative Requirements, Cost Principles and Audit Requirements. Unallowable costs cannot be included in the calculation of your unit rates. Any questions regarding whether costs are or are not allowable should be referred to your accounting professional. Any unallowable costs must be spread in the same manner as the allowable costs were originally spread. This budget package will handle the allocation of these costs for you.

All mental health, addiction & recovery services, regardless of funding source, must be costed on the worksheets. Non-mental health, addiction & recovery services should be shown in aggregate on the appropriate budget form worksheets.

The following worksheets are included in the workbook

“FY27_BUDGET_WORKSHEETS”

Worksheet Description

COVER - General Agency Information and Summary Budget Request

STAFF - Racial & Ethnic Composition of Agency Staff

STAFF COUNT - The Number of Employees in Each Category

BOARD - Racial & Ethnic Composition of Agency Board

UNITS - Service Volume Forecast

ALLOCATION - Allocation of Non-personnel Costs and Administrative Overhead Costs

PERS - Personnel Costs

NON-PERS - Non-personnel Costs

UCR - Uniform Cost Report

REVENUE - Revenue Projections

“GETTING STARTED”

1. It is recommended that a blank copy of the workbook be made prior to beginning data entry into the budget packet.

2. Once completed, print a copy of the worksheets (only those you have used to enter data) and keep for your records. Only the completed electronic workbook shall be returned to the MHARS Board.

3. If an agency does not have compatible software to allow the use of the workbook the MHARS Board’s computer system will be made available for data entry. Please contact the MHARS Board if this is necessary.

4. Please complete the worksheets in the order indicated in these instructions to avoid problems with the linking and formulas.

“COVER”

Please enter the information indicated in the top part of the worksheet. This includes general information about your agency and is relatively straightforward in nature. This information is linked to other worksheets to eliminate duplicative data entry.

The lower portion of the worksheet provides a summary of the funding request your agency is making. Once the entire budget package is completed, this sheet should be printed and submitted as the cover to your budget application.

There are additional calculations included on the Cover sheet that will flag errors in your budget. Be sure to review this sheet prior to submitting the budget to be sure that there are no “ERROR” messages and all are “OK”.

“STAFF/STAFF COUNT”

Please indicate the relative racial, ethnic and gender composition of the staff of your agency. You are also asked to indicate the number of employees that have lived experience and the number of family members that have lived experience related to employees in each category. Enter into each cell the number of FTEs appropriate for the staff of your agency. You should also be sure all employees are included in the ethnic composition and gender composition columns and that the totals here balance to the total FTE for your agency. Staff Count is equal to current actual number of positions not FTE’s, with the total equaling the total actual number of staff that you currently employ.

“BOARD”

Please indicate the relative racial, ethnic and gender breakdown of the members of the Board of Directors for your agency. You are also asked to indicate the number of Board members that have lived experience and the number of family members that have lived experience related to Board members. You must also make sure the gender and ethnic breakdown numbers foot to the total board members. You should also be sure all Board members are included in the ethnic composition and gender composition columns and that the totals balance to the total number of Board members.

“UNITS”

This worksheet asks that you enter the number of units you project your agency will actually provide during the current fiscal year (Projected Units FY26) as well as the number of units of each service your agency plans to provide during the next budget year (Estimated Units FY27). The units entered here are linked to the agency “UCR” worksheet. You are asked to complete a column included for clarifying description for the “Other Mental Health” services.

In Appendix A you will find the service codes which make up each of the blended categories. As an example, if you look under the detail for MH/SUD Assessment Evaluation & Testing you will find all the codes which should be grouped in this line item.

“ALLOCATION”

The first step you should complete on this worksheet is to complete the entry for Method 6 (Sq. Footage) if you are planning to allocate any of your non-personnel expenses via this method. This method is commonly used for costs like rent, utilities, etc. Enter the number of square feet that should be allocated to each service you will be providing, including Non-mental health services as appropriate. Please verify the total square feet at the bottom of the table are correct. The relative percentages for each service will be calculated for you and will be used to allocate costs.

The other method’s tables will be filled in automatically for you through the completion of other workbooks.

Move on to the PERS worksheet at this time. We will return to this workbook later.

Once you have completed the personnel and non-personnel worksheets you should return to this point.

This sheet allows you to select from five methodologies for spreading administrative overhead costs. No other methods are allowed. Remember that it is important to have a clear rationale for the method you choose for each cost being allocated.

1. Based on Direct FTE’s

2. Based on Total FTE’s

3. Based on Service Total Costs

4. Based on Total Personnel Costs

5. Based on Direct Personnel Costs

To indicate the method you wish to use, enter the option number in cell B7 of the worksheet. The distributed administrative overhead amount will be calculated for each service and these amounts will then be automatically loaded into the UCR Worksheet for you. You will note that the totals for these two items are already brought over to the UCR from the NON-PERS and PERS worksheets respectively. There is an error check area at the bottom of the UCR worksheet you can use to be sure you have fully allocated these expenses across the services. Any unallowable administrative costs are also distributed on the same basis as the total administrative costs and brought over to the UCR for you as well.

“PERS”

Please enter all personnel information into this worksheet.

The top area of the worksheet allows up to six different Rate Keys. You can alter the rate key percentages as you wish in order to assist you in projecting the impact of various potential staff increases on your unit costs. Use of the rate keys is optional.

The worksheet supplies an area to accommodate up to 500 employees. For each position, you need to enter the position title, position code (if desired), the FTE (Full Time Equivalent), the base salary figure for the position, and the rate key, if used. Note: 1.000 FTE represents the number of hours you specify for a full-time employee

on the COVER sheet, for example 2,080 hours per year. A half-time person would be entered as .500 of an FTE.

For each position, you should enter “Y” under the column “Unallowable?” if the salary and benefits for the position are considered unallowable costs. The unallowable costs will be spread on the same basis that the position is spread to the various services, including administration, and will be brought over to the UCR for you.

The Calculated Salary column is automatically filled as a function of the base salary and the rate key if used. If no rate key is used the calculated salary is equal to the base salary.

You next need to enter the total Fringe Benefit Expense for each position. These expenses are such items as health insurance, pension, FICA, workers compensation and the like. The “Total Personnel Cost” column will automatically be the sum of the “Calculated Salary” and the “Fringe Benefit Expense” cells.

Unallocated FTE – As each employee is allocated to various services, the portion of the FTE allocated there is deducted from the original FTE amount. If a position is over-allocated, the field will display in RED. All positions must be fully allocated when the budget is completed. If fully allocated this cell should equal zero.

Flowing out to the right columns is the area where you must allocate each position to one service or multiple services. You may allocate the employee as either direct or support or a combination of both. You may allocate the position to multiple services if desired.

Scroll to the right until you locate a service you wish to allocate some or all of the position to. Enter the FTE value in the FTE column as direct or support that you are dedicating from this position. The dollar amount will

automatically be loaded for that service. The amount loaded is a function of the FTE value allocated to the service vs. the FTE total for the position and the total personnel costs for the position.

Example 1: If the position is 1.000 FTE and you allocate .100 of them to direct service for Prevention, then 10% of the “Total Personnel Costs” for the position will be added to the Prevention Service’s Direct Personnel cost as will the .100 FTE.

Example 2: If the position is part time, say 0.500 FTE, and you allocate 0.500 of them to support service for Prevention, then 100% of the “Total Personnel Costs” for the position will be added to the Prevention Service’s Support Personnel cost as will the .500 FTE.

Please note that for Administration, you can only allocate staff to be support personnel.

“NON-PERS”

You should enter each of your agency’s discreet non-personnel cost line items on the rows of the worksheet. These are operating expenses, not capital expenses. You will then place the total amount budgeted for that line item. You must then choose the method for allocation of each cost line item. Each line item may be allocated on an individual basis. The valid choices are listed numerically at the top left of the worksheet and are:

1. Based upon direct FTE

2. Based upon total FTE

6. Based upon Square Footage (see section on ALLOCATION worksheet)

8. Direct Allocation of the Cost

No other methods may be used to allocate these costs. If you choose the option to directly allocate a particular cost line item then you should choose “8” as the “key” and then proceed to manually enter the amounts under the specific service(s) upon the blue shaded row associated with the cost line item. If you choose a method of allocation other than “Direct” enter the corresponding number in the “Key” field and the costs will automatically be distributed on the chosen basis, using the tables in the “ALLOCATION” worksheet along the un-shaded row associated with the cost line item. If any other methods are chosen, the worksheet will display in red that the costs are not properly allocated. Formulas are included to automatically distribute the costs among the services based upon the “key” method chosen. The totals by service are linked to the “UCR”

worksheet. Again, it is important for you to have a clear and defensible rationale for the allocation method you choose to use.

If a line item cost is unallowable per the “Uniform Guidance”, fill the cell under “Unallowable Cost” with “Y”. If the cost is auto-allocated on methods 1, 2, 6, or 8 then the “Y” should be on the un-shaded cell. If the cost is directly allocated using method 8, then the “Y” should be on the blue shaded cell. The unallowable costs are totaled at the top of the worksheet and are automatically brought over to the UCR report for you.

“ALLOCATION”

At this time, you should return to the “ALLOCATION” worksheet. You should choose the method to use to spread your Administrative Overhead expenses. Please refer to the first “ALLOCATION” section for more information on how this is done.

“UCR”

Once all of the previously covered worksheets are completed, there is nothing further you will need to enter into the UCR worksheet. At this time the UCR should be complete and should show your calculated allowable unit of service rates, except for the services without units. Note that by going back into the supporting schedules and changing allocation choices, your service unit costs will be automatically updated. Likewise, changing rate keys in the personnel calculations will automatically change the unit costs.

“REVENUE”

Please enter the expected revenues by source across service for the coming year. Please note that all Board related funding is noted across the upper portion of each page and non-Board related funding along the bottom of each page. .

If you need to indicate other specific sources of revenues either from the Board or from other sources, you should change the description from “Specify” to whatever description you need. Note you can make that change only on the first page of the REVENUE worksheet. All changes on the first page will automatically be copied to pages 2 through 8 for you. Please be sure to keep board funding and other funding in the appropriate areas of the report.

“FINAL CHECKS”

The final step you will need to perform is to return to the “COVER” sheet. Please review the audit area at the bottom to be sure no errors or exceptions are noted there. If there are errors shown, these must be fixed prior to submission of the budget to the Board.

PROBLEMS OR QUESTIONS?

If you should encounter any difficulties in the use of the workbook or have any questions pertaining to the detailed budget to be provided please contact Barry Habony via e-mail at bhabony@mharslc.org or (440) 787-2075.