115.109 Topic 7 Lab Worksheet: Comparing Means of Composite Variables

Task 1: In the SustainabilityData sheet, construct the following composite variables

Columns to combine:

(nothing to show here, or verify: if you have done this task correctly, the yellow boxes in Task 2 will turn green when you’ve finished that task)

Task 2: Generate means, standard deviations and sample sizes for the ‘composite’ variables constructed in Task 1, split by Gender

Process: There are four different methods you could use to generate the required data (don’t start these yet – read on!):

i) Repeated PivotTables, using the Count, Average and StDev field value options;

ii) Repeated Filtering with the Subtotal commands for Count, Average and StDev.S (codes 2, 1 and 7 respectively)

iii) Calculating the descriptive statistics manually in sets, having sorted the data;

iv) Using Excel’s Data Analysis Toolpak commands (which mostly require sorting).

For this task we will use method ii), calculated in cells CO1048-CV1050 of the SustainabilityData sheet, and Copy > Paste Values to the boxes beneath here.

1) In SustainabilityData, activate the filter for Gender, selecting only 1 (Males), and also retain the Avg, StDev and Count rows As per Lab 5 Task 3, calculate the Subtotals for CO5:CO1046 and the other columns up to CV5:CV1046 for the mean, count and std dev (using the =SUBTOTAL(1… etc function). {Use the area in CO1048:CV1050.} Copy and Paste (as Values) the results in the blank space underneath (if you can see rows 1052-1054 use those, otherwise anywhere beneath the data, then remove the filter and paste again into row 1052-1054) Repeat for Gender=2, and again copy the results beneath (rows 1056-1058 if possible). Transfer the results into the correct rows if needed (1052-1054 for Males, and 1056-1058 for Females)

2) Copy and transpose the results below (Paste Special > Values and Transpose)

3) Once complete, copy cells K33:M33 and Paste Special > Formulas into cells K34:K40 to copy down the Z-test calculations. Note any significant differences, and complete the interpretation box What is the Z-test doing? Males Females Null Hypothesis: Ho there is no difference in subsample means (eg male v female means) Column Composite Variable Variable Mean Variable Standard Deviation Variable Sample Size Variable Mean Variable Standard Deviation Variable Sample Size Numerator Denominator Z-test Alternative Hypothesis Ha: there is a difference in subsample means (2-tailed test is the default) CO Societal Domain 0.00 #DIV/0! #DIV/0! (one-tailed test: is one mean larger than the other) CP Environmental Domain Test statistic: see formula, compare to critical value of Z CQ Supplier Domain Zc ±1.96 (for ɑ = 0.05, two-tailed) CR Shareholder Domain Conclusion: are we in a rejection region? CS Community Domain CT Employee Domain CU Customer Domain CV Sustainability literacy

Task 3: Repeat for Income, this time using two PivotTables (method i) using the same groups constructed in Lab 6 (Lower, as groups 1+2); Middle (3+4) and Upper (5+6)

1) Create a PivotTable by Household Income2, with Values being Averages. Unfortunately you have to change each variable from Sum to Average – there is no quick way to change them all. Copy result of PivotTables here, Averages first, using Paste Special > Values. Then COPY cells E50:E57 (averages for Middle) into G50, and F50:F57 into J50

2) Duplicate the first table to repeat for StDev, changing each entry from Average to StDev (second table) but in this case paste in rows 60 and below (this sheet) to avoid overwriting. Then copy and paste Special > Values to cells E50:E57, H50:H57 and K50:K57.

3) Copy the sample size numbers into cells F50 and copy down, and repeat for I50 and L50. Then complete the sets of Z-statistic calculations. Highlight any significant differences and interpret

Task 4: Repeat Z-test, this time using the Excel Data Analysis Toolpak option, testing the composites by How Often Buy (column AY)

1) The Data Analysis Toolpak is turned off by default. For Mac: Tools menu > Excel Add-Ins > Analysis ToolPak For Windows: File menu > Options > Add-ins > Excel Add-ins > Go > Analysis ToolPak Once activated, you will see a Data Analysis group at the right of the Data tab ribbon. We will use the Z-test Two Sample for Means tool

2) The WHOLE dataset needs to be sorted by How Often Buy (column AY), smallest to largest, rows 5:1046 (ensure any Filters are turned off – Select All) Write down the row numbers where HowOftenBuy subgroup 1+2+3 starts & ends, and where subgroup 4+5 starts & ends (since we don’t include 6) BUT: the Z-test tool requires knowing the Population variance for the two (sub)sample means we want to test.

3) So we need to know variance for each variable for Don’t Buy Often (1,2&3) vs Often Buy (4+5). Repeat Task 2 above, this time Filering for How Often Buy (column AY: options 1,2&3 vs 4&5) Complete cells CO1060 to CV1066 of the SustainabilityData sheet and copy StDev here, and write down the variance values for Supplier Domain

4) Undo the Filter (ensure the data is sorted ascending by column AY: step 2 above). In the SustainabilityData sheet, in the Data ribbon, click the Data Analysis button and complete the Z-test dialog box like this (for columns CQ, then CT):

5) You can easily redo the tests for the different variables (columns), since Excel retains the last used settings Write down the variance values for Employee Domain, and redo – this time using column CT and the new variance values

Task 5: Create three line charts for the data (below the table), as follows:

115109 Topic 8 Lab Worksheet: Comparing Means of Composite Variables

Due date: October 5

Task 1: Generate descriptive statistics for the composite variables used in Lab 7, for SustainabilityData by Literacy

