How to Complete the UCM Excel Spreadsheet | Administration 5600 Manual

Before setting up any data, go into Line 1 Column A and enter the name of the Provider

Go to Line 2 Column H and Click on “Choose a Service” and a drop-down arrow will appear

Click on this arrow and a list of services from the DAS Taxonomy of Services will appear; select one service.

Go the next “Choose the Service” and repeat this process for each different service until all services have been listed for this provider.

Staff List is the inventory of all paid employees for a given provider. It should include employees’ names and positions.

Staff includes drivers if the provider directly operates client transportation or delivers meals using drivers employed by this provider. Only include the time the driver delivers meals.

To be able to associate personnel costs (Base Wages and Fringe Benefits) with a specific employee or position and allocate them to a service or cost pool.

To determine a unit cost for a specific service, costs of that service must be allocated correctly.

Payroll registry of complete Staff List

There are two methods to input a Staff List, either on the individual name level or on a position / role level.

Choosing the position level requires you to calculate Base Wages, Productive Hours, and Hour Types based on the number of staff in that position before inputting them into the spreadsheet. This choice is more ideal when employees of a position have the same salary and hours worked.

Choosing the individual levels allows you to enter Base Wages, Productive Hours, and Hour Types specific for that employee, and is therefore ideal when employees have different salaries and hours worked.

A provider employs tutors to give language guidance to older Americans.

According to last year’s payroll documents, two people were employed: Mary Smith and Emily Cooper.

They were both tutors.

Mary makes $45,000, while Emily makes $50,000. Both Fringe Benefits Rates are 22%.

Neither tutor works General Admin or Shared Building Space Hours. Mary works 1,800 Billable Hours, while Emily works 2,000.

Different hours worked further supports that it may be easier and more accurate to choose Individual Level inputs.

Input Mary Smith into one row of Column A of the Personnel Inputs sheet and EmilyCooper into another row. Enter “1” into Column B of that same sheet in each row. Calculate their respective Productive Hours and Billable Hours before entering each, as shown below:

Neither tutor works General Admin or Shared Building Space Hours. All of their hours are Billable Hours.

Input Mary Smith into one row of Column A of the Personnel Inputs sheet and EmilyCooper into another row. Enter “1” into Column B of that same sheet in each row. Enter their respective information in the remaining columns, as shown below:

A different provider also employs tutors to give language guidance to older Americans.

According to last year’s payroll documents, two people were employed.

This has no relevance to an input level decision.

They were both tutors.

By having the same role, this may make it easier to choose a Position Level input.

Both make $60,000 annually with Fringe Benefits rates of 25%.

Having the same salary supports it being easier and more accurate to choose a PositionLevel input.

Neither tutor works General Admin or Shared Building Space Hours. Both work 1,904 Billable Hours.

Both tutors take 10 vacation days, 8 holidays, and 4 sick days.

Input “Tutor” into a row of Column A of the Personnel Inputs sheet. Enter “2” into Column B of that same sheet and row. Calculate the Base Wages, Productive Hours, and Billable Hours before entering each, as shown below:

Both tutors take 10 vacation days, 8 holidays, and 4 sick days, or (10 + 8+ 4) 22 annual leave days.

Neither tutor works General Admin or Shared Building Space Hours. All of their hours are Billable Hours.

Input “Tutor” into a row of Column A of the Personnel Inputs sheet. Enter “2” into Column B of that same sheet and row. Calculate the Base Wages, Productive Hours, and Billable Hours before entering each, as shown below:

Enter names or positions in Column A of the Personnel Inputs sheet

Enter the number of the staff in that position or by that name in Column B of the Personnel Inputs sheet

Base Wages is the salary portion of an employee’s total compensation, or the amount an employee earns before taxes and other deductions.

Fringe Benefits Dollars are the other portion of an employee’s total compensation. These are perks provided to an employee which are discussed further in the Fringe Benefits Rate section.

