成功案例设置

This assignment requires you to make use of Software Excel to calculate Statistic and analyse data.

There are two part of the assignment, and each part contribute 50% of the assignment grade.

In this assignment, student will demonstrate their competency applying what they have learnt in

probability to analyse a scenario and the COVID dataset given in order to visualize the data using

Spreadsheet software. Students are highly advised to conduct regular meetings with team members

to discuss and to ensure the assignment is progressing. Teamwork and every member are expected

to contribute to the project. Working alone may resulting in unfavourable outcome / marks for the

module as a whole.

The weightage of this assignment is 20% of the overall grade of Discrete Mathematics.

DUE DATE: Check Blackboard Submission Link

ASSIGNMENT REQUIREMENT

Part (A) CASE STUDY: Analysing COVID19 Dataset

You are given a dataset in relation to COVID19 record called RAW DATA.

(covid19_July2020_dataset.xlsx). The dataset contains data such as country name, region, population,

total confirmed cases, total death, total recovery, and active case.

Your team has been assigned to perform data analysis using spreadsheet software such as Microsoft

Excel.

A.1 Creating New Spreadsheet

Within the dataset workbook, create a separate spreadsheet named “Part A Solution” next to Raw

Data. In Part A Solution construct a simple table which include all team members’ name, email

address and Contribution(%).

Raw Data Part A Solution

Provide an appropriate table header and highlight the team leader’s name. Team Leader will be

responsible for the submission of the final work (final version of the spreadsheet, any other

submission by members will not be accepted.)

Your file name for the submission to Blackboard should be Group xx DM.xls where xx is your group

number.

NOTE: You are expected to format and style all the tables in this spreadsheet with reference to the

following questions. Please label the table with reference to the question answered.

A2 Top 10 Countries with COVID19

Create a second table below the team members table, list down the top 10 countries with highest total

COVID19 cases and display the data of the top 10 countries identified. Do not just Copy/Paste from

Raw Data, use excel function to extract and display data appropriately.

Number Country Total COVID cases

A3 Statistics

Create a third table to show the lowest, highest, total and average of the population, total COVID

cases, total death, total recovery and total active cases for all countries from the RAW Data given.

You are expected to use Excel formula to generate the data. Do not just cut and paste data from Raw

Data or unknown sources.

Discrete Mathematics Page 2 of 8

A4 Create a fourth table in Part A Solution spreadsheet, use excel function to extract the country

name with its probability to answer the following questions:

a) Which country will have the highest probability of death if a person is diagnosed and tested

positive with COVID?

b) Which country will have the lowest probability of recovery if a person is diagnosed and tested

positive with COVID?

A5 Data Aggregation & Consolidation, for the fifth table,

a) Use the excel function eg “CountIF” or any other suitable function to calculate total number

of countries within each region.

b) Extract and consolidate the total cases, total death, total recovery and active cases for the 10

ASEAN countries (Identify the 10 ASEAN countries from the internet). Exclude any

countries with missing data OR countries with total cases below 50,000.

A6) Data Visualisation

At the end of the Part A solution spreadsheet, create the following charts:

a) Create a bar chart to show the number of total cases for the top 10 countries with highest

COVID cases.

b) Create a pie chart to show the distribution of countries for each region.

NOTE: Format and label all charts appropriately and provide descriptive title for each chart. Do

ensure the chart is readable and looks professional. Format the table and charts to improve the

presentation of all you answer and solution.

Discrete Mathematics Page 3 of 8

Part (B) Application of probability and analysis using Excel

Read up the following site to understand the Monty Hall problem.

https://en.wikipedia.org/wiki/Monty_Hall_problem and

https://betterexplained.com/articles/understanding-the-monty-hall-problem/

Summary of the Monty Hall problem: You are given the choice of three door, behind three doors

randomly lie two goats and a car, respectively. You pick a door e.g Door 1 (Goat behind it) , the host

who knows what is behind the doors, open another door e.g Door 2, which has a goat behind it. You are

asked if you would like to switch your choice or stick to your choice, what would be your suggestion

after the following analysis?

Create another Worksheet Part B Solution for this part.

Raw Data Part A Solution Part B Solution

B.1 One way to understand the problem is to explicitly list out all the possible outcomes and count how

often you will win if you stick to your choice versus if you were to switch door.

To set up the data, create a workbook to illustrate the data used in the Monty Hall problem, list out all

the possibilities, a suggested format is shown below. You may add more column subsequently to show

your understanding. The data for Door 1, Door 2, Door 3 and Choice should be random data.

Door 1 Door 2 Door 3 Choice Result if Result if

staying switching

Goat Goat Car 1 lose win

Goat Goat Car 2 lose win

Apply formulas to check the two columns: Result if staying and Results if switching. You can manually

enter test data for door 1, 2 and 3 with Choice of player to test if the formulas are working as expected.

B2. Analysis in Excel and Simulation of the results

Use Excel to verify your understanding by running some simulation test. To perform the test for more

runs eg 50 simulated results and analyse the results. You will begin with empty data in the worksheet and

use Excel RAND function to randomly generate the Door number behind the Car and assign the Goat to

the other two doors. Use RAND to randomly pick a door in Choice and Excel function to determine the

results (Win or Lose) if the participant choose to switch or stick to his choice.

Add formulas to enable all the values to be calculated automatically by adding additional row/column to

your spreadsheet for you 50 simulations. You could manually or use excel function read in the 50

