Congratulations. You have just inherited your uncle's business, which is a clothing store. Although the business was successful, your uncle was a seat of the pants" (pursuing effort, judgement based on personal experience rather than technological aids or formal theory) type of businessman. There was never any sales analysis conducted concerning, e.g. determining the effectiveness of promotional policies. You intend to analyze past sales data to gain insight into the business operations. The Scenario: Congratulations. You have just inherited your uncle's business, which is a clothing store. Although the business was successful, your uncle was a "seat of the pants" (pursuing effort, judgement based on personal experience rather than technological aids or formal theory) type of businessman. There was never any sales analysis conducted concerning, e.g, determining the effectiveness of promotional policies. You intend to analyze past sales data to gain insight into the business operations Attached you will find a hard copy of an Excel file called PROJDATA XLS. The data in this file corresponds to a random sample of 200 sales transactions that took place over the previous year. Because all transactions took place during the previous year and all transactions took place on Saturdays, on non-holiday weekends, you are confident that seasonal and cyclical effects are absent from the data The Scenario: Congratulations. You have just inherited your uncle's business, which is a clothing store. Although the business was successful, your uncle was a "seat of the pants" (pursuing effort, judgement based on personal experience rather than technological aids or formal theory) type of businessman. There was never any sales analysis conducted concerning, c.g. determining the effectiveness of promotional policies. You intend to analyze past sales data to gain insight into the business operations. Attached you will find a hard copy of an Excel file called PROJDATA XLS. The data in this file corresponds to a random sample of 200 sales transactions that took place over the previous year. Because all transactions took place during the previous year and all transactions took place on Saturdays, on non-holiday weekends, you are confident that seasonal and cyclical effects are absent from the data. The Analysis: Based on this sample, you seek to answer the following questions: 1. Should you consider targeting males or females in your promotional policy? In answering this question, consider the following: a) Is the proportion of male and female shoppers significantly different? (a=.05) b) Is the average sale amount for males different than the average sales amount for females? (o=.05) 2. Should you be encouraging or discouraging credit card sales? In answering this question, consider the following: a) Is the proportion of credit and non-credit sales significantly different? (a=.05) b) Is the average sale amount for credit sales different than the average sales amount for females? (a=.05) 3. What is the lag effectiveness of the current advertising policy? In answering this question, consider the following: a) Is the number of sales that occur 0, 1, and 2 weeks after an advertisement significantly different? (0-05) b) Does the average amount of a sale differ 0, 1, and 2 weeks after an advertisement? (@=.05) 4. Do "buy-one, get one free" and/or coupon promotions lead to larger sales? In answering this question, consider the following: a) Does the average amount of a sale differ by promotion type? (c=05) b) Which type of sale, if any, is largest? 5. Can multiple regression be used to "profile" a sale and determine whether a customer is spending more or less than anticipated, i.e. to predict the amount of a sale given the profile of a sale? a) To answer this question, conduct a regression analysis. b) How does the regression analysis support (or not support) your answers to part 1-4 above? Hints: 1. To complete this project, you will have to apply the following techniques: Hypothesis test for the difference between means. Hypothesis test for proportions ANOVA Chi Squared Analysis Regression Analysis 2. Read the following Excel help topics ANOVA tool FTEST worksheet function NORMDIST worksheet function Regression Analysis Tool FDIST worksheet function NORMSDIST worksheet function TDIST worksheet function DEVSQ worksheet function STDEV worksheet function VAR worksheet function TTEST worksheet function ZTEST worksheet function Weeks After Advertisement DOWN- NNO 2 2 1 . B 1 Transaction ID Sale Amount Gender 2 1 50.8 Female 3 2 93.5 Male 4 3 70.2 Female 4 36.3 Female 6 5 71.5 Female 7 6 79.7 Male 8 7 60.3 Female 9 8 74.5 Male 10 9 86.4 Female 11 10 20.6 Male 12 11 39.8 Male 13 12 67.4 Male 14 13 52.2 Female 15 14 99.9 Female 16 15 68.9 Male 17 16 74.5 Male 18 17 88.5 Female 19 18 88 Female 20 19 64.6 Female 21 20 55.8 Female 22 21 73 Male 23 22 64 Female 24 23 73.7 Female 24 58.7 Female 26 25 70.1 Female 27 26 78.7 Female 28 27 85.1 Female 28 66.3 Male 29 62.8 Male 31 30 75.4 Female 32 31 53.3 Female 33 32 65.2 Female 34 33 59.2 Male Sheet1 D Payment Type Non-Credit Non-Credit Non-Credit Non-Credit Non-Credit Credit Credit Credit Credit Credit Credit Non-Credit Non-Credit Credit Non-Credit Non-Credit Non-Credit Non-Credit Credit Non-Credit Non-Credit Credit Credit Credit Non-Credit Credit Credit Credit Credit Non-Credit Non-Credit Credit Non-Credit Nec E Promotion Type None BOGO BOGO None BOGO BOGO Coupon None BOGO None None Coupon Coupon None Coupon BOGO None BOGO Coupon None BOGO Coupon BOGO Coupon Coupon Coupon BOGO Coupon BOGO Coupon BOGO Coupon Coupon 0 1 0 0 2 2 1 2 0 1 0 1 1 1 2 1 1 1 2 2 0 0 1 1 0 2 1 25 29 30 JE BA TACCA 1 B G 35 36 37 38 39 40 41 42 43 34 35 36 37 38 39 40 41 42 43 44 45 46 44 47 48 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 co 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 104.6 Female 67.3 Female 51.5 Male 81.4 Male 49.6 Female 62.3 Female 54.8 Male 63.4 Male 47.4 Male 65.9 Male 42.2 Female 36.8 Male 88.1 Female 57.8 Male 96.7 Female 77 Female 93 Female 70.5 Female 66.5 Female 84.4 Male 27.6 Male 59 Male 49.1 Male 54.4 Female 63.4 Female 95.1 Female 69.3 Female 72.2 Female 39.1 Male 69.4 Female 60.7 Male 65.2 Female 74.5 Female 44.1 Male D Non-Credit Non-Credit Non-Credit Non-Credit Non-Credit Credit Credit Non-Credit Credit Credit Credit Non-Credit Non-Credit Non-Credit Credit Non-Credit Credit Non-Credit Non-Credit Non-Credit Non-Credit Non-Credit Non-Credit Non-Credit Credit Non-Credit Non-Credit Credit Credit Non-Credit Non-Credit Non-Credit Credit Non-Credit E Coupon None Coupon Coupon None Coupon Coupon Coupon None Coupon None None BOGO Coupon BOGO Coupon BOGO BOGO Coupon None BOGO Coupon None Coupon Coupon BOGO Coupon BOGO None Coupon Coupon Coupon BOGO BOGO 2 1 2 0 1 1 2 1 1 1 2 0 1 2 0 0 0 1 1 0 2 2 1 2 1 0 2 1 0 2 1 1 0 2 CO 03E DE Sheet1 C E F G 69 70 71 72 73 74 75 76 77 78 79 80 81 67 68 69 70 71 72 73 74 75 76 77 BOGO BOGO None None None None BOGO Coupon BOGO BOGO None BOGO BOGO BOGO 78 82 None 83 84 44.1 Male 83.1 Female 30.3 Male 49.8 Male 39.5 Male 48.9 Female 70.8 Male 78.5 Male 107.9 Female 101 Female 47.3 Female 82 Female 110.6 Female 73.7 Female 27 Male 68.9 Female 43.2 Female 61.5 Male 59.5 Male 28.2 Male 83.9 Female 50 Male 91.4 Female 55.8 Female 45.4 Male 103.8 Female 69.5 Female 69.6 Female 72.9 Male 67.1 Female 53.8 Male 94.1 Female 58.3 Female 86.4 Female D Non-Credit Non-Credit Credit Credit Non-Credit Credit Credit Non-Credit Non-Credit Non-Credit Non-Credit Non-Credit Credit Non-Credit Credit Non-Credit Credit Credit Non-Credit Non-Credit Non-Credit Credit Non-Credit Non-Credit Non-Credit Credit Credit Non-Credit Non-Credit Credit Non Credit Credit Non-Credit Non-Credit 85 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 86 87 2 0 2 1 0 1 1 0 0 2 2 0 0 1 2 1 2 1 1 2 0 1 0 2 2 2 2 2 1 1 2 0 2 1 88 89 90 91 92 93 94 95 96 Coupon None Coupon Coupon None BOGO None BOGO Coupon None BOGO Coupon Coupon BOGO None Coupon Coupon Coupon Coupon 97 98 99 100 101 Congratulations. You have just inherited your uncle's business, which is a clothing store. Although the business was successful, your uncle was a seat of the pants" (pursuing effort, judgement based on personal experience rather than technological aids or formal theory) type of businessman. There was never any sales analysis conducted concerning, e.g. determining the effectiveness of promotional policies. You intend to analyze past sales data to gain insight into the business operations. The Scenario: Congratulations. You have just inherited your uncle's business, which is a clothing store. Although the business was successful, your uncle was a "seat of the pants" (pursuing effort, judgement based on personal experience rather than technological aids or formal theory) type of businessman. There was never any sales analysis conducted concerning, e.g, determining the effectiveness of promotional policies. You intend to analyze past sales data to gain insight into the business operations Attached you will find a hard copy of an Excel file called PROJDATA XLS. The data in this file corresponds to a random sample of 200 sales transactions that took place over the previous year. Because all transactions took place during the previous year and all transactions took place on Saturdays, on non-holiday weekends, you are confident that seasonal and cyclical effects are absent from the data The Scenario: Congratulations. You have just inherited your uncle's business, which is a clothing store. Although the business was successful, your uncle was a "seat of the pants" (pursuing effort, judgement based on personal experience rather than technological aids or formal theory) type of businessman. There was never any sales analysis conducted concerning, c.g. determining the effectiveness of promotional policies. You intend to analyze past sales data to gain insight into the business operations. Attached you will find a hard copy of an Excel file called PROJDATA XLS. The data in this file corresponds to a random sample of 200 sales transactions that took place over the previous year. Because all transactions took place during the previous year and all transactions took place on Saturdays, on non-holiday weekends, you are confident that seasonal and cyclical effects are absent from the data. The Analysis: Based on this sample, you seek to answer the following questions: 1. Should you consider targeting males or females in your promotional policy? In answering this question, consider the following: a) Is the proportion of male and female shoppers significantly different? (a=.05) b) Is the average sale amount for males different than the average sales amount for females? (o=.05) 2. Should you be encouraging or discouraging credit card sales? In answering this question, consider the following: a) Is the proportion of credit and non-credit sales significantly different? (a=.05) b) Is the average sale amount for credit sales different than the average sales amount for females? (a=.05) 3. What is the lag effectiveness of the current advertising policy? In answering this question, consider the following: a) Is the number of sales that occur 0, 1, and 2 weeks after an advertisement significantly different? (0-05) b) Does the average amount of a sale differ 0, 1, and 2 weeks after an advertisement? (@=.05) 4. Do "buy-one, get one free" and/or coupon promotions lead to larger sales? In answering this question, consider the following: a) Does the average amount of a sale differ by promotion type? (c=05) b) Which type of sale, if any, is largest? 5. Can multiple regression be used to "profile" a sale and determine whether a customer is spending more or less than anticipated, i.e. to predict the amount of a sale given the profile of a sale? a) To answer this question, conduct a regression analysis. b) How does the regression analysis support (or not support) your answers to part 1-4 above? Hints: 1. To complete this project, you will have to apply the following techniques: Hypothesis test for the difference between means. Hypothesis test for proportions ANOVA Chi Squared Analysis Regression Analysis 2. Read the following Excel help topics ANOVA tool FTEST worksheet function NORMDIST worksheet function Regression Analysis Tool FDIST worksheet function NORMSDIST worksheet function TDIST worksheet function DEVSQ worksheet function STDEV worksheet function VAR worksheet function TTEST worksheet function ZTEST worksheet function Weeks After Advertisement DOWN- NNO 2 2 1 . B 1 Transaction ID Sale Amount Gender 2 1 50.8 Female 3 2 93.5 Male 4 3 70.2 Female 4 36.3 Female 6 5 71.5 Female 7 6 79.7 Male 8 7 60.3 Female 9 8 74.5 Male 10 9 86.4 Female 11 10 20.6 Male 12 11 39.8 Male 13 12 67.4 Male 14 13 52.2 Female 15 14 99.9 Female 16 15 68.9 Male 17 16 74.5 Male 18 17 88.5 Female 19 18 88 Female 20 19 64.6 Female 21 20 55.8 Female 22 21 73 Male 23 22 64 Female 24 23 73.7 Female 24 58.7 Female 26 25 70.1 Female 27 26 78.7 Female 28 27 85.1 Female 28 66.3 Male 29 62.8 Male 31 30 75.4 Female 32 31 53.3 Female 33 32 65.2 Female 34 33 59.2 Male Sheet1 D Payment Type Non-Credit Non-Credit Non-Credit Non-Credit Non-Credit Credit Credit Credit Credit Credit Credit Non-Credit Non-Credit Credit Non-Credit Non-Credit Non-Credit Non-Credit Credit Non-Credit Non-Credit Credit Credit Credit Non-Credit Credit Credit Credit Credit Non-Credit Non-Credit Credit Non-Credit Nec E Promotion Type None BOGO BOGO None BOGO BOGO Coupon None BOGO None None Coupon Coupon None Coupon BOGO None BOGO Coupon None BOGO Coupon BOGO Coupon Coupon Coupon BOGO Coupon BOGO Coupon BOGO Coupon Coupon 0 1 0 0 2 2 1 2 0 1 0 1 1 1 2 1 1 1 2 2 0 0 1 1 0 2 1 25 29 30 JE BA TACCA 1 B G 35 36 37 38 39 40 41 42 43 34 35 36 37 38 39 40 41 42 43 44 45 46 44 47 48 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 co 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 104.6 Female 67.3 Female 51.5 Male 81.4 Male 49.6 Female 62.3 Female 54.8 Male 63.4 Male 47.4 Male 65.9 Male 42.2 Female 36.8 Male 88.1 Female 57.8 Male 96.7 Female 77 Female 93 Female 70.5 Female 66.5 Female 84.4 Male 27.6 Male 59 Male 49.1 Male 54.4 Female 63.4 Female 95.1 Female 69.3 Female 72.2 Female 39.1 Male 69.4 Female 60.7 Male 65.2 Female 74.5 Female 44.1 Male D Non-Credit Non-Credit Non-Credit Non-Credit Non-Credit Credit Credit Non-Credit Credit Credit Credit Non-Credit Non-Credit Non-Credit Credit Non-Credit Credit Non-Credit Non-Credit Non-Credit Non-Credit Non-Credit Non-Credit Non-Credit Credit Non-Credit Non-Credit Credit Credit Non-Credit Non-Credit Non-Credit Credit Non-Credit E Coupon None Coupon Coupon None Coupon Coupon Coupon None Coupon None None BOGO Coupon BOGO Coupon BOGO BOGO Coupon None BOGO Coupon None Coupon Coupon BOGO Coupon BOGO None Coupon Coupon Coupon BOGO BOGO 2 1 2 0 1 1 2 1 1 1 2 0 1 2 0 0 0 1 1 0 2 2 1 2 1 0 2 1 0 2 1 1 0 2 CO 03E DE Sheet1 C E F G 69 70 71 72 73 74 75 76 77 78 79 80 81 67 68 69 70 71 72 73 74 75 76 77 BOGO BOGO None None None None BOGO Coupon BOGO BOGO None BOGO BOGO BOGO 78 82 None 83 84 44.1 Male 83.1 Female 30.3 Male 49.8 Male 39.5 Male 48.9 Female 70.8 Male 78.5 Male 107.9 Female 101 Female 47.3 Female 82 Female 110.6 Female 73.7 Female 27 Male 68.9 Female 43.2 Female 61.5 Male 59.5 Male 28.2 Male 83.9 Female 50 Male 91.4 Female 55.8 Female 45.4 Male 103.8 Female 69.5 Female 69.6 Female 72.9 Male 67.1 Female 53.8 Male 94.1 Female 58.3 Female 86.4 Female D Non-Credit Non-Credit Credit Credit Non-Credit Credit Credit Non-Credit Non-Credit Non-Credit Non-Credit Non-Credit Credit Non-Credit Credit Non-Credit Credit Credit Non-Credit Non-Credit Non-Credit Credit Non-Credit Non-Credit Non-Credit Credit Credit Non-Credit Non-Credit Credit Non Credit Credit Non-Credit Non-Credit 85 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 86 87 2 0 2 1 0 1 1 0 0 2 2 0 0 1 2 1 2 1 1 2 0 1 0 2 2 2 2 2 1 1 2 0 2 1 88 89 90 91 92 93 94 95 96 Coupon None Coupon Coupon None BOGO None BOGO Coupon None BOGO Coupon Coupon BOGO None Coupon Coupon Coupon Coupon 97 98 99 100 101