FIN10002 Financial Statistics - Assignment 2 - Major Assignment PDF

Preview

Summary

Download FIN10002 Financial Statistics - Assignment 2 - Major Assignment PDF

Description

FIN10002: Financial Statistics Assignment 2: Major Assignment

Debora Aguilar de Oliveira - 102932273 Swinburne University Online

Due Date: Monday 1st June 2020

Word Count: 1,645 words

Executive Summary This report aims to analyse and interpret the sales data of the company Office Supplies Pty Ltd, throughout 8 (eight) given variables: Order Priority, Order Quantity, Sales, Ship Mode, Shipping Cost, Region, Consumer Segment and Days to Ship. The method used included selecting a random sample of 60 orders, from a population of 2002 total orders. Descriptive Statistics were used to analyse the data, along with Hypothesis Testing and Correlation and Regression. During the examination it was discovered that the majority of the orders (60%) have Low, Medium and Not Specified priority levels, while Critical and High priority levels account for 40% of the total orders. Customers tend to order from 14 to 23 items per transaction (26.67%), and purchases containing over 44 items are the least common (15%). It is important to note that this data alone does not mean much as there is no strong indication that the sales amount increases as the quantity of items ordered increases. The sales data assessment unveils that 68.97% of the orders have their total amount of $800 or less, and the average amount spent per transaction is $1,071.13. When discussing shipping method, the cost paid by the company to dispatch parcels through Delivery Truck ($38.99) is nearly 5 (five) times more expensive than the cheapest method, but luckily customers mainly prefer Regular Air (78.33%) as their delivery method. The company should continue to engage with the Eastern Province, since they are responsible for 80% of the purchases and 93.67% of the sales total ($89,985.12). On the other hand, it is important to understand what factors are preventing the Western Province from strengthening their connection with Office Supplies. As for the customer segment, the company is recommended to explore the Home Office segment, as only 11.43% of the total sales come from them ($10,981.81). To offer some comparison figures, customers from the Corporate segment account for 41.18% ($39,555.06) of the total sales. From the day the order is placed until it is shipped, Office Supplies takes and average of 1.73 days to complete the process, with 93.33% of the orders being shipped in 0 to 2 days. Overall, Office Supplies displays an efficient process when it comes to Sales, resulting in positive outcomes. There are areas that can be improved though, and the company should be taking them into consideration to maximize the revenue and reduce operational costs.

1

Table of Contents Executive Summary ………………………………………………………………………………………………………… 1 Table of Contents ……………………………………………………………………………………………………………. 2 1. Introduction ……………………………………………………………………………………………………………... 3 2. Analysis ………………………………………………………………………………………………………………..…… 3 2.1.

Descriptive Statistic ………………………………………………………………………………………. 3

2.1.1. Order Priority ……………………………………………………………………………………….… 3 2.1.2. Order Quantity ……………………………………………………………………………………..… 3 2.1.3. Sales ………………………………………………………………………………………………………… 4 2.1.4. Shipping Mode ………………………………………………………………………………………… 5 2.1.5. Shipping Cost ………………………………………………………………………………………….. 5 2.1.6. Region ……………………………………………………………………………………………………. 6 2.1.7. Consumer Segment ………………………………………………………………………………... 7 2.1.8. Days to Ship ………………………………………………………………………………………….… 8 2.2.

Confidence Intervals …………………………………………………………………………………….. 9

2.3.

Hypothesis Testing ……………………………………………………………………………………….. 9

2.3.1. Hypothesis 1 …………………………………………………………………………………………… 9 2.3.2. Hypothesis 2 …………………………………………………………………………………………… 9 2.4.

Correlation and Regression …………………………………………………………………………… 10