To calculate an employee’s total compensation, also known as Total Wages and Benefits in the UCM spreadsheet. This allows providers to capture the total cost of employing staff in the UCM.

Payroll documentation

For Individual Level Inputs: Salary of that Individual

For Position level Inputs: Sum of Salary of Staff 1 + Staff 2 +…

Base Wages = 7 × $50,000 = $350,000

Input the Transition Coordinators’ Base Wages into the Personnel Inputs tab of the spreadsheet by entering “Transition Coordinator” in Column A, “7” in Column B, and $350,000 in Column C, as shown here:

Alex is a registered dietitian making $50,000 as an annual salary.

Julia is a nutrition counselor making $60,000 as an annual salary.

Input Alex and Julia’s Base Wages into the Personnel Inputs tab of the spreadsheet by entering each name in Column A, “1” in Column B, and the respective salary in Column C, as shown here:

The Base Wage for each staff member (or staff type) should be entered into Column C of the UCM Personnel spreadsheet.

Fringe benefits, or “perks”, are non-wage compensation provided by employers to employees, in addition to their regular salary.

These can vary based on employer, but common examples include a 401k, workmen’s compensation, health insurance, FICA, paid vacation, tuition assistance and more.

The Fringe Benefits Rate is the percentage of salary that represents the fringe benefits costs that a provider incurs on behalf of their personnel.

It is part of an employee’s total compensation package, and part of the total cost of employing staff, in addition to their salaries and wages earned. Hence it is captured as a cost in the UCM.

In the UCM Spreadsheet, the Fringe Benefits Rate is multiplied by Base Wages to calculate Fringe Benefits Dollars. This is then utilized in the calculation of Total Wages and Benefits Cost to help calculate unit cost per service. Additional explanation is provided in the “Support_Outputs” section.

Existing payroll documentation for all staff

To calculate Fringe Benefits Rate, fringe benefits costs are divided by annual salaries per employee. This decimal is then multiplied by 100%.

The UCM only reimburses costs for productive hours, so costs for non-productive hours need to be removed from the work week.

They are the basis of calculating Percentage of Staff Time spent on services or cost pools. Without it, the amount of time and cost allocated to each service or cost pool cannot be determined, and unit cost per service cannot be calculated.

Previous time studies or timesheets

Holiday and leave policies

Deduct hours for any annual leave or non-productive time from annual hours.

Productive Hours = Annual Hours – (Holidays + Vacation + Sick Leave + Other Leave)

Pat works full-time, at 40 hours/week:

Annual Hours = 40 hours/week × 52 weeks/year = 2,080 hours

Per his annual leave policy, he has 8 days holidays, 10 vacation days and 6 sick days.

Annual Leave Hours = (8 + 10 + 6) = 24 days

24 days × 8 hours/day = 192 hours

Pat does not anticipate taking other leave.

Productive Hours = 2,080 – 192 = 1,888

The UCM Spreadsheet uses General Admin, Shared Building Space, Billable, and All Other Hours to calculate the Percentage of Staff Time per Service, and the Wages and Benefits Cost per service, which are necessary for unit cost per service calculations.

All four Hour Types sum to Productive Hours in the figure below.

Employment contracts

Holiday, sick leave, and other leave policies

Timesheets with the number of hours spent on activities

Billable Hours = Total Annual Work Hours minus (Holiday + Sick + Vacation + Other Leave + Travel + Training + Administrative Time).

General Admin Hours = Total Annual Work Hours minus (Holiday + Sick + Vacation + Other Leave + Direct Service Hours).

Shared Building Space Hours = Total Annual Work Hours minus (Holiday + Sick + Vacation + Other Leave + Direct Service Hours). Same as General Admin Hours.

All Other Hours = Productive Hours minus (Billable + General Admin + Shared Building Space Hours)

She works full-time, at 40 hours/week

Per her annual leave policy, she plans to use all 8 holidays, 10 vacation days and 3 sick days:

Mary spends no time on direct service delivery.