simulation data and calculate the total score in order to derive and validate your conclusions based on the

possible outcomes of the simulation.

B3. Interpretation of results

Draw your conclusion based on the results of B2. Find the probability of winning by Staying with your

choice vs Switching, confirm which is the better strategy.

Discrete Mathematics Page 4 of 8

B4 How to you relate Monty Hall problem to conditional probability, substantiate you findings by

using conditional probability formula.

WHAT TO DO?

1. Form a team of 4 – 6 students within the same DM Group.

2. Discuss and understand the requirements of the assignment and defined the parts

/component/questions to be completed by each member.

3. Conduct regular meetings with team members to consolidate and complete above tasks.

4. Conduct peer review and evaluation.

5. Finalize the spreadsheet and submit to Blackboard, only one submission per group by the leader.

6. You workbook in .xls should contain the following tabs: Raw Data, Part A Solution and Part B

Solution.

WHAT IS TO BE SUBMITTED?

Deliverables & Submission

1. Updated Spreadsheets (ensure all members name are included).

Students are to submit their project prior the due date indicated in Blackboard. Any late submission

will be subjected to deduction of marks as follow:

PLAGARISM & PANALTY

Plagiarism is the practice of using another writer’s ideas or observations and presenting them as the

author’s own. If students directly or indirectly use another author’s words without due

acknowledgements to the original source, they are guilty of plagiarism and their work cannot be

accepted as academic writing.

Plagiarism is a serious offence in PSB Academy and may lead to penalties in the student’s assessment,

in most cases even failure of the assignment and / or module. In severe instances, plagiarism may

Discrete Mathematics Page 5 of 8

lead to exclusion of the student from the programme of study. (read more detail in

Student Handbook on 4.12 Academic Misconduct : PAC-ADM-G02 R15

Student Handbook)

TEAMWORK AND TEAM CONTRIBUTION

All members are expected to collaborate, contribute, and learn from each other while working on this

assignment. Poor teamwork may result marks deduction as a whole. For unequal contribution of

work, Individual marks will be calculated based on the following formula:

Individual contribution (%)

Individual marks = Assessment mark x ---------------------------------------------

highest contribution (%) in group

MARKING RUBRICS

Deliverables Not done / Marginal Below Meet

Incomplet Fail Expectation Expectation

e Good Excellence

A1 Format of

0 1 2 3 4 5

spreadsheet/table

A2 Top 10 Countries

0 2 4 6 8 10

with COVID19

A3 Statistics 0 2 4 6 8 10

Probability

A4 (Extreme cases in 0 2 4 6 8 10

a and b)

Data Aggregation &

0 2 4 6 8 10

Consolidation

A6 Data Visualisation 0 1 2 3 4 5

B1 Tabulation of data 0 2 4 6 8 10

B2 Simulation of

0 4 10 12 16 20

results

B2 Interpretation of

0 2 4 6 8 10

results

B3 Correlation of

0 2 4 6 8 10

probability.

Total 100

Discrete Mathematics Page 6 of 8

RUBRICS DESCRIPTIONS

Ranking Descriptions

Excellence • Exceptional knowledge base exploring, analysing and evaluating the

discipline and its theory with extraordinary originality and autonomy.

• Demonstrates an exceptional grasp of relevant analytical techniques, and the

ability to apply these to new and/or abstract information and situations.

• Shows a highly developed appreciation of the limits and/or appropriate uses

of particular analytical and evaluative approaches.

• Knowledge and understanding of theory, where relevant, is highly detailed.

Exceptional appreciation of the limits of theory demonstrated throughout all

•

assessment outcomes.

• Approach to assessment task is theoretically informed to an exceptional

standard.

Good • Very good knowledge base that supports analysis and/or evaluation and

problem solving in theory and/or practice within the discipline, with some

originality displayed.

• Makes very good use of established techniques of analysis and/or evaluation

relevant to the discipline.

• Shows developing ability to compare alternative theories and/or analytic

approaches, where relevant.

Meet Expectation • Good knowledge base that supports some analysis and/or evaluation and

problem solving in theory and/or practice within the discipline.

• Makes good use of established techniques of analysis and/or evaluation,

relevant to the discipline. Sound descriptive knowledge of key theories with

some appropriate application.

Below • Satisfactory knowledge base demonstrating comprehension and

Expectation formulation of basic knowledge with some omissions at the level of

theoretical understanding.

• Limited ability to discuss theory and solve problems within the discipline.

• Makes satisfactory but limited use of established techniques of analysis and/or

evaluation, relevant to the discipline.

• Selection of theory, if relevant to the assessment outcomes is satisfactory but

application and/or understanding is limited.

Marginal Fail • Outcomes not or only partially met.

Restricted knowledge base demonstrated. Limited understanding of

•

discipline.

Difficulty with linking theory and problem solving within the discipline.

•

Discrete Mathematics Page 7 of 8

Attempts at analysis and/or evaluation ineffective and/or uninformed by the

•

discipline. Knowledge of theory inaccurate and/or incomplete.

• Application and/or understanding very limited, choice of theory

inappropriate.

 Little or no evidence of knowledge base.

 Little evidence of understanding of discipline.

 Little or no evidence of knowledge base.

Not Available/  Little evidence of understanding of discipline.

Incomplete  Significant difficulty with theory and problem solving within the discipline.

Lacks any analysis and/or evaluation.