MCD2080 Business Statistics EXCEL Exercise 1
Descriptive Statistics Presentation Using Excel

This assignment is marked out of 84.25 marks.
It is designed to test your understanding of Descriptive Statistics (including Graphical Presentations using Excel
and interpretations) as well as Regression Analysis (including generating regression output using Excel and
interpretations).

DATA AND IMPORTANCE OF THIS EXERCISE
The file MCD2080_Exer_T3 22.xlsx contains data for the Excel Exercise covering week 1 – 4 Lectures and week
1 – 5 Tutorials.
The aim of this exercise is for you to get familiar with excel tools and functions which you will require in future
studies at the university and even the outside world.
In addition, this gives you an opportunity to be able to interpret the descriptive statistics and regression analysis
you learn during the first few weeks in this unit.
This is a very important task in learning the basic Excel skills.
Data Background: Tongrey Households
You are a statistician in the department of bureau of statistics who is interested in community welfare in the city of
Tongrey. To study the welfare, you take a survey of 500 households that is randomly sampled. Among the information
collected is the number of individuals in the household, the sector where they live, if they reside in their own home
or rented house, their income and either their monthly mortgage or rent payment.
Please answer the following questions and use Microsoft Excel where necessary.
The data for each household includes the following variables:

▪ Family Size: the number of people in the household.
▪ Location: 1 = southwest (SW) sector, 2 = northwest (NW) sector,
3 = northeast (NE) sector and 4 = southeast (SE) sector.
▪ Ownership: 0 = rent home, 1=own home.
▪ Income: total annual income of the household’s wage earners ($).
▪ Monthly Payment: monthly mortgage payment or rent payment ($).
OUTPUT AND RESULTS
• You are required to use Excel to generate the output.
• It is recommended to create your charts and tables in the “Results (output)” worksheet provided.
− To produce the descriptive (summary) statistics, use the excel functions you have learnt during the
tutorials (or Lecture).
− However, it is also recommended to understand how to use Excel’s Data Analysis Toolpak to produce
summary statistics.
• Make sure to save and organise your excel outputs (tables and the charts) in order to answer the questions on
Moodle.
• Keep your work safe until you get final grading and feedback.
• Organise your tables and the charts well, otherwise poor presentation will result in a loss of marks.

Due Date: by 5 pm Sunday of Teaching Week 6 (27th November 2022)
MCD2080 T3 2022 2 | P a g e
HOW TO ANSWER QUESTIONS AND UPLOADING TABLES AND CHARTS TO QUESTIONS
Use the link in the Week 2 Tutorial section labelled “Excel Assignment” and follow the instructions. There are a
set of questions, and you should answer all before submitting your work. Below is just a guide on what you are
expected to do in answering the questions.
For the FULL INSTRUCTIONS please refer to the QUESTIONS section below.
There are 10 questions in this exercise.
o Question 1(a-e): Requires you to provide the correct answer out of the multiple choices provided.
Use the data set provided (MCD2080_Exer_T3 22.xlsx) to answer the rest of the questions
o Question 2: Requires you to rename and upload (pdf) “Table 2a &2b. Upload the table ONLY otherwise, you
lose marks. Hint: copy the Table to a word document and then save as pdf.
o Question 3: Requires you to rename and upload (pdf) “Graph 3a & 3b. Name and upload the Graph ONLY
otherwise, you lose marks. Hint: copy the Graph to a word document and then save as pdf.
o Question 4: Requires you to rename and upload (pdf) “Table 4”. Name and upload the table ONLY
otherwise, you lose marks. Hint: copy this Table to a word document, then organise it carefully and then save
as pdf.
o Question 5: Requires you to interpret the data distribution listed in the question. Be careful not to delete the
box provided to type your answers.
o Question 6: Requires you to construct pivot tables that you will upload on Moodle question 8 (pdf). Name
and upload the Tables 6a), Table 6b) & Table 6c) ONLY otherwise, you lose marks.
Hint: copy the Tables to a word document, then organise them and then save as pdf.
o Question 7: Requires you to rename and upload the table on Moodle. In addition, you are required to provide
interpretations of some proportions/probabilities from the pivot table. These are short answers where you
chose the correct “word(s)”.
o Question 8a): Requires you to rename and upload (pdf) “Graph 8. Name and upload the Graph ONLY
otherwise, you lose marks. Hint: copy the Graph to a word document and then save as pdf.
o Question 8b-d): Requires you to calculate and/or interpret the results as asked
o Question 9a): Requires you to rename and upload (pdf) “Table 9.” Name and upload the Table ONLY
otherwise, you lose marks. Hint: copy the Table to a word document and then save as pdf.
o Question 9b-d): Requires you to use 9a) to calculate state and/or interpret the results as asked in b - d.
o Question 10a): Requires you to rename and upload (pdf) “Table 10.” Name and upload the Table ONLY
otherwise, you lose marks. Hint: copy the Table to a word document and then save as pdf.
o Question 10b – f): Requires you to use the regression output in 10a) to answer questions b – f.
You can start answering the questions on Moodle BUT DO NOT SUBMIT the exercise UNTIL you answer
ALL questions to your best level.
You can go to any question AT ANY TIME using “NEXT” and “PREVIOUS” buttons.
Caution: When you Click SUBMIT the Exercise (QUIZ), you will not be able to change any question that
will be final.
To be able to score high marks,
o you need to read all the instructions and questions carefully

