MARKETING HOMEWORK
The requested work in this mini-project assumes that you are familiar with what were done in the checkpoint assignments of your term project. Note that I will answer all questions regarding your term project, and will not provide help on this mini-project.
It is expected that by actively doing the work in the term project, you are able to show your skills here again with a different dataset with the same structure.
Please complete the following:
1) You are given a transaction dataset that is very similar to the one in your term project, but it is smaller – all the variables names have the same meaning. Please complete the industry analysis (on industry level sales trend across 3 years, seasonality, pricing pattern, and channel use). Please state the industry name at the top of your file. (20 marks)
2) Please identify the top 3 companies and compare their sales trends, pricing patterns, channel uses on a monthly basis. (20 marks)
3) Please add zipcode level median income information to this dataset (with a vlookup function), and then describe each of the top 3 companies’ customer base. Which of them are selling to relatively more affluent neighborhood? (20 marks)
4) Please use each of the top 3 companies’ 2005 data (the 2005 cohort of customers) and label their top/median/low spenders using an M-code (you need to do this one by one for each company).
a. For the 2006-07 period, what proportion of sales were from old vs. new customers for each firm? Please also show a few screenshots of your work in Excel on how you get the numbers to answer this question. (20 marks)
b. For the 2006-07 period, where did each firm’s top spenders go? Did they leave the industry? Or they were still buying but just buying from others? Please also show a few screenshots of your work in Excel on how you get the numbers to answer the questions. (20 marks)
You need to show good and complete work to earn credits. How these extra points earned will impact your overall grade will follow the rules specified in the “Grading” section in the Announcement on Canvas.
You will have about three weeks for this mini-project. Your work is due on May 8th, 2020 midnight. Any delay will result in NO extra credits. Please email me ONLY a word document with your comments and supporting charts (no supporting charts, no marks). Altogether 3-4 pages of comments are expected (excluding charts).
Here is a link to the data, and the product category is in the link:
https://www.dropbox.com/s/z6w16pe46xn41zh/cate31Home%20Electronics.xlsx?dl=0
