I am on the lookout for excel expert to help me with design of workload analysis excel template. I need to analyze work to produce man hour calculations for each job role in the organization. For example in call centers this would be the number of man hours required per call, or for the police this is separated into fixed and variable duties, such as the number of officers per incident per hour. I need to assess historical data to produce trends, averages and extreme situations.
This work is very dependent upon the type of operation involved. In a call center this will be the call rate, done in half hour or quarter hour segments. For a hospital this could be the number of patients admitted per day. For a factory this would be the number of orders per day. For distribution of products this could be orders and quantity per hour.
Essentially the final product should have three worksheets. The first worksheet will capture all key assumptions such as work days in any given year and productivity rate. Second worksheet will be a table that will have level 3 process activities in the first column and rows will contain job roles. Managers are expected to populate estimated workload, as well as define activities in accordance to frequency, complexity of work, KPIs etc. The third worksheet will contain dashboard with all the analysis including but not limited to required FTEs, analysis of activities, etc