Analyzing Business Data with Statistical Tools

This assignment delves into analyzing business data through various statistical techniques. It involves performing t-tests to compare the prices of orders with different priority levels and regressing sales against the number of orders placed. The analysis aims to understand relationships between variables like order quantity and sales, shedding light on factors influencing sales performance. Additionally, the assignment examines the impact of order priority on pricing strategies.

Running Head: FINANCIAL ANALYSIS
Financial Analysis
Name of the Student
Name of the University
Author Note

1FINANCIAL ANALYSIS
Executive Summary
To conduct this research, data has been collected and supplied by ‘Hardware and Garden
Supplies’. From the data set, 60 random samples were selected for this research. Descriptive
measures of all the 8 variables included in the dataset have been calculated. The confidence
interval of the sales amount and shipping costs will be calculated. Tests will be conducted to
show whether the price of critical orders is high that the price of low priority orders. Test will
also be done to check the difference in the sales between the Eastern and the Western stated.
Finally, regression will be conducted to check the effect of the number of orders on the sales
amount.

2FINANCIAL ANALYSIS
Table of Contents
1.0 Introduction................................................................................................................................3
2.0 Analysis.....................................................................................................................................3
2.1 Random Number Selection....................................................................................................3
2.2 Descriptive Statistics.............................................................................................................3
2.3 Confidence Intervals..............................................................................................................8
2.4 Hypothesis Testing................................................................................................................9
2.5 Correlation and Regression....................................................................................................9
3.0 Conclusion...............................................................................................................................10
Appendices....................................................................................................................................11

3FINANCIAL ANALYSIS
1.0 Introduction
The dataset on the office supply sales of the company ‘Hardware and Garden Supplies’
consists of information about 2002 orders on nine of the variables – Order ID, Order Priority,
Order Quantity, Sales amount, Shipment mode, Shipping Cost, Region, Consumer segment and
Days to ship. From the 2002 orders, 60 samples were selected randomly. Descriptive statistics
such as frequency distribution tables of nominal and ordinal variables and for interval variables,
necessary summary statistics will be mentioned along with suitable graphs. The range within
which the average sales amount and shipping costs will lie will also be provided. Test will also
be made to evaluate whether the shipping cost is higher in orders with critical priority. The
difference between the average sales of the orders in eastern and western states has also to be
tested. The test will be done using independent sample t-test. In the end, the relationship between
the order quantity and sales amount has to be evaluated using regression analysis.
2.0 Analysis
2.1 Random Number Selection
The dataset contains 2002 orders. It will be extremely laborious to run analysis on all the
2002 orders. Thus, 60 orders have been selected from the 2002 orders to make the analysis
easier. These orders have been selected randomly using uniform random number generation in
MS EXCEL. All the analysis will be done on these selected 60 samples. The samples were
selected randomly as the estimates obtained with this method of selection will represent the
population most appropriately.

4FINANCIAL ANALYSIS
2.2 Descriptive Statistics
Now, analysis of the descriptive measures of the variables will be conducted. The first
variable to be considered is ‘Order Priority’. The importance of the orders is denoted by this
variable. The priority of ‘critical’ is denoted by 4, ‘high’ is denoted by 3, ‘medium’ is denoted by
2, ‘low’ is denoted by 1 and 0 denotes ‘not specified’. The responses will be summarized in the
form of a table given in table 2.1.
Table 2.1: Frequency Table of Order Priority
Row Labels Count of Order Priority Percentage of Order Priority
0 8 13%
1 10 17%
2 11 18%
3 17 28%
4 14 23%
Grand Total 60 100%
0 1 2 3 4
0
2
4
6
8
10
12
14
16
18
Order Priority
Order Priority
Frequency
Figure 2.1: Frequency of Order Priority

5FINANCIAL ANALYSIS
It can be seen clearly that most of the orders are of high priority (28 percent) and
critical priority (23 percent). Only 14 percent of the respondents did not specify the priority of
their orders.
The second variable that will be considered is the order quantity. The number of items
that have been ordered under the order ID is given by this variable. It has been found out that the
average number of orders done per order ID is 28. Most of the order IDs have 17 orders. The
standard deviation is found to be low (13.46) and thus, it can be said that the quantity of orders
per order ID is mostly around 28. Orders per ID range between 1 and 49. It can also be said that
50 percent of the number of orders per ID lie between 17 and 38. The coefficient of variation (48
percent) being less than 100 percent indicates that the variance of the distribution is less. This
means that most of the order IDs have orders close to 28, the mean value.
The third variable that is to be considered is the total value of sales in $. The average
sales value has been found to be $1352.11. The range of the sales values are very high and the
coefficient of variation (216 percent) is a lot higher than 100 percent. Thus, it can be said that the
variance of the distribution is extremely high and the sales values are a lot scattered from
$1352.11, the average sales value. 50 percent of the sales values lies between $146.35 and
$936.75.
The fourth variable that is to be discussed is the shipment mode of the orders. The orders
are shipped via three modes such as Regular air, Delivery truck and Express air coded as 1, 2 and
3 respectively. It can be seen clearly from figure 2.2 that most of the orders are shipped by
Regular air.

