Statistical Analysis to determine Average Levels of Effort based on multiple variables

[Lexcon07]

I'm trying to figure out how to estimate the Average Level of Effort (LOE) required to complete an "Activity" based on a data set that looks like the following:

	Available Time (Minutes)	Activity 1	Activity 2	Activity 3	Activity 4	Activity 5	Activity 6	Activity 7	Activity 8	Activity 9	Activity 10	Totals
Employee 1	90,000	100	100								500	700
Employee 2	90,000			50			450	100	150	250		1,000
Employee 3	90,000			100			500	200	175	175		1,150
Employee 4	90,000			100			400	100	200	300		1,100
Employee 5	90,000		100	50	500	500						1,150
Employee 6	90,000	100	100	10			450	100	250	100		1,110
Employee 7	90,000										1,000	1,000
Employee 8	90,000	75			400						400	875
Employee 9	90,000	60			450						450	960
Employee 10	90,000		500	100	100	100	100					900
Employee 11	90,000		250		400							650
Employee 12	90,000	100	75	100	250	45						570
Employee 13	90,000	100	50	100				100	100	100		550
Employee 14	90,000		50	100			400	100	200	250		1,100
Employee 15	90,000	150	100	100				100	100			550
Employee 16	90,000	100									800	900
Employee 17	90,000	100	75		500							675
Employee 18	90,000		75		400	400						875
Employee 19	90,000		50				400	100	100			650
Employee 20	90,000			100						250	600	950
Employee 21	90,000	100	200	250				100	100	100		850
Employee 22	90,000	150			700							850
Employee 23	90,000				100	100	100				700	1,000
Employee 24	90,000	150	150	150				150	150	150		900
Employee 25	90,000	100	200	200				100	100	100		800
Employee 26	90,000	100	100	300							600	1,100
Employee 27	90,000				800						200	1,000
Employee 28	90,000	75	150	10	200	250	50	50	50	50		885
Employee 29	90,000	50	50	50	50	50	50	50	50	50		450
Employee 30	90,000	100	100	100	100	100	100					600
Totals	2,700,000	1,710	2,475	1,970	4,950	1,545	3,000	1,350	1,725	1,875	5,250	25,850

The data set is actually quite large in reality (i.e. 3,000 employees and 4,000 activities).

Any suggestions would be greatly appreciated.

Thanks,

David

 

[steve the fish]

Have you tried average? It will ignore the blank cells.

 

[Lexcon07]

Sorry, perhaps my table wasn't clear enough. The numbers under the "Activity" columns represent the number of times each employee completed that particular activity.

Key Notes/Assumptions:
- Each employee is 100% productive during their 90,000 minutes of available productive time.
- Activities are all unique and do not take the same amount of time to complete

For example:
Activity 1 was completed 1,710 times by 17 different employees, but there is no simple way to determine how much of each of those 17 employees' 90,000 minutes were spend completing Activity 1. Similarly, Employee 1 completed 700 activities (100 x Activity 1, 100 x Activity 2 and 500 x Activity 10), but there is no simple way to determine how much of their 90,000 minutes were spent on each activity type.

I can establish some Average LOEs at the Employee Level (not overall) through a simple manual process:

For example:
- Employee 7 completed 1000 x Activity 10 and nothing else, therefore Average LOE for that activity for that employee is simple 90 minutes.
- If I then assume that all Activity 10s take 90 minutes, I can start to narrow down remaining available time for some other employees
Employee 16 completed 800 x Activity 10 and 100 x Activity 1 or 72,000 (800 x 90 minutes) spent on Activity 10, leaving 18,000 minutes to complete 100 x Activity 1 for an Average LOE of 180 minutes.
Employee 1 completed 500 x Activity 10 or 45,000 minutes (500 x 90 minutes) and 100 x Activity 1 or 18,000 minutes (100 x 180 minutes), leaving them with 27,000 minutes to complete 100 x Activity 2 for an Average LOE for Activity 2 of 270 minutes.

However, the data set is much too large to consider doing this manually. Not to mention that it does not take into account the fact that Average LOEs at the Employee Level are not necessarily reflective of the Average LOE overall (i.e. It would not be correct to assume that because it took Employee 7 on average 90 minutes to complete Activity 10 that all other employees would be working at the same level of efficiency).

 

[steve the fish]

Logically i dont think what you ask for is possible with the data you have presented us here.

 

[Sulprobil]

This seems to be sort of a solver problem where you need to come up with estimated times for each activity and estimated efficiency factors for each employee (hopefully only one for each) so that each sum of the products of matrix value times activity time times employee efficiency equals 90,000.
But for Excel's Solver there are too many variables.
Which means you (we) need to program it on your (our) own.

 

[Lexcon07]

@steve the fish, Is there something that you see missing from the data or something that I haven't explained clearly? The data I provided is just a sample. As I mentioned in my original post, the actual data set is very larger (3,000 employees and 4,000 activities).

@Sulprobil, your understanding is correct. Do you have any suggestions on what we would need to solve this? Could we do it using VBA or do you think we would need another more robust analysis tool? I don't think Power BI is capable of it either.

 

[Sulprobil]

I have not done too much research. Maybe
OpenSolver: Open Source Optimisation for Excel
could be of help.