management assignment

57

Kong MGM 403

Using Excel for Statistical Analysis

National Insurance Case

58

Kong MGM 403

National Insurance Case • The company serves over 10 million customers

• Questionnaire was mailed to a random sample of 1000 policyholders from National’s customer base, and eventually got back 285 respondents

• Respondents are asked to rate National Insurance on five dimensions – Reliability, Empathy, Tangibles, Responsiveness, Assurance

• …and also asked them to give an Overall Quality Rating to National

• …and recorded their demographic information

59

Kong MGM 403

First things to do when you get data?

1. Become familiar with the data (variables and values)

2. Conduct descriptive analysis

3. Check sample representativeness

60

Kong MGM 403

Descriptive Statistics • Frequency distribution / histogram for categorical

data – Insert -> PivotTable

– Insert -> PivotChart -> Column

• Distribution of income

Row Labels Count of inc < $10K 12 $10K-$19.9 K 20 $20K - $29.9 K 59 $30K-$49.9K 106 $50K-$64.9 38 >$65K 32 Grand Total 267

61

Kong MGM 403

Descriptive Statistics • For Windows users only:

• Histogram for metric data (interval/ratio) – Insert -> Insert Statistic Chart -> Histogram

– Right click on horizontal axis -> Format Axis -> Axis Options -> Bin width=1

62

Kong MGM 403

Descriptive Statistics • For Mac/Windows users:

• Histogram for metric data (interval/ratio) – Load “Analysis ToolPak” add-in

• Instruction see link below

• http://www.excel-easy.com/data-analysis/analysis-toolpak.html

– Data -> Data Analysis -> Histogram

63

Kong MGM 403

Descriptive Statistics

• Central tendency and variability

• Load “Analysis ToolPak” add-in – http://www.excel-easy.com/data-analysis/analysis-toolpak.html

– Data -> Data Analysis -> Descriptive Statistics (metric data)

• One variable and many

• Manually compute mean and standard deviation – Excel command: AVERAGE(data range); STDEV(data range)

64

Kong MGM 403

Inferential Statistics

• Check whether the survey respondents are representative of your target market – E.g., distribution of demographic variables

• Is sample distribution close to target market customer profile?

65

Kong MGM 403

Chi-square Goodness of Fit : distribution of variable A = hypothesized distribution

• Useful for checking sample representativeness

• Test statistic:

– Expected counts = sample size * hypothesized proportion

• P-value = CHITEST(Observed Range, Expected Range) – Larger the difference between observed and expected frequency,

smaller the p-value

• When p<alpha, reject null; distribution is significantly different from the hypothesized distribution

O – Observed frequency, E – Expected frequency

66

Kong MGM 403

Chi-squared Goodness of Fit

• Null: sample distribution of “use” matches with the following distribution

Number of years with the company

Less than 1 year 8% 1 to less than 2 years 9% 2 to less than 5 years 18% 5 years or more 65%

67

Kong MGM 403

Doing Chi-Squared Tests in EXCEL

• Step 1: Create a Pivot Table of Observed Data

• Step 2: – Goodness-of-Fit Test: provide hypothesized proportions to

create expected frequencies – Test of Association: use row and column sums to create

expected cross-tab assuming independent

• Step 3: CHITEST(Observed Range, Expected Range) – Do NOT include the “total” cell!

• Step 4: Reject null if p-value < 0.05

68

Kong MGM 403

T-tests • One sample t-test

– Compare mean of a single variable against a given standard

• Independent samples t-test – Compare mean of same variable for two different

groups

• Paired sample t-test – Compare means of different variables for same

respondents

69

Kong MGM 403

One Sample T-test • Compare mean of a single variable against a given

standard

• Question: is customers perception on reliability different from neutral (4)? If significantly different, is it higher or lower than neutral? – Null: average reliability rating = 4

బ

– Where is the sample mean

is standard error of X, , where is the standard deviation, is sample size

• Excel command: p-value = TDIST(tstat, df, tails=2) – df is degree of freedom; df = n-1

70

Kong MGM 403

Independent Samples t-test • Compare mean of same variable for two different

groups – The response of first group has no effect on the second

group

• We want to test whether female and male have different means for overall quality rating (op) – Null: means are equal

• EXCEL Command to obtain p-value: Data -> Data Analysis -> t-Test: Two-Sample Assuming Unequal Variances

71

Kong MGM 403

Paired sample t-test • Compare means of different variables for same

respondents

• Test whether average customers satisfaction on reliability (reliavrg) and empathy (empavrg) are different – Null: mean of reliavrg = mean of empavrg

• EXCEL Command to obtain p-value: Data -> Data Analysis -> t-Test: Paired Two Sample for Means – Data needs to pair up exactly

72

Kong MGM 403

Friday

• More practices using Excel – Discussions

• Assignment 2