Admin Hours = 2,080 – 168 = 1,912 hours

Input Mary’s 1,912 Admin Hours into the Personnel Inputs tab of the spreadsheet in Column F and the row associated with Mary, as shown here:

Kyle works 40 hours per week

Per his annual leave policy, he has 10 holidays, 10 vacation days and 4 sick days:

Similar to last year, Kyle anticipates he will spend 50 hours on training. He does not spend time on administrative tasks or have travel expenses.

He works full-time, at 40 hours/week. He spends 15 hours/week on group services and 25 hours/week on individual sessions:

Per his practice’s annual leave policy, he has 10 holidays, 10 vacation days and 8 sick days. These are divided equally amongst his services.

Similar to last year, Nick anticipates he will spend 10 hours on training for counseling overall. These are divided equally amongst his services.

He does not spend time on administrative tasks or have travel expenses.

Group Billable Hours = 780 – 88 – 5 = 687 hours

Individual Billable Hours = 1,300 – 136 – 5 = 1,159 hours

She works full-time, at 40 hours/week. **Admin Annual Hours = 40 hours/week × 52 weeks/year = 2,080 hours

Per her annual leave policy, she has 10 holidays, 5 vacation days and 4 sick days.

She spends 2 hours/week directly providing Elderly Legal Assistance. This is her Direct Service Time or Billable Hours.

Direct Service Time = Billable Hours = 2 hours/week × 52 weeks/year = 104 hours

General Admin Hours = 2,080 – 152 – 104 = 1,824 hours

Expenses entered in the Support Spreadsheet represent money spent in the process of providing a service, that do not directly relate to employee compensation.

These are usually physical items or supplies.

They may also be services or activities, such as rent or utilities.

Goods are physical items, such as supplies, food, or clothes.

Services are intangible activities, such as vehicle maintenance and utilities.

Capital is assets that can be used for multiple years to generate more money, such as buildings or cars.

To capture the supplies and services costs incurred and allocate them to specific services or cost pools to calculate unit costs per service.

Cost pools are groupings of expenses that are related to an activity or function. The four UCM cost pools within the Support Spreadsheet are the General Administrative Cost Pool, Shared Building Space Cost Pool, Support Cost Pool, and Client/Meal Transportation Pool, which are depicted below:

Expenses should be assigned to services only if there is supporting documentation that ties them to a service. Otherwise, they will be auto-assigned to a cost pool.

Cost pools that expenses can be allocated to varies by expense per this table:

Documentation from general ledger statements, paid invoices, vouchers, and logs/journal entries

Vendor Contracts (for Service Contract Expenses)

Gas is $500 annually.

Car insurance is $1,000 for the year.

Car maintenance, such as oil changes and tire rotations, was $250 throughout the year.

These inputs are shown below:

If these expenses were not allocated to the “Transportation – Individual” Service Column, they would be automatically allocated to the Client/Meal Transportation Cost Pool in Column F.

Computer maintenance was needed to repair broken keys and clear viruses, which cost $100 over the course of the year.

She also needed to buy a new charger and mouse for $50.

She took a virtual training course on group counseling for $25.

Depreciation is the process of spreading the cost of an asset over the period of time it is intended to be used.

Commonly used to measure the cost of long-term, physical assets, such as buildings and equipment.

In the UCM, depreciation is calculated for building expenses and capital equipment expenses.

Straight-line depreciation is the most common method of depreciation used on an organization’s financial statements.

It allocates the cost of the asset evenly over time.

Other depreciation methods may be used in the UCM if supporting justification and calculation is provided.

Providers may want to consult with their financial specialist to confirm methodology and calculation.

Annual depreciation expenses are commonly calculated as:

On July 1, 2022, Elizabeth purchased a company vehicle for $10,500 to deliver meals to seniors.

Elizabeth initially paid $10,500 for the vehicle.

The estimated amount that the car will be worth at the end of its use is $2,000, because it depreciates each year from wear and tear.