MCD2080 T3 2022 3 | P a g e
Questions
1. State the data type for each of the variables below, i.e., categorical (nominal or ordinal), numerical
(continuous or discrete). Explain your reasoning in each case.
a) Family Size
b) Location
c) Ownership
d) Income
e) Monthly Payment
[5 marks]


2. To start your analysis, first, you conduct some descriptive statistics on the monthly home payment for both
households who pay rent and those who own homes (pay mortgage).
To do this analysis you need to sort the data by ‘Ownership’. Once the data is sorted follow the steps below:
o In the Results (Output) worksheet, cell A1 has the heading ‘Rent’ and cell C1, ‘Mortgage’.
o Copy/paste the values for monthly payments for the people who rent in column A (i.e. under ‘Rent’) and
for the people who make monthly mortgage payments in column C (i.e. under ‘Mortgage’).
o Use Excel’s Pivot Table function to create a percentage frequency (count) distribution table for each of
these variables. [Hint: use Count and NOT Sum]
o For the Rent Payment variable, the class intervals (groups) should start at $300 and have a width of $200
and for the Mortgage Payment variable the class intervals should start at $400 and have a width of $200.
o Show percentages correct to 2 decimal places.
o Use the Tabular Format report layout and label your table accurately and informatively.
Name them Table 2a and Table 2b respectively and upload it to Moodle as required.
[6 marks]
3. Use the tables from Question 2 to construct percentage frequency histograms of Monthly Payment for each
ownership type.
Make sure you Hide/remove all field buttons from the graphs and ensure that the histograms are appropriately
labelled. Name them Graph 3a and Graph 3b respectively and upload to Moodle as required.
(Marks will be deducted for the poor presentation of your graph) [8 marks]

Use the data set provided (MCD2080_Assignment_T322.xlsx) to answer the rest of the questions
MCD2080 T3 2022 4 | P a g e
4. Obtain the indicated summary measures in the following table (Table 4), for the Monthly Rent Payments and
Monthly Mortgage Payments.
Report values to 2 decimal places. Name it Table 4 and upload it to Moodle as required.
Table 4: Table of Summary Measure for monthly payments ($)
Statistics
Monthly rent
payment
Monthly mortgage
payment
Excel Function Used for Monthly mortgage
payment
(including cell references)
Mean
Median
Standard Deviation
Minimum
Maximum
Range
Count
First Quartile
Third Quartile
Interquartile Range (IQR)
Coefficient of Variation (CV)
[8.75 marks]
5. Refer to your Questions 2, 3 & 4 results to describe the Monthly rent payment distribution. Discuss areas of:
a) Central location,
b) Variation, and
c) Shape.
[12.5 marks]
6. Use Excel’s Pivot Table function to create a table of the frequency distribution of all households in
relation to Location (ROWS) by Ownership (COLUMNS).
Use the “Tabular Form” report layout.
(Hint: For this question use the original data and make sure your table values are “Count and
NOT Sum. Also, label variable categories as defined – see data background)
From this table create and label the following three tables, rounding off percentage values to 2 decimal
places:
o Table 6a: the frequency distribution with values shown as “% of Grand Total”.
o Table 6b: the frequency distribution with values shown as “% of Column Total”.
o Table 6c: the frequency distribution with values shown as “% of Row Total”.
[7.5 marks]
7. Refer to Tables 6a - c in Question 6 to answer the following:
a) (i) What is the proportion of households that make a monthly rent payment?
(ii) What is the percentage of households that make a monthly mortgage payment and are from the NW
sector?
b) If a household was randomly selected from those who pay a monthly mortgage, what is the probability
that they are located in the NE sector?
c) For the households located in the SE sector, what is the proportion that pays monthly rent?
d) In the process of investigating if a household ownership is independent of the location,
(i) the probability of household paying monthly rent is supposed to be equal to the probability of a
household paying monthly rent given it is located in the NE sector? What are these two probabilities?
(ii) based on these probabilities, state if the ownership and location are independent and why?
[6 marks]
MCD2080 T3 2022 5 | P a g e
For the next part of the analysis you will be analysing the relationship between Monthly payment ($)
and Annual income ($).
8. Use Excel to answer the following questions:
a) Create a scatterplot of the relationship between Monthly payment and Annual income.
Name it Chart 8 and upload it to Moodle as required
b) Describe the relationship between Monthly payment and Annual income from the scatterplot in part a).
c) Calculate the correlation of the relationship between Monthly payment and Annual income and interpret
the value
[7.5 marks]
9. Use Excel’s Data Analysis toolpak to
a) Run a linear regression to analyse the relationship between Monthly payment and Annual income.
Name it Table 9 and upload it to Moodle as required.
Use the regression output from part a) to:
b) Write down the estimated linear regression equation
c) State and interpret the estimated y-intercept
d) State and interpret the estimated slope
[12 marks]
Hint on writing equation for Question 9b:
10. Use Excel’s Data Analysis toolpak to
a) Run a linear regression to estimate the mean of the Monthly payment. Use a 99% confidence level.
Name it Table 10 and upload it to Moodle as required
Use the regression output produced in part a) to:
b) Write down the mean value (3 decimal places only)
c) Interpret the mean value
d) State and interpret the 99% confidence interval
e) Without calculations compare the precision between the 95% and 99% confidence intervals
f) Apart from the confidence level, state and explain other factors that affect the precision of this
confidence interval.
[11 marks]