Descriptive Statistics

Cumulative Distributions

1

Business Analytics

Lecture # 03

TOPICS to be COVERED

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Creating Distributions from Data

Cumulative Distributions

• Cumulative frequency distribution: A variation of the frequency distribution that provides another tabular summary of quantitative data
• Uses the number of classes, class widths, and class limits developed for the frequency distribution
• Shows the number of data items with values less than or equal to the upper class limit of each class

​

​

3

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Cumulative Frequency, Cumulative Relative Frequency, and Cumulative Percent Frequency Distributions for the Audit Time Data

4

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

MODIFYING DATA IN EXCEL

Sorting and Filtering Data in Excel

Conditional Formatting of Data in Excel

Modifying Data in Excel

Sorting and Filtering Data in Excel

• To sort the automobiles by March 2010 sales:
• Step 1: Select cells A1:F21
• Step 2: Click the Data tab in the Ribbon
• Step 3: Click Sort in the Sort & Filter group
• Step 4: Select the check box for My data has headers
• Step 5: In the first Sort by dropdown menu, select Sales (March 2010)
• Step 6: In the Order dropdown menu, select Largest to Smallest
• Step 7: Click OK

​

6

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Top-Selling Automobiles Data Sorted by Sales in March 2010 Sales

7

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Modifying Data in Excel

Sorting and Filtering Data in Excel

• Using Excel’s Filter function to see the sales of models made by Toyota
• Step 1: Select cells A1:F21
• Step 2: Click the Data tab in the Ribbon
• Step 3: Click Filter in the Sort & Filter group
• Step 4: Click on the Filter Arrow in column B, next to Manufacturer
• Step 5: If all choices are checked, you can easily deselect all choices by unchecking (Select All). Then select only the check box for Toyota.
• Step 6. Click OK

8

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Using Excel’s Sort Function to Sort the Top-Selling Automobiles Data

9

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Top Selling Automobiles Data Filtered to Show Only Automobiles Manufactured by Toyota

10

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Modifying Data in Excel

Conditional Formatting of Data in Excel

• Makes it easy to identify data that satisfy certain conditions in a data set
• To identify the automobile models in Table 2.2 for which sales had decreased from March 2010 to March 2011:
• Step 1: Starting with the original data shown in Figure 2.3, select cells F1:F21
• Step 2: Click on the Home tab in the Ribbon
• Step 3: Click Conditional Formatting in the Styles group
• Step 4: Select Highlight Cells Rules, and click Less Than from the dropdown menu
• Step 5: Enter 0% in the Format cells that are LESS THAN: box
• Step 6: Click OK

​

​

11

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Using Conditional Formatting in Excel to Highlight Automobiles with Declining Sales from March 2010

12

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Using Conditional Formatting in Excel to Generate Data Bars for the Top-Selling Automobiles Data

13

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Modifying Data in Excel

• Quick Analysis button appears just outside the bottom-right corner of a group of selected cells
• Provides shortcuts for Conditional Formatting, adding Data Bars, etc.

​

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Creating a Frequency Distribution for Soft Drinks Data in Excel

15

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Using Excel to Generate a Frequency Distribution for Audit Times Data

16

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Histogram

Histograms can be created in Excel using the Data Analysis ToolPak.

Following are the steps to create histogram in Excel.

Step 1. Click the DATA tab in the Ribbon

Step 2. Click Data Analysis in the Analysis group

Step 3. When the Data Analysis dialog box opens, choose Histogram from the list of Analysis Tools, and click OK

In the Input Range: box, enter A2:D6

In the Bin Range: box, enter A10:A14

Under Output Options:, select New Workshee Ply:

Select the check box for Chart Output

Click OK

​

17

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Figure 2.13: Creating a Histogram for the Audit Time Data Using Data Analysis Toolpak in Excel

18

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Figure 2.14: Completed Histogram for the Audit Time Data Using Data Analysis ToolPak in Excel

19

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Measures of Location

1. Mean (Arithmetic Mean):

• The most commonly used measure of location is the mean (arithmetic mean), or average value, for a variable
• If the data are for a sample (typically the case), the mean is denoted by x .
• the population mean is computed in the
• same manner, but denoted by the Greek letter m.

20

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Sample Mean

21

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Example

22

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Solution

23

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Measures of location (MEDIAN)

2. Median

• measure of central location, it is the value in the middle when the data are arranged in ascending order (smallest to largest value).
• With an odd number of observations, the median is the middle value.
• An even number of observations has no single middle

24

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

• Let us apply this definition to compute the median class size for a sample of five college classes.

​

• Arranging the data in ascending order provides the following list:

32 42 46 46 54

• Because n = 5 is odd, the median is the middle value. Thus, the median class size is 46 students.

25

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

MEDIAN

• Suppose we also compute the median value for the 12 home sales in Table 2.9. We first arrange the data in ascending order.

​

108,000 138,000 138,000 142,000 186,000 199,500 208,000 254,000 254,000 257,500 298,000 456,250

26

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

• Because n =12 is even, the median is the average of the middle two values: 199,500 and 208,000.

27

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Measure of Location (MODE)

• A third measure of location, the mode, is the value that occurs most frequently in a dataset. To illustrate the identification of the mode, consider the sample of five class sizes.

32 42 46 46 54

• The only value that occurs more than once is 46. Because this value, occurring with a frequency of 2, has the greatest frequency, it is mode

28

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Measure of Location (Geometric Mean)

• The geometric mean is a measure of location that is calculated by finding the nth root of the product of n values. The general formula for the sample geometric mean, denoted xg, follows.

29

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

• The geometric mean is often used in analyzing growth rates in financial data. In these types of situations, the arithmetic mean or average value will provide misleading results.

30

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

• To illustrate the use of the geometric mean, consider Table 2.10, which shows the percentage annual returns, or growth rates, for a mutual fund over the past 10 years

31

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

• $100 - 0.221($100) = $100(1- 0.221) =$100(0.779) =$77.90

​

• We refer to 0.779 as the growth factor for year 1 in Table 2.10
• We can compute the balance at the end of year 1 by multiplying the value invested in the fund at the beginning of year 1 by the growth factor for year 1: $100(0.779) =$77.90.

32

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

$100[(0.779)(1.287)(1.109)(1.049)(1.158)(1.055)(0.630)(1.265)(1.151)(1.021)]

​

=$100(1.335) = $133.45

33

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

34

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Measures of Variability

In addition to measures of location, it is often desirable to consider measures of variability. For example:

Suppose that you are considering two financial funds. Both

funds require a $1,000 annual investment.

• Fund A has paid out exactly $1,100 each year for an initial $1,000 investment.
• Fund B has had many different payouts, but the mean payout over the previous 20 years is also $1,100.

But would you consider the payouts of Fund A and Fund B to be equivalent?

Clearly, the answer is NO

The difference between the two funds is due to variability.

35

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

36

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

• Figure 2.18 shows a histogram for the payouts received from Funds A and B. Although
• the mean payout is the same for the two funds, their histograms differ in that the payouts associated with Fund B have greater variability.
• Sometimes the payouts are considerably larger than the mean, and sometimes they are considerably smaller.

37

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

38

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Range

• The simplest measure of variability is the range. The range can be found by subtracting the smallest value from the largest value in a data set.
• E.g. Refer to the data from home sales prices in Table 2.9. The largest home sales price is $456,250, and the smallest is $108,000.
• The range is $456,250 - $108,000 = $348,250.

39

• Range =MAX(B2:B13)-MIN(B2:B13)

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.

Thank You !

© 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use.