FIN10002 Financial Statistics - Assignment Help

Assessment 2: Major Assignment

For this assessment, you need to produce a report by generating responses to eight tasks presented below. Note the assessment will be submitted in two instalments.

For some of these sections, you will need to use Excel and Python to generate statistical output (statistical analyses and graphs). The report should be presented in the form of a business report to a senior manager who cannot be assumed to have any knowledge of statistical methods. Microsoft Word, Excel, Python, Presentation software (PowerPoint, Sway or similar) and Video software (e.g., PowerPoint can do both, Adobe Spark Video, Premier or similar) should be used to complete this assessment. Your statistical calculations should be carried out using Excel and Python only. You will submit the Excel file, a zipped file containing the Python files and a pdf version of the report as soft copies via the submission folder in Canvas.

Assessment details

You will need to download the Excel dataset ‘Online Sales in USA.xlsx' from Canvas. The data set contains online transactions from October 20 to end of September 2021. There are 143,194 transactions in this data set and ten (10) columns variables as follows:

1. Order Id
2. Order_Date
3. Qty_Ordered
4. Price
5. Total
6. Category
7. Payment Method
8. Gender
9. Age
10. Region

You will use this data set to generate responses to the following eight tasks which are contained in the two Parts.

Part A

Part A Weight 12%

This part of the assignment relates to the first 3 tasks.

Using the Excel file, ‘Online Sales in USA.xlsx' from Canvas to perform these tasks.

1.    Select a random sample

Select a random sample of size 120 transactions from the 143,194 transactions found in the Online Sales in USA.xlsx file. You will use this sample data to complete tasks 2 to 8.

2.    Descriptive statistics

Use data summary methods to describe the returns in your sample using nine

variables – items 2 to10 above. Do not perform the task on Order Id.

Use an appropriate graphical and summary statistical technique, chosen according to the type of variable (note that less appropriate/inappropriate techniques will receive fewer/no marks).

All nine (9) variables must have at least one table and graph produced by Excel. Why at least two table, as some variable can have a frequency table and a descriptive statistics table.

Also, for any four (4) must also have a table and graph generated by Python. They can be the same as the graphs generated in Excel. Thus, four variables will have at least, two tables and two graphs – one set from Excel and the other set from Python

Choose your techniques from:

Tabular Techniques: frequency tables and grouped frequency tables Summary Statistics: mode, median, mean, standard deviation, range, coefficient of variation and interquartile range

Graphical Techniques: pie chart, bar graph, histogram, frequency polygon.

(See topics 1 and 2)

Do not draw an ogive curve, stem plot, or a box plot in this assignment and do not draw 3-D graphs.

1. For a nominal or an ordinal variable draw a graph and present a frequency table in
2. For a ratio or an interval, variable draw a graph and a summary statistics table, including summary statistics appropriate to the type of distribution
3. Try to use variation in drawing graphs e.g., pie chart/bar chart or histogram/polygon.
4. Do not draw two different graphs for the same variable. You can draw the same type of graph for two variables.
5. Do not include any information that you will not include in your discussion such as
6. Display and describe one variable at a

3.    Dashboard

Construct a dashboard in Excel like the one below. Note yours will have different data as it is based upon your sample. However, you must have the same charts as below. Do not develop your own different combinations of variables. Note the slicers should work.

Required:

Submit a report summarizing the information for this task. The report will contain how you obtained your random sample and a summary of each variable. The sample will be contained in the appendix. A brief description of each variable and graphs and summary tables will be in the report.

This information will be used as part of your part B submission. Note the full structured report will be presented in Part B submission

You will submit the Excel file, python code and both a pdf version of the report.

Part B

Part B Weight 18%

You need to expand on your Part A submission by producing a report by generating responses to the additional four tasks presented below. For some of these sections, you will need to use Excel to generate statistical output (statistical analyses and graphs). The report should be presented in the form of a business report to a senior manager

 You will submit the Excel file, python code and both a pdf version of the report

4.    Confidence intervals

Estimate the following quantities, using 95% confidence intervals. Explain the meaning of your confidence intervals.

1. The average price for the Men’s Fashion only
2. The average age for customers from the Midwest only.

For this task produce the calculations in both Excel and Python for your data.

Compare both intervals with their respective true means by calculating the actual population mean from the full 143,194 transactions, and comparing the true population mean to the sample mean and confidence interval (note: it is not usual to do this, so you are asked to do this for the purpose of this assignment).

Your confidence interval should start with ‘We are 95% confident that…”. This section should take half a page or less.

NB: Please make sure you provide sufficient information in the appendix for your confidence interval calculations to be replicated, so they can be checked.

5.    Hypothesis testing

