1
MCD2080 Business Statistics Assignment
Descriptive Statistics (Using Excel)
This assignment is marked out of 76 and is worth 10% of the assessment for the unit.
It is designed to test your understanding of Descriptive Statistics using Excel.
Submission Details
Hard copy (Microsoft Word document: printed)
Submit your assignment, by the due day and time, to the MCD2080 Assignment Box - as a printout of
a Microsoft Word document with your full name and student ID number in the top left corner on
the first page. You must include a completed Cover Sheet, which has been signed and dated. A
penalty of up to 5% of the total mark allocated to this assignment may apply if you submit your
assignment without fully completed cover sheet (especially without you tutor name). Your tutor will
mark only this hard copy.
Soft copy (Microsoft Word document: electronic file)
You must also submit your assignment on the MCD2080 Moodle site via the upload link. Submit
only one (1) Word file - without the Cover Sheet. You may submit this soft copy at any time before
the due day and time. The file must be labelled using your Given Name, Surname and ID, for
example, Huan Wang_12345678.docx. The soft copy is a back-up. Your tutor will not print or mark
this soft copy.
Late submissions
If you are unable to submit the assignment by the due day and time, you may wish to apply for
Special Consideration. Place late assignments in the MCD2080 Assignment Box and also submit
them on Moodle. All late submitted assignments will be compared with soft copy version.
A penalty of up to 5% of the total mark allocated to this assignment may apply for each day an
assignment is late (unless the Team Leader grants an extension of time subject to approval of your
application for a special consideration).
Data and Output
The file MCD2080_A1_T317.xlsx contains the assignment data in the worksheet labelled Data.
You must use Excel to generate the output.
We recommend that you create your graphs and tables in the Data worksheet. (You must work
in this way on some computers, e.g. network computers, because Excel’s Data Analysis Tools
may work only on the worksheet where the data resides.)
Copy your graphs and tables from the Data worksheet and paste them to your Word document.
This document must be labelled using your name and ID, for example,
Huan Wang_12345678.docx
Do not print out the data in the Data worksheet.
The presentation of the assignment is important, and some marks are allocated for this. Your
work must be easy to read (eg, a whole table should not partly presented on two pages). The
recommended font size is 10 - 14 point. However, elaborate features are not required.
Due Date: by 10 am Monday of Teaching Week 5
2
Background
ElecCom is a direct-marketing company that sells digital electronic products. The worksheet Data in
MCD2080_A1_T317.xlsx contains data on 800 best ElecCom customers. The ElecCom marketing
manager wants to analyse the data for its customers’ distributions and buying patterns. He wishes to
use the results to guide future promotions. The data for each customer includes
ID The customer ID.
Experience Years of experience with ElecCom: New=less than 2 years,
Old=2 years or more.
Gender Female, Male.
Membership 1: Member, 0: Non-member
Age Age in years: 30 or younger, 31-55, 56 or older.
Income Combined annual income of person and spouse ($000’s).
Spend Amount spent total on purchases last year ($).
Questions
1. Explain the data type for each of the three variables: Gender, Age and Spend.
If the variable is categorical, also explain whether it is nominal or ordinal.
If the variable is numerical, also explain whether it is discrete or continuous.
[3 marks]
Questions 2 – 12, below, are based on the 800 customers.
2. (a) Use Excel’s Pivot Table function to create a table of the frequency distribution of
customers – broken down by Age (“ROWS”) and Experience (“COLUMNS”). Use the
“Tabular Form” report layout. Label your table as Table 1.
(b) Construct a column chart of the frequency distribution in relation to Age (along the
horizontal Axis) and Experience. Ensure that the chart is well-presented.
(c) Comment on the age distributions for the new and old customers, respectively. Justify
your answer with suitable values from the Pivot Table.
(d) Compare the number of new and old customers among each age group.
[9 marks]
3. Use Excel’s Pivot Table function to obtain the following tables, use the “Tabular Form”
report layout and label your tables accurately and informatively. Round off percentage
values to one decimal place.
(a) Table 2: the frequency distribution of Table 1, but with values shown as “% of
Grand Total”.
(b) Table 3: the frequency distribution of Table 1, but with values shown as “% of
Row Total”.
(c) Table 4: the frequency distribution of Table 1, but with values shown as “% of
Column Total”.
3
[6 marks]
4. For the following questions, justify your answers with suitable values from Tables 1-4.
For each question, state the table (eg, Table 2) that you use. No calculation is needed.
Quote percentage values to one decimal place.
(a) Which age group has the highest proportion of new customers? What is this
proportion?
(b) What proportion of customers are aged 31-55 and are old customers?
(c) How many old customers are aged 30 or younger?
(d) What proportion of old customers are aged 56 or older?
(e) What proportion of customers are new customers?
[5 marks]
5. (a) Create a Pivot Table of the mean Spend - broken down by Age (ROWS) and
Experience (COLUMNS). Use the “Tabular Form” report layout and label your table
accurately and informatively. Round off the spend values to the nearest dollar.
(b) Construct a column chart of mean Spent in relation to Age (along the horizontal Axis)
and Experience. Ensure that the chart is well-presented.
(c) Describe the overall relationship between the mean Spend and Experience. Does this
relationship hold true for each age group? Justify your answer with suitable values from
the Pivot Table.
[7 marks]
6. The marketing manager generated the following Pivot table (Table 6) using the data,
where the variable Income is categorised into three categories (Low: $10,000-$60,000,
Middle: $60,000-$110,000, High: $110,000-$160,000) and the variable Membership is
categorised into two categories (Non-member and Member).
Table 6
Using the “Suggested Approach” for pivot tables, analyse how customers’ spends are
related to their incomes (Low, Middle and High) and membership status. Quote relevant
values from the table. Comment on the cohort of customers, on whom should ElecCom
focus in the future promotions.
[11 marks]
7. In the Data worksheet, we wish to separate the Spend data into New Customer Spend and
Old Customer Spend.
Sort the data by Experience.
4
In cell I1, type the heading New Customer Spend; cell K1, Old Customer Spend.
Copy the relevant data under the appropriate heading.
Do not copy this table of separated data into your Word document.
Obtain the indicated summary measures in the following table (Table 7), for the
New Customer Spend values,
Old Customer Spend values.
Round off values to the whole numbers.
Table 7
Summary Statistics New Customer Spend Old Customer Spend
Count
Maximum
Minimum
Range
Mean
Median
Standard Deviation
CV (coefficient of variation)
Upper Quartile
Lower Quartile
IQR (interquartile range)
[5 marks]
8. Use Excel’s Pivot Table function to create the percentage frequency distribution tables
(Table 8 and Table 9) for the
(a) New Customer Spend values,
(b) Old Customer Spend values.
The class intervals (groups) should start at $10 and have a width of $150. Show
percentages correct to 2 decimal places. Use the “Tabular Form” report layout and label
your tables accurately and informatively. Copy these tables into your Word document
[4 marks]
9. Use the tables from question 8 to construct a percentage frequency histograms (Graph 1
and Graph 2) for the
(a) New Customer Spend values,
(b) Old Customer Spend values.
Ensure that the histograms are appropriately labelled.
[6 marks]
10. Refer to your results from questions 7, 8 and 9.
For the New Customer Spend distribution, describe the shape of the frequency
distribution and quote relevant numerical evidences for your answer along with the table
references.
[5 marks]
11. Refer to your results from questions 7, 8 and 9.
5
(a) The manager claimed that old customers tend to spend more at ElecCom. Do your
results from question 7 support the manager’s claim? Quote relevant numerical
evidences for your answer.
(b) Interpret in context each value of the measures used in part (a) for old customers
only.
(c) Which is the “best” central tendency measure to use when reporting the “average”
spend? Why?
[5 marks]
12. Refer to your results from questions 7, 8 and 9.
(a) Compare all variation measures obtained in question 7 for the amount spent between
new customer and old customers. Quote relevant numerical evidences for your
answer.
(b) Interpret in context each value of the measures used in part (a) for old customers
only.
(c) Comment on the “best” variability measure to use in this case and give the reason.
Which group of customers has the greater variability of Spend: New customers or
old customers?
[8 marks]
Presentation
In business, a high standard of presentation is usually required. In this unit, your submitted
work is expected to be legible and well laid out. Legibility is enhanced by appropriate font
size, borders, colour choice and labelling in graphs, as well as care in spelling, grammar and
punctuation. Marks may be deducted for poor presentation.
[2 marks]
Excel Tips
The Excel Notes document on Moodle also has information that you may need for this assignment.
Sorting Data
When sorting several of columns of data by one variable, first highlight the whole block of data (all
rows and columns of the data set). If you highlight just the column for the variable by which you are
sorting, then the values of this variable will be separated from the cases to which they belong.
Percentage frequency histograms
Use Excel’s “Column” Chart Type. To remove the gaps between the columns of a histogram,
right-click on any column and choose “Format Data Series”.
reduce the “Gap width” to “No Gap”.
Data Analysis Tools
If Data Analysis does not appear in the “Data” menu, consult Section E5 of the Excel Notes.
Word Tips
To capture a screen image “Paste” it into Word
To capture the image (eg, Excel page, internet page, photo, etc.) currently displayed on your screen,
use Windows’ Snipping Tool (available via the START button/All Programs/Accessories).
go to the desired location in your Word document. Hold the Ctrl key and press the letter “V”.
6
To trim (“Crop”) an image in Word
Click on the image.
From the ‘ribbon’ at the top of the screen, select Format/Size and click on the “Crop” icon:
Apply the “Crop” icon to the image.
To arrange graphs and pictures within a “Word” document
Graphs (eg, from Excel) and pictures can be more manageable if you insert a table into Word and
place your graphs and pictures (and text) into cells of the table.