Instructions for Budget Packet FY 2017 GRANT Excel Spreadsheet Helpful Hint: To move to other spreadsheets in the Excel workbook, click on the sheet “tab” names (at the bottom of the screen). Excel Spreadsheet Helpful Hint: In each spreadsheet, cells which require or permit data entry are highlighted in red or blue, while formula cells or cells that do not require or permit data entry are in black.

EXHIBIT A – UNITS Step 1: Input the projected number of billable units to be provided for all services. Step 2: Input the projected unduplicated number of persons to be served. Step 3: Input demographic information for the number of persons who are: minorities, in poverty, age 75+, and living alone, for the unduplicated number of persons served. Also, input the number of low-income minorities. Step 4: For Agencies serving multiple counties, breakout units by county and total the units.

EXHIBIT B – PERSONNEL INFORMATION Excel Spreadsheet Helpful Hint: Data from the Personnel Information is used in Exhibit C - Personnel Costs. The information in Exhibit B must be input manually into Exhibit C. Use the Exhibit B – Personnel Information Spreadsheet to help calculate benefits costs for staff, if service grantee doesn’t have another system to do the calculation. If you have an accounting system that provides the required information, proceed to Exhibit C. Step 1: Column A - Position – Input the title of Staff. Step 2: Column B – Last Name – Input the name of the person in each position. Step 3: Column D – Annual Wages – Input each employee’s annual wages/salary. Step 4: Column E - Annual Hours – Input each employee’s total annual hours for the year October 1, 2013 – September 30, 2014. Step 5: Columns F-K – Benefit Costs - Enter benefit cost percents in row 6. Input total cost of other benefits by employee, if known. Otherwise, input fringe benefit costs for paid staff on benefits rows in Exhibit F- Support Costs (Expenses). If you enter the appropriate percentages on row 6 (Exhibit B), the spreadsheet will automatically calculate your FICA, Workers Comp and Retirement. Step 6: Columns L-M – Total Fringes and Total Wages and Fringes – Spreadsheet calculates these. Step 7: Columns O-W – Percent of Time - Input % of employee time spent in each service. Column X – Total – The spreadsheet calculates the total % of time by employee.

2

EXHIBIT C – PERSONNEL COSTS EXCEL SPREADSHEET HELPFUL HINTS 

You may want to freeze panes so that you can see the titles when moving about the spreadsheet. To do this, put your cursor at the location between the left hand titles and the titles at the top of the page where you wish to freeze panes and then click on “Window, Freeze Panes.” (this is already done) Your titles will remain stationery while you move about the spreadsheet. To unfreeze panes, click on “Window, Unfreeze Panes.”



Complete all of the information for columns A, B, C and D before you begin to allocate the percentage of time spent by each employee in the various services.



Be sure to only input each employee’s % of time spent in a service.



If the Percent of Wages and Benefits does NOT equal 100%, you have an error in your input.



Driver’s time must be listed in the blue rows on the Excel Spreadsheet. This is used by the Transport Cost Pool to allocate the vehicle operating expenses appropriately based on the percentage of time the driver spends in the various services.

NOTE:

DO NOT INPUT any other staff position other than driver on the blue rows or it will not appropriately allocate transport costs on Exhibit F - Support Costs (Expenses).

3

SPREADSHEET INFORMATION Exhibit C – Personnel Costs is intended to capture all employee positions, their total wages and individually assigned benefits, and the number of hours each works in a specific OAA service. This information must be based on