The estimated amount of time the car can be utilized for is 10 years.

Input $850 in line 35 in Column B for Capital Equipment Expenses in the Support Inputs Tab, as shown below:

To prevent overestimating the general administrative costs for larger contracts.

The Office of Management and Budget only considers $25,000 of large contracts to be attributable to general administrative costs for contracts over $50,000. Any excess of this amount is not used to distribute costs from the General Administrative Cost Pool to services.

Example: Jane is a provider and contracts with Bob to do some plumbing work on the bathrooms in her recreational center. The amount of time and cost Bob spends on completing paperwork does not differ if the contract is $100,000 or $150,000. Therefore, a flat $25,000 rate for the general administrative cost of this contract is applied, regardless of the contract price.

Vendor contracts

Invoices

Receipts

Service Subcontract Adjustment = Contract Amount - $25,000

Example of calculating the Service Subcontract Allowance*

Tony contracts a software designer for $60,000 for Service 1 who takes the organizational ideas Tony has and turns them into visuals for an elder to see.

There is no service subcontract for Service 2.

Input Tony’s Service Subcontract Adjustment in line 96 of the Support Inputs tab under the column associated with Service 1, Map Habits, as shown below:

Salaries or payroll documents

Building blueprints or square footage documentation

Receipts

Square Footage is a measurement taken of a room or space

Salary Ratio = (Sum of all salaries per Service / Sum of all salaries of workers using the space) * Square footage of space

Total Building Expenses are $100,000 and need to be allocated among the five services housed in the building.

The building is 5,000 square feet.

Room 1 houses Alzheimer’s service; Room 2 houses Congregate Meal services; Room 3 houses Homemaker Services, Nutrition Counseling Services, and another Office of Management (OM) service.

Room 1 accounts for 500 square feet of the building space and is devoted to the delivery of Alzheimer’s services.

Square Footage for Room 1 = 500 sq ft

Room 2 accounts for 2,000 square feet of the building space and is used for the provision of Congregate Meals services. The site includes the kitchen for preparing meals and the dining room for serving.

Square Footage for Room 2 = 2,000 sq ft

Room 3 is 2,500 square feet, but it is not possible to determine the space split amongst the three services with physical measurements: Homemaker Services, Nutrition Counseling Services, and Office of Management (OM) service.

Room 3 is 2,500 square feet.

Homemaker Services = 34% × 2,500 = 850 sq ft

Nutrition Counseling = 45% × 2,500 = 1,125 sq ft

Office Management = 21% × 2,500 = 525 sq ft

Below is a depiction of how salary ratios were used divide Room 3:

Input the square footages in line 99 of the Support Inputs tab under the column associated with each service, as shown below:

A billing unit is how one unit of measure is defined for each service.

By dividing total costs by the number of billing units, the unit cost per service is determined:

Actual Cost Per Unit of Service = Total Actual Costs By Service ÷ Number of Billing Units Documentation providers should gather to input Billing Units

Invoices

Service receipt documentation

Ryan has a log of last year’s services provided. He provided 2,000 meals last year.

Input billing units in line 101 of the Support Inputs tab under the column associated with the service, as shown below:

To help providers identify costs they may have to pay, should the in-kind support disappear, the UCM calculates Potential Unit Costs.

Calculating Potential Unit Costs provides the following benefits:

Determining unit costs for services with in-kind donations, and reimbursement for them, are separate processes.

The unit cost for a particular service with in-kind donations is the same, regardless of the funding source (Federal, State, Local).

When entering values for in-kind donations, providers should not consider reimbursement for or funding source of these items or services. Rather, they should be entered based on the estimated value, and the number of units of services provided.

Log of volunteer hours (including the name of volunteer, hours worked, date, associated program, and purpose of volunteering)

Salary data for positions volunteers are working

Fringe benefits rate

Depreciation of equipment donated

Receipts from donors for items donated

Itemized donation list (including donor name, description, quantities, condition, estimated values, date, associated program, and purpose of volunteering)

