Transcript US Companies 2005

Guide to Using Excel 2007 For Basic
Statistical Applications
To Accompany
Business Statistics: A Decision Making
Approach, 7th Ed.
Chapter 3:
Describing Data Using Numerical
Measures
By
Groebner, Shannon, Fry, & Smith
Prentice-Hall Publishing Company
Copyright, 2008
Chapter 3 Excel Examples



Population Mean Foster City Hotel
Mean, Median,and ModeUS Companies 2005
Percentiles and Quartiles
US Companies 2005
More Examples
Chapter 3 Excel Examples (continued)


Measures of Variation US Companies 2005
Empirical Rule Burger N’ Brew
Population Mean
Foster City Hotel
Issue: Determine the mean nightly revenue for
the Foster City Hotel.
Objective: Use Excel 2007 to calculate the
population mean revenue
Data File is FosterCity.xls
Population Mean – Foster City Hotel
Open the Excel file called
FosterCity.xls
Population Mean – Foster City Hotel
•Select Formulas
•Select More Functions
•Select Statistical
•Select Average
Population Mean – Foster City Hotel
Population Mean – Foster City Hotel
Optional method:
•Select Data
•Select Data Analysis
•Select Descriptive Statistics
from the next dialog box
Population Mean – Foster City Hotel
•Highlight Column C to fill the
Input Range Box
•Select either New Worksheet
or Output Range to decide
where the output is placed
•Select Summary Statistics
•OK
Population Mean – Foster City Hotel
Mean Revenue
Measures of Central Tendencies – US
Companies 2005
Issue: What is the distribution of Stock Prices
for US Companies
Objective: Use Excel 2007 to develop histograms
for the Stock Prices
The data file is US Companies 2005.xls
Measures of Central Tendencies – US Companies 2005
Open the file US Companies.xls
Measures of Central Tendencies US Companies 2005
•Insert a column for the Bins –
label it appropriately
•Select the Data Tab
•Select Data Analysis
•Select Histogram
•Input Range: G1:G6993
•Bin Range: H1:H14
•Select New Worksheet Ply
•OK
Measures of Central Tendencies US Companies 2005
•Enlarge the Histogram by
pulling on the “handles”
•Right click on bars and Use
the Format Data Series
Option Setting the Gap
Width to zero
Measures of Central Tendencies US Companies 2005
Descriptive Statistics US Companies 2005
Issue: Determine the Descriptive Statistics for
US Companies 2005 Stock Prices
Objective: Use Excel 2007 to calculate the
descriptive statistics for Stock Prices .
Data file is US Companies 2005
Descriptive Statistics – US Companies 2005
Open the file US Companies.xls
Descriptive Statistics – US Companies 2005
•Select the Data tab
•Select Data Analysis
•Select Descriptive Statistics
•OK
Descriptive Statistics – US Companies 2005
•Input Range = g1:g6993
•Grouped by Columns
•Labels in First Row
•New Worksheet Ply
•Summary Statistics
Descriptive Statistics – US Companies 2005
Standard Deviation
Variance
Range
Empirical Rule Burger N’ Brew
Issue: Analyze the Phoenix Burger Sales
Distribution
Objective: Use Excel 2007 to compute graphs
and numerical measures necessary for using the
empirical rule to analyze sales at Burger N’ Brew
Data file is BurgerNBrew.xls
Empirical Rule – Burger N’ Brew
Open the data file called BurgerNBrew.xls.
The file contains data for 365 days
Empirical Rule – Burger N’ Brew
First develop histogram –
Class width = 2. Form bins
Empirical Rule – Burger N’ Brew
•Select the Data Tab
•Select Data Analysis
•Select Histogram
•OK
Empirical Rule – Burger N’ Brew
•Input Range- Chilli-Burgers Sold
•Bin Range – the Bins just created
•Select New Worksheet Ply
•Select Chart Output
•OK
Empirical Rule – Burger N’ Brew
This is the default output – now to
reformat the Histogram a bit
Empirical Rule – Burger N’ Brew
Looks better?
Now to compute the statistics
Empirical Rule – Burger N’ Brew
•Select the Data Tab
•Select Data Analysis
•Select Descriptive Statistics
•OK
Empirical Rule – Burger N’ Brew
•Input Range is the column to
be analyzed
•Select New Workbook
•Select Summary Statistics
Empirical Rule – Burger N’ Brew
And the statistics are:
The Empirical rule tells us –
•68% falls between 15.12 +/- 3.13
•95% falls between 15.12 +/- 2(3.13)
•99% falls between 15.12 +/- 3(3.13)