how employees spend time in specific services, and should not be based on positions being allocated because they had always been charged to that service in the past due to funds being available. The Exhibit C – Personnel Costs documents personnel costs and accounts for the time of paid staff only. Several of the statistics used to allocate costs in Exhibits E & F – Support Costs in the reallocation section are developed in Exhibit C. Much of the grantee’s non-direct costs are allocated based on the amount of personnel expenditures associated with an OAA service. The services listed on the spreadsheet are by service and not by fund source. (For example Transportation is listed, rather than Title III-B) The purpose of the Costing Model is to allocate all costs of a service – regardless of fund source. After you determine your cash costs for a service, then you determine the fund sources available to support the service. The Exhibit C – Personnel Costs is vital to allocating one of the most valuable resources that a grantee has and that is the employee’s available time to provide quality aging services. The top row across the spreadsheet indicates the various cost pools and services to which personnel costs will be allocated. The column headings consist of two of the four cost pools, which include General Administration and Space Cost Pool and the specific service titles funded by the Agency. Columns (Q-S) for “Central Kitchen Operation” are included as an option for those grantees that operate a central kitchen and need to know the cost of the meal production without the operational costs associated with operating a congregate meals program. Costs will be distributed to congregate and home delivered meal programs based on the number of meals prepared, unless another distribution method is shown to be more accurate. There is also an “All Other” column (AC) that is used to account for personnel time and wages not associated with Agency funded services. Whatever time is not accounted for in cost pools or services automatically defaults to “All Other.” The system allows grantees to calculate the employee’s associated wage cost per service through formulas utilizing different time and effort statistics.

4

Step 1: Column A - Position – Enter the title of the Employee Position. Place Drivers in the cells labeled Driver-Transport or Cong/HDM only. Excel Spreadsheet Helpful Hint: Drivers’ time must be listed in the blue rows on the Excel Spreadsheet. This is used by the Transport Cost Pool to allocate the vehicle operating expenses appropriately based on percentage of time the driver(s) spends in the various services. NOTE:

DO NOT PUT any other staff position other than driver on the blue rows or it will not appropriately allocate transport costs on Exhibit F - Support Costs Spreadsheet.

Example: A Driver is involved in two services (Home Delivered Meals and Transportation) where the units are episodic (number of meals & per trip), that is, they do not measure time increments. Estimated percentages of time spent in each service should be input in the row corresponding with the employee titles and the “% Of Time” column. Therefore, based on the fact that the van driver spends 2 hours a day in home-delivered meals and 6 hours a day transporting clients, the grantee allocates 25% in home-delivered meals and 75% in transportation.

Step 2: Column B – Last Name – Enter the name of person listed in Column A. Step 3: Column D – Annual Wages & Benefits – Enter each employee’s annual salary and individual fringe benefits. (This is calculated in Exhibit B, column M.) Personnel Expenditures

Salary and wage costs include compensation paid to personnel for services rendered during the budget year or contract period. These costs encompass administrative, space-related (janitorial), and all service areas (except for volunteer staff who may not be paid salaries or wages). Donated personnel are not factored into the determination of service cash costs.

Step 4: Column E - Total Hours – Enter each employee’s total annual hours. Step 5: Columns F-H – General Admin Cost Pool – Allocates percentage of staff time that is not directly assigned to a specific service, but benefits all services non-directly. (For example Executive Director, fiscal and clerical staff). Input the projected % of time spent on each service. Step 6: Columns I-K – Janitor Salaries Only – List only paid janitorial personnel on staff. If janitorial service is contracted, it will be listed in Exhibit E (row 27) & Exhibit F (row 27) - Support Costs under Space Expenses.

5

Note:

Space salary and wage allocation methodology - Space related wage and salary costs include only the janitorial staff that has the responsibility to maintain the physical space. Salary costs are assigned on a percent of time basis. This does not include contract staff, but direct employees of the grantee only.

Step 7: Columns L-AF – Services - Enter the projected percentage (%) of time each staff member spends providing services. Columns AG-AH – All Other – Employee time and salaries not directly assigned will default to these columns. This is used to account for personnel time and wages not associated with Agency funded services. Once you have entered staff position, name, total wages and benefits, total service hours, and you have allocated each employee’s time based on how they spend their time in various services or to the appropriate “cost pool”, then the spreadsheet will add all of the information together. The total wages, percent of wages and benefits, and total hours per service are calculated at the bottom of Exhibit C – Personnel Costs from the information entered. Once all personnel allocations are complete, the totals at the bottom of each column will indicate the number of annual hours spent in delivering these services, as well as a total wage cost. The spreadsheet pulls these summary pieces of information to calculate the percent of total grantee wages and total grantee hours used in each service. These statistics are carried forward to the Exhibits E & F - Support Costs Spreadsheets and used as allocation statistics for two cost pools.

6

