Purpose
This week is in preparation for writing your report to senior management in Week 2. Complete the following descriptive statistics analyses with Excel®.
Instructions. Use the Week 1 Pasta’s R Us (find below) with the following columns:
Sqft Square Feet
Sales/Customer Per Customer Average Spending
Sales Growth% Sales Growth Over Previous Year (%)
Loyalty Card% Loyalty Card % of Net Sales
Sales/Sqft Annual Sales Per Sqft
Med Income Median HH Income
Med Age Median Age
Bach Deg% % w/Bachelor’s Degree
Steps
A. Insert a new column, Annual Sales ($/1000). Annual sales result from multiplying a restaurant’s Sqft * Sales/Sqft with one decimal: If the current Sqft is 2000 and Sales/Sqft is 700, calculate the Annual Sales ($/1000) as 2000*700/1000 = 1400.0.
B. Calculate the mean, population standard deviation, skew, five-number summary, and the interquartile range (IQR) for each variable. Show how you made the calculations.
C. Use Insert > Insert Statistic Chart > Box and Whisker for the Annual Sales variable. Finish the chart with a y-axis label.
Does it look symmetric? Would you prefer the IQR instead of the population standard deviation to describe this variable’s dispersion? Explain? Answer these questions in your Excel sheet.
D. Use Insert > Insert Histogram for the Sales/Sqft variable.
Is the distribution symmetric? If not, what is the skew (negative, positive)? Are there any outliers? If so, which one(s)? What is the Sqft area of the outlier(s)? Is the outlier(s) smaller or larger than the average restaurant in the database? What can you conclude from this observation? Answer the questions in your Excel sheet.
E. What measure of central tendency (mean, median) is more appropriate to describe Sales/Sqft? Explain? Answer the questions in your Excel sheet.
0 comments