Subject Name:Business Maths and Statistics Assessment:Project based Assessment
Course Name:Bachelor of Business, Bachelor & Diploma of Applied Commerce
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.
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.
Student Services wants to undertake a full analysis of the relationship between class attendance hours during the semester and the Accounting final exam mark.
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 suburb of your choice (notify teacher as soon as possible of your choice to ensure no duplications).
Task 1: Sales Sheet
Above pivot table and chart presents breakup of revenue for regions. Further, it is broken up by representatives and products also.
Above pivot table and chart presents breakup of revenue for regions. Further, it is broken up by representatives and products also. The difference from previous section is that numbers are presented as percentage of total revenue.
The above pivot tables present information the units sold and revenue is presented for various regions, categorized by representatives. Filter on column labels has been used to create separate tables for separate products.
The above pie charts present region-wise percentage of revenue for each of the three products.
The above pie charts present representative-wise percentage of revenue for each of the three regions.
The above data is useful in determining various factors, such as, what product is selling the maximum, which product is generating the maximum revenue, which region has maximum demand and of which product, which representative is performing well, etc. Such information can be further utilized for forecasting purposes, appraisal of representatives, marketing strategy etc.
Task 2: Weight of Tub Sheet
For calculating various probabilities, z score has been calculated using formula (x-Mean/SD). Since it is a normal distribution, z table has been used to find corresponding probabilities as follows:
Task 3: Hours of Attendance
It can be seen that the correlation between two variables, namely, hours of attendance and marks, is high and positive at 0.6534. This indicates that a unit increase in hours of attendance will lead to 0.65 unit increase in marks. Also, a unit decrease in hours of attendance will lead to 0.65 unit decrease in marks.
(b) The regression tool in excel was used:
The above indicates same value for R as correlation, that is, 0.653.
The regression equation is: y^ = 15.13 + 1.37 x1
The above equation has a constant of 15.13 which is also known as Y-intercept coefficient. It is the minimum value for y even when all x values are zero. It is the point where regression line crosses the vertical axis and is also known as ‘β0’ or ‘constant’. Also, x1 is greater than zero indicating that the relationship is positive such that x and y increase or decrease together.
The output also provides coefficients and other statistically significant information, such as p-values for each of the coefficients. These values help in determining whether the variable has statistically significant relationship with the dependent variable or not.
Each of the p-value entails a null hypothesis that the variable has no correlation with the dependent variable. The alternative hypothesis is that the variable has correlation with the dependent variable. In above, the p-value for both intercept (0.0013) and x variable (0.0000) is less than assumed significance level of 0.05, indicating that there is correlation between the two variables.
(c) The regression equation is: y^ = 15.13 + 1.37 x1
Now, if x = 44, y^ = 15.13 + 1.37*44 = 75.29.
Hence, a student who attended 44 hours can be predicted to get 75.29 marks.
(d) The regression equation is: y^ = 15.13 + 1.37 x1
Now, if x = 40, y^ = 15.13 + 1.37*40 = 69.82.
Hence, a student who attended 40 hours can be predicted to get 69.82 marks.
(e) From the above output, the ANOVA output helps to understand reliability of the regression model generated. The F value is 132.62 while Significance F is 0.000. Hence, it can be concluded that regression model is reliable. In other words, the above predictions through the regression model are also reliable.
(f) Some of the other factors that can impact marks are: number of hours studied, difficulty level of questions in the exam, and number of hours of sleep before the exam.
Task 4: Census
The selected region is Bondi Beach, NSW (State Suburb). The required graphic representations are:
|85 years and over||178||11659|
Median = 11659/2 = 5829.5. This will lie in group 30-34 years.
Q1 step 1 = 11659/4 = 2914.75 = This will lie in group 25-29 years.
Q1 step 2 = 25 + ((2914.75-2262)/1952)*4
Q1 = 26.3 years
Q3 step 1 = 3*(11659/4) = 8744.25 = This will lie in group 45-49 years.
Q3 step 2 = 45 + ((8744.25-8676)/684)*4
Q3 = 45.4 years
(c) As can be seen, the data for age is normally distributed: