II (50 pts) Introductory Production Data Analytics

1. Review the TechPrac02 Tech Tips video for focused help on completing this activity.
2. Download the TechPrac02ProdData Excel spreadsheet. This contains hypothetical data from a manufacturer which includes
   • Date - the day on which production occurred
   • Shift - the shift (1, 2, or 3) on which production occurred
   • Operator - the operator number (1 thru 10) who did the work
   • Item - the item (A, B, C or X, Y, Z) that was run
   • TotalUnits - number of units produced, including any rejects
   • RejectUnits - number of TotalUnits which had to be rejected or reworked
3. On the ProdData tab, add a column named StdUnits which calculates the number of units that would have been produced using the following standard units per hour (uph): A 60 uph; B 50 uph; C 40 uph; X 40 uph; Y 30 uph; Z 20 uph. Hint: Create a formula using VLOOKUP to find the standard rate for the item produced and multiplies by the hours the operator ran that item.
4. Create a worksheet tab called Operators to hold the following analyses:
   1. Use PivotTables (or another tool) to calculate totals for TotalUnits, RejectUnits, & StdUnits for each operator.
   2. Use these totals to calculate the reject rate for each operator (total RejectUnits divided by total of TotalUnits for the operator expressed as a percent to 1 decimal place (e.g. 5.1%).
   3. Calculate the percent of standard for each operator (total TotalUnits minus total RejectUnits with that difference divided by total StdUnits) also expressed as a percent to 1 decimal place.
   4. On the same tab, prepare a PivotChart which shows each Operator's total TotalUnits and total NetUnits (calculated TotalUnits - RejectUnits) on a bar chart.
5. Repeat the analysis from II.4 above for each Shift on a worksheet tab named Shifts.
6. Repeat the analysis from II.4 above for each Item on a worksheet tab named Items.
7. Save the revised spreadsheet file and submit it through your MIS lab.