1. It is often felt that the customers in the South are more likely to place larger orders than their Northeast Thus, the average quantity ordered for South customers is more than the average quantity ordered for Northeast customers. Investigate this contention by carrying out an appropriate hypothesis test.
2. It is often felt that the average total spent per transaction would be different between the two specified genders. Test if there is a difference in average total spent for males and

In both cases assume equal variances and a significance level of 0.05.

Only report a non-technical explanation of your methodology and your findings in the main section of the report. The computations and output should be placed in an appendix, including the test statistic, p-value, and degrees of freedom. This should take half a page or less.

6.    Correlation and regression

In this section, you will investigate the relationship between age and total amount spent. Using these two variables develop a regression model to predict credit score from the estimated salary of the customer.

For this task produce the calculations in both Excel and Python for your data.

Make sure that you undertake a full regression analysis, with appropriate discussion and include:

1. a scatterplot and a brief discussion
2. an estimate of the linear regression model
3. the coefficients of correlation and determination
4. a test of the hypothesis that there is no linear relationship

between age and the total amount spent.

Ensure your scatterplot includes a line of best fit. Also, make sure you describe the relationship between the variables using R and R-square and interpret the slope, coefficients and the results of the hypothesis test. Use a significance level of 0.05. Need to submit both outputs.

7.    Conclusion

Provide a brief, concise summary of all your findings and briefly mention any limitations in your findings.

Make sure you do not give tables or graphs here.

8.    Video - Presentation of results

Provide a concise summary presentation in video form showing yourself and the results (via PowerPoint or similar software package). It should be concise summary of findings within 5 to 8 minutes maximum

Presentation for report

The report should be presented in the form of a business report to a senior manager who cannot be assumed to have any knowledge of statistical methods.

Make your report informative but concise and use a non-technical style. Do not just quote statistics or analysis results but explain what they mean. In general, do not include in the report formulae, calculations, definitions of statistical terms or discussions on how graphs are constructed. Where appropriate all these items should be included in the appendices.

It is important that the values which have been calculated are correctly analysed, discussed and interpreted, and that a written description of the main features of the tables and graphs that have been constructed is included. The emphasis in this assignment is on interpretation and analysis, not just the computation of statistics and construction of graphs. It will be assumed that all computations have been correctly performed and that graphs have been properly constructed. Nevertheless, marks will be deducted if these are inaccurate or incorrect.

The presentation is an important feature of a business report. The guide to the presentation that follows gives a general outline to report writing.

Executive Summary

1. Report only the highlights of the
2. Entice an Executive to read
3. Essentially a lively summary of the main
4. No longer than one page; this is not counted in the word count and must be on a separate page from the rest of the report

Introduction

1. State the purpose of the report e. what you will discuss in the report
2. Outline the contents of the Report
3. Provide a brief description of the methodology
4. Describe the source of the data and state its location in the
5. This should contain information about what we expect to read in the This should take about half a page.

Analysis

1. Contains a thorough yet non-technical description of all the findings (graphs and tables will be included only where they help this discussion).
2. Details the results that were highlighted in the Executive Summary
3. Do not include any calculations here but include appropriate graphs, results and tables which are needed to support your

Conclusion

1. Report the findings and results of your
2. Essentially an expansion of the executive summary written from the point of view that the Executive Summary has not been
3. End with a discussion of the limitations of your analysis (e.g. reference to sample size if small, or comment on the data if it is old).

Appendices

1. Must be referred to in the main body of the

2.    Must contain your selection of random numbers and related random data.

3. Include the raw data, charts and tables that are not essential, but support the ANALYSIS section.
4. Include your EXCEL output for descriptive statistics, confidence intervals, hypothesis testing and regression. Include Python code and output for tasks 2, 4 and 6
5. Include any other relevant

Please make sure the information in the appendix is sufficient for all calculations to be replicated, so they can be checked e.g., if you include the output for your confidence intervals, please show how this output was used to calculate the confidence intervals. Same for tasks 5 and 6 – show the output in the appendix Graphs must be in the Main body along with relevant tables and discussion. Graphs presented only in the appendices will not score any marks for graphs.

Keep the appendices to a moderate size. Your Excel and Python work confirmation will be checked in the Appendices. The emphasis in this assignment is on interpretation and analysis, not just the computation of statistics and construction of graphs. Make your report informative but concise and use a non-technical style. Do not just quote statistics or results of analyses but explain what they mean. In general, do not include formulae, calculations, definitions of statistical terms, or discussions on how graphs are constructed. Where appropriate these may be included in the appendices.

Submission details

This assessment will be submitted in softcopy only.

The softcopy will be submitted via Canvas in the Major Assignment submission. You are to submit both the report in pdf format, the excel file, your video and a zip file containing the python files.

Make sure that your student id and name is in the footer of the assignment.