2.4.1. Scatterplot Graph …………………………………………………………………………………… 10 2.4.2. Linear Regression Model ………………………………………………………………………… 10 2.4.3. Coefficients of Correlation and Determination …………………………………….… 10 2.4.4. Hypothesis 3 …………………………………………………………………………………………… 11 3. Conclusion ……………………………………………………………………………………………………………….. 11 Appendices …………………………………………………………………………………………………………………… 12 Appendix A: Random Sample ………………………………………………………………………………….. 12 Appendix B: Descriptive Statistics …………………………………………………………………………… 14 Appendix C: Confidence Intervals ………………………………………………………………………….... 18 Appendix D: Hypothesis Testing ………………………………………………………………………………. 20 Appendix E: Correlation and Regression ………………………………………………………………….. 22

2

1. Introduction This report presents the results of analysed sales data from Office Supplies Pty Ltd, considering the following 8 (eight) variables: Order Priority, Order Quantity, Sales, Ship Mode, Shipping Cost, Region, Consumer Segment and Days to Ship. From a sales report containing 2002 orders, a random sample of 60 orders was selected to be investigated in this study. The sample can be found on Appendix A.

2. Analysis 2.1.

Descriptive Statistic

Descriptive statistics is one of the two general categories of business statistics, and involves the procedures and techniques designed to describe data, such as charts, graphs, and numerical measures.

2.1.1. Order Priority The order system of Office Supplies prioritizes orders as Not Specified, Low, Medium, High and Critical. Orders with Critical priority level represent 21.67% of the total orders, while High priority orders account for 18.33% of the total. Orders with Low, Medium and Not Specified priority levels combined constitute 60% of the total orders.

Figure 2. Order Priority Bar Chart

2.1.2. Order Quantity The average of items per order is 25.97, while 39 is the items per order that occurs the most. The standard deviation of 14.32 indicates that there are more values farther from the mean, therefore the distribution is more spread out. 3

Orders with a total of items between 14 and 23 are the most common (26.67%), followed by orders with total of items between 04 and 13 items (21.67%). Orders containing over 44 items seem to be the less common (15%).

Figure 7. Order Quantity Histogram

2.1.3. Sales According to the data sample, 68.97% of the orders have their total amount of $800 or less, and the average amount spent per transaction is $1,071.13. The mode is $59.76, having appeared twice on the sample. The standard deviation is $1,779.96 and indicates that the distribution is also spread out – meaning that the values are not very close to the mean. The Interquartile Range is $1,144.33 and can be explained by this being the amount that it is the in middle 50% of the sales transactions. Note: Due to the skewed data, outliers were removed without impacting on the overall data.

Figure 12. Sales Line Graph

4

2.1.4. Ship Mode Office Supplies has 3 different shipping methods: Delivery Truck, Express Air and Regular Air. The majority of the customers prefer Regular Air (78.33%) as their delivery method. Express Air (13.33%) comes in second and Delivery Truck (8.33%) is the least preferred approach. Considering that Express Air ($7.96) is cheaper than Regular Air ($9.05), the company is encouraged to prioritize the first shipping method over the second. Delivery Truck ($38.99) should be avoided as it is nearly 5 (five) times more expensive than the cheapest method. Data is presented on Appendix B – Figure 14. Ship Mode vs Average Shipping Cost Table.

Figure 15. Ship Mode Pie Chart

2.1.5. Shipping Cost The data gathered indicates that shipping cost can vary greatly, from $0.50 to $60 per transaction. The standard deviation of 13.51 confirms this affirmation, as it means that the data is stretched and does not concentrate near the mean. Even though the cost is not steady, nearly 72% of the transactions costed Office Supplies under $10 to be shipped out, which is a positive figure. The average shipping cost is $11.40, and the most common shipping cost value is $4.50.

5

Figure 20. Shipping Cost Bar Chart

2.1.6. Region A simple analysis of the geographic region where Office Supplies’ sales come from indicates that the Eastern Province brings 80% of sales against only 20% from the Western Province. Not surprisingly, the Eastern Province is responsible for 93.67% of the total sales – the equivalent to $89,985.12. Consequently, the 20% transactions from the Western Province only account for $6,077.55, or 6.33% of the total.

Figure 23. Region Pie Chart

6

Figure 24. Total Sales Per Region Pie Chart

