FNS50217 – DIPLOMA OF ACCOUNTING
UNIT CODE: FNSINC602
UNIT NAME: INTERPRET AND USE FINANCIAL STATISTICS AND TOOLS
ASSESSMENT 1
Graphs, Tables & Sampling
INSTRUCTIONS FOR STUDENT:
Overview:
• Read the Assessment Task information below carefully
• This is an individual assessment
• Your assessment should be competed in a Report format not Questions Answer. Ensure you include relevant headings for each section.
• Include a footer with your name, student number, name of task and date
• You will need to complete all questions successfully to achieve competency in this assessment
• Assessments are to be submitted no later than the due date as per this unit guide
• Late submission without application and approval of an extension will forfeit your chance for a resubmission
• Ask your assessor if you are unsure about any aspect of the assessment. Note: your assessor may ask to see your practice exercises and advise these need to be completed prior to assistance being provided
Documents to submit:
• A Microsoft Word document with embedded excel sheets where calculations are required, correctly formatted with all tasks completed.
• The Excel data file with all calculation included for reference, if required.
• The file name should follow the following convention:
Unit Code – Assessment Title – First & Last Name Student I.D.
• Soft copy to be uploaded to VU Collaborate Dropbox
Assignment Background:
The data set called Environmental Policies.xlsx represents the responses collected from a questionnaire concerning the government’s environmental policies.
The information has been placed in the sheet labelled Data in the file Environmental
Policies.xlsx available on VU Collaborate
The data collected includes the following information:
• Age (years)
• Gender (Male, Female)
• State of Residence
• Number of Children
• Annual Salary ($)
• Opinion Rating of Government’s Environmental Policies
Observations for each of the 30 people who participated in the survey were recorded. The
‘Opinion’ variable is expressed numerically on a 1-5 scale. It is based on the following categories:
1: Strongly disagree
2: Disagree
3: Neutral
4: Agree
5: Strongly agree
These six pieces of information represent the variables. It is customary to include a row (row 1 in this case) that lists the variable names. These variable names should be concise but meaningful. Note that an index of the observation is always included in column A. If you are sorting on other variables, you can always sort on the index to get back to the original sort order.
For any project involving data analysis (almost all projects in the workplace these days!), the place to start is with data familiarisation, knowing what type of data you have, understanding the units of measure used, the context in which the data was collected, how the data is distributed, summary measures, simple graphs etc.
This process of data familiarisation and the tools that Excel offers to help with this, form the primary focus of this assignment. If you do this well, you will have developed a methodical approach that you can use to your advantage in the workplace.
When completing your assessment please ensure you display all data in one neatly formatted report produced using Microsoft Word. Remember when presenting any information you need to consider it from the readers perspective.
PART 1 – INTRODUCTION TO THE DATA
1.
a) State what type of data each of the variables included in the survey is ie, categorical (nominal or ordinal), numerical (continuous or discrete). Explain your reasoning in each case.
b) Do the results from this survey represent a sample or a population? Explain your reasoning.
c) Explain two methods that could have been used to collect this survey data.
d) Describe two methods of random sampling.
2. Is this data ‘cross-sectional’ or ‘time series’? Explain your answer.
3. Describe in non-technical terms, what a frequency distribution is. Use simple examples in your explanation.
PART 2 – PRESENTING THE DATA
4. It is important to gain an understanding of the breakdown of respondents across the various categories. The Pivot Tables AND Charts function in Excel, can facilitate this. This function can help construct summary tables, frequency distributions and draw relevant charts needed in analysing a data set.
Using the data given, for each of parts a – e below, construct a frequency distribution and relevant graph.
a) Breakdown of respondents by Gender
b) Breakdown of respondents by State
c) Breakdown of respondents by Opinion
d) Breakdown of respondents by Age, using the “Group” option in Pivot Tables to create class intervals, starting at 20 years with class intervals of 5 years.
e) Breakdown of respondents by Salary using the “Group” option in Pivot Tables to create class intervals, starting at $30,000 (accept default class interval width).
Report both raw count and percentages in your tables.
Be sure to use the graph type that is appropriate for the data type. When using the Pivot Chart function, always remember to use ‘tabular form report layout’ and edit all graphs to include appropriate headings and axis labels.
5. Provide a brief explanation of why it was necessary to employ the “Group” option in parts
(d) and (e) above
6. Based on the output you produced in part 4, write a sentence or two that describes the key findings of the exhibits (a)-(e).
7. For the numeric variables Age and Salary, use Excel to calculate the following summary measures and present in a table. You will need to present your answers in an appropriately labelled and formatted table. Do not include unnecessary statistics. (Note: Provide age values using one decimal place and salary rounded to the nearest $)
• Maximum
• Minimum
• Range
• Mean
• Median
• Standard deviation
• Q1
• Q3
• Interquartile Range
• Count
8. For the Salary variable only, interpret, in context, Quartile 1, Quartile 3 and the interquartile range (IQR). (Hint: A contextual interpretation is required).
9. Discuss the shape of the distribution of the Salary variable.
10. Use the VLOOKUP function and the following age categories: 0-34 years: Young 35-59: Middle-aged 60 and above: Elderly.
Using this information, begin by inserting a new column, G, in your data file. Label this column “Age Group”.
Now, use the VLOOKUP function to complete the information in this column.
11. Based on the information in column G, construct a contingency table (using a Pivot Table), cross-classifying ‘Age group’ and ‘Opinion’.
12. Based on the tabulation in question 11, how many respondents strongly agree with the Government’s environment policies and are young?
13. Is there any other Excel function that could be used to complete the Age Group column? State the function using any appropriate cell reference.
PART 3 – ANALYSING THE DATA
14. Refer to the data set and create a cross tabulation (using a Pivot Table) of ‘Gender’ versus ‘Opinion’. Report as percentages.
15. You have been asked to investigate the following question, “Do males and females tend to differ in their opinion about the environmental policy?” Please ensure that you use the suggested approach to pivot table analysis and that references are made to table/s when answering this question. (Hint: You may need more than one Pivot Table to assist in answering this question)