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:
- Bikes:A range of bikes from Road, Mountain (MTB) and Commuter bikes.
- Clothing: Cycling apparel such as jackets, bib shorts, gloves, waterproofs, compression clothing, arm and leg warmers, etc. This also includes sunglasses, helmets and shoes.
- Accessories:Everything to support your ride from lights, mudguards, locks, water bottles, wearable tech, panniers, nutrition and hydration, etc.
- Parts:Everything to keep you mobile from chains, tyres, lubrication, cleaning products, seat posts, pedals, wheels, drivetrain, etc.
- Servicing:At some of the retail stores a “Bike Doctor” service is available. Customers can bring their bike in for a regular service, perform simple repairs (e.g., punctures) and fit modifications or replacements (such as, an improved hydraulic braking set up, drivetrain, etc). Servicing is completed by the company’s well trained and certified staff.
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:
- Annual sales revenue analysis. Provide a summary analysis of the total annual sales revenue and the total weekly sales revenue at the store in 2018. Do include any insights you have made from the analysis and breakdown of the sales performance. It would be very good if you can also provide some strong visualisations (graphs, charts, etc) to support your sales revenue analysis.
- Bike Doctor expansion planning. Expanding the bike servicing will require significant investment so we need to be clear on the benefits before we undertake this plan. We would need to refit the stores with a bike workshop, which will disrupt the sales floor during construction. We would also need to upskill existing staff and hire mechanics to provide this service. We like to think that providing this service improves our connection with our customer base and their loyalty to the Collective’s brand. We also believe that the Bike Doctor service improves in-store sales revenues, particularly for the “in-store” bike parts sales revenues. In your report, analyse the impact of the Bike Doctor service on parts revenues and, if possible, estimate the effect this service has on parts revenues. Use this evidence to support any recommendations that you make.
- Customer insight analysis. In the last quarter of 2018 we asked customers who used the Bike Doctor service one question when they collected their bike: “Would you use the service again”. We had an overwhelmingly positive response from users with 97% replying that they would use this service again. We believe this is good evidence of the quality of the Bike Doctor service. In your report we would like you to evaluate our survey approach and whether you support our findings.
- Bike Doctor survey and questionnaire. We want to understand the customer demand for such a service and identify stores that would benefit from this expansion. We intend to do a survey in early 2019 and need your advice in planning the survey and designing the questionnaire. Include in your report how we should plan our survey for 2019 and develop a short questionnaire of 3-5 questions we can used to collect data to investigate our question.
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