2.1.7. Customer Segment Office Supplies has 4 types of customers: Consumer, Corporate, Home Office and Small Business. Corporate represents 38.33% of the total transactions, followed by Small Business (25%), Consumer (21.67%) and Home Office (15%). The data analysis shows that the trend is also true when it refers to how much money these customers bring to the company, where Corporate remains the major customer with $39,555.06 (41.18%) and Home Office stays last with $10,981.81 (11.43%).

Figure 27. Customer Segment Bar Chart

7

Figure 28. Total Sales Per Customer Segment Pie Chart

2.1.8. Days to Ship From the day the order is placed until it is shipped, Office Supplies takes and average of 1.73 days to complete the process. The low standard deviation of 1.21 confirms that there is no great discrepancy and the data is close to the mean of 1.73. Additionally, the mode indicates that the majority of the orders are shipped in 2 days, once again reassuring the above statement.

Figure 33. Days to Ship Pie Chart

8

2.2.

Confidence Intervals

With 95% of confidence, the study reveals that the average sales for the entire population falls between $1,567.94 and $1,865.44, having this data confirmed by the average sales for home office customers only returning a lower level of $1,330.29 and an upper level of $1,839.87. Concerning the average shipping costs, we are also 95% confident that the average amounts for the entire population can be found between $11.69 and $13.21. The data for the shipping cost display a slightly different window though, having the lower levels set at $7.91 and $14.89. This minor discrepancy could be explained by the highest shipping cost amount of the sample being $60, while of the population it is $143.71. Calculation is presented on Appendix C – Confidence Intervals – Figures 34 to 41.

2.3.

Hypothesis Testing

Hypothesis testing are the procedures used to accept or reject statistical hypotheses. All calculations are presented on Appendix D – Hypothesis Testing – Figures 42 to 47.

2.3.1. Hypothesis 1 The first hypothesis testing refers to the claims that order priority of CRITICAL would have higher shipping costs than an order of LOW priority. The null hypothesis tested formulated is H0 : µC - µL ≤ 0, and the alternative hypothesis is Ha : µC - µL > 0. The result of a one tailed test has returned a Z value of -0.727777458, while the critical value of the test is 1.644853627. Since the Z value is smaller than the critical value, we fail to reject the null hypothesis, meaning that there is no evidence that orders of CRITICAL priority have higher shipping costs than orders of LOW priority.

2.3.2. Hypothesis 2 The second hypothesis testing refers to the claims that the average sales order in dollars differs from Eastern provinces (E) to Western provinces (W). The null hypothesis tested formulated is H0 : µE - µW = 0, and the alternative hypothesis is Ha : µE - µW ≠ 0. The result of a one tailed test has returned a Z value of 1.442682605, while the critical value of the test is 1.644853627. Since the Z value is larger than the critical value, we reject the null hypothesis, meaning that there is evidence that the average sales orders, in dollars, shipped to EASTERN region differ from the average sales orders shipped to WESTERN region.

2.4.

Correlation and Regression 9

Using 2 variables (sales and order quantity), a regression model has been developed to predict average sales in dollars from order quantity. The correlation relationship between sales in dollars and order quantity has also been investigated. The significance level used is 0.05. Calculation is presented on Appendix E – Correlation and Regression – Figures 48 to 53.

2.4.1. Scatterplot Graph The scatterplot graph suggests a positive, weak and linear relationship between sales and order quantity. There is no strong indication that the sales amount increases as the quantity of items ordered increases. There are also few outliers, related to the data fairly off the line.

Figure 48: Sales and Order Quantity Scatterplot

2.4.2. Linear Regression Model Linear Regression is a method used to model a relationship between two sets of variables, with the result being used to make predictions about data. The result is given equation y’= a + bx. In saying that, the analysis data presents us with the following equation y’ = 24.074 + 0.001, while R² = 0.0799, indicating that there is either none or a weak relationship between Order Quantity and Sales.

2.4.3. Coefficients of Correlation and Determination The Coefficient of Correlation (R) and Coefficient of Determination (R²) intend to measure how strong the relationship between two variables are, classifying them in none, weak, moderate and strong.

