RJM Excel Reports



Resource Justification Model

Data Analysis System

U.S. Department of Labor

Unemployment Insurance Service

200 Constitution Avenue N.W.

Washington, D.C. 20210

Prepared by:

HeiTech Services Inc.

4200 Forbes Boulevard

Suite 202

Lanham, MD 20706

May 1, 2003

Table of Contents

RJM Data Analysis System

Summary 3

Instructions 3

Report 1: Year to Year Variance (Yr – Yr) 4

Report 2: Non Personal Services – Per Staff (NPS-Staff) 4

Report 2a: Non Personal Services – Per Staff (NPS-Staff Sample) 4

Report 3: Non Personal Services – Per Workload (NPS-WKLD) 4

Report 3a: Non Personal Services – Per Workload (NPS-TOT-IC Sample) 5

Report 4: Minutes Per Unit (MPU) 5

Report 4a: Minutes Per Unit (MPU APP Sample) 5

Report 5: Benefit Payment Control (BPC) 6

Report 5a: Benefit Payment Control (BPC Sample) 6

Report 6: UI Performs Per Case Load (UIP) 6

Report 6a: UI Performs Per Case Load (UIP Sample) 6

Report 7: Support as Percent of Staff (SUP) 6

Report 7a: Support as Percent of Staff (SUP Sample) 6

Report 8: AST as Percent of Staff (AST) 7

Report 8a: AST as Percent of Staff (AST Sample) 7

Report 8b: AST + SUP as Percent of Staff 7

Report 9: Leave Factor (Leave) 7

Report 9a: Leave Factor (Leave Sample) 7

Report 10: Rank (Rank) 8

Report 10a: Rank (Rank IC Sample) 8

Report 10b: Rank (Rank SQ Feet Sample) 8

Report 11: State Dollars (State $) 8

Report 12: Non Personal Services – Per Workload (% Increase) 8

Report 12a: Non Personal Services - Per Workload (% Increase NPS Sample) 9

Data: Previous Year (PY) 9

Data: Current Year (CY) 9

Data: Next Year (NY) 9

Data: Request (RY) 9

RJM Data Analysis System

Summary

The following document details descriptions of each report tab in the RJM Data Analysis System Excel workbook. There are currently 13 reports, four sets of RJM data labeled PY, CY, NY, RY and one Variance table

Each report is followed by a sample report, which is complete with data. The data in the sample reports should not be deemed valid for reporting purposes. This data should only be viewed as a model of how a completed report should look.

The RJM Data Analysis System workbook will be posted on the RJM website located at . DOL headquarters will regularly update this workbook, therefore it is suggested that the user view the file date and revision date of the workbook located beneath the report title. The file date is important when comparing reports; data and calculations may change with the update of the workbook.

Please note that the four RJM data worksheets, labeled PY, CY, NY, RY, are a total of 31 pages if printed. Because of the size of these worksheets, it may be more desirable to print individual reports instead of the entire workbook.

If there are any questions about the use of these reports, please contact Timothy Felegie at felegie.timothy.

Instructions

Copy & Paste data from any of the RJM data worksheets into the blue highlighted cells.

1. To copy and paste data into the report, select the range of data that you want to copy from the RJM data worksheet.

2. Select Copy from the Edit menu.

3. Select the range on the report that you would like to paste the data into. Make sure to align data with corresponding State.

4. Select Paste from the Edit menu.

Formulas in the adjoining cells and the top on the report will automatically display calculations based on the input data.

Note: A sample data tab displaying the report populated with data to be used as a model follows each report tab. The report data in the sample tabs is sorted from high to low. This sort is not an automatic function of the individual report. The user will need to use the Excel "sort" function in the (Data) drop-down menu to sort individual reports according to their report requirements.

Report 1: Year to Year Variance (Yr – Yr)

