The SYSTEM_USER_COUNTS Worksheet

 

§         Click on the System User Counts button.

 

systemThe user will be presented with the SYSTEM_USER_COUNTS worksheet as shown below:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

§         The above screenshot of the SYSTEM_USER_COUNTS worksheet has been pre-populated with data. The ALL_SYSTEM_USAGE information has been copied to this worksheet using the “Copy for System User Counts” button located on the ALL_SYSTEM_USAGE worksheet.

 

§         This worksheet is used to capture the number of benefiting program recipients or caseloads that will be using the system functionality in order to determine the “fair share” contribution for small and large programs.

 

§         The usage=”X” markers in the benefiting program columns will be replaced by the actual user counts, which are either the benefiting programs’ number of recipients or the number of caseloads that will be benefit from or impact the use of the system functionality. For example, if the Food Stamps (FS) program has 500,000 recipients or caseloads then all of the usage=”X” markers under the FS column will be replaced with the value of 500,000.

 


  • SYSTEM_USER_COUNTS MENU FEATURES

§         The SYSTEM_USER_COUNTS worksheet has menu buttons to provide automated functionality to the user.

 

§         system user countAssign the Benefiting_Programs Worksheet User Counts: Users can use this button to insert the user counts captured on the Benefiting_Programs worksheet automatically.

 

§         The following screenshot shows the use of the “Assign the BENEFITING_PROGRAMS Worksheet User Counts” button.

button

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


assign usersAssign User Counts by Program: Users use this button to enter user counts manually, if the user would like to capture the specific number of program participants that would assign user buttonactually benefit from the system functionality. The “Enter User Counts” window will appear after clicking on this button.

 

§         Users select the benefiting program in the drop-down box, select the system functionality where usage =”X” for the program and enter a user count to insert.

 

§         As an example, this option would be used if FS (e.g., Food Stamps) had a total user count of 500,000 but only 225,000 of the program participants would interact with the system functionality. So, instead of entering the standard of 500,000 where usage =”X”, then specific numbers like 225,000 would be entered. The benefiting programs involved in the cost allocation would need to determine actual users by system functionality. 

 

§         PLEASE NOTE: The use of this button may be rare because collection of such detail may not be cost effective. Having user counts of actual benefit would be more accurate. However, capturing user counts at this level of detail may have only a small impact on the cost allocation “fair share” for small and large programs.

 

enter counts

§         Calculate Shared Usage Level of Effort (LOE): Users use this button to calculate the data used to determine the final cost allocation “fair share” (i.e. percentages) for the benefiting programs.  The user counts are used to calculate an “adjusted” level of benefit for any “small” programs that are involved in the system cost allocation. If a system only has large programs then the cost allocation will be divided equally among all programs using the specific system functionality. However, if the system has both small and large programs then the small programs’ percentage of total user counts are used to help determine the small program share and the large program share. Clicking this button creates and inserts the data for the SHARED_USAGE_LOE worksheet.

 


An example of how the “Calculate Shared Usage Level of Effort (LOE)” button uses the user counts to calculate the data for the SHARED_USAGE_LOE worksheets is as follows:

 

§         Step 1: Assume that a system has 5 benefiting programs indicated on the Benefiting_Programs worksheet as follows: 2 Small and 3 Large programs

 

PROGRAM_ABBREV

PROGRAM

PROGRAM_SIZE

USER_COUNTS

FS

Food Stamps

Large

500,000

TANF

TANF

Large

465,000

MED

MEDICAID

Large

500,000

PROGRAM 4

PROGRAM 6

Small

750

PROGRAM 5

PROGRAM 7

Small

900

 

§         Step 2: The following program usage assignments are made on the ALL_SYSTEM_USAGE worksheet. Base values and weights are assigned on the ALL_SYSTEM_USAGE worksheet as well. However, these values will be shown later in this example.

 

Functional Module

SubModule

Detail

Usage

Type

Num

Small

Num

Large

FS

TANF

MED

Program 4

Program 5