10

Aiming to explore further the relationship between Order Quantity and Sales, the Coefficient of Determination (R²) has been calculated and returned a result of 0.0799, or 7.99%, and demonstrates a weak relationship.

2.4.4. Hypothesis 3 The hypothesis testing 3 refers to the allegation that there is no linear relationship between sales and order quantity for an order. The null hypothesis tested formulated is H0 : µS - µO = 0, and the alternative hypothesis is Ha : µS - µO ≠ 0. The result of a two tailed test has returned a Z value of -3.565183725, while the critical value of the test is 1.95996398. Since the Z value is larger than the critical value, the null hypothesis is rejected, confirming no linear relationship between sales and order quantity for an order.

3. Conclusion Based on a sample of 60 orders randomly extracted from a population of 2002 orders, it is possible to confirm with 95% certainty that the average sales varies from $1,567.94 to $1,865.44. As for the average shipping costs, the average amount stays between $11.69 and $13.21. While there is no evidence that orders of critical priority are responsible for higher shipping costs, the company should select a cost-efficient shipping method for its own benefit. The dispatching of orders through delivery trucks must be revised, due to its excessive high cost and very low demand by customers. The average sales orders shipped to the Eastern and Western regions are different, and this should be investigated further. Only 20% of the transactions come from the Western province, enforcing the need of the company to strengthen their connection with that region. In terms of customer segment, Office Supplies is recommended to invest in gaining market into the Home Office segment, as only 11.43% of the total sales come from them ($10,981.81).

11

Appendices

Appendix A. Random Sample The table below displays the sample of 60 orders randomly selected from a population of 2002 orders from Office Supplies Pty Ltd database.

Region

Customer Segment

$18.46

OFFICE SUPPLIES PTY LTD Shipping Ship Mode Cost Regular Air $0.50

Western Province

Consumer

$54.30

Regular Air

$2.03

Eastern Province

Consumer

2

$1,429.09

Regular Air

$1.25

Western Province

Small Business

2

$6.92

Eastern Province

Small Business

2

$5.45

Eastern Province

Corporate

2

Eastern Province

Corporate

1

Eastern Province

Corporate

2

Eastern Province

Consumer

2

Eastern Province

Corporate

2

Eastern Province

Consumer

2

9829

Order Priority Critical

Order Quantity 4

21894

Critical

6

56006

Critical

20

30593

Critical

20

$97.65

Express Air

23943

Critical

24

$376.53

Regular Air

40962

Critical

28

$1,553.66

Regular Air

$0.99

40870

Critical

28

$146.69

Regular Air

$5.32

45794

Critical

28

$1,208.35

Regular Air

$10.25

6432

Critical

30

$311.08

Regular Air

$4.50

9824

Critical

39

$1,939.66

Express Air

$4.50

Order ID

Sales

Days to Ship 2

21414

Critical

46

$3,644.60

Regular Air

$35.00

Eastern Province

Consumer

2

6086

Critical

48

$1,734.72

Regular Air

$19.99

Eastern Province

Corporate

2

16160

Critical

50

$514.86

Regular Air

$4.50

Eastern Province

Home Office

1

14884

High

6

$473.99

Regular Air

$4.90

Eastern Province

Corporate

2

51557

High

14

$37.90

Regular Air

$2.40

Western Province

Corporate

0

20102

High

15

$333.30

Regular Air

$1.99

Eastern Province

Small Business

1

47492

High

22

$649.71

Regular Air

$8.65

Eastern Province

Corporate

2

21927

High

23

$8,225.24

Delivery Truck

$30.00

Eastern Province

Home Office

1

30081

High

26

$213.49

Regular Air

$8.94

Eastern Province

Home Office

2

326

High

34

$218.27

Regular Air

$4.92

Eastern Province

Consumer

1

20743

High

39

$231.21

Express Air

$8.16

Western Province

Consumer

1

19616

High

42

$116.94

Express Air

$1.25

Eastern Province

Corporate

1

48486

High

42

$271.14

Regular Air

$7.91

Western Province

Cor...