Business statistics

Case Study: Online Orders at a Department Store

Case Study: Buying Pattern of Online Customers in a Large Department Store

The data file “Case-Online Orders-Pivot Tables.xlsx” contains data on 500 customer orders. The data was collected over a period of two months from the customers placing orders online. As the orders are placed, customer information is recorded in the data base. Data on several categorical and numerical values are recorded. The categorical variables shown in the data file are day of the week, time, payment type (credit, debit cards, etc.), region of the country order was placed from, order volume, sale or promotion item, free shipping offer, gender, and customer survey rating. The quantitative variables include order quantity and the dollar value of the order placed. The manager of the store wants to understand the buying pattern of the customers by summarizing the data.

Suppose you are an intern for this store chain and are given the responsibility to prepare a summary of the customer data that can help understand the buying pattern of the customers and also help improve the online order process to attract more online customers to the store.

You are familiar with one of the tools available in EXCEL – the Pivot Table that can be used in extracting information from such large data base. In this case, the pivot tables can help break the data down by categories so that useful insight can be obtained. For example, this tool can create a table of orders received by the geographical region, or summarize the orders by the day or time of the week. Perform the following analyses on the data to answer the questions and concerns the manager expressed in the meeting.

(Note: Identify each problem clearly and for each problem analyze your results and the graph and summarize your findings.)

(1) Create a pivot table that will provide a summary of number of orders received on each day of the week. Create a bar chart and a pie chart of the pivot table created to visually see the orders received by the online department on each day. Your pie chart should display both the numbers and the percent for each category. Briefly describe the table and graphs and comment on the number of orders.

(2) Create a pivot table to provide the count of number of orders by the time of the day (for example: morning, midday, etc.). Create a bar chart and a pie chart of the pivot table created to visually see the orders received by the online department by the time of day. Your pie chart should display both the numbers and the percent for each category. Explain the table and graphs and comment on the number of orders received by the company.

(3) Compile the orders by the region that is, create a pivot table and a bar chart to summarize the number of orders by the region. What conclusion you can draw?

(4) Create a pivot table to summarize the customer rating by gender where the row labels should show “Gender” and the column labels should show the count of “Customer Survey Ratings” (excellent, good, fair, poor). Construct a bar chart of the count of “Customer Survey Ratings” (excellent, good, fair, poor) on the y-axis and gender on the x-axis. Create a separate table to display the gender and the percent of ratings instead of count. Construct a bar chart of your table. Comment on the results.

(5) Calculate the descriptive statistics of the “Total Orders ($)” column. What are the minimum, maximum, and the average of the total orders received? From the descriptive statistics calculated, compare the mean and the median of the total orders. What conclusion you can draw regarding the shape of total orders data? (Note: to calculate the descriptive statistics, use the following command sequence in Excel: Data Data Analysis Descriptive Statistics and complete the dialog box that is displayed. Do not forget to check the Summary Statistics box.

(6) Construct a frequency distribution of the “Total Orders ($)” column using the Pivot Chart option. Group the values from a minimum value of 0 to a maximum of 400 in steps of 40. Show your frequency distribution and the histogram. Label each bar of the histogram to show the values for each bar.

(7) The calculated statistics in part (5) and it seems appropriate to conclude that the the total orders data is left skewed so that Chebyshev’s rule can be applied. What conclusions you can draw using the Chebyshev’s rule? (use the mean and the standard deviation of the orders to draw your conclusion. If you get negative values consider the values to be zero.)

(8) If the total orders data can be assumed to be approximately symmetrical, what conclusions you can draw about orders received? Use the mean and standard deviation calculated in part (5).

Present all the pivot tables and charts in a word file format and prepare a brief report with your comments and suggestion to improve the online order process.

Page | 1