EXHIBIT D – VOLUNTEER TIME Excel Spreadsheet Helpful Hint: Totals from Exhibit D – Volunteer Time (row 30) go into Exhibit F - Support Costs (Expenses) (row 151). These should update automatically. Be sure to reallocate any expenses charge to General Admin, Space, Transport and Support Cost Pools. Use Exhibit D - Volunteer Time Spreadsheet to help calculate “in-kind” cost of volunteers and to allocate the costs to different services, if the volunteers help in more than one (1) service. Multiple volunteers for the same position may be combined on one row. Estimate the total number of dollars and hours for all volunteers doing the same job. Step 1 - Column A - Position – Input the title of Volunteer Positions. Step 2 - Column B – Ignore – Step 3 - Column D – Annual Wages – Input the volunteers’ salary, as if they were paid to do their position. Step 4 - Column E - Annual Hours – Input the volunteers’ total annual hours. Step 5 - Columns F-K – Benefit Costs - When you enter the appropriate percentages in row 6, the spreadsheet will automatically calculate your FICA, Workman’s Compensation and Retirement. Input total cost of other benefits by volunteer position, if known. Otherwise, input fringe benefit costs that would be paid for volunteers on benefits row in Exhibit F - Support Costs (Expenses) (Fringes - row 152). Step 6 - Columns L-M – Total Fringes and Total Wages and Fringes – The spreadsheet calculates these non-cash costs. Step 7 - Columns O-X – Percent of Time - Input % of volunteer time spent in each service. Step 8 - Columns Z-AI – Allocated Dollars – Exhibit D - Volunteer Time calculates “in-kind” costs of volunteers by service for input into Exhibit F Support Costs (Expenses) (row 151). This should update automatically.

7

EXHIBITS E & F – SUPPORT COSTS (E - Donated Expenses & FExpenses) Note: Specific Exhibit E information begins on Page 12. Excel Spreadsheet Helpful Hint: The numbers calculated in Exhibit E (row 137) should update Exhibit F - Support Costs Spreadsheet under NonPersonnel Non-Cash Match (row 162). These should update automatically. Excel Spreadsheet Helpful Hint: Enter the total amount of the annual projected cash expenditure (or cash value of donated service) for each expense item in Column B on this spreadsheet, before you assign this cost to a service(s). If you do not assign this cost, then it defaults to the cost pool, if one is available. On this spreadsheet, it does not default to “All Other” as it did on the Exhibit C - Personnel Costs. Expenses that are not automatically charged/allocated to a service are highlighted in “neon aqua” color. The service grantee must decide how to allocate these expenses to the OAA services(s). Exhibits E & F - Support Costs, illustrate the total cash costs, reallocate the cost pools, and calculate the cost per unit or service in total. After cash costs have been allocated to all services, then donated personnel and non-cash match are detailed to determine the potential cost of services. It should be noted that several of the statistics used to allocate costs in Exhibits E & F are developed in Exhibit C. Much of the grantee’s non-direct costs are allocated on the amount of personnel expenditures associated with each service. Exhibit E – Support Costs (Donated Expenses) is identical to rows 8-138 of Exhibit F - Support Costs (Expenses). Its purpose is to allocate donated services expenses to the proper OAA service. Listed in Column A in are the line-item budget expenditures beginning with salary and wages and fringe benefits. Listed across the top of the spreadsheet are the cost pools and services, much like in the Exhibit C - Personnel Costs. Across the top the percent of wages (row 9) and percent of hours (row 10) rows should be the figures generated on Exhibit C - Personnel Costs (row 70 wages & benefits & row 72 - time) and brought forward (automatically updated) to Exhibit E – Support Costs (Donated Expenses) and Exhibit F - Support Costs (Expenses).

8

Step 1 - Column B – Input the total projected annual cash costs for each row item as appropriate.  The row-item expenditures related to Fringe Benefits that have not been included in salaries in Exhibit C - Personnel Costs – are entered in the Fringe Benefits section of the spreadsheet. Fringe Benefits will be allocated to the cost pools and services based on the percent of wages per service. (If the exact figure of fringe benefits absorbed in this service is known, a direct manual allocation to the service can be used to override the formula). The remaining row-item expenditures are apportioned to the cost pools and/or services using either a manual assignment directly to a particular service or services or an automatic default to a cost pool. Associated costs are distributed to the services manually if documentation supports the direct allocation. 

