This project will require students to apply statistical analysis and calculations to data and analyse the results of these calculations in a business setting.
Students must also submit an excel file with their calculations. This file must have formulas in the appropriate cells, and not data entered solutions. No marks will be awarded for written calculations.
Students are to include all parts of the question in their final report. Submit the Excel file in the Excel file upload section, submit the report in the report upload section. The report will be checked in Turnitin for plagiarism, please do not copy someone else’s work nor give your work to others. If plagiarism is detected both parties will be held accountable and appropriate marks will be deducted.
The assignment covers weeks 1 through to 10.
Structure of the assessment
This project will require students to apply statistical analysis and calculations to data and analyse the results of these calculations in a business setting. This assignment is to be presented as a report of no more than 8 pages A4 pages plus the appendices. The report should include all references and resources used in its compilation. All calculations for task 1, 2, 3 and 4 must be performed and presented using MS Excel and Word. The report shall include four sections to represent the four different tasks.
Task 1
Using the Excel data - ‘Sales’ sheet
1. Generate one table and one graph that provides a
eak up of all Sales Revenue by Region and Representative (in rows) and for all products (in the columns). The data presented in the table for each product
epresentative should be shown in dollars. A pivot table/chart is a great tool in Excel to do this. Make sure your graphs and tables look professional.
2. Generate one table that provides a
eak up of all Sales Revenue by Region and Representative (in rows) and for all products (in the columns). The data presented in the table for each product
epresentative should be shown in percentages of the grand total of sales revenue. Percentages should be to two decimal places.
3. Generate separate table for each of the 3 product categories. You will have table for Cricket Balls, a table for Stumps, and a table for Cricket Bats. Each table should include data for each representative within each region, detailing sales in units and revenue in dollars. Make sure your tables look professional.
4. Generate 3 pie charts, one for each of the product categories, detailing the sales for each region as a percentage of the total sales for each product category.
5. Generate 3 pie charts, one for each of the three regions detailing the sales for each representative as a percentage of the total region’s sales.
6. Comment on the data you have created. As the manager of this business, how could you use this data?
Task 2
Using the Excel data - ‘Weight of Tub’ sheet
You are the production manager for Yummy Yoghurt. Your production line produces 750g tubs of yoghurt for sale at supermarkets all over Australia.
Your company has a target that at least 95% of its yoghurt tubs will contain between 745g and 755g grams of yoghurt. Assume the distribution of the weight of yoghurt is normal.
1. Calculate the average and standard deviation of the tubs that have been measured. You should use excel formulas for this.
2. What is the probability that a tub will weigh between 745 and 755 grams?
3. What is the probability that a tub will weigh less than 740 or more than 760 grams?
4. What is the probability of a tub weighing less than 735 grams?
5. What is the probability of a tub weight over 765g grams?
6. Has the company meet their target? What are the consequences of not meeting this target? How can you use this data in your business? (approx. 250 words)
Task 3
Use the Excel Data ‘Hours of Attendance”
Student Services wants to undertake a full analysis of the relationship between class attendance hours during the semester and the Accounting final exam mark.
a) Undertake a full co
elation analysis of the relationship between between class attendance hours during the semester and the Accounting final exam mark
b) Develop the linear regression model of the relationship between the between class attendance hours during the semester and the Accounting final exam mark. Include in the report a full detailed analysis concerning the model that has been fitted to the data.
c) Predict the expected Accounting final exam mark of a student who has attended 44 hours of the 48 scheduled hours of classes this semester.
d) Predict the Accounting final exam mark if a student attended 40 hours.
e) Comment on the reliability of this predicted final exam mark (approx. 50 words)
f) Suggest 3 other factors that may influence the Accounting final exam mark. (approx. 100 words)
Task 4
Using the Census Data and Census Tables available on the Australian Bureau of Statistics abs.gov.au, select a Census Data, Quick Stats and the subu
of your choice (notify teacher as soon as possible of your choice to ensure no duplications).
a) Copy and paste the Frequency Tables for the following variables and Construct appropriate graphs for each of them.
• Marital Status
• Gender
• Age
• Employment – hours worked
• Highest Educational attainment
• Languages spoken other than English
) Using the Frequency Table for Grouped Age, locate the Age groups where Q1, Median and Q3 are located. Highlight the appropriate groups in the data table for each. Give an estimate of each of Q1, Median and Q3 and explain what each of them mean in relation to the Age of the sample.
c) What shape is the data for Age?
d) The data from the Australian Census of 2016 is now available from the Australian Bureau of Statistics. Determine 6 statistical snapshot facts that indicate some of major demographic changes in Australia in recent years. (approx. 400 words)