Industrial Engineering Project: Expanding On Data Collection Report.

I have a an industrial engineering class project where we designed an a model which showed how many employees are required within the department by analyzind the time spent in each task and the frequency that those task were done. Other than the actual project you we have to write a lengthy report about it as well.

 

We have a mid report already written which i attached. I am responsible for writing about the data collection which is part where i need to write everything regarding the data that was collected for the project. I have written a couple of pages on the section already. I need help expanding the Data Collection section of the mid report attached. I need for that section to have at least 6 to 7 more pages. I also attached a copy of the syllabus to help you understand the purpose of the project and the report and I also  highlighted the parts regarding data collection. I will also attach a small timestudy that was done that as used to analyze the time it take to do certain tasks and should be mentioned on the data collection section. If you have any questions let me know. Thank you.

Introduction

The University of Miami Hospital IT Department is in need of a Workforce Requirements Model that functions based on work volume, current skills available, required skills, and available resources in order to accurately forecast and determine the precise number of FTEs that will be required during future time periods. This model will be utilized by the project manager to obtain data-driven recommendations for workforce staffing levels, and the findings may be shown to the Finance department in order to obtain the funds required to bring on a sufficient number of employees.

 

Current State Analysis

 

In the current state, the University of Miami IT Department is being stretched thin by a vast and growing number of medical campus faculty and staff. With only three employees on this IT team due to a recent employee departure, there is too much work, and not enough man hours to successfully accomplish this work at the highest level. For example, these three people are now doing the work of at least four employees – the UMIT department was understaffed before the employee’s leave – in regards to new employee onboarding and registration, accessing user rights to the EPIC system and other systems across the healthcare organization, and various other tasks for the thousands of users entering the system every year.

Juan Carlos has the ability to request the hiring of extra employees from the Finance department, but without sufficient data to back up his claims that the IT team has a severe workload, the project management department is unable to establish a sufficient claim that will convince Finance to bring on the extra employees that are desperately needed. In an effort to obtain this data using a data-driven approach, observations, and historical information regarding workload, this is where the intern team enters the picture with the creation of an Excel-based model/GUI that allows the user to input work volumes, required skillsets, and task frequencies in order to determine the number of FTEs that will be required by UMIT during specific months or years.

On top of the task list that will be put together in order to capture all of the day-to-day activities that take up the bulk of the IT employees’ time, there are a number of unpredictable events that take place in the current state of the organization, and in the foreseeable future. The model will include an allowance for these unforeseeable tasks and events, and a balance needs to be struck between the hiring of an additional employee (being overstaffed) and ensuring that enough employees are available to accomplish these tasks as they arise.

In order to predict the frequency and duration of the tasks that are being completed in the current state, we will be utilizing data obtained from Human Resources. This data will provide information on what we are referring to as the Driving Parameters for these tasks, and will allow us to predict workloads for certain tasks using historical data and forecasts. An example of this is the employee turnover rate for clinical and nonclinical functions, which will affect the number of ORFs (Online Request Forms) that the IT team receives and must attend to.

 

 

 

 

Data Collection Efforts

 

When you’re working with a department in which most of the work being done is intangible it is very complicated to get a clear understanding of the amount of work and analysis that each IT task can take. We need a very accurate estimate to be able to forecast the labor demand in this department. During our meetings with Nicholas Meeks we noticed that the amount of different tasks that this department does on a yearly basis is in between 50 and 100 depending on what has been done every year. This is due to the fact that the yearly events change based on the amount of work that has to be done. For example, a major update to the system is only done once every four years and a major maintenance is done every two years. So instead of collecting data we have dedicated our efforts toward obtaining a better understanding of the different amount of tasks that are being done by the IT department. We were able to elaborate this following list with Nicholas Meeks, the senior IT security employee.

 

1) User Activations

a. Basic

b. Advanced

c. Modifications

d. HAIKU/CANTO

e. EPICARE Link

f. SER Creation

g. User Notification e-mal.

i. Note: This workload can double during residency season

2) User Terminations

a. User records

b. Provider records

c. Urgent terminations

3) SER Imports

a. Daily import

b. Troubleshooting errors (time varies)

c. Data courier

d. E-mail

4) ServiceNow Incidents

a. Simple incidents

b. Complex incidents

5) Emailed Incidents (UChart User Security Mailbox)

a. Daily response to emailed incidents

6) On-the-fly troubleshooting (in person)

a. Help desk calls

b. Application team member walk-ins

7) Application team troubleshooting

a. Application Build/modification