Travel Expenses – Includes all staff and volunteer paid travel expenses. All travel expenses must be assigned to a specific service. They will not default to a cost pool.



Space Expenses – Includes building depreciation, insurance, maintenance/janitorial contracts, building repairs, rent, utilities and other space expense. Any cost that is not manually assigned to a service, defaults into the Space cost pool and will be allocated based on the square footage utilized by each service.



Vehicle Expenses – Includes all vehicle support costs including gas, oil, insurance, and maintenance. Any vehicle operation cost that is not manually assigned to a service, defaults into the Transport cost pool.



Meal Expenses – Includes Capital Kitchen Equipment Depreciation/Usage Fee, disposable supplies, home delivered meal packaging, meal service contracts, production costs, raw food costs, and other meal costs. All costs must be manually assigned to specific services of congregate meals, homedelivered meals, or to central kitchen operation. They will not default to a cost pool.



Supply Expenses – Includes office/paper supplies, site supplies, computer supplies and other supply expenses. Any cost that is not manually assigned to a service defaults into the Support cost pool.



Capital Equipment (including Computers) – Includes the depreciation/usage fee (not cash expenditures) for grantee equipment and capital equipment purchases. The definition of capital equipment is any equipment that costs $5,000 or more and has a useful life of one year or more. Equipment will be an allowable cost for outright purchase during a 9

fiscal year, but it must be purchased according to IDOA standards and have Agency approval. All costs must be manually assigned to a specific service or cost pool. They will not default to a cost pool. 

Other Operating Expenses – Includes training expense/meeting expense, background checks, employee testing, telephone and other telecommunications such as Internet access, postage, insurance, audit/legal fees, copy/printing, advertising, dues/subscriptions, equipment maintenance, other contractual or consulting services (not program related), vehicle operations (not program related), and other miscellaneous operating costs. Any cost that is not manually assigned to a service defaults into the General Administration cost pool.

Step 2 Columns D-G – General Admin, Space, Transport and Support Cost Pools and Columns H-N – Services which are detailed on Exhibit C carry forward to this page. After costs have been assigned, then the system adds the total allowable cash costs for each service. Excel Spreadsheet Helpful Hint: There are spreadsheet Differences listed on rows 79, 117, 138, 187 & 193. If this number does not match the number below it, then the spreadsheet is out of balance. This means that you have not allocated all costs listed in Column B of this spreadsheet. Review the costs that do not automatically default to a cost pool (travel, capital equipment, and meals.) Once all costs have been allocated, the spreadsheet should balance. Step 3 - Column P – Check Total. This is calculated by the spreadsheet to make sure the expenses are allocated. If the dollars in this column don’t match dollars in Column B, some expense(s) is not allocated. Remember, you must allocate those expenses manually that are highlighted in the “neon aqua” color. There are four costs pools that reallocate costs to the various services. You enter data in some sections and/or the formulas calculate the information already entered into the spreadsheet.

10

Excel Spreadsheet Helpful Hint: This section of the Excel Spreadsheet performs the following functions using formulas and additional information entered into this section. Step 4 - Reallocate Space Costs – Enter the square footage occupied (Exhibit F, row 92) by each service to allocate the space costs among services using square footage occupied as the allocation statistic. (This will automatically update Exhibit E, row 92). Step 5 - Reallocate Transport Costs – If you have entered information about drivers’ time on Exhibit C and there are vehicle operating costs that defaulted to the Transport cost pool, then this information will calculate automatically. Step 6 - Reallocate Support Costs – This should calculate automatically based on percentage of total employee hours in a specific service. No data entry is necessary in this section. Step 7 - Reallocate General Administration Costs – This should calculate automatically based on percentage of total employee wages in a specific service. No data entry is necessary in this section. Step 8 - Reallocate Central Kitchen Costs – This should calculate automatically based on percentage of total number of meals in each meal program. No data entry is necessary in this section.

11

