Lecture 3 Descriptive Stats Tabular Graphical PDF

EC 220: Business Statistics DESCRIPTIVE STATISTICS: TABULAR AND GRAPHICAL Learning Excel. Use Lynda.com (available through Inside Loyola) to learn the basics. The link is also provided in Moodle. Textbook provides nice tutorials as well. I. Summarizing Qualitative Data Frequency...

EC 220: Business Statistics DESCRIPTIVE STATISTICS: TABULAR AND GRAPHICAL Learning Excel. Use Lynda.com (available through Inside Loyola) to learn the basics. The link is also provided in Moodle. Textbook provides nice tutorials as well. I. Summarizing Qualitative Data Frequency distribution – a grouping of data into mutually exclusive classes showing the number of observations in each. Relative frequency – the fraction or proportion of items belonging to each class. Percent frequency – the percent of the data for each class. Cumulative frequency – the percentage of items that are less (or equal to) some value. Example 1: Busy portals like Google and Yahoo charge plenty for the privilege of advertising on their pages. If you are a retailer, how are you to decide which locations deliver buyers? Let’s focus on the choices faced by an advertising manager at Amazon. She has a budget for advertising to allocate among three busy hosts: MSN, RecipeSource, and Yahoo Host Visits Proportion/ Relative Percent (Frequency) Frequency Frequency MSN 7,258 0.41 41.19 Recipe Source 4,283 0.24 24.31 Yahoo 6,078 0.34 34.5 Total Shoppers 17,619 1 100 Excel: In Excel 2013, block off your data range (including variable and frequencies); go to Insert tab; and pick the desired chart. Bar Chart Visits (Frequency) 8,000 7,000 6,000 5,000 4,000 3,000 Visits (Frequency) 2,000 1,000 0 1 MSN Recipe Yahoo Source  EC 220: Business Statistics Pie Chart Visits (Frequency) 35% MSN 41% Recipe Source Yahoo 24% Example 2: We have a sample of data for CEO pay. What type of data is this? Useful video for working with Pivot Tables: http://www.youtube.com/watch?v=peNTp5fuKFg Data CEO Pay Low High High Moderate CEO Pay Moderate 10 High Moderate Pivot Table 8 Low 6 Moderate Count of CEO 4 High Row Labels Pay 2 Low High 6 Moderate 0 Low 5 High Low Moderate Moderate Moderate 9 High Grand Total 20 Moderate Low Moderate Low Moderate High 2  EC 220: Business Statistics II. Summarizing Quantitative Data A. PivotTable and Data Analysis We can summarize quantitative data using both PivotTable (Chart) and Data Analysis. To Activate Data Analysis go to File Tab> Options> Add-ins> Manage Excel Add-ins> Go. Check Analysis ToolPak. Data Analysis is now installed under the Data Tab. Note: Data Analysis is preferable to using Pivot Table to create quantitative frequency tables and histograms since Pivot Table does NOT include classes with no observations. Example 3: Suppose we have collected some data for a sample of 50 programmers in a large software company. Let’s analyze salaries. To create a frequency table for quantitative data, we need to specify classes. In general, we would like something on the order of 5-20 classes. The groups (classes) should be all inclusive so there is no data left out (no gaps) The groups (classes) should be mutually exclusive (non-overlapping) 1. Using PivotTable Steps: 1. Block off salary 2. Insert PivotTable 3. Drag salary to row; drag salary to values (make sure it is ‘count’, not ‘sum’) 4. Specify groups 5. Insert PivotChart 3  EC 220: Business Statistics Histogram of Salaries (thousands) 16 14 12 10 8 6 4 2 0 55-65 65-75 75-85 85-95 95-105 105-115 Salary 2. Using Data Analysis Steps: 1. Set up bins (maximum values in each class) Bin 65 75 85 95 105 115 2. Go to Data Tab > Data Analysis > Histogram 3. Pick Cumulative Percentage and Chart Output. The output will be a frequency table, which includes both frequencies and cumulative % frequency. In addition, the histogram and plot of the cumulative % frequency will be provided. 4. On the histogram, set gap width at 0. The classes are adjacent to one another and should have no gaps between the bars. To remove the gaps, right click on a bar, pick format data series and set gap width=0. 5. Replace bins with actual class values. 4  EC 220: Business Statistics Here is the output: bin (class) Frequency Cumulative % 65 5 10.00% 75 12 34.00% 85 14 62.00% 95 6 74.00% 105 9 92.00% 115 4 100.00% Histogram of Salary 16 120.00% 14 100.00% 12 80.00% Frequency 10 8 60.00% 6 40.00% Frequency 4 2 20.00% Cumulative % 0 0.00% Salary 3. Shape of the distribution- 3 patterns 5  EC 220: Business Statistics III. Crosstabulations and Scatter Diagrams Crosstabulation: a tabular summary of data for two (or more variables). This is also called a bivariate (two variable) Pivot Table. Example 4: Salary data Let’s look at a bivariate pivot table between degree and salary. Steps: 1. Block off the columns of data (including labels) 2. Go to Insert tab 3. Pick Pivot Table 4. Drag salary into the row 5. Drag degree into the column 6. Drag salary into values (Click on value< Value field settings < count) 7. Click on any row label. Use Group command to set up classes Here is the output: Count of salary Column Labels Grand Row Labels Grad UG Total 55-65 1 4 5 65-75 6 6 12 75-85 9 5 14 85-95 3 3 6 95-105 6 3 9 105-115 4 4 Grand Total 29 21 50 Question: Do we see anything interesting? Convert a cross-tabulation of Salary and Degree into relative and percent frequencies. 6  EC 220: Business Statistics We can also create a bivariate chart, which is shown below. 10 9 Salary and Degree 8 7 6 5 Grad 4 UG 3 2 1 0 55-65 65-75 75-85 85-95 95-105 105-115 Scatter Diagram: a graphical presentation of the relationship between two quantitative variables. Trendline: a line that provides an approximation of the relationship. Question: Is there a relationship between experience and salary? Steps: 1. Select experience and salary 2. Go to Insert < Scatter 3. Position the mouse pointer over any data point in the scatter diagram and right-click < Add trendline