BUAD 2060, Assignment 1
Baracuda Stores, a division of National Clothing, is a chain of women’s apparel stores operating throughout the country. The chain recently ran a promotion in which discount coupons were sent to customers of other National Clothing stores. Data collected for a sample of 100 in-store credit card transactions at Baracuda Stores during one day while the promotion was running are contained in the attached Excel file. The Proprietary Card method of payment refers to charges made using a National Clothing charge card. Customers who made a purchase using a discount coupon are referred to as promotional customers and customers who made a purchase but did not use a discount coupon are referred to as regular customers. Net Sales represent the total amount ($) charged to the credit card.
Because the promotional coupons were not sent to regular Baracuda Stores customers, management considers the sales made to people presenting the promotional coupons as sales it would not otherwise make. Of course, Baracuda also hopes that the promotional customers will continue to shop at its stores.
Baracuda’s management would like to use this sample data to learn about its customer base and to evaluate the promotion involving discount coupons.
Use the methods of descriptive statistics discussed in Chapters 2-3 to help management develop a customer profile and to evaluate the promotional campaign. At a minimum, your report should include the following:
- Percent frequency distribution for key variables.
- A bar chart or pie chart showing the number of customer purchases attributable to the method of payment.
- A cross-tabulation of type of customer (regular or promotional) versus net sales. Comment of any similarities or differences present.
- A scatter diagram and descriptive statistics to explore the relationship between net sales and customer age.
- Descriptive statistics on net sales and descriptive statistics on net sales by various classifications of customers.
- Your recommendations.
For those without experience with the use of Excel, and especially its pivot tables, the following 2.5 hour video is recommended: https://www.youtube.com/watch?v=R0GjjPvswlQ
- Re: Task 1. Assume the following key variables: Method of Payment, Number of Items, Net Sales, and Age. See Excel instructions on pages 40- 41 and 51-53 for computing the frequency distribution of categorical and quantitative data. For the variable Number of Items assume that the last class is “More than 6”. Assume the following classes for Net Sales: 0 – 30, 30 – 60, 60 – 90,…, 150 – 180, “180 or more”. Assume the following classes for Age: 20-29, 30-39, 40-49, 50-59, 60-69, 70-79.
- . For quantitative data, the frequency distribution can be also found by using Histogram in Data Analysis of Excel. For example, in the case of Net Sales, you have to specify the upper limits of your classes, that is, 30, 60, 90, 120, 150, 180. Then in the Histogram dialog box, under Input Range enter the column with your data, and under Bin Range enter the column with your upper class limits. Excel automatically adds another class labeled ‘More” to the resulting frequency distribution.
- Re: Task 2. The Excel instructions for construction bar and pie charts are given on pages 43-45.
- Re: Task 3. The Excel instructions for constructing cross-tabulations are given on pages 70-71.
- Re: Task 4. The Excel instructions for constructing scatter diagrams are given on pages 80-81. Find the corresponding coefficient of correlation and interpret it; use Excel function CORREL; see pages 155-156.
- Re: Task 5. Find the descriptive statistics for all net sales, net sales for regular customers, net sales for promotional customers, net sales for married customers, and net sales for single customers. Use Excel’s Descriptive Statistics tool in Data Analysis; see instructions on pages 130-131. In order to extract net sales for regular and promotional customers from all net sales, you may sort the column “Type of Customers”. Similarly, in order to extract net sales for married and single customers from all net sales, you may sort the column “Marital Status”.
Use Microsoft Word to write a managerial report with your name shown on the first page. In this report you should present all Excel tables, graphs and charts (paste them) together with your written summaries corresponding to Tasks 1-5. (Do not attach any separate Excel files!) What are your recommendations? Hint: You may assume that you are an intern in National Clothing and your boss, who has a very limited knowledge about statistics, asked you to evaluate the effects of the sent discount coupons. Your report may look like a letter written to your boss in which you present your findings and make recommendations.
Attach your managerial report in Blackboard, and bring its hard copy to the class on September 15, 2016.