EXHIBIT E – SUPPORT COSTS (DONATED EXPENSES) Excel Spreadsheet Helpful Hint: Identify the type of donated support in Column A. Then enter the total value in dollar amounts of the donated expense in Column B. You may manually allocate this value to the specific services or cost pools, or let it default to the cost pool. Use Exhibit E for calculating allocation of donated expenses. Use Exhibit D-Volunteer Time to calculate and allocate Volunteer time. STEP 1 – INPUT “DONATED EXPENSES” INTO COLUMN B. Valuation of Donated Support

Donated or non-cash support represents the value of the service or item the grantee is receiving. This support is separated in the costing model and isolated. Segregating this support allows the grantee to isolate those potential costs they would have to pay should the donated support no longer be available. This also provides information about potential costs, should such circumstance arise. Donated support costs indicate the cost to the grantee of soliciting and developing this assistance. This cost is an allowable grantee cost, and should be included in the costing model under the service for which the effort is expended, or in the General Administrative cost pool if a specific service cannot be identified.

VOLUNTEER TIME MAY BE CALCULATED IN EXHIBIT D - “VOLUNTEER TIME” OR DIRECTLY IN EXHIBIT F - SUPPORT COSTS (EXPENSES) SPREADSHEET Be sure to include both Wages and Benefits in the cost of Volunteers. Personnel: Paid Personnel/Not Charged to Reimbursed Services Volunteer Staff

Donated personnel support includes personnel assigned to work as staff of the grantee organization but who are not paid wages or are subsidized from a non-agency funding source. There are two types of donated personnel: paid personnel not charged to reimbursed services and volunteer staff. Valuation: The value of donated time equals the amount of time donated for service times the comparable hourly rate of compensation. Rates should be consistent with regular rates paid for similar work performed within the organization or in the labor market. The minimum wage rate can be used as well. The value of this support can be included as matching funds on federal programs.

12

Fringe Benefits:

Donated fringe benefits support associated with the salary and wage charges incurred through volunteer work or staff funded through other sources. Valuation: Fringe benefits for volunteer positions are calculated by comparing the fringe benefits for paid positions of a similar nature within the grantee or by comparing with positions doing similar work in the labor market.

Travel Expenses:

Travel support donated by individuals or agencies outside the organization. Valuation: Travel support is valued and allocated in the same manner as non-volunteer travel costs.

Space Expenses:

Donated building space indicates donated space or space paid for by non-agency resources. If the building is donated, and the donor transfers title to the property, the value should be determined as if the property had been purchased at fair market price or at the assessed value of the property, whichever is less. Building use charge value should be calculated by either depreciation or a use allowance charge. Valuation: Building space can be valued through depreciation, or a use allowance (federal cost principles stipulate that use allowance computations cannot exceed two percent of the acquisition cost, and may be used for as long as the building has economic usefulness). Donated space-rent may be valued at the market rate based on square footage. Maintenance, utilities and insurance expenses of the grantee donated or paid for by non-agency resources constitute donated support. Valuation: The support is valued at current market prices.

Vehicle Expenses:

Program vehicle expenses that are donated or paid by non-agency resources make up donated support.

Meal Expenses:

Donated meals are meals donated by other agencies or

13

paid for by non-agency resources. Valuation: Donated meals/food is valued at current market prices. Supply Expenses:

Office, computer and site supply expenses that are donated or paid by non-agency resources make up donated support. Valuation: The support is valued at current market prices.

Capital Equipment Expenses:

Any Capital equipment expenses that are donated or paid by non-agency resources make up donated support. Valuation: The support is valued at current market prices.

Other Expenses:

Any other expenses that are donated or paid by nonagency resources make up donated support. Valuation: The support is valued at current market prices.

DONATED - NON-CASH MATCH REALLOCATION SECTION Excel Spreadsheet Helpful Hint: There are spreadsheet differences (Rows 79, 117 & 138) to assure that all donated personnel or non-cash match has been allocated to a specific service. If this number does not equal the number below it, then review each donated personnel or non-cash match items listed to assure that it has been allocated. This section will reallocate donated support allocated to the General Admin Cost Pool (column D), the Space Cost Pool (column E), the Transport Cost Pool (column F) and the Support Cost Pool (column G). The spreadsheet updates this automatically. TOTAL DONATED COSTS - NON-CASH MATCH The spreadsheet will calculate the total costs of donated personnel and other non-cash match including the reallocated portions to determine Total Donated Non-Cash Costs of a service. The number of billing units will be automatically updated from the units input in Exhibit A - Units. The spreadsheet will automatically calculate Non-Cash Unit Cost (row 142).