This report includes the Year-to-Year variance data for a single state. The report includes a state drop-down list from which this report can be generated for each of the states individually. There are four data entry fields for this report (Previous Year; Current Year; Next Year, and Request Year). There are three calculated data fields in this report (Increase; Percent Change, and Out of Range).

Report 2: Non Personal Services – Per Staff (NPS-Staff)

This report details the Non Personal Services – Per Staff data for all states. There are two data entry fields in this report (Dollars and Staff). There are seven calculated data fields in this report (Per Staff; Out-of-Range, High; Out-of Range, Low; Average; Top of Range; Bottom of Range; and Range from Average). This report includes a drop-down list with 11 item categories, see chart below. This report can be generated with any of the item categories selected, which enables this report to function as eleven separate reports. The report also includes a year drop-down list, for the four RJM fiscal year options.

|Item Categories: |

|Total NPS |

|Communications |

|Facilities |

|Computer Services |

|Travel |

|Office Equipment |

|Supplies |

|Personal Service Contracts |

|Converted Contracts |

|State Indirect |

|Miscellaneous |

Report 2a: Non Personal Services – Per Staff (NPS-Staff Sample)

This report details the Non Personal Services – Per Staff data for all states. This report is included for use as a model to give the user an example of what the completed report should look like. The data included in this report should not be deemed valid for the purpose of reporting. The data in this report is sorted from high to low. This sort is not an automatic function of the individual report. The user will need to use the Excel "sort" function in the (Data) drop-down menu to sort individual reports according to their report requirements.

Report 3: Non Personal Services – Per Workload (NPS-WKLD)

This report details the Non Personal Services – Per Workload data for all states. There are two data entry fields in this report (Total Dollars; and Workload). There are seven calculated data fields in this report (Assigned Dollars; Per Workload; Out-of-Range, High; Out-of-Range, Low; Average; Top of Range; Bottom of Range; and Range from Average). This report includes a drop-down list with eleven item categories and six workload categories, see charts below. This report can be generated with any of the item categories or workload categories selected, which enables this report to function as 66 separate reports. The report also includes a year drop-down list, for the four RJM fiscal years options.

Item Categories: Workload Categories:

|Total NPS |Initial Claims |

|Communications |Weeks Claimed |

|Facilities |Non Monetary Determinations |

|Computer Services |Appeals |

|Travel |Wage Records |

|Office Equipment |Tax |

|Supplies |

|Personal Service Contracts |

|Converted Contracts |

|State Indirect |

|Miscellaneous |

Report 3a: Non Personal Services – Per Workload (NPS-TOT-IC Sample)

This report details the Non Personal Services – Per Workload data for all states. This report is included for use as a model to give the user an example of what the completed report should look like. The data included in this report should not be deemed valid for the purpose of reporting. The data in this report is sorted from high to low. This sort is not an automatic function of the individual report. The user will need to use the Excel "sort" function in the (Data) drop-down menu to sort individual reports according to their report requirements.

Report 4: Minutes Per Unit (MPU)

This report details the Minutes Per Unit data for all states. There is one data entry field in this report, (MPU). There are six calculated data fields in this report (Out-of-Range, High; Out-of-Range, Low; Average; Top of Range; Bottom of Range; and Range from Average). This report includes a drop-down list for the six MPU item categories, see chart below. This report can be generated with any of the item categories selected, which enables this report to function as six separate reports. The report also includes a year drop-down list, for the four RJM fiscal years.

Item Categories:

|Initial Claims | |

|Weeks Claimed | |

|Non Monetary Determinations | |

|Appeals | |

|Wage Records | |

|Tax | |

Report 4a: Minutes Per Unit (MPU APP Sample)

This report details the Minutes Per Unit data for all states. This report is included for use as a model to give the user an example of what the completed report should look like. The data included in this report should not be deemed valid for the purpose of reporting. The data in this report is sorted from high to low. This sort is not an automatic function of the individual report. The user will need to use the Excel "sort" function in the (Data) drop-down menu to sort individual reports according to their report requirements.

Report 5: Benefit Payment Control (BPC)

