You have been approached by the organisers of a Beauty Pageant who described to you the difficulties they faced in last years’ contest. All the scoring was done manually, this caused significant delays and ran the risk of manual errors.
The organisers continue to describe their requirements for a system they would like to use for this years’ show. They require a system that can let them:
• Easily input scores as they are entered by the judges.
• Calculate weighted scores and rankings quickly and accurately based on multiple criteria.
You instantly think a Pivot Table would do the job and you come up with the following sketch as the organisers explained their requirements for the system in detail.
Figure 1. Desired Output
Where the pivot table has:
• Measures: ScoreTotal, ScoreWeighted, Rank
(Measures in Power Pivot are calculations used in data analysis).
• Slicers: Category (Miss, Mrs), Day (Day1, Day2), Round, ScoreType (Slicers are a visual filter in the form of an interactive button).
This will allow the organisers to easily determine:
a) Which contestants advance to the final round.
b) Overall winners after the final round.
c) Specific awards like Ms.Talent (based on the scores in the Talent Round).
You then proceed to come up with following data tables:
• Judges: We have six judges.
• Contestants: We have two groups of contestants (Miss, Mrs categories), each contestant is identified by a number which will be used by the judges for scoring.
• Rounds: We have multiple rounds on day one and day two. Each round receives separate scores for presentation and performance. The top three contestants from each category would go on to the Q&A round, where the final winners would be decided.
• Scoring sheets: Judges enter the scores on paper scoring sheets, which would need to be collected and quickly entered into our data model.
Now they have left you to get to work!
Please find the Excel file named lab1_data which includes the above data tables. Using Power Query and Power Pivot, mould your data in Excel so you can explore and visualise it with Pivot Tables creating the desired output shown in Figure.1
Create a document describing your solution (the steps taken and the reasoning behind each step), also include a diagram view of your data model and report the winners from each of the Miss and Mrs categories (1st, 2nd and 3rd place rankings).
