You have recently joined Northern Cycling Collective as a Graduate Management Trainee. Northern Cycling Collective are a “Bricks and Clicks” specialist cycling store. i.e., this company have a chain of cycling retail stores across Northern England (Bricks) and an online presence through their webstore (Clicks), with supporting warehousing. Northern Cycling Collective sell a comprehensive range of bikes and cycling related products and services which are broadly classified in the following categories:
You have received an email (on the next page) from Karen Spall, one of the company directors, who is aware of your background in Analytics as well as your impressive Excel and Report Writing skills. Karen is an inspiring colleague and is very influential in driving the business forward. This is an excellent opportunity for you to showcase your skills and impress Karen with your abilities.
In the email from Karen, she has asked you to perform an analysis on the sales performance of one of the company’s retail stores in the data set “NCC_StoreA_revenues2018.xlsx”. Your colleague, Sebastian Delacourt, has provided some additional information about the data set below.
The data set concerns the sales at one of our stores (Store A) during 2018. In contains weekly sales data form 1/1/2018 to 30/12/2018. Stores are open for business Monday to Sunday, but are closed on New Year’s Day, Christmas Day, Boxing Day and New Year’s Eve. However, we remain open during Easter and the Summer Bank Holidays as the warmer weather attracts more cyclists! The columns in the data are:
Week: Identifier for the sales week. Week 1 is 1/1/2018 (Monday) to 7/1/2018 (Sunday). Week 52 is 24/12/2018 to 30/12/2018.
Bikes (Commuter, MTB, Road): Data on the weekly number of bikes sold in each category and the revenue from the bike sales for each category.
Clothing: Data on the weekly number of clothing items sold and the revenue from clothing sales.
Accessories: Data on the weekly number of accessories sold and the revenue from accessory sales.
Parts: We have two types of parts sales, those bought buy customers “in-store” and those used in the Bike Doctor “servicing”. Data on the weekly number of parts sold “in-store” and used in “servicing”; and the revenue from the sale of all parts are provided.
Servicing: Data on the weekly number of bikes serviced/repaired and the revenue from servicing. Note that the servicing includes only the labour charges for servicing. Revenue from parts used in servicing are accounted for in the parts sold data.
Communication from Karen Spall:
From:Karen Spall [mailto:firstname.lastname@example.org]
Sent:23 January 2019 11:52
Subject:Annual Sales Analysis Report
I have a small project that I would like to you take the lead on. It should help us understand how our stores are performing and will aid us in some of our near-term business planning decisions.
This project will be a pilot study that will require you to analyse the 2018 sales data from one of our retail stores that also has one of the workshops for servicing customers’ bikes. We would like to use your analysis and report as a template to deploy this analysis procedure to all our retail stores. The data has been provided in the Excel spreadsheet NCC_StoreA_revenues2018and I believe that Sebastian has already given you a detailed description of the contents.
Additionally, we are considering extending the “Bike Doctor” servicing to more of our retail outlets and we would like to see what evidence you can glean from the data to support this plan. If it the case that there is other data we could collect that could provide further supporting evidence then do make that clear in your report.
Finally, we have done some initial customer insight analysis (described below) by getting feedback from users of the “Bike Doctor” service. We would like you to comment on our methodology and consider how it could be improved. We would like to do a new survey to understand demand for this service and which stores we should consider providing this service in, so also include that in the report.
To clarify, the key actions required for inclusion in the report are as follows:
Get the finalised report to me by the afternoon of Monday 25thFebruary 2019, I need it for a board meeting later that week. I’m very much looking forward to reading your findings.
Best regards, Karen