Because using spreadsheet software is a basic business skill, the assignments are intended to give you practice in structuring problems as well as simply finding the answers. All problems must be solved using Excel formulas to receive credit. If, for example, you solve a problem on paper and then copy the results to Excel, you will receive no credit for that problem.
All assignments are to be submitted using the Blackboard site. Assignments should be uploaded using the upload link associated with the assignment. It is important that all homework files include the student’s NetID, so they can be easily identified and sorted. The file containing homework assignment 1 submitted by the student with NetID rbaker2 should be named rbaker2hw1.
Make sure your submission is a working file. If you upload a corrupted file (or a file which cannot be downloaded and opened completely), you will receive 0 points for this assignment.
For this assignment, please submit one Excel file. Use a separate worksheet for each question set and label the worksheets. To rename a worksheet, right-click the worksheet tab located at the bottom of your current sheet and use the rename option. Label the tabs as “Q Set 1” and “Q Set 2”, respectively. Be sure to label all results clearly.
Question Set 1.
You are in charge of quality control for computer keyboards at Dell. You have data on twenty-five batches of keyboards, tracking five types of keyboard defects: key brightness, dusting, scissor mechanics, dead connections, and sticky keys. These data are given in the table below.
1. For each defect type, find the average number of defects per batch. So, you should have an average defect rate for displays, another for color, and so on. (2pts)
2. For each batch, find the total number of defects (the sum of all five types). So, you should have one number for batch 1, another for batch 2, and so on. (2pts)
3. Sort the five columns of defects by descending average defect rate. The lowest rate should be on the right. (4pts)
4. Sort the batches by descending total defects. The batch with the highest total should be at the top. This will not affect the column sorting from the part 3 above. (4pts)
5. Create a Pareto chart showing the average defect rate for each of the five defect types. This will be a column chart, in descending left-to-right order, with each column and the axes labeled. (8pts)