Introduction to Stat Packs
The Stat Packs are meant to supplement and broaden your understanding
of statistics. A computer can ease many of the necessary calculations,
it is not, however, a replacement for statistical knowledge.
All directions are given under the assumptions that students will be
using Excel on a Microsoft device.
All analysis will involve either the DATA ANALYSIS TOOL or STATISTICAL
FUNCTIONS.
ONCE IN EXCEL:
To access STATISTICAL FUNCTION click on the Function Wizard icon,
fx, located on the toolbar, to the immediate left of the input line.
To access the DATA ANYLSIS TOOL click DATA TAB on the toolbar.
Data Analysis is available under the Analysis Group (far right corner
of the Tool Bar.)
If you do not see Data Analysis you will need to load it into Excel.
It is free. The following applies to Excel 2007, Excel 2010, Excel
2013, and Excel 2016.
1. Click the File tab, click Options, and click the Add-Ins category.
If you're using Excel 2007, click the Microsoft Office Button, and
then click Excel Options
2. In the Manage box select Excel Add-ins, click Go.
3. In the Add-Ins box, check the Analysis ToolPak check box, click OK.
• If Analysis ToolPak is not listed in the Add-Ins available box,
click Browse to locate it.
• If you are prompted that the Analysis ToolPak is not currently
installed on your computer, click Yes to install it.
The Data Analysis should now be loaded into Excel.
MAC USERS: Many Excel versions for MAC do NOT have the Data Analysis.
If this is the case for you and you cannot load it into your version,
complete those sections requiring Data Analysis on another computer
(such as those in the library).
Mandatory Standards for All Stat Packs
Read, Use, and Save This Document
The following applies to all Stat Pack assignments. To receive full
credit:
1. Put your name in the marked cells, copying over the words Last
Name, First Name.
2. Put your Red ID# in the marked cells, copying over the words Red
ID#.
3. Complete the Stat Pack.
4. Turn it in on time (paper copy, no email attachments).
5. Format it like a business document.
A) Format ALL graphs & tables, including but not limited to
axis titles, scales, data legends, data labels, and titles.
When printing:
B) No gridlines*
C) No Excel row and column headings*
D) No cutting off of words in cells*
E) No cutting tables by right margin, leaving column table
data hanging on a separate page.*
*Before printing, use Print Preview (if given the option or
in the document itself), Portrait Orientation and/or the NO
SCALING so your stat pack is clean and business-like.
6. Put the printed sheets in the same order as they are presented
in the stat pack.
7. Work turned in greater than 1 in length page must be stapled.
8. Do not attach a cover sheet.
9. Do not write anything in pen, pencil, or highlighter, except
signature & date in pen if required.
10. There is no need for fancy fonts or color schemens.
ATTENTION: Where instructed to do so input your 9 digit RED ID # on
the Excel sheets. Then substitute the last four digits of your Red ID
# for the letters A, B, C, and D in the actual data sets.
A student with the Red ID # 888887654 would make the following
substitutions: A = 7, B = 6, C = 5, D = 4.
Thus, the Twisty Player’s Theatre problem for this student would
contain the following values:
(A) Cell A9: 117
(B) Cell A16: 116
(C) Cell A20: 105
(D) Cell A24: 114
When printing keep these “lettered cells” either highlighted yellow or
printed in grey scale.
A Note on Popup Boxes: At times input requirements/input wording is
slightly different across various versions of Excel. If your screen’s
popup box does not exactly match the directions use the help function
and good judgement.
A
B
C
D
1
Chillani Yogurt Company
2 Your Last Name, First Name Here
3 Your Red ID # Here
4
5 East Hopkins
6
Weight (oz)
7
7.94
8
8.00
9
8.01
10
7.9A
11
7.78
12
7.88
13
7.85
14
7.94
15
8.02
16
7.94
17
7.81
18
7.78
19
7.84
20
7.79
21
7.88
22
7.8B
23
7.9C
24
7.81
25
8.00
26
8.03
27
8.01
28
8.00
29
7.8D
30
7.80
31
7.87
32
7.55
33
8.00
34
7.81
35
7.84
36
7.76
37
7.92
38
8.00
39
8.01
40
7.61
41
7.76
42
7.88
43
7.77
44
7.94
45
8.00
46
7.78
E
F
Statistical Outputs
Pop Std
G
East Hopkins
0.18
Sample Mean
Sample Std.
Sample Size
Label Weight (oz)
Alpha
Test Statistics
Critical z =
Calculated z =
p-value =
8.00
0.001
A
B
C
D
1
MERCURY MOTELS
2 Your Last Name, First Name Here
3 Your Red ID # Here
4
5
Hotel
Phone
Online
6
1
8
6
7
2
7
6
8
3
11
9
9
4
7
11
10
5
11
9
11
6
6
7
12
7
7
10
13
8
A
8
14
9
12
15
15
10
14
10
16
11
13
6
17
12
16
9
18
13
9
C
19
14
7
10
20
15
11
4
21
16
11
4
22
17
13
7
23
18
11
5
24
19
10
10
25
20
9
D
26
21
B
7
27
22
9
6
28
23
11
9
29
24
13
6
30
25
17
8
31
26
16
12
32
27
11
5
33
28
10
13
34
29
8
5
35
30
10
7
Excel Stat Pack 2
Chillani Yogurt Company
Hypothesis Testing: 1 Sample Z-Test
The Facts
The law firm Wi, Cheatem & Howe has hired you as a consultant,
regarding a potential “slack fill” case involving the Chillani
Yogurt Company. “Slack fill” cases concern consumer products being
sold under the stated label weight/volume.
Wi, Cheatem & Howe received a tip from a supervisor working at
Chillani’s East Hopkins plant who claimed the company purposely and
routinely under-fills its 8 ounce yogurt cups, thus saving the
company money and padding the plant manager’s bonuses.
The tipster further revealed the filling process has a standard
deviation of .18.Your assistant collected samples. Based on the
insider’s tip about the product being sold underweight you will run
1 tail, left-side, Z-tests.
Steps:
1) Pull up Chillani Yogurt Company data. Swap out the letters in
the highlighted cells with your red id #s as you did in Stat
Pack 1. For the East Hopkins data Click fx:
2) In Cell G9 select AVERAGE, and input A7:A46 in the first
dialogue box and then click OK.
3) In Cell G10: input STDEV (It may be STDEV.S), using A7:A46.
4) In Cell G11: input COUNT, using A7:A46.
5) In Cell G18: input NORMSINV, using G14 in the dialogue box.
6) In Cell G19 Type: =(G9-G13)/(G10/SQRT(G11)).
7) In Cell G21: click fx, select NORMSDIST and input G19 in the
dialogue box (If there is a CUMULATIVE box, input 1).
1
Excel Stat Pack 2
A Word About P-Values
The p-value is the exact probability of obtaining a particular
calculated value or higher/lower, assuming all test assumptions are
true. Being a probability, a p-value can be compared to alpha
(another probability).
If p-value < alpha: Reject
If P-value > alpha: Fail to Reject
SEE LAST PAGE: SUMMARY POSITION
Responses must be typed. State your decisions, basis*, and
recommendation based on the analysis of your results.
*”Basis” is the reason on which you’re basing your Test Decision.
Your statements can and should be concise, clear, and in your own
words!
Feel free to move headings for formatting reasons.
2
Excel Stat Pack 2
MERCURY MOTELS
Hypothesis Testing: 2 Sample T-Test
The Facts
Mercury Motels operates a chain of economy priced motels. As Director of
Operations you collected data concerning the number of complaints
regarding reservations, dividing the data into two groups: complaints
stemming from reservations made over the phone and complaints stemming
from their online proprietary e-bookings software.
You want to determine whether there is a significant difference in the
number of complaints based upon these two methods, however, you do not
believe one method is superior/inferior.
Steps:
1)
2)
3)
4)
Pull up the MERCURY MOTELS data.
Click on the DATA TAB and then select DATA ANAYLSIS.
Select: T-Test: Two Sample Assuming Unequal Variances.
Input the following:
Variable 1 Range: B5:B35
Variable 2 Range: C5:C35
Hypothesized Mean Difference: 0 (zero).
Select Labels
Alpha: .01
Output Range: E6.
SEE LAST PAGE: SUMMARY POSITION*
*For “DOPS Position” assume as Director of Operations (DOPS) you are
reporting the owners of Mercury Motels.
Responses must be typed.
Test Decision and DOPS Position should be concise, clear and in
your own words!
Excel Stat Pack 2
CLASS SURVEY DATA
1. T-test: 2 Samples Assuming Unequal Variances
From the Class Survey File, using the data from the Female, Male
Data page -A. If your section meets once-a-week:
a. Test Hours Worked per Week for females versus males
b. Set alpha at, see “*” below
c. Set Output Option to New Worksheet Ply
B. If your section meets twice-a-week:
a. Test MIS 301 Stress Level for females versus males
b. Set alpha at, see “*” below
c. Set Output Option to New Worksheet Ply
2. Paired Difference Test
All Sections: From the Class Survey File, Copy & Paste the Last
Semester Rating and This Semester Rating data (Columns J & K) to
a new page –
1) Click on the DATA TAB and then select DATA ANAYLSIS.
2) Select: t-Test: Paired Two Sample for Means
3) Inputs: Select Labels (Column Headings), for Alpha see “*”
below, and for Output Range select New Worksheet play
*Alpha → If your class size is < 100 → Alpha = .01
→ If your class size is > 100 → Alpha = .001
For both tests:
• Turn in ONLY the OUTPUT page. [Both outputs can be arranged on
one page.] Do NOT turn in the survey data.
SEE SUMMARY POSITION page
• Responses need to be typed.
• Responses should be concise, clear, and in your own words!
• Type your name where directed and sign.
• Attach the Summary page to the front of your work packet.
4
Excel Stat Pack 2
SUMMARY POSITION
Chillani Yogurt Company
1. East Hopkins Test Decision & Basis:
2. East Hopkins Advisory Recommendation:
Mercury Motel
1. Test Decision & Basis:
2. DOPS Position:
Student Survey Data (2 Sample t-test)
1. Test Decision & Basis
2. Interpretation of Test Decision:
Semester Ratings (Paired Difference Test)
1. Test Decision & Basis:
2. Interpretation of Test Decision:
Last Name, First Name (Type):
_________________________________________________________________
Signature (Pen)
________________________
Date
Purchase answer to see full
attachment