MCD2080 Business Statistics Assignment 代写

MCD2080 Business Statistics Assignment

Descriptive Statistics (Using Excel)

This assignment is marked out of 63 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. You must include a completed Cover Sheet, which has been signed and

dated. 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.

A penalty of up to 5% of the total mark allocated to this assignment may apply for each day an

assignment is late (unless a staff member grants an extension of time).

Data and Output

The file MCD2080_A1_T117.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

EasyBuy is an online retailer of large varieties of many categories of item: sports, fashion, electronic,

etc. The worksheet Data in MCD2080_A1_T117.xlsx contains data on the 200 customers who

responded to a promotional one-day offer emailed to all customers on the EasyBuy database. The

manager wants to analyse the data for customer buying-patterns which will guide future promotions.

The data for each customer includes

AmountSpent The total amount ($) spent on the promotion day.

Membership EasyBuy membership status: 1=member, 0=non-member.

Region Shipping region: 1=central, 2=northern, 3=southern.

Highitem The category of the highest-priced item purchased:

1=electronic, 2=fashion, 3=sports and 4=other goods.

Items The number of items purchased.

Questions

1. Explain the data type for each of the following variables.

(a) Membership (b) Items

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.

[4 marks]

Questions 2 – 10, below, are based on the 200 customers who responded to the one-day offer.

2. Using Excel’s Pivot Table function, obtain the following tables and copy them into your Word

document. Use the “Tabular Form” report layout and label your tables accurately and

informatively. Round off percentage values to one decimal place.

(a) Table 1: frequency distribution of the customers - broken down by Membership

(“ROWS”) and Region (“COLUMNS”).

(b) Table 2: the frequency distribution of Table 1, but with values shown as “% of Grand

Total”.

(c) Table 3: the frequency distribution of Table 1, but with values shown as “% of Row

Total”.

(d) Table 4: the frequency distribution of Table 1, but with values shown as “% of Column

Total”.

[6 marks]

3. For the following questions, where appropriate, 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) How many customers in the southern region are EasyBuy members?

(b) What proportion of customers are EasyBuy members?

(c) What proportion of customers are from the central region and are not EasyBuy members?

(d) What proportion of EasyBuy members are from the northern region?

(e) Does the proportion of members exceed the proportion of non-members in all regions?

Justify your answer.

(f) Which region has the highest number of members? What is this number?

(g) Which region has the highest proportion of members? What is this proportion?

[9 marks]

4. In the Data worksheet, we wish to separate the AmountSpent data into members’ and non-

members’ amounts.

3

Sort the data by Membership.

In cell G1, type the heading Non-member AmountSpent; cell I1, Member AmountSpent.

Copy the relevant data under the appropriate heading.

Do not copy this table of separated data into your Word document.

Use Excel’s Pivot Table function to create a percentage frequency distribution table for

the Non-member AmountSpent values.

the Member AmountSpent values.

The class intervals (groups) should start at $20 and have a width of $30. 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]

5. Use the tables from question 4 to construct a percentage frequency histogram for the

(a) Non-member AmountSpent values.

(b) Member AmountSpent values.

Ensure that the histograms are appropriately labelled.

[6 marks]

6. Obtain the indicated summary measures in the following table, for

Non-member AmountSpent,

Member AmountSpent.

Summary Measures Non-members

AmountSpent

Members

AmountSpent

Mean

Median

Mode

Standard Deviation

CV (Coefficient of Variation)

Range

Minimum

Maximum

Sum

Count

Lower Quartile

Upper Quartile

IQR (interquartile range)

Modal class

[8 marks]

7. Refer to your results from questions 5 and 6.

For the Non-member AmountSpent distribution,

(a) describe the shape of the frequency distribution and provide evidence for your answer.

(b) interpret this shape in context for EasyBuy.

[4 marks]

8. (a) Briefly discuss the usefulness to EasyBuy management of each of the measures of central

tendency in question 6, as well as the usefulness of the modal class. Justify your answers.

(b) Which group of customers has the greater level of contribution to EasyBuy’s revenue?

Discuss. Justify your answer with suitable values of the measures in (a).

[7 marks]

4

9. (a) Interpret in context each of the measures of variability in question 6.

(b) Which group of customers has the greater variability of AmountSpent: Non-members or

Members? Discuss.

[6 marks]

10. (a) Create a Pivot Table of the mean AmountSpent - broken down by Membership (Row) and

Region (Column). Use the “Tabular Form” report layout and label your table accurately and

informatively.

(b) Construct a column chart of mean AmountSpent in relation to Membership and Region.

Ensure that the chart is well-presented.

(c) Which cohort of customers is most valuable to EasyBuy?

Justify your answer with suitable values from the Pivot Table.

[7 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”.

To trim (“Crop”) an image in Word

Click on the image.

MCD2080 Business Statistics Assignment 代写

MCD2080 Business Statistics Assignment 代写

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.

MCD2080 Business Statistics Assignment 代写