Department of Information Systems and Business Analytics
The assignment requires you to analyse a given dataset, interpret the results, and then draw conclusions such that you are able to reply to specific questions being asked of you. (These questions are asked in the following memorandum.)
The data relate to a random sample of customers of a fictitious company called Swains, a small family-owned supermarket chain with 4 outlets. The managing director is John Swain. You play the role of Kim Holt in Swains' Research and Analysis Department and respond to a memo from John using data in the Excel file Swains.xls.
The assignment may be completed individually or as a group of two or three students. There are learning advantages to working as a group, though these exist only when each member of the group contributes. Your group should meet, or communicate with one another, to discuss what analysis is required and also to review your report before submitting. Submit one copy only per group for marking. Please include the names and IDs and of all group members.
The aims of the assignments are to:
• provide you with some examples of the application of data analysis and statistics within an organisation,
• test your understanding of the material in the relevant topics,
• enable you to demonstrate your ability to use Excel to analyse statistical data,
• test your ability to analyse and interpret your results, and
• test your ability to effectively communicate the results of your analysis to others.
Scenario
You play the role of Kim Holt who is in the Research and Analysis Department of a fictitious company called Swains (Swains is a fictitious company and meant to bear no resemblance to any existing organisation. All data in the spreadsheet is fictitious.) Swains is a small family-owned supermarket chain with 4 stores (called A, B, C and D in Swains.xls). The managing director of Swains is John Swain. Each store has a local manager who runs the store on a day-to-day basis, but John makes most of the important final decisions.
Swains takes regular surveys of its customers. The data from the most recent survey is in Swains.xls. This contains data for a sample of 546 customers across some 17 variables. John Swain studies the results of the surveys himself and is a fairly good data analyst, but he is very busy and gives much of the work to Kim. The specific questions John has for Kim are in the following memo from John.
Date: 18 November, 2015
Memorandum
To: Kim Holt, Research and Analysis Dept. From: John Swain, Managing Director.
Subject: Analysis of customer survey data Dear Kim
Can you please carry out some analysis of the recent customer survey data (in the file Swains.xls) and prepare a report with findings that I can present at the next board meeting. Your report will need to use plain language because not all of the board members will understand statistical jargon. Keep it under 10 pages in total.
The matters I want to focus on relate to (1) the time customers wait at the checkout and (2) aspects of the new store layout. The specific questions are as follows:
1 Waiting Time
(a) In the spreadsheet is a variable called "Waiting Time (mins)". This is the time (in minutes) that the sampled customers waited in line before being served. As you'll know, the company policy is to keep this below 2 minutes on average.
Please prepare summaries of this variable overall (i.e., with all four stores together) and for each store separately. I believe relative frequency histograms of the data with classes of a suitable width will show the overall shape of the distributions. To go with this, please prepare tables with all the standard numerical summary measures for the data. Please provide a brief description of each chart noting any "strange" features of the data such as outliers.
Do customers at any of the stores appear to have to wait, on average, longer than the expected 2 minutes? Compute and use 95% confidence intervals and provide a brief explanation of each (remember to use non- technical language).
(b) In the survey, we asked customers if they were satisfied with the speed which they were serviced at the checkouts (see the variable Sat. Wait Time). (Be a little careful - not all customers answered this question.) I would expect that customers who had to wait a long time would be less satisfied - does this appear to be the case? What is the nature of the relationship? Provide appropriate charts and numerical summaries.
(c) This year the manager at Store D (which, traditionally, has had the longest waiting times for customers) came up with a novel idea for trying to keep people happier while they waited in line to be served - he installed a TV set that played cartoons at the check-out counters. He's convinced that now people are no less happy at his store than at others, even though they have to wait longer. The other managers are sceptical and so am I. We think that the mean score on "Sat. Wait Time" will still be lower at Store D than the other stores. Can you please check this for me by conducting appropriate hypothesis tests to compare Store D with each of the other stores (use a significance level of 5%)
2 Shopping time and new shelf layout
This year we trialled a new shelf layout design at Store A - the "density" of products was increased by decreasing the shelf space for each item. One hope was that this would increase the efficiency for customers and decrease the amount of time people spent actually shopping. There is a variable "Shopping Time" in Swains.xls that you can use.
It is, unfortunately, not sufficient to just compare shopping times between stores because not all the stores carry the same number of lines (Store B carries the lowest number of lines, A carries the most, C and D are about the same) so customers do not buy the same number of items, on average, in each store (you might want to check this). But a colleague suggested that a simple way of determining whether the new shelf layout is effective is to work with a quantity the colleague called the "Gather rate":
Gather Rate = Number Items / Shopping Time.
Compute this new variable and, for each store, please draw appropriate charts. Then determine 2-sided 95% confidence intervals for the mean of the Gather Rate for each store and compare. Does it appear that the new shelf layout used in Store A helps? Note: for the purposes of this investigation, it might be a good idea to not include customers who purchased only a few items (say less than 5) - these people are likely to know exactly what they want and go for it and not be influenced by the shelf layout - please check and discuss in your report.
Data Analysis instructions:
• John has provided guidelines for your analysis in his memo, but you may wish to include results from other analyses (eg other charts or tables or tests) if you find they are useful. If you do include other material, please ensure that you explain clearly what they mean. Remember, your report is going to the board members who will need everything explained in plain language.
• It is easy to generate a great deal of computer output in your analysis. Ensure that you only include relevant results in your report.
• Limit your report to a maximum of 10 pages
• To answer some questions you may need to make certain assumptions about the data. Mention these in your report, where relevant, using non-technical language.
• You may use any data analysis package you wish. Excel can do it all.
• Details of your working are not expected. In particular, do not include derivations or formulas. Instead explain things in plain language.
Requirements
• Your report must be no longer than 10 pages (including the introduction and all charts and tables). Word format. Single-line spacing, no smaller that 10 point font, page margins at least 25mm, good use of white space.
• All Microsoft Excel output must be copied then pasted into Word with the paste special option (paste as a picture).
• Your report must have an informative title.
• Your report must contain an introduction/executive summary explaining in plain language what the report is for and summarising the main findings. The introduction must be no more than 1 page (approx. 500 words) with no charts or tables.
• The body of your report must be set out in the same order as in the originating memorandum from John Swain with each section clearly marked.
• Keep language simple and the explanations succinct. Avoid the use of technical or statistical jargon. Your readers will not necessarily understand even simple statistical terms.
• Marks will be deducted for the use of technical terms, irrelevant material, poor presentation/organisation.
Attachment:- Swains.xls