Alerts

Management

AL1

Shared

1

3

X

X

X

X

 

Alerts

Management

AL2

Shared

2

2

X

 

X

X

X

Alerts

Management

AL3

Shared

1

1

X

 

 

 

X

Alerts

Management

AL4

Shared

2

2

X

X

 

X

X

Alerts

Management

AL5

Shared

0

3

X

X

X

 

 

 

§         Step 3: The ALL_SYSTEM_USAGE worksheet information is copied to SYSTEM_USER_COUNTS worksheet and the user counts from the benefiting_program worksheet are assigned in cells where usage=”X”

Functional Module

SubModule

Detail

Num

Small

Num

Large

FS

TANF

MED

Program4

Program5

Total

 

Alerts

Management

AL1

1

3

500000

465000

500000

750

 

1465750

Alerts

Management

AL2

2

2

500000

 

500000

750

900

1001650

Alerts

Management

AL3

1

1

500000

 

 

 

900

500900

Alerts

Management

AL4

2

2

500000

465000

 

750

900

966650

Alerts

Management

AL5

0

3

500000

465000

500000

 

 

1465000

 

§         Step 4: A user count percentage for all small programs is calculated by dividing the small program user counts by the total user counts.

 

Functional Module

SubModule

Detail

Num

Small

Num

Large

FS

TANF

MED

Program4

Program5

Total

Alerts

Management

AL1

1

3

 

 

 

0.0005

 

 

Alerts

Management

AL2

2

2

 

 

 

0.0007

0.0009

 

Alerts

Management

AL3

1

1

 

 

 

 

0.0018

 

Alerts

Management

AL4

2

2

 

 

 

0.0008

0.0009

 

Alerts

Management

AL5

0

3

 

 

 

 

 

 

 


§         Step 5: The percentage for the large programs is calculated by equally dividing the remainder of the total share after deducting the total of the small programs share.

o        Using row 2 as an example, 1 (i.e.,100% of the Total) - [.0007+.0009] (i.e., the total share of small programs) = .9984

 

o        Each of the large programs’ share is .9984 divided by the number of large programs. In row 2, Num Large = 2. Therefore .9984/2 = .4992 and is the share designated for each of the large programs in row 2.

 

Functional Module

SubModule

Detail

Num

Small

Num

Large

FS

TANF

MED

Program4

Program5

Total

Alerts

Management

AL1

1

3

.3332

. 3332

. 3332

0.0005

 

1

Alerts

Management

AL2

2

2

.4992

 

.4992

0.0007

0.0009

1

Alerts

Management

AL3

1

1

.9982

 

 

 

0.0018

1

Alerts

Management

AL4

2

2

.4991

.4991

 

0.0008

0.0009

1

Alerts

Management

AL5

0

3

.3333

.3333

.3333

 

 

1

 

 

 

Functional Module

SubModule

Detail

Base Value

Weight

FS

TANF

MED

Program4

Program5

Total

Alerts

Management

AL1

20

 

.3332

. 3332

. 3332

0.0005

 

1

Alerts

Management

AL2

25

 

.4992

 

.4992

0.0007

0.0009

1

Alerts

Management

AL3

10

 

.9982

 

 

 

0.0018

1

Alerts

Management

AL4

5

 

.4991

.4991

 

0.0008

0.0009

1

Alerts

Management

AL5

5

 

.3333

.3333

.3333

 

 

1

 

 

Functional Module

SubModule

Detail

Base Value

Weight

FS

TANF

MED

Program4

Program5

Total

Alerts

Management

AL1

20

 

6.664

6.664

6.664

0.01

 

20

Alerts

Management

AL2

25

 

12.48

 

12.48

0.0175

0.0225

25

Alerts

Management

AL3

10

 

9.982

 

 

 

0.018

10

Alerts

Management

AL4

5

 

2.4955

2.4955

 

0.004

0.0045

5

Alerts

Management

AL5

5

 

1.6665

1.6665

1.6665

 

 

5

 

calculate button