This report details the Benefit Payment Control data for all states. There are two data entry fields in this report (Positions Requested and Weeks Claimed). There are seven calculated data fields in this report (Positions per 100,000; Out-of-Range, High; Out-of-Range, Low; Average; Top of Range; Bottom of Range; and Range from Average). This report includes a year drop-down list, which includes the four RJM fiscal years. This report functions as a single report with the four RJM fiscal years.

Report 5a: Benefit Payment Control (BPC Sample)

This report details the Benefit Payment Control data for all states. This report is included for use as a model to give the user an example of what the completed report should look like. The data included in this report should not be deemed valid for the purpose of reporting. The data in this report is sorted from high to low. This sort is not an automatic function of the individual report. The user will need to use the Excel "sort" function in the (Data) drop-down menu to sort individual reports according to their report requirements.

Report 6: UI Performs Per Case Load (UIP)

This report details the UI Performs Case Load data for all states. There is only one data entry field in this report (Positions Requested). There are seven calculated data fields in this report (Cases per Position; Out-of-Range, High; Out-of-Range, Low; Average; Top of Range; Bottom of Range; and Range from Average). This report includes a year drop-down list, which includes the four RJM fiscal years. This report functions as a single report with the four RJM fiscal years.

Report 6a: UI Performs Per Case Load (UIP Sample)

This report details the UI Performs Per Case Load data for all states. This report is included for use as a model to give the user an example of what the completed report should look like. The data included in this report should not be deemed valid for the purpose of reporting. The data in this report is sorted from high to low. This sort is not an automatic function of the individual report. The user will need to use the Excel "sort" function in the (Data) drop-down menu to sort individual reports according to their report requirements.

Report 7: Support as Percent of Staff (SUP)

This report details the Support as Percent of Staff data for all states. There are two data entry fields in this report (Positions Requested and Staff). . There are seven calculated data fields in this report (Positions per Staff; Out-of-Range, High; Out-of-Range, Low; Average; Top of Range; Bottom of Range; and Range from Average). This report includes a year drop-down list, which includes the four RJM fiscal years. This report functions as a single report with the four RJM fiscal years.

Report 7a: Support as Percent of Staff (SUP Sample)

This report details the Support as Percent of Staff data for all states. This report is included for use as a model to give the user an example of what the completed report should look like. The data included in this report should not be deemed valid for the purpose of reporting. The data in this report is sorted from high to low. This sort is not an automatic function of the individual report. The user will need to use the Excel "sort" function in the (Data) drop-down menu to sort individual reports according to their report requirements.

Report 8: AST as Percent of Staff (AST)

This report details the AST as Percent of Staff data for all states. There are two data entry fields in this report (Positions Requested and Staff). There are seven calculated data fields in this report (Positions per Staff; Out-of-Range, High; Out-of-Range, Low; Average; Top of Range; Bottom of Range; and Range from Average). This report includes a year drop-down list, which includes the four RJM fiscal years. This report functions as a single report with the four RJM fiscal years.

Report 8a: AST as Percent of Staff (AST Sample)

This report details the AST as Percent of Staff data for all states. This report is included for use as a model to give the user an example of what the completed report should look like. The data included in this report should not be deemed valid for the purpose of reporting. The data in this report is sorted from high to low. This sort is not an automatic function of the individual report. The user will need to use the Excel "sort" function in the (Data) drop-down menu to sort individual reports according to their report requirements.

Report 8b: AST + SUP as Percent of Staff

This report details the AST + SUP as Percent of Staff data for all states and provides a means of combining these two items as a percent of staff. This is another example of the flexibility of using the AST report worksheet by adding additional columns to provide a more detail analysis of staff overhead as a percent of staff. This data in this report is sorted from high to low. This sort is not an automatic function of the individual report. The user will need to use the Excel "sort" function in the (Data) drop-down menu to sort individual reports according to their report requirements

Report 9: Leave Factor (Leave)