6FINANCIAL ANALYSIS
80%
10%
10%
Shipment Mode
1
2
3
Figure 2.2: Shipment Mode of the Orders
The fifth variable that will be considered is shipping cost. The average shipping cost has
been found to be $9.41. The range of the shipping cost are very high and the coefficient of
variation (138 percent) is a lot higher than 100. Thus, it can be said that the deviation of the
values of the distribution from the average cost of shipping is extremely high and the sales values
are a lot scattered from $9.41. Moreover, in 50 percent of the cases, the shipping cost lies
between $2.2 and $9.04.
The next variable that will be considered is the region to which the orders are delivered.
The orders are delivered to only two regions – the eastern region (E) and the western region (W).
It can be seen clearly from figure 2.3 that 60 percent of the orders are delivered to the eastern
regions and 40 percent of the orders are delivered to the western regions.

7FINANCIAL ANALYSIS
60%
40%
Region of Delivery
E
W
Figure 2.3: Delivery Region of the Orders
The next variable that is to be considered is the consumer segment. This variable denotes
the type of customers that who order the products. It can be seen clearly from figure 2.4 that 30
percent of the customers are corporate, 28 percent of the customers are consumers, 27 percent
hold a small business and 15 percent run a home office.
28%
30%
15%
27%
Customer Type
Consumer
Corporate
Home Office
Small Business
Figure 2.4: Segment of Customers ordering the products

8FINANCIAL ANALYSIS
The last variable that is to be considered is the number of days that is required to ship the
order. The average number of days required to ship the orders is 1.93 days. Most of the orders
are shipped in 2 days. About 50 percent of the orders are shipped below 2 days. The range of the
number of days to ship are low and the coefficient of variation (69 percent) is a less than 100.
Thus, it can be said that the deviation of the values of the distribution from the average number
of days to ship is low and the values are close to 1.93 days. Moreover, in 50 percent of the cases,
the number of days to ship lies between 1 day and 2 days.
0 1 2 3 4 5 7
0
5
10
15
20
25
30
Number of days to Ship
Total
Number of Days
Frequency
Figure 2.5: Number of days to Ship
2.3 Confidence Intervals
We are 95 percent confident that the average sales cost for the home office customers lies
between -$835.51 and $5299.20. The population mean for the average sales cost is found to be
$1716.69. Thus, it can be seen that the population mean lies well within the limit given.

9FINANCIAL ANALYSIS
We are also 95 percent confident that the average shipping cost for all sample orders lies
between $6.13 and $12.70. The population mean for the shipping cost is found to be $9.41. Thus,
it can be said that the population mean lies well within the interval.
2.4 Hypothesis Testing
With the help of tests, it has been observed that the shipping cost of the orders with
critical priority is less than the shipping cost of the orders with low priority. It has also been
found from the test that the average sales order in dollars differs for the eastern and the western
states.
2.5 Correlation and Regression
Relationship has to be established between the variables order quantity and the sales in
dollars. It can be seen clearly that there is not much relation between the order quantity and sales
amount from figure 2.6. The regression equation can be given by:
y=63.971 x−441.2
In the above equation, y is the predicted value of the sales, x is the quantity of the
products ordered. From the analysis, it can also be said that only 9 percent of the variations in the
value of sales can be explained by this model.

