Q 1. Given the survey information as shown , you are required to produce a Pareto Chart using Excel
Food Service Customer Survey Data Collection
Complaint type Count Cumulative Cumulative %
Count
Wait time 60 60 47
Soup not hot 30 90 71
Food too salty 10 100 79
Food portion too small 5 105 83
Food items overpriced 6 111 87
Salad not fresh 7 118 93
Staff not well trained 9 127 100
The result of this exercise/answer is a Pareto Chart, showing the following: the bar chart and a curve line and 80% cut off ( 80/20 rule)
Y axis on the left side =frequency / count
Y axis on the right side = cumulative %
X axis shows the types of complaint clearly label.
Each column shows the complaint with the count number on the top of the bar
Title : Customer complaint survey data over 30 days
Please show your excel calculations step by step. I tried the following steps provided by my tutor but I was stuck.
- I selected columns Complaint Type, Count and Cumulative %
- On the Insert tab, in the Charts group, click the Column symbol.
- Click Clustered Column , select the 2 D column
Sample Solution