Tutorial Assignment TA4: Problem Solving analysis in Excel
Overview:
This is an intriguing data analysis problem that is harder than it first appears. It is unlikely that more than a few students will receive 100 (or close to that), so don’t take this personally.
In truth, there are several distinct levels of solutions to this problem each reflecting different degrees of depth and quality in performing analysis of the data using the tools which we covered in the prior tutorial assignment. Moreover, beyond directly applying the data analysis tools covered previously (in the recently completed tutorial assignment)s, the reality of this assignment is that the more career experience you have in crunching numbers with a spreadsheet, the better you will likely perform on this assignment – this is unavoidable since the assignment is also about applied quantitative analysis. Although applied Excel skills are being covered in this course, raw quantitative analysis skills are not taught in this course. Those skills are taught in other business school courses like Finance, Operations Management, Economics or Accounting. You can think of this assignment challenge as part of the integrated business school experience.
A significant part of the assignment grade is assessed according to the quality of the written presentation of your solution. Therefore, those of you with weaker career experience in number-crunching should not despair – you could do an adequate job on the data analysis portion of this assignment but still do quite well in coherently presenting your solution (i.e. write a good solution narrative which references supporting data analyses in a way that is easy for the reader to follow). Conversely, those of you with greater career experience in number-crunching should not assume that is sufficient – you will still need to describe clearly and document how you did your data analyses.
Please do your best. You may opt to simply submit a very basic solution to the problem and receive a mediocre grade, or you may embrace the challenge of determining the source of the mystery of the production errors!
Assignment narrative:
Established by Ira Kelly in 2015, Kelly’s Gloves has grown rapidly. Kelly’s produces it own unique branded gloves in 3 styles known as A, B & C. However, while ramping up its production, Ken Dolls (Kelly’s quality manager for the production plant) has noticed a disturbing number of defective gloves coming out of production.
In every production batch exactly one out of 3 types of glove is produced by one employee on one production machine. Kelly’s has 4 production machines ( 1,2,3,4) and 5 employees (known as 111,222,3333, 444 & 555). So there are 3* 4* 5 (60) possible combinations of employee, machine and product.
Indeed, since there are 4 different batch sizes that are run (10000, 20000, 100000 & 200000) it might be more accurate to say that there are really 240 potential permutations. Of course, many combinations will wind up never occurring.
Mr. Dolls has hired you to assist Kelly’s in determine where the production defects are concentrated. He has provided you with a listing of the most recent 75+ production batches from the plant. He would like you to use Excel’s PivotTable and conditional formatting features to perform analyses on the data in order to figure out what factors (or factor) are driving these production errors.
Here is a description of the data he has provided you with:
a. Batch: A unique number that identifies each batch produced.
b. Product: A unique letter that identifies each product.
c. Machine: A unique number that identifies each machine on which products are produced.
d. Employee: A unique number that identifies each employee producing products.
e. Batch Size: The number of products produced in a given batch.
f. Num Defect: The number of defective products produced in a given batch.
Assignment Instructions:
Your goal is to:
Analyze the source data and determine what factor (or factors) are causing errors (defective gloves) in the production batches
Write up a document file describing your approach to this problem and what your analysis revealed
Provide recommendations
Reference the labeled worksheets you have created in your Spreadsheet analysis
You can obtain the required spreadsheet data file for this assignment by T4_GLOVES_Data.xlsx
Your solution must contain at least two Pivot tables AND one PivotChart, but will likely contain at least several more . Each should be labeled – and referenced by label in your written presentation of your anaylsis and your solution & recommendations.
In creating your pivot tables (one per workseet please), you are free to consider interaction effects between 2 or more factors (e.g. a pivot table that looks at the error rate in respct to both machine and product type).
Since you will be using more than one worksheet, each one should be labeled clearly. For example, each Pivot Table or Pivot Chart you create should be in its own labeled worksheet identifying what you did in that analysis.
Your submission for this assignment will consist of two files, both of which should be uploaded to the assignment folder:
One will be your assignment spreadsheet, containing the worksheets you have used to solve this problem (pivot tables, custom filters & conditional formatting). (“TA4_YOUR_NAME.xslx”)
The second file will be a one page Word document, in which you communicate your solution to this problem (as you identify what factors are causing the production errors – i.e. which variables appear to be most significantly associated as causes of errors in the production batches) and in which you reference each of the worksheets you created in order to justify your answers (i.e. verbally describing what each of your data analysis worksheets shows and what conclusions can be drawn from it).(“TA4_YOUR_NAME.DOC”)
Helpful Hint: To achieve an especially good presentation/substantiation of your answers, don’t hesitate to use conditional formatting upon select rows/columns on your pivot table(s) to better highlight the computations and conclusions which underlie your answers and your overall solution narrative. Moreover, in composing your document, please try to also go beyond this by telling a coherent and unified story – a story that logically justifies your recomemndations to Mr. Dolls.
Your instructor will answer student questions about this assignment – BUT in the interests of both efficiency and fairness to all students, all inquiries about this assignment must be posed to the Grapevine discussion forum – that way all students can see all the responses.
Try to have fun with this challenge & don’t ignore the Special Instructions listed below!
Special Instruction: Two things you MUST start with to complete this assignment
In order to even out imbalances in skill and prior experience among students in the class, the following two paragraphs tell you how everyone has to start their analysis (this is to help you solve this problem):
The secret to this assignment is that all the pivot table computations should not be utilizing as data the raw data column of Num Defect (i.e. the actual number of production errors for each batch) but should instead use a new computed column you will create (in the source data worksheet) that is a % error rate for each batch. This is intuitively obvious since 200 defective products in a batch of 10,000 products is more significant than the same number of errors in a batch of 100,000. Therefore your first activity is copy the raw batch data inot a new worksheet and then add a column onto this data: A computed batch error rate for each production batch (i.e. each row of data).
Instead of computing pivot table results using SUMs or COUNTs in your pivot tables you will usually have to use AVERAGE instead. Computing a sum of the error rates for each employee will be meaningless as the basis of comparison among any of the factors (Products, Employees etc) that characterize each production batch. You will need to be comparing averages of computed error rates, which means that you have to change the basis of computation in your Pivot tables from Sum(X) to Average(X) – where X is a batch error rate.
How this assignment is graded
There are 2 sets of criteria used for grading this assignment.
Solution accuracy criteria Solution presentation criteria
As per the assignment instructions the assignments submission contains 2 files (a WORD document and an Excel Spreadsheet file) that have been submitted to the correct dropbox (the first time around) and were named correctly with the assignment number and student name.
The student used at least two pivot tables in the solution, as well as at least one instance of conditional formatting or a custom filter.
The student has effectively used the hints and instruction/guidance provided by the instructor for this assignment.
The student’s computations justify the conclusions that the student drew in their analysis and recommendations,
The analysis reaches specific conclusions about the data,
The conclusions of the student’s quantitative analysis logically support written conclusions about the source(s) of the production errors
The written conclusions logically support recommendations about what this business should do to remedy the situation.
All the worksheets in the spreadsheet file are labeled.
Even if the student uses several worksheets, this not negated by having poorly labeled (or unlabeled) worksheets, or by having any worksheets (other than the raw data and computed columns in the data worksheet) that were not cited in the write-up (i.e. no extraneous worksheets) – or even having worksheets that are labeled but contain so many pivot tables in any one worksheet that it is difficult for the reader to understand what’s there.
The write-up for your solution is written clearly,
The write-up for your solution sets forth its reasoning and logic
Each conclusion set forth in the student’s write-up cites specifically (by title of the labeled worksheet) which of the supporting worksheet analyses justifying that conclusion.
The student’s recommendations are clearly related to their conclusions
Grading:
Grade Criteria met
100
Not only were all assignment criteria met, but the student’s assignment submission was so logical, actionable, well-presented and clear (for a manager with a two-minute attention span) that the instructor would happily recommend the student for an entry-level management consulting position.
90
All 14 criteria listed above were met
80
All but one criteria above (from each category) were met
70
All but two criteria above (from each category) were met
60
All but three criteria above (from each category) were met
50
All but four criteria above (from each category) were met
OR
Same as 100, but late
40
All but five criteria above (from each category) were met
OR
Same as 90 but late
30
Same as 80 but late
20
Same as 70 but late
10 Same as 60 but late