In-kind Donations do not impact the Actual Cost per Unit of Service within the UCM, only the Potential Unit Cost.

The UCM first calculates Actual Cost per Unit of Service without in-kind donations by dividing the Total Allowable Costs by the Number of Billing Units.

It then calculates Potential Unit Cost with in-kind donations to measure the total cost of providing services. It does this by adding the Valuation of the In-kind Donation to the numerator to make it the sum of the Total Allowable Costs and the Valuation of the In-kind Donations divided by the Number of Billing Units.

Last year, a Meals on Wheels provider, Matt, spent $15,000 on raw food, $20,000 on labor, $10,000 on meal delivery costs, $3,000 on utilities, and $238 on disposable supplies.He served 9,440 meals.

Meals on Wheels received personnel volunteer support to provide meals, valued at $9,994 (per Example 1 above).

The Potential Unit Cost is $6.17:
$58,232 ÷ 9,440 = $6.17

This represents a $1.06 decrease in unit cost by using volunteer support:
$6.17 - $5.11 = $1.06

To calculate the cost of providing employees with perks (i.e., workers comp, retirement, healthcare, etc.).This is a part of an employee’s total compensation and is utilized in the calculation of Total Wages and Benefits Cost to calculate the unit cost per service.

Base Wages × Fringe Benefits Rate

Purpose
To calculate the total cost of employing each worker, including their base wages along with any benefits. It utilizes the Base Wages calculated in the Personnel Inputs tab and adds Fringe Benefits Dollars to derive the Total Wages and Benefits cost.

Methodology
\(\text{Base Wages} + \text{Fringe Benefits Dollars}\)

Purpose
To calculate the percent of staff time spent on General Admin tasks entered in the General Admin Hours column of the Personnel_Inputs sheets, relative to Productive Hours.

Methodology

Purpose
To understand how much of the Total Wages and Benefits costs are incurred from administrative tasks.

Methodology
(Admin Hours ÷ Productive Hours) × Total Wages and Benefits or General Admin % × Total Wages and Benefits)

Purpose
To calculate the percent of staff time spent on building maintenance tasks.It is derived from the Productive Hours and Shared Building Space Hours in the Personnel Inputs tab.

Methodology
(Shared Building Space Hours ÷ Productive Hours)

Purpose
To understand how much of the Total Wages and Benefits costs incurred are from building maintenance tasks.

Methodology
(Shared Building Space Hours ÷ Productive Hours) × Total Wages and Benefits or (Shared Building Space % × Total Wages and Benefits)

Purpose
To determine the cumulative number of hours spent on each service.

Methodology
(Productive Hours × % of Staff Time for that service)

Purpose
To calculate the amount of time spent per service relative to Productive Hours.

Methodology
(Unit of Service × Billable Hours) ÷ Productive Hours

Purpose
To calculate personnel costs per service. It helps derive the unit cost per service by calculating the wages and benefits cost per service.

Methodology
(% of Staff Time × Total Wages and Benefits)

Purpose
To calculate the percent of staff time spent that was not allocated to a service or cost pool. This functions to capture the cost that providers could not allocate elsewhere.

Methodology
(Unit of Service × All Other Hours) ÷ Productive Hours

Purpose
To calculate the cost of staff time that was not allocated to a service or cost pool.

Methodology
(All Other Programs % × Total Wages and Benefits)

Purpose
To sum all personnel costs, both by service and cost pool.

Methodology
Sums each column that lists costs (i.e. sum of General Admin Costs for all employees)

Purpose
To calculate proportion of personnel costs in services or cost pool relative to total personnel costs.

Methodology
Sum of all personnel costs for a service or cost pool ÷ Sum Total Wages and Benefits for all services and cost pools

Purpose
To calculate total hours spent by all staff per service or cost pool.

Methodology
Sum of all staff’s hours per service or cost pool

Purpose
To calculate personnel cost per unit. The unit of service is key in determining the unit cost because it is used to allocate the cost associated with each provided service.

