PROJ PivotTable Analysis - Instructions and Write-up Spring24
docx
keyboard_arrow_up
School
Brigham Young University *
*We aren’t endorsed by this school
Course
310
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
8
Uploaded by CoachHorsePerson1062
Activity: PROJ PivotTable Project - Production Analysis Assignment
The purpose of this activity is to demonstrate a synthesis of your problem solving and data analysis skills by using Excel Pivot Tables and Pivot Charts to uncover the root cause of a business problem, and then MS Word to write a memo that communicates the root cause and recommends a solution.
You will be analyzing data about various factors that may be causing defects in a t-shirt production line. Use Pivot Tables & (Pivot)Charts for the analysis (Excel users on Macs may use Charts instead of PivotCharts). Then write a report (well-formatted memo) that explains your findings and integrates the charts/tables providing the evidence to support your findings.
This is a graded assignment worth 50 points, with approximately 55% of the points awarded for the content of the report, and 45% of the points awarded for writing quality.
Student Directions
Step 01.
Download the project instructions file, contents listed below.
Step 02.
Download the data file from the Canvas module where you found these instructions. Its name ends in ...T-shirt Production Data.xlsx. Step 03.
Prepare your data for analysis by calculating two ‘Defect Rates.’ The first rate (
BatchDefectRate)
should be created in column G of the data table using an Excel formula. This tells you the (per row) rate of defects in a %. After you’ve created a Pivot Table from the table, select Fields, Items & Sets, and create a Calculated Field and name it to DefectRate.
The Formula for DefectRate = DEFECTIVE QTY / BATCH QTY (see screenshots below). Play around with these two rates in the “Values” area of a PivotTable (using the “Average” aggregation for BatchDefectRate and “Sum” for DefectRate), and convince yourself that they yield different answers
1
.
Step 04.
Conduct your analysis. You will use the calculated field defect rate for all analyses. While the executives have set you up to answer some initial questions first (in the data description section), this is the smaller part of the analysis. The important part is to determine which combination of two factors is causing the highest defect rate. Use the suggested notes on the last page of the instructions file or your 1
To understand why they’re different, note that calculating the Average of BatchDefectRate sums the percentage defective for each batch and then divides by the number of batches. Sum of DefectRate actually adds up all the defective shirts, adds up all the batch quantities, and divides the total defects by the total shirts produced. This is a subtle distinction, but understanding it is one key to being a knowledgeable PivotTable user.
1
own method for capturing insights from the many PivotTables you'll create (complete analyses generate 10+), along with your observations about each.
Step 05.
Once you have finished your analysis, write up a maximum
3-page report in memo format communicating your results. This should contain all the sections requested in the “Required Organization and Content” section of the instructions and include the top 3-5 tables and/or charts in the
appropriate sections as indicated by the rubric. These will answer the executives' questions and show the root cause of the problem. Step 06.
Submit the report, Excel analysis file, and (optionally) your notes file on Canvas
2
Contents
Student Directions
.......................................................................................................................................
1
Required Report Organization and Content
................................................................................................
2
Required Report Sections
........................................................................................................................
2
Report Grading
........................................................................................................................................
3
Content (55%)
......................................................................................................................................
3
Writing (45%)
.......................................................................................................................................
3
General Project Information
........................................................................................................................
4
Business Problem: What’s causing defective T-Shirts?
............................................................................
4
Data File Description
...............................................................................................................................
4
Help Resources
............................................................................................................................................
5
Writing with Data Visualizations - Integrate Text and Numbers
..............................................................
5
PivotTable Resources - Microsoft Help Videos
........................................................................................
5
Writing Resources
....................................................................................................................................
6
Process Notes from the Instructor (AKA “How to get started”)
...................................................................
6
Notes for yourself (NOT GRADED)
...............................................................................................................
7
Required Report Organization and Content
Required Report Sections
Memo Heading
– follow COBE writing guide standards for the memo heading (TO:, FROM:, RE:, date, etc). Address the memo to the T-shirts.com Process Improvement Committee
.
After the memo heading, please format your report to include headings and content for the following sections: PROBLEM
-- Briefly describes the problem you were asked to solve. Excellent papers preview the solution, too.
DATA DESCRIPTION
– Describe the data about defects using descriptive statistics (counts, percentages, sums, averages, etc). Each of the executives in the audience will want to know about how the batch defect rates relate to the factor they are in charge of, so you should present the numbers in either text or table form so that they can answer ALL of the questions below from each member of the executive team. Be as concise as possible – this section should be shorter than the Root Cause Analysis section. The executives and their questions are:
Operations manager – (1) How many total defects have we encountered? (2) What % of t-shirts produced does this represent? (3) How many batches have been run on each machine? (4) Which machine has the lowest defect rate, and (5) which has the highest defect rate?
Product Director – (6) Which products have we made the most of? (7) Which are easiest to make (have the lowest defect rate)?
HR Lead – (8) Which employees are on the factory floor? (9) Who has produced the most T-
shirts? (10) Who has the lowest defect rate? ROOT CAUSE ANALYSIS
– [Defects are cause by ….] (Largest section) Before you can write up the root cause analysis section, you must first conduct an analysis by using the same PivotTable techniques you used in the Data Description
above. This section of the memo identifies and discusses the most important factor (or two-factor combination)
that is the ‘root cause’ of the issue. Illustrate your discussion of the most important factor(s) with two or
3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
more tables and/or charts from your analysis. Clearly identify and illustrate what you believe to be the principle cause, and note if it’s a one-time issue or an ongoing issue. To be persuasive and complete, this
section should also briefly discuss what factors were analyzed, but do not impact defects (no details required besides the names of the factors).
Analyzing the individual factors, plus the write up, can earn up to 90% of the points for this section. To earn 100% for this section, combinations
of factors should be analyzed. A typical analysis that has to be done before writing this section starts by analyzing the dataset to identify what individual
factors appear to be most strongly associated with (and potentially causing) the increased defects. For example, Employee is one factor, Machine is another factor, Product is a third factor, Batch Size is a fourth factor, and all four should be looked at, but only one is associated with the highest rate of defects. You actually conducted the machine single-factor analysis and employee single-
factor analysis to answer the Operations Manager and HR lead questions in the last section of the memo. Complete the rest. After that’s done, analyze the dataset to see if any combination
of 2 factors are more strongly associated with (and potentially causing) defects. When you put one factor along the rows of a PivotTable and another factor across the columns, you have created a cross-tabulation. These will often reveal even higher rates of defects than any one factor alone. Once you have analyzed all the single factors and combination of factors, choose the most important/influential/consequential one (or two closely-related) factor or factor combinations. This factor or factor combination should be causing the highest number of defects, and have a feasible business solution that you can recommend in the next section (feasible means that it won’t radically alter the business model, so no solutions like “no longer sell purple shirts”, or “only take orders from customers who buy at least 10,000 shirts”). Once the analysis is complete, write your “Root Cause Analysis” section and make sure the root cause is supported with 2 or more pivot charts (or a chart and a table).
SOLUTION
-- Make at least one recommendation on how the appropriate member of the executive team might solve the cause of the problem. (Be creative here, but it must impact the cause.)
KPI TRACKING
– Define a KPI which will allow the executive team to track how well the solution addresses the problem after it is implemented. Everyone’s KPI should be based on the defect rate, but matched to the factor that you identified as most important (e.g. “Defect rate by Batch Qty”). Make sure
you address all four elements of the KPI: 1. calculation, 2. unit of measure, 3. comparison, and 4. target. You must clearly explain how this KPI is relevant. KPIs will be based on the batch defect rate (whose unit is a %), but will have different comparisons and targets that match the proposed solution. Be sure to give both a numeric target, as well as a justification for how this was arrived at.
CONCLUSION
– Briefly summarize the problem, your analysis, solution and criteria.
Report Grading Content (55%)
Report Sections
are graded on the quality of the ideas, evidence provided to support those ideas, and whether all requested sections are present
Root Cause Analysis
section
will be additionally evaluated on the quality of the figures presented as evidence to substantiate your arguments
KPI metric section
will be additionally evaluated for its relevance to evaluate the recommended solution, as well as the completeness of its definition (be sure to include all 4 elements: target, comparison, increment of measure, and calculation)
4
Writing (45%)
Logistics
o
Three (3) page maximum. Single spaced; blank line added between paragraphs and memo sections. 1 inch margins. Calibri (or equivalent) 11 point font.
o
Use Memo format (FROM, TO, DATE, RE)
Integrates Text and Numbers
(See "
Writing with Data Visualizations
" for more details and an example)
Writing Quality
(Refer to the COBE Writing Style Guide under “Course Resources” on Canvas for specifics on any of the following)
o
Bullets used when necessary to list multiple important items
o
Correct grammar, word choice, and spelling
o
Meaningful, complete, and short, concise sentences
o
Meaningful, complete, and short paragraphs that begin with a topic sentence
o
Section Headings separate sections in your writing and orient the reader
Alternate Writing Quality
:
Full writing quality points may be automatically earned by submitting
a nearly-finished draft to the writing center, visiting with a writing center tutor, and making the recommended changes. Requires submission of both drafts and documentation of interaction with the writing center personnel. A submission comment noting the consultation and additional documentation may also prove useful
General Project Information
Business Problem: What’s causing defective T-Shirts?
Established in 2002, T-shirts.com has rapidly become the place to find, order, and save on T-shirts. One huge advantage T-shirts.com has over other companies, is that they manufacture their own high-quality T-shirts. However, the quality manager for production has noticed an unacceptable number of defective T-shirts being produced and the executive team is all wondering whose responsibility it is to fix. The executive team has created a committee and hired you to head it and figure out, from the data collected by the quality manager, the extent of the problem and what is the primary cause of these defects. They would appreciate also your recommended solution, and a KPI (key performance indicator) to track whether the problem is fixed. On the second tab of the data spreadsheet is the production data that has been collected. Each row describes one batch of T-shirts that was produced. Below is a brief description of each of the columns in
the data sheet. Data File Description
The executive team gave you a data file. Each row describes one batch of T-shirts that was produced. Here is a brief definition of the information within the data file:
A.
Batch:
A unique number that identifies each batch or group of products produced. This number has no meaning, other than the order that the batches were started.
B.
Product:
What product was produced in that batch. Each product has a unique identifier.
C.
Machine:
Which machine produced the T-shirts in that batch (each machine has a unique identifier). D.
Employee:
Which employee operated the machine for that batch. Each employee has a unique identifier. E.
Batch Qty:
The quantity of T-shirts created in that batch. 5
F.
Defective Qty:
The quantity of defective T-shirts produced in that batch.
G.
Batch Defect Rate:
[Needs calculated] Should represent the % of defective T-shirts in that batch
Help Resources
Writing with Data Visualizations - Integrate Text and Numbers Integrate the important figures into your report text. As you do so, apply the three best practices listed below to your report. The example that follows is used to point out various best practices to use when incorporating quantitative data into your report.
A.
Label Figures
: Label every table, chart or visual item, with a Figure #
and a Title
. (The figure title [A] is separate from the graphic’s title [D] on the graphic itself.) Place it just above or below the Figure. The Figure #
and the Title
are actually part of the text—separate from the figure--so you can easily add/change them while writing.
B.
Refer to Figure #:
In your writing, refer to a table or chart by its Figure #
. Your charts, tables, and
other illustrations, should tightly integrate with the text. C.
Including Numbers in Your Text:
1) Add a few important numbers to your text. This ties the figure into the text. 2) Numbers in text can be used to add detail (e.g., 416 to 598) or to add impact (grew nearly 50%). 3) For the numbers you use, remove distractions: round the numbers and drop the decimals, unless exact amounts are the important message.
Example Text and Figure
****************************************************
There is only one positive note regarding our profitability. Figure 3 shows that Gross Margin for online sales in France grew nearly 50% over the year, from $416 to $598. Meanwhile French and Southeast store sales slowly dropped, while the Southeast online margins were basically zero. The strong online profitability gains appear to be due to the new website. 2003
2004
$0.00
$100.00
$200.00
$300.00
$400.00
$500.00
$600.00
$700.00
Gross Margin
Online - France
Online - Southeast
Store - France
Store - Southeast
Gross Margin
************************************************************************
PivotTable Resources - Microsoft Help Videos
Create a PivotTable Manually
o
(5:36) How to create and configure a PivotTable. It will show you a number of tricks to using PivotTables
6
B
A
Figure 3 Comparison of Gross Margin by Channel and Territory
C
D
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
o
https://support.office.com/en-us/article/video-create-a-pivottable-manually-9b49f876-
8abb-4e9a-bb2e-ac4e781df657?ui=en-US&rs=en-US&ad=US
Sort, filter, and calculate
o
(4:07) You can analyze PivotTable data in many ways, including sorting for trends. Also, to filter, summarize and calculate your data.
o
https://support.of
fi
ce.com/en-us/article/video-sort-
fi
lter-summarize-and-calculate-
your-pivottabledata-e3d4bee4-c5ef-4e3c-b990-61a7d540207c
Use slicers, timelines and PivotCharts
o
(3:52) Slicers and timelines are a user friendly way to filter PivotTable data and PivotCharts visually make trends easier to see.
o
https://support.of
fi
ce.com/en-us/article/video-use-slicers-timelines-and-pivotcharts-to-
analyze-yourpivottable-data-4db5de3b-735e-4b03-b3b2-f2105d79dcb5
Writing Resources
COBE Writing Style Guide includes a sample memo format extensive help for common writing issues, and is available in “00.04 Course Resources”.
Additional style guides can be found on the COBE style guide page: https://www.boisestate.edu/cobe/cobe-writing-style-guide/writing-style/
The Writing Center can help writers at all levels of proficiency to improve their writing through a
consultation (in person or online). Please plan to give yourself 3 days for this process. (
https://writingcenter.boisestate.edu/
)
Process Notes from the Instructor (AKA “How to get started”)
You are free to follow your own individual process, as long as your report includes the sections requested in the instructions on Canvas. That said, following a defined problem-solving process can make your analysis easier to start and more comprehensive. For instance, you could follow a form of the
COBE Problem solving model, which starts with what we know (the problem seems to be ….) and then analyzes probable causes (in this case with PivotTables and Pivot Charts) until determining the root cause. After the root cause is determined, potential solutions can be brainstormed, with the best solution selected and a KPI defined to monitor if it’s working. If you want to follow this process, there is a place below to keep track of your notes (these will NOT be graded, even if you turn them in). When you get to the “analyze probable causes” section, apply pivot table/chart techniques to analyze the data, looking for what might cause a higher than normal rate of defects. Good reports (that earn at least 90% of the credit for their analyses) will look at each possible individual factor available in the data
, such as: Is a particular employee a problem? Is a certain product associated with a high rate of defects? Could a machine be causing the problems? What effect does batch size have
on the defects?
Exceptional reports
(earning 100% of the credit for their analyses) will effectively examine not just individual factors, but also combinations of factors. Examples of such an analysis might answer questions like: Does a certain machine have higher rates of defects when a certain employee is running it? Does a certain machine have higher rates of defects when a certain product is produced on it?
NOTE: Before starting your analysis, you need to calculate the rate or proportion of defects and add this column to your data. Consider: if you have two (2) batches that each have 20 defects, but Batch A has 100 items and Batch B has 1000 items, which is the better batch? Batch B is better by a long shot, as it 7
only has 2% defective items, while Batch A has 20% defective items—1 in 5 items are bad. The point is we need to compare the rates or proportions of defects, not the total number of defects. Notes for yourself (NOT GRADED)
Problem Statement (one sentence): "The problem seems to be ..."
Probable Causes (each individual factor or combination of factors), each should be evaluated using Pivot Tables and/or Pivot Charts, which will show how much of an impact each cause has on the metric used to measure the problem. Make sure you can answer the questions from each member of the executive team
Restatement of the Problem: "The underlying problem is..."
List 3-5 feasible solutions
Select the best solution. Explain why it was selected What KPI should be monitored to show that the solution is working? Be sure to clearly define all four (4) elements for your KPI (target, comparison, increment of measure, calculation)
Target:
Comparison:
Increment of Measure:
Calculation:
8