There is a fifth method (beyond the four listed in Task 2 of Lab 7) that can be used to generate subsample descriptive statistics. In previous labs we have used =COUNTIF(range, criteria), where criteria could be Sustainability knowledge = “5” [see Lab 2]; and =SumIf [Lab 4 SKUs] There is also =AVERAGEIF (range, criteria, [average_range]), where average_range is what you want to take the average of Excel trainer ‘Exceljet’ (Dave and Lisa of Colorado) explain it here: https://exceljet.net/excel-functions/excel-averageif-function There is no =STDEVIF() function, but you can use a nested IF statement as =STDEV(IF(… instead: =STDEV(IF(range=criteria, target_range), and the same for =average(IF( and =count(IF(

1) We will use the composite variable ‘Literacy’ to analyse the others, dividing into “Higher Literacy” (above mean, calculated in cell CV1068) and “Lower Literacy” levels (below mean) Use these three formulae to create descriptive statistics in the SustainabilityData sheet. I’ve entered the formulas for Higher:Societal; complete the rest.

2) In the SustainabilityData sheet; cells CO1069:CO1071: copy and paste the descriptive stats for each composite variable for the (above mean) Higher Literacy group This uses the formula =AVERAGE(IF(SustainabilityData!$CV$5:$CV$1046>$CV$1068,SustainabilityData!CO$5:CO$1046)) for Mean for Societal for the Higher literacy group For StDev Societal:Higher the formula is =STDEV(IF(SustainabilityData!$CV$5:$CV$1046>$CV$1068,SustainabilityData!CO$5:CO$1046)) For sample size Societal:Higher: =COUNT(IF(SustainabilityData!$CV$5:$CV$1046>$CV$1068,SustainabilityData!CO$5:CO$1046)) [Count result should be the same across the domains]

3) Copy the formulas in CO1069:CO1071, and paste into CO1072 (this will also paste into CO1070 and CO1071), and update for below the mean: change the > to < for each formula, then copy across

4) Once complete in the SustainabilityData sheet, copy CO1069:CV1071, and Paste Special > Values and Transpose in cell D20 below. Repeat for CO1072:CV1074, and Paste Special > Values and Transpose into G20 below.

Task 2: Contrast the SustainabilityData descriptive statistics with those for ClassData

This is far simpler to generate the base data, as we are not doing any subsample analyses here. However, you do need to repeat the creation of the composite variables in cells CO5:CV5, and copy down to row 372 in the ClassData sheet.

1) Copy and Paste Special > Formulas cells D48:D55 (this sheet) into E48 and while E48:E55 is selected, click Home ribbon >> Find & Select >> Replace>> Find: average; Replace with: stdev; click Replace All [8 replacements]. Repeat into F48:F55, this time replacing average with count

2) Copy and Paste Special > Formulas cells D48:F55 into G48 and while G48:I55 is selected, Find and replace Sustainability with Class and Replace All [24 replacements];

3) Without closing the dialog box (or click Find&Select again), Find and Replace 1046 with 372 [24 replacements]. Now complete the z- and t-tests boxes

[NOTE: I have cleaned the data to remove any responses that did not complete the Domain questions. If there were any blank rows, these calculations would fail.]

Task 3: Use the Excel Data Analysis Toolpak (ANOVA) to test the Employee Domain composite by How Often Buy (SustainabilityData, column AY)

If activated as per Lab 7, you will see a Data Analysis group at the right of the Data tab ribbon. [Activating Mac: Tools menu > Excel Add-Ins > Analysis ToolPak] [Activating Windows: File menu > Options > Add-ins > Excel Add-ins > Go > Analysis ToolPak] ANOVA requires the data to be tested to be in separate columns. Preparing the data is tedious if you do it manually (by sorting, duplicating, then deleting), but there is a simpler way to do this: formulas.

1) Check that your data is still sorted by HowOftenBuy (column AY), smallest to largest, rows 5:1046 (same as for Lab 7 Task 4 – if not, sort it again)

2) In SustainabilityData cell CX5, enter: =IF($AY5=1,$CT5,”x”). Copy this to cell CY5, and change =1 to =2 Copy to cell CZ5, and change to =3; copy to DA5, and change to =4; copy to DB5 and change to =5. Then copy CX5:DB5 and paste from CX6:CX1046

3) ANOVA cannot cope with non-numeric characters, so we need to remove the “x” values. Copy CX5:DB1046 and while the whole range is selected, Paste Special > Values, replacing the formulas with their results. Now Find & Select > Replace, and put x into the Find box, and leave the Replace box blank; click on Replace All. There should be 4469 replacements

4) You should end up with a staggered set of columns, which have broken column CT into five non-overlapping columns. 5) In cell CX1050 use =COUNT(CX5:CX1046) and copy across to DB1050. If you (and I) have done this correctly, your counts should be: 33, 53, 240, 261, 154 6) Now call up the Data >>> Analysis Toolpak >>> ANOVA Single Factor, and complete as Input Range: $CX$5:$DB$1046 The results will appear in a new sheet. Copy and Paste Special > Values into the Summary and ANOVA tables below, and interpret the ANOVA result in the box.

Task 4: Complete confidence intervals around the five means above.

What can you interpret about the difference in means, from these confidence intervals?

Task 5: Use the Analysis Toolpak option t-Test Two-Sample (Assuming Equal Variances), testing the Employee Domain composite by How Often Buy =4 vs =5 1) Use column DA as the Variable 1 range and DB as the Variable 2 range Use alpha = 0.05

(1 chance in 20 of making a Type 1 error: incorrectly rejecting a true Null Hypothesis)

2) Copy results below, and interpret

all excell tasks