Report Introductory Data Analysis Using Excel 代写
UTS Business Statistics 26134
Report (Group Assignment)
SPRING 2017
Assessment Value: This assignment is worth 20%
It is be completed in: A GROUP OF 3, 4 or 5 STUDENTS
Due times and dates:
Due before: 6pm Thursday 12 th October 2017 (Week 11)
You can hand in the assignment earlier to one of your tutors or take until the
Thursday and submit to the assignment boxes located in Building 8. Full details are
located in Section 9 of this document.
This document contains the general instructions and assignment questions for the
assignment. Make sure to read this document very carefully.
It outlines all of the procedures for the submission.
Read this in its entirety before asking questions on UTSOnline.
Page 1 of 15
General Instructions
1. Overview
This section of the assignment document contains all the information about administrative
matters relating to your submission. The final section details the actual questions
themselves. Where applicable, a separate set of Excel data files will also be provided which
you will use to answer the Excel based questions. In some cases, you may be required to
input data yourselves (e.g., from a newspaper article) or locate publicly available data from
an online source. Additional questions not answered in these documents can be asked on
the UTSOnline Discussion Board (please search before posting a new question).
2. Role of the Assignment to Ensure Student Learning Objectives
The questions are designed to combine your theoretical and practical based knowledge of
Business Statistics and integrate this with developing your skills in using analytical software,
namely Microsoft Excel.
First, this assignment speaks to the assigned learning goal of Business Statistics in the
Bachelor of Business, which is to ensure that students “have well developed critical and
analytical skills and be able to access and interpret statistics and business information”.
The assignment considers the subject objectives (objectives 1 and 4 listed below) that
students are expected to complete successfully. These are:
1. Apply standard statistical tools in various business decision contexts within a
professionally responsible framework;
2. Apply appropriate quantitative analytical techniques to qualify, support, select and
evaluate data as information for business decision‐making;
3. Effectively interpret and communicate results of quantitative analyses for business
decision‐making;
4. Effectively use a computer‐based data analysis package (i.e. Excel) to analyse
data critically.
Finally, the assignment aims to aid students in their development of desirable the entire
range of desirable graduate attributes. Graduate attributes that will receive greatest
development relate to:
- Communication
- Teamwork
- Problem Solving
- Planning and Organising
- Technology
Page 2 of 15
3. Marking Criteria
Each part will ask you to consider one or more weeks of lecture topic materials. The due
date submission will follow at least a week after you have completed a lecture and a tutorial
on these particular topics.
Each part of the assignment will contain several questions. The expectation of each question
is that you are able to answer the managerial component clearly as well as demonstrate
your knowledge in Business Statistics.
The questions will consider your ability to justify your approach (e.g., why did you choose to
use the median as a measure of central tendency) and present some information that you
obtain using Excel (e.g., a scatter-plot) and reach a managerial conclusion about what you
have presented (e.g., should the manager launch the new product based on your analysis?).
The following criteria will be used to evaluate your assignment submission:
o Does it present a clear and well-constructed argument towards the use of this
tool, approach or measure?
o Does it present inputs into calculations correctly?
o Does it present a justification for the use of these inputs as opposed to
alternative inputs?
o Does it present output correctly and accurately?
o Is this output easy to understand and read?
o Does it reflect a correct interpretation/conclusion about the output that has
been generated?
o Does it avoid the use of unnecessary information and presents a concise
answer with a logical flow of ideas?
o Does it communicate an answer in a suitable manner, and is it free from
spelling and grammar mistakes?
4. Submission Layout / Requirements
There is a strict upper maximum page limit of 12 pages. Your submission should not
exceed this total. Nor do we think that you should use all 12 pages.
An additional page for references is available if you need it, but it can only contain the
reference list. We will assume that any statistical techniques or formulas you are using make
reference to the text book (Black et al.), lecture notes or some other standard Business
Statistics textbook (e.g., Berenson et al). Therefore, for this assignment only we will say
there is no need to include references to formulas or techniques. You should not, however,
simply paraphrase or directly quote passages that relate to theory or examples out of a
textbook without referencing – in such cases, a reference can be used. Be careful, however,
that you do not use paraphrasing or quotes from textbooks too much (even if you have used
a reference) as it may appear you do not understand what you are stating.
Unnecessary information and answers that appear to be “covering all bases” will be marked
down. Just as this would not be acceptable when submitting a report to the managing
director of a company, it will not be accepted in this assignment.
Students should not include copies of any data sets in the assignment in any format (unless
specifically asked for). In most cases, we have copies of all the necessary data – you do not
need to provide it to us a second time.
Page 3 of 15
ANDERSON (00001898); LENNON (09101940); EINSTEIN (14031879);
CURIE (07111867); MARLEY (06021945)
Each submission should use the layout specified to ensure homogeneity and fairness across
submissions. We also do this to familiarise you with a specific layout request. For instance,
your boss may ask you to produce a report in a certain style to allow integrating with a much
larger report – failure to do this would mean making your boss frustrated.
Your answer to each part should appear on separate pages. We would advise using Page-
Breaks (ctrl-enter or Insert > Page Break in Word) to help with this layout requirement.
In addition, student surnames and student numbers must be listed in the header on ALL
pages on the top left of the page, using the same format that is used at the top of THIS
PAGE. That is, list the surname in capitals followed by the student number in brackets.
Separate each group member by a semi colon. First names should not be included.
A cover page SHOULD NOT be included. If you include one then it will count towards you
page count resulting in a deduction of marks.
Layout should use the following specification for ALL pages:
[ ] Body text: Arial 11 point font or Times New Roman 12 point font
[ ] Readable size text in tables or graphs (any font size and type is acceptable for these)
[ ] No comment boxes or text boxes
[ ] 2.54cm margins on all sides (top, bottom, right and left) (the MS Word standard)
[ ] Portrait orientated text rather than landscape
[ ] No use of columns
[ ] Single-spaced
[ ] Spelling and grammar checked
[ ] Appropriately labelled and presented (e.g. size) figures and tables.
[ ] No colour printouts – greyscale or black and white only
[ ] Single sided printing only
[ ] If required, referencing using appropriate style (e.g., Harvard) - ask Librarian for help if
required.
[ ] Page numbers on the bottom right of all pages
[ ] An indication of which assignment part is being attempted, along with indication of where
each answer starts and ends. For example Q1.a, Q5.b.I, etc. There is no need to
include a copy of the question or any raw data (unless specifically requested).
[ ] Submission NOT in a plastic sleeve
[ ] Submission is stapled in the two places: the top left and bottom left (like a booklet)
[ ] Page limits (and page structure) followed
[ ] Header included as described
5. Marking Deductions
• Presentation Elements
Deductions of 10% for poor presentation can be made. Yes, if you do not do something like
label a table/chart or use a different font to the one specified you would lose 10%.
• Incorrect Student Names or Numbers
Your assignment layout should include the appropriate header with the surnames and
student numbers of each group member. Failure to include this header will result in an
automatic deduction of 10% for the whole group. This is on top of any previous deductions
made based on presentation.
Page 4 of 15
NOTE: Failure to include (e.g., a group member’s surname is presented but number is
missing) or having one of the details incorrect (e.g. one of the digits in your student number
is wrong) then the full 10% penalty will be applied.
• Late Submissions
In turn, you are expected to meet your deadline. Do not leave this assignment to the last
minute. As such, issues with computers are not a satisfactory excuse for failing to submit on
time. Instead, you are all encouraged to make backup/duplicates of your work including a
photocopy of your assignment before it is handed in.
Late submissions will be accepted by delivery to the Discipline of Marketing (CB08, Level
10) during office hours, but a deduction of 20% per day will occur. Assignments submitted
more than five working days will not be marked.
6. Special Consideration
Students anticipating having difficulties with their assignment due to illness or misadventure
are advised to complete an Application for Special Consideration form from UTS Student
Admin Unit. This may be necessary to account for issues of peer evaluation etc. This form
must be submitted to SAU, prior to the due date.
Irrespective of circumstances, you should still submit the assignment even if it is incomplete.
This will help us gauge your learning even if marking accommodations need to be made.
As this is a group submission, we are aware that several people are responsible for your
submission. The group should be working far enough ahead of the deadline to
accommodate the problems (or errors) of any one member. The failure of one member (for
whatever reason) does not constitute a good excuse for the group not submitting.
Extensions for assignments will not be granted even if you (or a member of your group) have
applied for special consideration. At the end of the semester, the subject coordinator will
make a review of grades and possible readjustment of grades will take place. This process
will be informed by your performance in other assessments.
7. Plagiarism
Plagiarism is considered intellectual theft and, hence, penalties are in accordance with its
seriousness. Plagiarism includes use of another persons’ work including authors (use
referencing where applicable) and other students. If you feel pressured to copy another
student's work then consider the penalties of simply delaying your submission relative to
being reprimanded for plagiarism. Reprimands for plagiarism can be severe and may affect
your standing within the university. Please note we check for plagiarism each semester and
catch at least one act of plagiarism.
There is to be only ONE submission per group. No two assignments should be the same. If
you lend your assignment out to someone else and they copy it, or you work together but
hand in a separate assignment, you have contributed to plagiarism and reprimanded. Just
because you were the one who lent the assignment and you may have not known the extent
to which they copied your work is NO EXCUSE – you are just as much to blame as the
person who copied your work. Therefore, we advise that you …
DO NOT LEND OUT YOUR SOLUTIONS TO ANOTHER PERSON
OUTSIDE OF YOUR GROUP!
Page 5 of 15
8. Assistance/Help with Completing the Assignment
Several resources are available to help you complete the assignment.
• UTSOnline: You can ask general questions about the assignment on the assignment
discussion board. Administrative matters can also be dealt with here. Please follow the rules
of the discussion board before posting. We will not be providing answers in this forum.
Instead, we will provide general advice and clarification of any ambiguities arising. DO NOT
put anything resembling a solution or comment on a solution, as these posts will be deleted.
DO NOT "fish" for answers, as these types of posts will be deleted. You may also be
reprimanded based on assisting others, which may be interpreted as plagiarism.
• Your textbook: This text book was chosen because of its ability to discuss materials in an
applied fashion. It also offers specific help on using Excel to do various tasks.
• Other textbooks: Do not feel limited to using the prescribed textbook. For instance, there are
plenty of other books on how to do something in Excel. Some of these texts are specifically
mentioned in the ‘other readings’ section of the subject outline.
• Google: If you do not know how to do something, there is tons of information on the Internet
that may be able to assist you. Remember to reference if appropriate.
• Online videos: A series of tailored videos for working with Excel using various techniques
useful to Business Statistic students have been produced by A/Prof Paul Burke and Dr David
Bond. See https://tinyurl.com/kckyecg
Topic Tiny URL What it demonstrates
Installing the
Analysis
Toolpak in Excel
https://tinyurl.com/lqe4y9k
This is a short demonstration of how to install the
Analysis Toolpak in Microsoft Excel. The
Toolpak provides access to additional data
analysis tools, which are extremely useful for
analysing large data sets.
Introductory
Data Analysis
Using Excel
https://tinyurl.com/hnswy64
This is a short demonstration of how to undertake
some data analysis in Microsoft Excel. We will
manually create some basic descriptive statistics
(including average, median, standard deviation,
minimum, maximum and count), as well as using
the Analysis Toolpak to generate the same (plus
more).
Sorting Data
Using Excel
https://tinyurl.com/nykb63v
This is a short demonstration of how to sort data
in Microsoft Excel. We'll look at single and
multiple level sorts, as well as how to use (and
drawbacks of) the quicksort button.
Filtering Data
Using Excel
https://tinyurl.com/lq5v4lj
This is a short demonstration of how to filter data
in Microsoft Excel. We'll look at different ways to
filter data including a range filter, a search filter as
well as using multiple filters
Creating
Scatterplots
using Excel
https://tinyurl.com/n4whb8x
This is a short demonstration of how to create
Scatterplots in Microsoft Excel. We'll look at how
to create them as well as some of the various
formatting options available.
Navigating Data
in Excel
https://tinyurl.com/lrbkml5
This is a short demonstration of how to navigate
data in Microsoft Excel. We'll look at how to
freeze rows, columns and panes, as well as the
zoom function.
Creating a Pivot
Table using
Excel
https://tinyurl.com/m3sd9jy
This is a short demonstration of how to create a
PivotTable in Microsoft Excel, as well as some of
the options available for data analysis.
Page 6 of 15
9. Where to Submit the Assignment
There is to be only ONE assignment submitted per group. Each group member DOES NOT
need to submit individually. Your work will be marked as a group only.
Assignments are to be submitted in the assignment boxes for Business Statistics. You have
the option to submit to your lecturer or tutor earlier if you would like.
The assignment boxes are located on Level 5 of CB08 (Dr Chau Chak Wing Building). When
coming out of the elevator lobby heading towards the student study pods, the assignments
boxes are on the left. The box will be marked with “UTS Business School, Marketing
Discipline Group, Business Statistics 26134”.
Assignments either accidentally or deliberately submitted to another location (e.g. the wrong
assignment box) will be deemed to have not been received and will not be marked. Make
sure to submit to the right location.
10. Reminder about Due Dates
You can submit your assignment to the assignment box at any time prior to the time and
date listed on the front of this document. Any benefit of an additional lecture or tutorial for
students is negligible, as all materials required for the assignment would have been covered
prior.
We would like to advise that students should introduce a buffer into their timeline. For
instance, you may like to think of the assignment due the week prior so that all group
members can meet at least a week before and fine-tune the final submission. Time
management is key in meeting your assessment requirements. You can also start your
assignment before you have found all your group members – you can then compare each
person’ interpretation of the question and settle on the most appropriate response.
11. How much is the assignment worth?
As per the subject outline, this will form 20% of your total grade. The other 75% will be
formulated based on your performance in the mid-semester examination and final
examination.
12. How much are the exams worth?
The mid-semester examination is worth 30% of your total grade.
The final examination is worth 50% of your total grade.
You do not have to pass every assessment to pass the subject overall.
13. Group Size
You will submit all parts of your assignment as a group submission, but any issues of
working in a group should be managed by yourselves.
Groups must consist of THREE, FOUR or FIVE people.
Page 7 of 15
You should note that NO special allowances will be made at ALL in marking your work
based on group size. All group members will receive the same mark for any one submission
regardless of contribution. You should provide adequate warning to a group member who is
not pulling their weight and let them know that they are not contributing equally.
If we receive an assignment as an individual or two persons, the individual/pair will receive
a 50% deduction on their assignment grade. Therefore, feel free to work on your own, but
you WILL NOT pass! Likewise, if we receive an assignment with six or more persons, the
group will receive a 50% deduction on their assignment grade.
You cannot complete the assignment by yourself.
14. Who can be in your group?
Your group may consist of ANY student enrolled in Business Statistics 26134 from any
tutorial (that is, you do not have to all be in the same tutorial group) or any day (that is, you
may be taking class on Tuesday and they made take class on Thursday).
15. Finding Group Members
Your tutor will NOT be assigning you to groups. It is up to you to form your own groups in
your own time. Consider asking around your tutorial and lecture for available people.
Groups can also be formed using the ‘Find Group Members’ discussion forum available on
UTS Online if you find your personal schedule makes it difficult to find group members. Do
not use any group email facilities to find group members.
In using this UTSOnline ‘Find Group Members’ discussion board facility, we would advise
noting your lecture day and time and perhaps tutorial time to make finding group members
easier. Simply start a new post (after checking out the other posts).
For example, the appropriate standard thread heading is recommended if a group is looking
for a new member to join an existing group:
WE REQUIRE 1 MORE MEMBER: Tue 11.30pm Lecture; 4pm Tutorial
Or, if you are a student looking to join a group, use:
I AM LOOKING FOR A GROUP: Thu 6pm Lecture; 7.30pm tutorial
With your contact details (e.g., student email) contained in the post.
Can we also request that you make a note to users on the discussion thread once a group
member has been found. “FOUND” in the reply subject heading would be sufficient. From
time to time, the online team may hide such completed posts to streamline this system.
Page 8 of 15
16. Avoiding Group Contribution Issues
You are expected to resolve any group problems you are having yourselves. It is no good
handing the assignment in and then saying that you had a group issue. Likewise, if the
group issue arises in the few evenings or weekend before the assignment it is due, then the
group’s poor time management implies the group itself is just as much to blame for an
individual group member being unavailable than simply that individual. The group should
approach the assignment consistently over the semester.
We ask that you monitor the contribution made by your other group members. This can be
done by recording attendance at meetings, actions agreed upon and keeping
correspondence in emails.
If the group member fails to contribute then you should confront the group member early on.
Give the person an opportunity to respond and recognise that their expectations may not
have been aligned with the rest of the group. Explain your expectations as a group. If the
issue still remains or cannot be resolved then indicate to the group member that their name
will not be included on the group assignment if their behaviour is not adjusted. Again, leaving
this decision until the week before the assignment is due is unreasonable given the
assignment is designed to be approached as a week-by-week exercise.
In terms of group tasks, be inclusive – calling a person with one hour’s notice or scheduling
meetings on a weekend when a person is playing sport and then turning around and saying
they didn’t turn up to group meetings is not fair. Organise your group to be inclusive so that
everyone is given the opportunity to contribute.
You should recognise that contribution does not simply constitute knowledge. A student may
simply not have the skills to be accurate in their responses or analysis. However, if they
make an effort to construct a response to the best of their ability, this is deemed a
contribution. On the other hand, simply turning up to meetings and providing no input
through silence is not contributing.
You should help each other and we recommend that you do not leave the responsibility of a
single question to a single individual. Let an individual manage the process (e.g., decides
when to do the analysis; collates the responses from each member) but not having a
mechanism to check each other’s contributions nor leaving time to do this is likely to end in
disaster. We have seen the two issues of time and responsibility ruin a project too many
times. Take note! Rather than allocate group members a single question, we would suggest
that each member attempts the question, a comparison is made of each attempt and that
you discuss any differences and why they occurred. This results in a self-checking
mechanism and you are more likely to generate a better answer (hence, reflected in your
marks) using this approach.
17. Peer Evaluation
Peer evaluation need only completed if: a) you feel someone has not made a satisfactory
contribution, but they have been adequate warnings and opportunities to do so; or, b) you
have been asked by the subject coordinator to do so. If you have been asked to do so and
do not respond, we will assume that you are happy with performance of all members’ of your
group including your own.
Page 9 of 15
Peer evaluation will be undertaken electronically as required. You will be asked to indicate
each person’s contribution to the group on items such as attendance and participation in
group discussions or meetings; contribution of ideas; quantity of work done; quality of work
done relative to skill level.
In serious cases, a poor peer evaluation may result in the downgrading of an individual’s
mark. This downgrading will be based on a number of factors including the group’s
comments, ratings, and students’ performance in other assessments as well as the student’s
own comments about the matter. No “upgrading” will occur for members that have
contributed more than others have. Please note, group issues arising at the last minute will
be given little consideration.
THIS IS THE END OF THE GENERAL INSTRUCTIONS SECTION OF THIS DOCUMENT
THE NEXT PAGE CONTAINS THE TASK YOU NEED TO COMPLETE
Page 10 of 15
Business Statistics 26134
Spring 2017
Group Assignment – The Task
The purpose of this assignment is to improve your understanding in choosing between and
using methods to assess the relationship between variables and make comparisons across
different segments. It will develop your skills in being able to critically think and enhance
your analytical skills.
Specifically, we want you to access the Excel database provided on UTSOnline that describes
the evaluation of 215 travellers who took a journey on Sydney Trains recently. Travellers
were interviewed about their most recent journey. Respondents were asked to provide details
about the journey details, provide some insight about their perceptions regarding aspects of
Sydney Trains’ service along with a number of characteristics describing the traveller and the
nature of their trip.
We want you to investigate use this database using the various statistical techniques you have
been exposed to in Business Statistics and answer several managerial questions.
You must complete this as a group of three, four or five students.
Each group must complete all FOUR questions. We strongly recommend you do not assign a
question to each group member to complete, but instead consider having an initial discussion,
then each member completing all four questions on their own, and then come together as a
group to find a common (superior) solution.
In answering each question, please consider the following:
• Explain which variables you used as inputs into your answers
• You should clearly show the various inputs to the calculations you used to construct
your answer and justification or assumptions made in some cases for why you chose
these as inputs (e.g., degrees of freedom; assumption).
• As you have access to a computer, you will be expected to use critical table values
that are accurate (i.e., not rounded). This selection of table should be based on your
knowledge about the population parameters, level of significance and degrees of
freedom inputs, which you should also state to justify your selection of table.
• Present any inputs and answers to two decimal places, but round your calculations
only at the end though to avoid round error.
Page 11 of 15
Report Introductory Data Analysis Using Excel 代写
Question 1) CLEANLINESS IN TRAVEL: A CASE OF TWO JOURNEYS
Management have suspected that the perceived cleanliness of trains and facilities experienced by
passengers differs depending on whether the travel takes place on an intercity train as compared to
a suburban style train.
Prepare a report to management using the concept confidence intervals that examines the average
perceived cleanliness of trains experienced on the two different types of trains. Are their suspicions
correct or incorrect?
Prepare a second component to the report to examine differences in perceived cleanliness of
facilities. Using the concept of confidence intervals examine whether the average cleanliness of
facilities for passengers travelling on an intercity train differ to that of passengers travelling on a
suburban train.
If management had resources to address cleanliness in terms of intercity or suburban travel, what
would you recommend? You may like to think about various reasons behind your findings
particularly with respect to measurement issues (e.g., are there issues with respect to what location
the individual is evaluating when responding to the questions?).
Your report should include a table that clearly reports the summary statistics (mean, standard
deviation, number of observations) associated with the two types of travel for each of the two
dimensions of cleanliness along with your confidence interval outcomes.
A graphical representation of the confidence intervals may also be useful in reporting your findings.
Your report should obviously also provide details about the calculations behind the confidence
intervals.
Page 12 of 15
Question 2) SATISFIED TRAVELLERS? LENGTH OF JOURNEY and COST OF TRAVEL
Management are interested in how perceived satisfaction among passengers may vary as a function
of the length of the journey and travel costs.
First, confirm for management that cost of the journey and length of journey has a linear
relationship as expected. You should do so using a suitable graphical and numerical method. What
does your analysis indicate with respect to costs encountered by those on longer journeys?
Second, can you examine the relationship between length of journey and satisfaction levels. Use the
concept of a correlation coefficient to do so. What does your analysis suggest?
Third, examine the relationship between length of journey and satisfaction levels, this time using a
suitable graphical method. Does your analysis offer further insight regarding satisfaction and length
of journey as compared to insight based on the correlation coefficient?
Finally, summarise your findings in a way that management can understand.
Page 13 of 15
Question 3) TRAVEL TIME AND PURPOSE: THE INFLEXIBLE TRAVELLER
Management is interested in whether the time that passengers commence their travel depends on
their reason for travel. They suspect that those who have more flexibility over their travel times
(e.g., not travelling for work or education purposes) may be more likely to commence travel in off‐
peak rather than peak periods. That is, they expect that people who are visiting friends, places or
travelling for other reasons have flexibility to conduct their travel in off‐peak periods. They expect
that those travelling for work or education purposes do not have this level of flexibility.
Can you perform contingency analysis (see Chapter 4 and 12 of the text) to examine whether there is
evidence to support this outcome.
To facilitate the analysis, you should consider just two outcomes for the start time of the journey:
namely one that focuses on travel commencing in a peak period relative to travel commencing in an
off peak period. The reason for visit does not require transformation.
Your analysis should present three tables including a joint frequency table, an expected frequency
table and a table of inputs into the chi‐square calculation similar to that on and around Slide 29 in
Lecture 3. You should also present details of how you obtained your critical value and a clear
conclusion about the evidence for independence between the two variables of interest.
Finally, you should report to management some examples comparing suitable marginal and
conditional probabilities to demonstrate the nature of the relationship that you have found to exist
in the data. Use these illustrative examples to support or refute the expectations of management in
a way that is consistent with the statistical conclusion based on the contingency analysis.
Page 14 of 15
Question 4) HELPING THE TRAVELLER
Management is interested in the average performance of their staff with respect to customer
service. One indication of this is the ratings that passengers have provided about the helpfulness of
staff they have encountered.
Examine this data to determine if the average rating provided regarding the helpfulness of staff
significantly exceeds a benchmark of 5 out of 7.
You should answer the managerial question, but show from a statistical point of view the inputs,
assumptions and statistics used to reach your conclusion.
THIS IS THE END OF THE DOCUMENT
GOOD LUCK WITH YOUR ASSIGNMENTS
Page 15 of 15
