Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Descriptive Statistics Worksheet using Excel 2000 Fast Food – Who (PDF) by dfsiopmhy6


									             Descriptive Statistics Worksheet
                          using Excel 2000

                   Fast Food – Who can do without It?

Britons consume more fast food than any other Europeans, with the number of fast food
restaurants in the UK having doubled between 1984 and 1993. The trend towards healthier
eating has caused some fast food giants to revise their menus, offering vegetarian meals,
salads and other healthy options. With the market in a state of change the need for up-to-
date market research is increasing. The data here consist of the responses of a random
sample of 200 males and 200 females aged 15-70 from a wider telephone survey of fast
food consumers. Respondents answered questions about themselves (e.g. sex, age) and
about their fast food purchasing – how often, what brand they bought last, and what brand
first comes to mind.

Getting started
The data are supplied in Excel format in the file FastFood.xls.
   •   Start Excel and from the main menu select File > Open;
   •   in the dialog box that appears click in the Look in box and navigate to the folder
       containing FastFood.xls;
   •   click on this file and then Open.

In this worksheet you will concentrate on analysing the frequency of purchase of fast food (in
Column A), which is a quantitative variable, and the brand last bought (Column L), which is a
qualitative variable. Your analysis will use summary statistics and tables of percentages.

Summarising frequency of purchase
Because the data in Column A have been grouped, we do not know precisely how often per
month each person bought fast food. This is not only because of the grouping – e.g. 7 or
more times – but because the person interviewed had to choose one of the stated categories
and could not therefore respond with, say, 2.5 times per month.

The variable you are charting is essentially a continuous measurement, being an average
figure per month. For example, the category “Less than once” presumably covers all values
from 0.0000 to 0.9999, an interval with midpoint (near enough) 0.5. Similarly, the category
“3 to 4 times” presumably means at least 3 times but not 5 times, i.e. covering values from
3.0000 to 4.9999, an interval with a midpoint of (near enough) 4.0. We have no idea how far
the category 7 or more extends but, assuming it extends up to 10.0, a midpoint of 8.5 would
seem reasonable.

In order to perform any calculations on these figures you will need to assume that the data
are evenly distributed throughout each category. This is equivalent to assuming that the
actual response for each person was the midpoint of the interval that they chose.

You now need to recode Column A to reflect this assumption.

Descriptive Statistics                                                      FastFood / Excel
                                            1 of 5
   •   Highlight Column A and from the main menu select Edit > Replace.
   •   Set Find what: Less than once, and Replace with: 0.5, then click on Replace All.
   •   Repeat to change Once to 1.5, Twice to 2.5, 3 to 4 times to 4.0, 5 to 6 times to 6.0
       and 7 or more times to 8.5, then Close.

Summary statistics may be calculated using cell formulae entered at the foot of each

It is important to leave a blank row between the end of the data and the start of any
calculations, otherwise Excel may later treat the calculated statistics as additional data.
   •   In B403 enter the label Mean.
   •   In A403 enter the cell formula =AVERAGE(A2:A401). This will calculate the overall
       mean (average) number of purchases per month. You should get the answer 3.20.
   •   Similarly, by replacing AVERAGE by STDEV, enter a formula in A404 to calculate the
       overall standard deviation number of purchases per month. You should get the
       answer 2.265.
   •   Although Excel has functions that will calculate other statistics such as MIN, MAX,
       MEDIAN and MODE, these are not really appropriate here because of the way you
       have had to ungroup the data. For example, the maximum would be given as 8.5,
       which was simply your estimate of the typical figure in the group “7 or more times”.

Effects of other factors on frequency of purchase
To calculate means and standard deviations within different subgroups of the data,
tabulation may be used, or Pivot Tables in Excel terminology.
   •   Click on cell A1 to identify the area of the spreadsheet containing your data.
   •   From the main menu select Data > PivotTable and PivotChart Report.
   •   At Step 1 accept the default settings, then Next.
   •   At Step 2 check that the data range is A1:AG401, and amend if necessary, then
   •   At Step 3 accept the default setting of putting your table on a new worksheet. Click
       on Finish to produce a skeleton table outlined in blue. The PivotTable toolbar should
       be visible. (If it is not, select View > Toolbars > PivotTable to retrieve it.)
   •   Click anywhere inside the skeleton table and you should see on the toolbar the
       names of all your variables (i.e. the column headings in your data spreadsheet).
       These are live buttons that can be dragged and dropped onto the skeleton table.
       Where the column heading is abbreviated (e.g. All fas…), hover over the button with
       your mouse to view the tool tip which shows it in full.
   •   Drag the Sex button and drop it in the Drop Row Fields Here area.
   •   Drag the All fas… button from the toolbar and drop it in the Drop Data Items Here
       area. By default this will calculate the sum (or total) of the number of fast food
       purchases per month for each gender.
   •   As it happens, this sample includes an equal number of males and females so the
       totals for each gender are comparable, but it would make more sense to calculate the
       average. Double-click on the Sum of All fast food / month button and a dialog box
       should appear. Select Summarize by: Average. To prevent a ridiculous number of
       decimal places being displayed, click on the Number… button and under Category:
       Number select 2 decimal places, then OK and OK to finish.

