Guide to Using Excel 2003 For Basic Statistical

Guide to Using Excel 2003 For Basic Statistical Applications To Accompany Business Statistics: A Decision Making Approach, 7 th Ed. Chapter 3: Describing Data Using Numerical Measures By Groebner, Shannon, Fry, & Smith Prentice-Hall Publishing Company Copyright, 2008

Chapter 3 Excel 2003 Examples ® Population Mean Foster City Hotel ® Mean, Median, Mode Standard Deviation. Stock Prices – US Companies More Examples

Chapter 3 Excel Examples (continued) ä Empirical Rule Burger N’ Brew

Population Mean Foster City Hotel Issue: Determine the mean nightly revenue for the Foster City Hotel. Objective: Use Excel to calculate the population mean revenue Data File is Foster. City. xls

Population Mean – Foster City Hotel Open the Excel file called Foster. xls

Population Mean – Foster City Hotel Option 1 – Use the Excel Average function. Click the function wizard – Select Statistical category – Select Average

Population Mean – Foster City Hotel Specify data range Click OK

Population Mean – Foster City Hotel Population Mean

Population Mean – Foster City Hotel Optional Method: Select Tools, then Data Analysis

Population Mean – Foster City Hotel Select Descriptive Statistics

Population Mean – Foster City Hotel Define Data Range and select options

Population Mean – Foster City Hotel Mean Revenue

Population Mean – Foster City Hotel To calculate means for all three variables

Population Mean – Foster City Hotel

Mean, Median, Mode, and Standard Deviation Stock Prices Issue: Describe the distribution of stock prices for U. S. Companies Objective: Use Excel to develop histograms for the U. S. Companies’ stock prices and to compute the mean, median, mode and standard deviation for stock price. The data file is US Companies 2005. xls.

Descriptive Statistics- Stock Prices Open the data file called US Companies 2005. xls Data File contains 6, 992 stocks. Last row is 6, 993

Descriptive Statistics- Stock Prices Constructing histograms was covered in Chapter 2 (See Capital Credit Union) Begin by developing thebins (upper limit of each class. )

Descriptive Statistics- Stock Prices Click on Tools > Data Analysis > Histogram.

Descriptive Statistics- Stock Prices Fill in data and bin ranges – check labels – location for output – check Chart Output

Descriptive Statistics- Stock Prices Review Steps in Chapter 2 to complete histogram

Descriptive Statistics- Stock Prices Completed Histogram for Stock Prices. Then compute mean, median and modes for stock price.

Descriptive Statistics- Stock Prices Select Tools – then select Data Analysis > Descriptive Statistics

Descriptive Statistics- Stock Prices Define Data Range and select options

Descriptive Statistics- Stock Prices Mean Median Mode Standard Deviation

Empirical Rule Burger N’ Brew Issue: Analyze the Phoenix Burger Sales Distribution Objective: Use the Excel to compute graphs and numerical measures necessary for using the empirical rule to analyze sales at Burger N’ Brew Data file is Burger. NBrew. xls

Empirical Rule – Burger N’ Brew Open the data file called Burger. NBrew. xls Data File contains sales for 365 days. Last row is 366.

Empirical Rule – Burger N’ Brew First, develop histogram – class width = 2. Form Bins (upper limit of each class) Refer to Example 2 -6 for detailed steps in developing the histogram

Empirical Rule – Burger N’ Brew Click on Tools tab – then Data Analysis – then Histogram

Empirical Rule – Burger N’ Brew Define input range, bin range, output location – check Chart Output

Empirical Rule – Burger N’ Brew Excel default output – See Capital Credit Union example Chapter 2 for specific steps to enhance the histogram – close gaps, label , etc.

Empirical Rule – Burger N’ Brew Finished Histogram – Bell Shaped Distribution

Empirical Rule – Burger N’ Brew To compute numerical measures – Click Tools – Data Analysis – Descriptive Statistics

Empirical Rule – Burger N’ Brew Define data range, output location – request Summary Statistics

Empirical Rule – Burger N’ Brew Mean and Standard Deviation Empirical Rule: 68% within 15. 12 + (1)3. 13 95% within 15. 12 + (2)3. 13 99. 7% within 15. 12 + (3)3. 13)