Summary

The Unit Utilization reports provide bed and unit utilization collectively for both Individual and Family Household types.  These reports should be used in unison to create the required Goal 5 calculations for projects, based upon a user-defined reporting period. 

 

Procedure

Please review the instructions below, to create your calculations.  Sample spreadsheets are attached.

 

Report Location:  Data Analysis > Project Performance Reports Section

Looks:  

• Report 1:  Unit Utilization - Bed/Unit Inventory
• Report 2: Unit Utilization - Beds/Units Provided

 

Report 1:  Unit Utilization - Bed/Unit Inventory

This report shows the total available bed inventory during the reporting period.

 

Step 1:  Change the "Bed and Unit Inventory Start Date is before" the day after the last day of your reporting period.

Step 2: Select the project's name(s) from the dropdown list.

Step 3: Change the "Filters Date Filter" to the desired reporting period.

Step 4: Change the "Bed and Unit Inventory End Date is on or after" the first day of your reporting period.

Step 5:  Click "Run".

Step 6:  Download your results to an EXCEL spreadsheet.

 

Report 2:  Unit Utilization - Beds/Units Provided

This report shows beds occupied during the reporting period. 

 

Step 1:  Using the same reporting period as the Unit Utilization - Beds/Units Provided report, input the dates in the "Reporting Period Filter".

Step 2:  Select the project's name(s) from the dropdown list.

Step 3:  Update the project type.

Step 4:  Click "Run".

Step 5:  Download your results to an EXCEL spreadsheet.

 

Calculating the Bed/Unit Utilization

Bed /Unit Utilization Calculation = Total Bed/Unit Nights Provided (Column M - Unit Utilization - Bed_Units Provided.xls) / Total Possible Bed/Unit Nights (Column J - Unit Utilization - Bed_Unit Inventory.xls)

 

 

Step 1: To create the denominator, open the Unit Utilization - Bed/Unit Inventory report.  Only three columns will be used:  Full Name, Program ID, and Total Possible Bed/Unit Nights.

 

Step 2: Sum projects with multiple records:

Agencies that have multiple records have changes in bed inventory during the reporting period so "Days Beds/Units Available" will show a different number of days based upon when beds/units were available.  The "Total Possible Bed Nights" for these agencies must be summed first in order to get an accurate calculation for projects.

 

1. Rename the tab to "RAW".
2. Create a pivot table from the "RAW" tab in a new sheet.  Be sure to change the "Table/Range" to $A$2:$J$x (where "x" = the number of rows in your spreadsheet), if necessary.                                                 
3. Name the new sheet "Pivot".
4. Drag the following parameters to each area:

• Rows = Full Name
• Values = Sum of Total Possible Bed Nights

 

 

 

Step 3:  Create a new sheet and name it "Goal 5 Score" and add the following column headers: 

• Column A - Project Name
• Column B - Program ID
• Column C - Total Bed/Unit Nights Provided
• Column D - Total Possible Beds/Unit Nights
• Column E - Unit Utilization

 

Step 4:  Copy the Project Full Name from the "Pivot" tab - column A into the "Goal 5 Score" tab - column A (Project Name).

Step 5:  Copy the Grand Total column from the "Pivot" tab into the "Goal 5 Score" tab - column D (Total Possible Beds/Units Provided). 

Step 6:  Get the Program ID.  Use XLOOKUP to capture the Program ID from the "RAW" tab, using the Program ID.  Enter the formula in column B (Program ID).  From the example:  =XLOOKUP(A2,RAW!$A$3:$A$5,RAW!$B$3:$B$5)

Step 7:  To create the numerator, in column C use XLOOKUP to search for the Program ID in the Unit Utilization - Beds_Units Provided report and capture the "Total Possible Beds/Units Nights":

1. Open the Unit Utilization - Beds/Units Provided report.  Note: The only columns used will be "Program ID" and "Possible Beds/Units Provided".
2. Return to the Unit Utilization - Bed/Unit Inventory report and in Column C type the following formula, substituting the filename.xls with the file you are using:  From the example, =XLOOKUP(B2,'[Sample Unit Utilization - Beds_Units Provided.xlsx]sheet1'!$B$3:$B$4,'[Sample Unit Utilization - Beds_Units Provided.xlsx]sheet1'!$M$3:$M$4)  Note: if you receive "#N/A" values, make sure to adjust your formula for your spreadsheet.  It could also mean that no clients had a bed-night in your project during the reporting period.  Please Submit a ticket to the HMIS Helpdesk if you need assistance.
3. Copy this formula down the column to the end of your data.

 

Step 8:  Calculate the Unit Utilization (column E) using this formula:  =C2/D2  

 

 

1. Copy the formula down the column.
2. Format the cells to percentage.