14

EXHIBIT F – SUPPORT COSTS (EXPENSES)

TOTAL ACTUAL COSTS (Row 116-142) Excel Spreadsheet Helpful Hint: “Units” from Exhibit A - Units Spreadsheet (row 8) should automatically update “Number of Billing Units” (row 140). The spreadsheet calculates the Total Actual Cash Cost for each service. The spreadsheet calculates the Cash Cost per Unit of Service.

DONATED PERSONNEL & NON-CASH MATCH SECTION (rows 144-169) Excel Spreadsheet Helpful Hint: “Total Volunteer Costs” from Exhibit D – Volunteer Time Spreadsheet (row 30) should automatically update “Volunteer Time from Exhibit D” (row 151) Excel Spreadsheet Helpful Hint: “Total Donated Costs” from Exhibit E Support Costs (Donated Expenses) Spreadsheet (row 137) should automatically update “Non-Cash Match from Exhibit E” (row 162). Input Volunteer Wages and Fringe Benefits if needed.

NON-CASH MATCH REALLOCATION SECTION (rows 171-186) This section will reallocate donated support allocated to the General Admin Cost Pool (column D), the Space Cost Pool (column E), the Transport Cost Pool (column F) and the Support Cost Pool (column G). The spreadsheet updates this automatically. The spreadsheet calculates the Total Non-Cash Cost for each service. The spreadsheet calculates the Non-Cash Cost per Unit of Service.

POTENTIAL TOTAL COST (row 197) Excel Spreadsheet Helpful Hint: “Units” from Exhibit A - Units Spreadsheet (row 8) should automatically update “Number of Billing Units” (row 195). The potential cost of services includes actual cash costs along with the additional donated support and non-cash match. This is the potential cost of the service if the donated support was not made available to this service. The potential cost should always be higher than the actual cash cost if the service received any donated support or non-cash match as detailed in this section.

15

EXHIBIT G – REVENUE SOURCES Excel Spreadsheet Helpful Hint: “Total Costs” from Exhibit F - Support Costs (Expenses) Spreadsheet (row 192) should automatically update “Total Expenditures” (row 10). Excel Spreadsheet Helpful Hint: “Total Non-Cash Match” from Exhibit F Support Costs (Expenses) Spreadsheet (row 175) should automatically update “Non-Cash Match” (row 39). Excel Spreadsheet Helpful Hint: “Units” from Exhibit A - Units Spreadsheet (row 8) should automatically update “Units” (row 66).

Exhibit G – Revenue Sources is intended to list all the sources of revenue, by service: federal, state, NSIP (nutrition services only), county, city, donations, fund raising, other cash, and program income, along with donated expenses and volunteer time. It will also show if revenues and expenses are in balance. It will correctly calculate match, to determine if the required minimum 15% Grantee match is met. It will calculate potential Agency unit reimbursement rate. Step 1 - List all revenues by service. For Agency Grant money, list it all under the correct Federal Title(s) (i.e.: III-B or III-C 1) in Rows 14-16. Leave row 22 (NSIP) blank. Step 2 – If you receive other Federal Grant money, input it on row 23. Step 3 – If you receive other State Grant money, input it on row 35. Step 4 – Input program income on row 42. Step 5 – If you receive “other funds” (non Federal or State funds), allocate the money to the proper fund(s), if it is restricted. For unrestricted “other funds” (cash), allocate the money as needed amongst services. (Rows 46-57) The spreadsheet will calculate any differences between Expenses and Revenues (row 63). Step 6 – If your revenues and expenses do not balance, then review your allocation of “other funds”. Also review Exhibits C, D, E, F for proper allocations. These allocations must be based on actual hours and spending, not because there is a surplus or deficit in a service. These may be reviewed at a future monitoring visit. Step 7 – If your revenues and expenses still do not balance after reviewing again, call the Agency to discuss the situation.

16

The spreadsheet will calculate the percent of match by category (rows 60-62). The spreadsheet will calculate the potential reimbursement unit rate (row 67). The spreadsheet will calculate the program income unit rate (row 69).

17