10FINANCIAL ANALYSIS
0 10 20 30 40 50 60
$0.00
$2,000.00
$4,000.00
$6,000.00
$8,000.00
$10,000.00
$12,000.00
$14,000.00
$16,000.00
f(x) = 63.9707894954418 x − 441.203840522217
R² = 0.0866061993945342
Scatterplot of Sales and Order Quantity
Order Quantity
Sales (in $)
Figure 4.6: Relation between Order quantity and Sales amount
3.0 Conclusion
From the analysis of the data supplied by Hardware and Garden Supplies, it has been
seen that the average quantity of products ordered is approximately 28, the average value of sales
is $1352.11, the average shipping cost is $9.41 and the average number of days required to ship
the products is approximately 2 days. Also, it has been seen that 80 percent delivery is done by
regular air, most of the orders are of high priority, the demand of the products is more in the
eastern states. It can be said with 95 percent confidence that the population mean of the average
sales amount of orders for the home office customers and shipping costs for all sample orders lie
within the limits calculated. The shipping cost of orders with critical priority is less than that of
low priority. The sales in eastern and western regions differ significantly. The sales can be
estimated 8 percent accurately from the order quantity.
The sample size collected for the 2002 data points is extremely low. Thus, it can be said
that a sample of higher size will be able to predict the population more accurately.

11FINANCIAL ANALYSIS
Appendices
Table 1: Frequency distribution of Order Quantity
Row Labels Count of Order Quantity Percentage of Order Quantity
1-10 8 13%
11-20 10 17%
21-30 15 25%
31-40 14 23%
41-50 13 22%
Grand Total 60 100%
Table 2: Frequency distribution of Sales amount
Row Labels Count of Sales Percentage of Sales
9.75-1009.75 45 75%
1009.75-2009.75 7 12%
2009.75-3009.75 2 3%
3009.75-4009.75 1 2%
5009.75-6009.75 1 2%
7009.75-8009.75 1 2%
10009.75-11009.75 1 2%
12009.75-13009.75 1 2%
14009.75-15009.75 1 2%
Grand Total 60 100%
Table 3: Frequency distribution of Shipment Mode
Row Labels Count of Ship Mode Percentage of Ship Mode
1 48 80%
2 6 10%
3 6 10%
Grand Total 60 100%

12FINANCIAL ANALYSIS
Table 4: Frequency distribution of Shipment Cost
Row Labels Count of Shipping Cost Percentage of Shipping Cost
0.49-10.49 49 82%
10.49-20.49 5 8%
20.49-30.49 2 3%
30.49-40.49 1 2%
40.49-50.49 1 2%
50.49-60.49 1 2%
60.49-70.49 1 2%
Grand Total 60 100%
Table 5: Frequency distribution of Region
Row Labels Count of Region Percentage of Region
E 36 60%
W 24 40%
Grand Total 60 100%
Table 6: Frequency distribution of Customer Segment
Row Labels Count of Customer Segment Percentage of Customer Segment
Consumer 17 28%
Corporate 18 30%
Home Office 9 15%
Small Business 16 27%
Grand Total 60 100%
Table 7: Frequency distribution of Number of Days to Ship
Row Labels Count of Days to ship Percentage of Days to ship
0 4 7%
1 20 33%
2 25 42%
3 5 8%
4 1 2%
5 4 7%
7 1 2%
Grand Total 60 100%

13FINANCIAL ANALYSIS
Table 8: t-Test to test the difference of sales amount in eastern and western states
East Sales West Sales
Mean 1054.73 1798.17
Variance 5281293.25 13566362.66
Observations 36 24
Pooled Variance 8566751.81
Hypothesized Mean Difference 0
df 58
t Stat -0.964
P(T<=t) one-tail 0.170
t Critical one-tail 1.672
P(T<=t) two-tail 0.339
t Critical two-tail 2.002
Table 9: t-Test to test whether the price of orders with critical priority more than low priority orders
Critical Priority Low Priority
Mean 8.45 9.19
Variance 179.97 169.33
Observations 14 10
Pooled Variance 175.62
Hypothesized Mean Difference 0
df 22
t Stat -0.134
P(T<=t) one-tail 0.447
t Critical one-tail 1.717
P(T<=t) two-tail 0.894
t Critical two-tail 2.074
Table 10: Regression Statistics to predict sales with number of orders
Multiple R 0.29
R Square 0.09
Adjusted R Square 0.07
Standard Error 2819.60
Observations 60
Table 11: ANOVA to predict sales with number of orders
df SS MS F Significance F
Regression 1 43721453.415 43721453.415 5.499 0.022

14FINANCIAL ANALYSIS
Residual 58 461109075.120 7950156.468
Total 59 504830528.535
Table 12: Coefficients to predict sales with number of orders
Coefficients
Standard
Error t Stat P-value Lower 95%
Upper
95%
Intercept -441.204 846.927 -0.521 0.604 -2136.512 1254.104
Order Quantity 63.971 27.279 2.345 0.022 9.367 118.575