Methodology
Always 1

Purpose
To calculate the percent of staff time allocated to a service or cost pool for all employees.

Methodology
\(\text{Sum of Billable Hours or General Admin Hours or Shared Building Space Hours} ÷ \text{Sum of Productive Hours}\)

Purpose
To pull the personnel expenses from the Personnel Inputs tab and auto-populate wages & benefits cost and hours to its associated service or cost pool.

Methodology

Purpose
To calculate the total cost of a service, or all services, including both personnel costs and expenses. Total Allowable Costs is the basis of determining Total Actual Cost by Service, which is then used to actual unit cost and potential unit cost.

Methodology
Wages and Benefits Costs + Expenses

Purpose

Purpose
To reallocate in-kind donations for Shared Building Space, Transportation and General Admin to a service in order to calculate potential unit costs.

Purpose
To calculate the total cost of providing a service including actual costs as well as donated and in-kind expenses. The total cost is utilized to calculate the potential unit cost per service.

Purpose
To calculate Unit Cost with In-kind Donations or the total unit cost per service, should providers not receive anticipated donations.

Structured educational or training sessions that are designed and delivered based on research and empirical evidence.

These workshops provide patients with knowledge and skills that have been shown to be effective in achieving specific outcomes or addressing certain issues.

EBPs have similarities and differences to other OAA programs

Similarities to other services include:

Differences from other services include:

Payrolls with hours worked and salaries

Vouchers, invoices, or receipts of materials or licensure payments

The most up-to-date Taxonomy which provides the requirements for workshops, standards for reimbursement, and units of measure to calculate unit cost

Personnel and Support costs associated with providing the workshop, must be entered in their respective line-items, as detailed throughout the manual and UCM instructional videos

Providers should note the following:

Confirm that the number of classes/sessions meets the workshop definition in the Taxonomy

Determine the correct unit of in the Taxonomy to enter the correct billing units in the Support Spreadsheet

Log time spent on obtaining certifications/licensure in the Support tab as 'Training/Meeting Expense'’' and not as billable hours in the Personnel tab

He makes $30/hour and his organizations fringe benefits rate is 25%

To promote and deliver services, he renews his certification for $30, purchases $40 worth of posters to advertise, and buys $50 worth of stretching bands for participants.

He rents out a room in a gym to hold the workshops in. This costs $80 per month, or $960 per year ($80 × 12)

David would begin entering his personnel costs in the Personnel_Inputs tab by following the steps:

David has entered all his personnel costs now. He will enter his additional expenses in the Support_Inputs tab by following the steps:

He makes $30 per session and his organizations fringe benefits rate is 22%

To promote and deliver services, he renews his certification for $20, purchases $15 worth of posters to advertise, and buys $60 worth of healthy snack samples. He also rents out space to host classes in for $250/month, or $3,000 that year ($250 × 12)

Sam would begin entering his personnel costs in the Personnel_Inputs tab by following the steps:

Sam has entered all his personnel costs now. He will enter his additional expenses in the Support_Inputs tab by following the steps:

Reimbursement requires completers, which is a defined number of participants and sessions/classes per workshop with the Taxonomy. Completers vary by workshop.

For example, a Tai-Chi workshop completer is one participant who attends 5 of 8 classes/sessions

The following are not reimbursed for: canceled sessions, no-shows, and session where the completer requirement is not met.

Providers may not be reimbursed for costs in their UCMs if there are not completers

When completing the UCM, providers should examine the number of completers in past years to estimate unit costs for the upcoming year

For providers without historical data, enter costs based on the planned number of workshops

Promote workshops through multiple channels (e.g., social media, email, newsletters, flyers, etc.)

Share testimonials or past success stories

Conduct targeted outreach to specific groups of older adults

Offer convenient scheduling options to accommodate different schedules

Send advance remainders to participants

Stay engaged with past attendees and invite them to future sessions

Provide incentives like free refreshments for attending workshops