Descriptive Statistics                                                        FastFood / Excel
                                             2 of 5
Q1.    Do males and females purchase fast food equally often on average?
       On average males purchase fast food once per month more frequently than females.
       (Male average = 3.71, Female average = 2.70)

The Sex button may be dragged off your pivot table and replaced by another variable from
those listed on the toolbar. We will first look at Age groups.

Q2.    Do people in the different age-groups purchase fast food equally often
       on average?
       Age-group           Average

       15-17               4.24

       18-24               4.28

       25-35               3.25

       36-54               2.95

       55-70               1.94

       The frequency of purchase is virtually the same for the two youngest age-groups, but
       thereafter there is a steady decrease in frequency of purchase with age.
       People in the 55-70 age-group consume fast food just under half as often as people
       in the 15-17 age-group.

Q3.    Is the pattern of purchase among different age-groups the same for both
       males and females? (Hint: you will need to make Sex a column field.)
       The averages are:

       Age-group           Female           Male

       15-17               4.42             4.20

       18-24               3.31             5.03

       25-35               3.15             3.40

       36-54               2.40             3.56

       55-70               1.38             2.43

       No, with females there is a consistent decline in purchasing with increasing age,
       whereas for males the 18-24 age-group purchases more than the 15-17 age-group
       and the 36-54 age-group purchases slightly more than the 25-35 age-group on

Descriptive Statistics                                                     FastFood / Excel
                                           3 of 5
Of course, averages do not tell the whole story. You need to also consider the variability of
purchase levels within each group.
   •   Double-click on the Average of All fast food / month button and replace Average
       by StdDev (the sample s.d. as distinct from StdDevp which is the population s.d.) as
       the method of summary, then OK.

Q4.    Is the variability of purchase frequency roughly the same among the
       different age and sex groups?
       The standard deviations are:

       Age-group           Female              Male             Overall

       15-17               3.29                2.45             2.58

       18-24               1.87                2.18             2.20

       25-35               1.99                2.33             2.12

       36-54               1.95                2.37             2.23

       55-70               1.10                1.96             1.68

       Overall             2.02                2.39             2.27

       The standard deviations are broadly similar among the different age-groups,
       although the 15-17 age-group shows slightly more variability and the 55-70 age-
       group slightly less variability than the others.

       Males show slightly more variability than females, except among the 15-17 age-
       group. The 15-17 year old females vary the most in how often they purchase fast
       food, suggesting that there are many who eat fast food often and many who eat fast
       food very rarely.

Summarising the brand most recently purchased
The data in Column L tell you which brand of fast food the person purchased most recently.
This information is qualitative and it is therefore not possible to calculate averages and
standard deviations. However, you can summarise the information by tabulation.
   •   Drag all the buttons off your existing pivot table to return to a blank skeleton pivot
       table outlined in blue.
   •   Drag the Last bought button and drop it in the Drop Row Fields Here area. This will
       ensure that the table has a row for each brand.
   •   Drag the Last bought button (or the button for any other column containing text)
       again and drop it in the Drop Data Items Here area. This will produce a table
       showing the number of people surveyed who last bought each brand.

Descriptive Statistics                                                      FastFood / Excel
                                              4 of 5
Q5.    Which named brands have been last purchased the most and the least?
       McDonalds was the most common named brand last purchased, cited by 231
       people. Starbucks was the least common named brand last purchased, cited by just
       1 person.

Having seen the relative popularity of each brand overall, it is of interest to know whether
there is a difference between males and females.
   •   Click inside your pivot table, so that the PivotTable toolbar is activated.
   •   Drag the Sex button from the toolbar and drop it onto the cell containing the label
       “Total” at the top of the column of counts. The table should update to show separate
       counts for Females and Males.

Q6.    How do males and females differ in the brand last bought? Why might
       this be expected?
       Males and females have virtually identical patterns of purchase. The brand last
       bought does not necessarily indicate that it was the brand preferred by the person
       interviewed. Many of those interviewed may have been in a mixed-sex (e.g. family)
       group when they last purchased fast food.

By dragging the Sex button off your pivot table and replacing it by another button, e.g. Kid
influence, you can investigate what other factors affect the brand most recently purchased.

Because there are unequal numbers of people in the two categories of kids’ influence, you
need to use percentages to make a fair comparison:
   •   Double-click on the Count of Last bought button and click on the Options>> button
       so that an extended dialog box appears. Select Show data as: % of column. To
       avoid Excel displaying the percentages to a ridiculous (supposed) accuracy, click on
       the Number… button and choose to display the figures as Percentage with 0
       decimal places, then OK, and OK again.

Q7.    What is the effect of kids’ influence on the brand last bought?
       Where kids exerted an influence McDonalds was 15% more likely to be the last
       purchase, with most of the other brands losing out. Interestingly the three pizza
       outlets all show a 1% increase in popularity when kids influence the purchase.

Further investigation
In the same way you could investigate the effect of education level, region and household
income on the brand most recently purchased.

Descriptive Statistics                                                       FastFood / Excel
                                             5 of 5

To top