This report details the Leave Factor data for all states. There are two data entry fields in this report (Hours Paid per Position; and Hours Worked per Position). There are seven calculated data fields in this report (Leave Factor; Out-of-Range, High; Out-of-Range, Low; Average; Top of Range; Bottom of Range; and Range from Average). This report includes a year drop-down list, which includes the four RJM fiscal years. This report functions as a single report with the four RJM fiscal years.

Report 9a: Leave Factor (Leave Sample)

This report details the Leave Factor data for all states. This report is included for use as a model to give the user an example of what the completed report should look like. The data included in this report should not be deemed valid for the purpose of reporting. The data in this report is sorted from high to low. This sort is not an automatic function of the individual report. The user will need to use the Excel "sort" function in the (Data) drop-down menu to sort individual reports according to their report requirements.

Report 10: Rank (Rank)

This report details the Rank data for all states. There is one data entry field in this report (Item). There is one calculated data field in this report (Average). This report includes a year drop-down list, which includes the four RJM fiscal years. This report functions as a single report with the four RJM fiscal years.

Report 10a: Rank (Rank IC Sample)

This report details the Rank data for all states. This report is included for use as a model to give the user an example of what the completed report should look like. The data included in this report should not be deemed valid for the purpose of reporting. The data in this report is sorted from high to low. This sort is not an automatic function of the individual report. The user will need to use the Excel "sort" function in the (Data) drop-down menu to sort individual reports according to their report requirements.

Report 10b: Rank (Rank SQ Feet Sample)

This report details the Rank data for all states. This report is included for use as a model to give the user an example of what the completed report should look like. The data included in this report should not be deemed valid for the purpose of reporting. The data in this report is sorted from high to low. This sort is not an automatic function of the individual report. The user will need to use the Excel "sort" function in the (Data) drop-down menu to sort individual reports according to their report requirements.

Report 11: State Dollars (State $)

This report details the State Dollars data for all states. This report provides a complete recap of State dollars, by fund ledger, used for FY 02 for each state. The fields in this report are auto populated from the PY data tab. This report functions as a single report.

Report 12: Non Personal Services – Per Workload (% Increase)

This report details the Non Personal Services – Per Workload data for all states. There are two data entry fields in this report (Item 1 and Item 2). . There are eight calculated data fields in this report (Increase; % Increase; Out-of-Range, High; Out-of-Range, Low; Average; Top of Range; Bottom of Range; and Range from Average). This report includes a drop-down list with 11 item categories, see chart below. This report can be generated with any of the item categories selected, which enables this report to function as eleven separate reports.

|Item Categories: |

|Total NPS |

|Communications |

|Facilities |

|Computer Services |

|Travel |

|Office Equipment |

|Supplies |

|Personal Service Contracts |

|Converted Contracts |

|State Indirect |

|Miscellaneous |

Report 12a: Non Personal Services - Per Workload (% Increase NPS Sample)

This report details the Non Personal Services – Per Workload data for all states. This report is included for use as a model to give the user an example of what the completed report should look like. The data included in this report should not be deemed valid for the purpose of reporting. The data in this report is sorted from high to low. This sort is not an automatic function of the individual report. The user will need to use the Excel "sort" function in the (Data) drop-down menu to sort individual reports according to their report requirements.

Data: Previous Year (PY)

This tab includes the RJM previous year data in column format. There are 103 data elements, which include RJM Start-Up worksheet data elements.

Data: Current Year (CY)

This tab includes the RJM current year data in column format. There are 92 data elements, which include RJM Start-Up worksheet data elements.

Data: Next Year (NY)

This tab includes the RJM next year data in column format. There are 86 data elements, which include RJM Start-Up worksheet data elements.

Data: Request (RY)

This tab includes the RJM request year data in column format. There are 86 data elements, which include RJM Start-Up worksheet data elements.

Table 1: National Variance Table (Format VT)

This table includes the list of data values that are used to calculate variances for reports.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download