b. Build analysis

c. CAB-Approved Build

8) Active Directory

a. Granting uses to Citrix application icons

b. Distribution Lists

c. IMPRIVATA Enrollment

i. Adding work stations

9) Quarterly System Update (update of current version)/Upgrade (new version) Loads

a. Release Note Review

b. Analysis & Build (if needed)

c. Downtime preparation

d. Application Analyst Support

10) Implementation of New Applications

a. Population analysis

b. Business needs

c. Weekly meetings

d. Schedule of events

e. Go-Live

f. Support & Stabilization

11) Projects (e.g. security restructure)

a. Population Analysis

b. Build

 

Driving Parameters

· Personnel turnover

· Hires/terminations

· Yearly addition of residents (focused within a specific time, so this peaks during June/July. Look at not only a baseline, but also a peak variability)

· New departments/facilities being brought onto the system

· Implementation of new IT systems (i.e. University of Miami hospital)

· Quarterly updates

· Help desk calls/ServiceNow tickets

· Total # of employees (EMP Records), clinical users (SER, EMP Records)

· Project rate (#11). Will have to consider means and variance (peaks) of project lengths since we won’t have actual data for this

 

After developing this list, we solidified it with Craig, the head of the IT security department at the University of Miami Hospital, and he confirmed that each one of these tasks are performed on a yearly basis. During our most recent meeting with Craig and Nick, they agreed to give us a time confidence interval for each task on the list in order to get a better understanding of the time each task took vs the volume of work required to keep things at a steady state. After collecting this information we want to be able to make some time studies in at least the crucial tasks within the department. These are the tasks that have to be done to keep the security system well organized, such that daily operations are not affected. Another way we are going to be able to deliver a more accurate estimate of the workforce required is with the information provided by Craig and the Human Resources department. We have not yet discovered exactly what data will be available, but we believe that it will be a useful tool due to the fact that HR has information related to the amount of people that are hired throughout the year. Therefore, this would allow us to create a timetable for the user activations in the security task list. HR also has information of the amount of people that either were fired or left the hospital, thus giving us an estimated number of the amount of user terminations that are performed on a yearly or monthly basis.

 

Analysis

Appendix will include:

· Data sheets for users (A1), Activations (A2), UChart (A3), Service Now (A4), ser1 (A5)

· Daily task time study (A6)

· Screenshots of the model

· Task list (A7)

· Driving parameters (A8)

· Workload Analysis (A9)

· Flow charts for Basic User Activation (A10), Hiring Process Before Model (A11), Hiring Process After Model (A12)

· Analysis Outline:

· Recap of where the data came from

· Discussion of the data

· Verification of data with time study (and task-specific flow chart)

· Discussion of the model (task list, driving parameters, workload analysis)

· Link to data sheets

· Discussion of before/after hiring process

 

As can be seen in the discussion of our data collection above, the vast majority of the data required for the UMIT Workforce Requirements Model was obtained from both extracts of historical data for the monthly frequency of each task, and anecdotal information for the average task time. This information was obtained with the assistance of Nicholas Meeks and Craig Scott from the IT department, and Cory Hall, our project sponsor. This method of obtaining data was necessary for a number of reasons:

· Due to the computer-based nature of IT tasks, it is very difficult to observe day-to-day processes for time study purposes without a high degree of interruption

· The variety of tasks performed on a daily, monthly, and quarterly basis is expansive (Appendix 7), including 45 different tasks. A time study on each individual task would take months to perform fully

· The limited current staffing level of the IT team inhibits their availability to take part in work sampling/time studies

In order to develop the Workforce Requirements Model, this data was required in order to perform a number of Excel-based calculations, eventually resulting in a calculation for required FTEs by month and role. A more detailed discussion of the Workforce Requirements Model and its calculations will be provided below.

Because the average task times for the tasks performed in the IT department were provided anecdotally by our subject matter experts (SMEs) Nicholas Meeks and Craig Scott, we wanted to perform a time study in order to verify the accuracy of some of the daily tasks described. Specifically, we performed a short time study on Daily SER Imports, Basic Activations, and Advanced Activations (Appendix 6). For an example of the observed task from beginning to end, Appendix 10 includes a flow chart of a Basic Activation.

Once the task time and monthly frequency for all 45 tasks performed by the UM IT team was solidified (Appendix 7), the framework and logic of the model could be put into place. As our project sponsor preferred for the Excel Document to remain formula-driven for ease of editing and application to other departments, we were requested not to utilize VBA macros and forms in the development of our Workforce Requirements calculator. Therefore, the user-specified data input and calculations are all performed within the cells across the three sheets of the model. In order to discuss the functionality of the model, a tutorial-esque style will be used to demonstrate that this model can be applied to departments aside from the UM IT team on the medical campus.

To begin using the model, the user inputs a list of tasks into the Task List sheet, specifying high-level categories for each of these tasks. The category for each task should, but is not required to, mirror the Parameter ID, which will be used to determine the monthly frequency of each task – these details will be discussed. Moving rightward on the Task List sheet, the user will enter a minimum and maximum time (in minutes) for the performance of each specific task. Ideally, this information will be obtained most accurately using an observation-based time study across all tasks and averaging all recorded observations, simply entering those averages into the Average Time column. Otherwise, an anecdotal average time can be calculated using the minimum and maximum expected time for each task. The values in the Average Time column will be referenced in various calculations across the remaining sheets.

On the Task List sheet, the final information required by the user will be the Capable Roles matrix (Columns H-M on Appendix 7). First, the Role Descriptions can be filled out for ease of use and reference, and then the user can input any character (“X” in our example) to specify that a particular role is capable of performing a task. In the case of UM IT, there are two levels of roles with differing capabilities: Senior System Analyst and System Analyst. Because the Senior System Analyst is highly knowledgeable and the System Analyst has not undergone this level of training, Role 1 is capable of performing every task on the UM IT list, while Role 2 is able to perform approximately half of these tasks. This information will be utilized on the Workload Analysis sheet when calculating Required Monthly FTEs by Role. To complete discussion of the Task List sheet, a simple color-coded key is provided in Columns O and P: All beige cells are headers, white cells should be filled out by the user, and grey cells are automatically calculated.

The Driving Parameters sheet, which for the purposes of the model calculations could also be referred to as the “Monthly Frequency” sheet, requires a certain level of understanding from the user. Driving Parameters, by our definition, include a list of task categories under which each task falls. Using historical data of monthly task frequency, or simply known frequencies, the user can first fill out the Driving Parameters column with the applicable parameters, and then enter the monthly frequency for each parameter, ensuring to extend the Parameter ID column (column A). There is another important consideration to make here: in order to properly link tasks to their Driving Parameter, the Parameter ID column on the Driving Parameters sheet must exactly match the Parameter ID column from the Task List sheet. This will ensure that the proper frequencies and VLookups are applied on the Workforce Analysis page. Column O on the Driving Parameters sheet specifies the type of data, while Column P can be used for any additional information (i.e. the logic behind certain calculations or any percentage increase).

Finally, now that task names, task times, task frequencies, and required identifications have been entered, there is only one more column left to fill in on the Workload Analysis sheet: % of Total Parameter (Column F). Remember, our definition of a Driving Parameter is a category under which a task falls – for example, Activations. Therefore, all tasks that fall under this category (i.e. Basic User Activation, Advanced User Activation, etc. under Parameter ID A) make up a certain percentage of all Activations, the Driving Parameter. This process was necessary because the data available from UM IT applied to these large categories, and then had to be filtered for the specific task types. Once the user determines what percentage of each Driving Parameter is made up by each Task, the Excel model performs the rest of the calculations automatically (indicated by the grey-colored cells).

The Workload Analysis sheet’s primary fields, therefore, consist primarily of VLookups referencing the Task List sheet and calculations referencing the Driving Parameters values. Now, the formulas for Monthly Man Hours (MMH), and therefore FTEs, can finally be applied:

 

 

The variables in these calculations correspond to the columns of the Workload Analysis sheet as follows:

· Avg. Task Time (mins): Column E

· Total Parameter Frequency: Corresponding Month Column of Driving Parameters Sheet

· % of Total Parameter: Column F

Because the average task time was entered in minutes, the Monthly Man Hours calculation has 60 in the denominator to convert to hours. For the UM IT Team, Cory Hall suggested 130 hours as the Control Value for FTE Hours per Month. This allows the IT Team Member 30 hours (160 – 130) per month for PTO and meetings that would interrupt desk time spent performing the previously mentioned tasks.

Finally, the Required FTEs by Role calculation is performed at the bottom of the Workload Analysis sheet. This is relatively simple, and based on the “X”’s filled into the Capable Roles matrix of the Task List sheet. These “X”’s populate Column T-Y of the Workload Analysis sheet, and Rows 55-60 are calculated based on whether or not these columns are filled. Therefore, the Required FTEs by Role column sums only the Monthly Man Hours for the tasks that each role is capable of performing.