Data Analysis with Microsoft Excel: Updated for Office 2007

468 Statistical Methods

5 Click the Categories button and select Month from the list of range

names. Click OK.

6 Click the Output button and direct the plot to a new chart sheet

named Sales Boxplot. Click OK twice.

7 Rescale the y axis to go from 1000 to 5000.

8 Insert the chart title Liquor Sales. Add the x-axis title Month and

the y-axis title Sales ($mil). Edit the labels at the bottom of the

boxplot, removing the “Month =” text from each.

Figure 11-25 shows the edited boxplot for the liquor sales data.

Figure 11-25

Liquor sales

boxplot

The boxplot in Figure 11-25 shows how monthly liquor sales vary

throughout the year. The sales peak in December shows a slight dip in the

months of September and October. The boxplot also indicates the range

of production levels for each month. There are a couple of outliers in the

months of March and June, but there is nothing extreme.

Examining Seasonality with a Line Plot

You can also take advantage of the two-way table to create a line plot of sales

versus month for each year of the data set. This is another way to get insight

into the monthly sales fi gures during this time period. You will fi rst have to

create a two-way table of the data in the Liquor Sales worksheet.