Originally published: 16/07/2018 12:14
Publication number: ELQ-24349-1
View all versions & Certificate
Publication number: ELQ-24349-1
View all versions & Certificate
2016 Round 2, Section 3: Tally Up
Excel training/competition model from the 2016 Financial Modeling World Championships
by Full Stack ModellerTo help finance professionals transform their Excel, financial modelling, data visualisation & analytics skillsFollow 78
financial modelexcel skillsexcel trainingfinancial modeling competitionworld championshipsfinancial model training
Description
This workbook is taken from the Second Round of the 2016 Modeloff financial modeling world chamionships.
Follow the instructions in order to complete the model:
An election has been held for the Congress in the fictional country of Excelstan. Excelstan is a small country and is divided into 9 Districts, named after letters of the Greek alphabet. Each District elects one member to Congress. There are 1000 voters. Each voter is assigned a District Code based on where they live. The District Code is a number between 105 and 194 and determines what District the voter votes for.
There are 8 political parties in Excelstan competing for seats in the Congress. The parties are Red, Orange, Yellow, Green, Blue, Purple, Brown and Black. Voters cast their ballot by numbering 1 against their first choice. After this, they can choose to provide preferences by numbering 2 against their second choice, 3 against their third choice, and so on up to 8 against their eighth choice. Voters can choose how far down they give preferences to. In the data provided, every voter has voted for either 4, 5, 6, 7 or 8 parties in preferential order. When counting votes, if a voter has not provided a preference number against a particular party, assume they gave that party an 8 (i.e. they ranked any unmarked party equal last). Each District is counted independently of the other Districts, using only the votes from that District.
COUNTING THE VOTES
Excelstan has two different systems for counting votes to determine which party wins each District.
Counting System 1: First Past The Post
Counting System 2: Points Allocation (also known as ‘Eurovision style’)
COUNTING SYSTEM 1: FIRST PAST THE POST
The party with the most number of “1” votes wins. If two or more parties tie for the most number of “1” votes, then the winner is the party that received the most “2” votes (from the set of parties that tied for the most “1” votes). If there is still a tie, then count the “3” votes and so on until a winner is determined.
COUNTING SYSTEM 2: POINTS ALLOCATION
Parties receive points for each vote. The better the preference, the more points receive (See PDF for scoring)
The party with the highest number of points wins the District.
The workbook provided contains all of the voting data. In order to answer all of the questions, you will need to use the rules of each counting system below to determine the winning party or parties for each District under each counting system.
Questions 17 to 19 and 26 relate to multiple Counting Systems.
Questions 20 to 22 relate to Counting System 1: First Past the Post
Questions 23 to 25 relate to Counting System 2: Points Allocation
Allotted time: 36 minutes
Once finished, feel free to upload your model to your own dedicated author channel!
This workbook is taken from the Second Round of the 2016 Modeloff financial modeling world chamionships.
Follow the instructions in order to complete the model:
An election has been held for the Congress in the fictional country of Excelstan. Excelstan is a small country and is divided into 9 Districts, named after letters of the Greek alphabet. Each District elects one member to Congress. There are 1000 voters. Each voter is assigned a District Code based on where they live. The District Code is a number between 105 and 194 and determines what District the voter votes for.
There are 8 political parties in Excelstan competing for seats in the Congress. The parties are Red, Orange, Yellow, Green, Blue, Purple, Brown and Black. Voters cast their ballot by numbering 1 against their first choice. After this, they can choose to provide preferences by numbering 2 against their second choice, 3 against their third choice, and so on up to 8 against their eighth choice. Voters can choose how far down they give preferences to. In the data provided, every voter has voted for either 4, 5, 6, 7 or 8 parties in preferential order. When counting votes, if a voter has not provided a preference number against a particular party, assume they gave that party an 8 (i.e. they ranked any unmarked party equal last). Each District is counted independently of the other Districts, using only the votes from that District.
COUNTING THE VOTES
Excelstan has two different systems for counting votes to determine which party wins each District.
Counting System 1: First Past The Post
Counting System 2: Points Allocation (also known as ‘Eurovision style’)
COUNTING SYSTEM 1: FIRST PAST THE POST
The party with the most number of “1” votes wins. If two or more parties tie for the most number of “1” votes, then the winner is the party that received the most “2” votes (from the set of parties that tied for the most “1” votes). If there is still a tie, then count the “3” votes and so on until a winner is determined.
COUNTING SYSTEM 2: POINTS ALLOCATION
Parties receive points for each vote. The better the preference, the more points receive (See PDF for scoring)
The party with the highest number of points wins the District.
The workbook provided contains all of the voting data. In order to answer all of the questions, you will need to use the rules of each counting system below to determine the winning party or parties for each District under each counting system.
Questions 17 to 19 and 26 relate to multiple Counting Systems.
Questions 20 to 22 relate to Counting System 1: First Past the Post
Questions 23 to 25 relate to Counting System 2: Points Allocation
Allotted time: 36 minutes
Once finished, feel free to upload your model to your own dedicated author channel!
This Best Practice includes
1 Excel Workbook & 1 PDF File
