Hello Frasers Managers and Recruiters,
I was asked to complete this task during my second stage interview, and I hope I have met your expectations.
Description :You have been given a set of raw sales data for the past 52 weeks across 4 different products. Weather data (rainfall & temperature) has also been provided.
Objective: To provide a sales forecast (units), by week, for the next 52 weeks on all 4 products.
Summarise in a few sentences, the sales performance of last year. Explain how and why you have forecasted next years numbers.
Use any tool to analyse the data
Use your creative freedom to display your results.
Can you guess what the products are?
Data in excel – SDI Forecasting Exercise_Jacek
I feel quite comfortable working with SQL and I know all data In Frasers Group coming from databases so I decided to transfer data into my Database
GitHub code https://github.com/jacenty79/FG_code/blob/main/FG_code.sql
It is important to prepare and understand the data. For my convenience and better data manipulation, I decided to load the data into a database with a few changes, such as additional columns for Seasons, Rain, and Temperature. I also created four separate tables for each product.
I converted the data column using format 105 and enforced the date format using dd-MM-yyyy. I discovered that the data covers 52 weeks for each product; however, the time frame is not the same for all products.
When the data was ready, I connected Tableau to the database using the ODBC Database Driver 17 for SQL.
Once the data and connection were successfully configured, I started building Tableau worksheets for my dashboard project. I used five different worksheets:
The Sales Units chart shows separate Store sales and Web sales, sorted by month. A chart with 12 values is more readable than one with 52 overlapping values (any necessary changes to the chart can be made very quickly).
The Sales Summary is also separated and sorted by month. The Sales Units chart provides information about the difference between Store sales and Web sales.
The Seasons Revenue chart indicates which season the product sold best.
The Rainfall / Temperature chart covers the time period for all four products, showing temperature in ºC and rainfall in mm.
Compare Sales Units in store and online
In this project, I used more charts than those mentioned above; however, for the dashboard, I decided to include only the five most important ones for a better presentation. I’ve attached screenshots from other Tableau sheets to highlight differences in the data, such as the price difference between web and store sales.
The chart is based on the data dates in the worksheet and all charts are interactive except Rain fall and temperature
TABLEAU LINK – https://public.tableau.com/app/profile/jacek.kobrenczuk/viz/FG-Task/Fraser
Please select a product
As one of the largest brands of the Frasers Group is Sport Direct, selling sportswear, I would also take into consideration sports events analysing a data in range dates provided in this task :
2016:
1. Summer Olympics (Rio 2016) August 5 – 21, 2016 Location: Rio de Janeiro, Brazil
2. UEFA Euro 2016 June 10 – July 10, 2016 Location: France
3. ICC T20 World Cup 2016 March 8 – April 3, 2016 Location: India
2017:
1. FIFA Confederations Cup 2017 June 17 – July 2, 2017 Location: Russia
2. ICC Champions Trophy 2017 Location: England and Wales
2018:
1. FIFA World Cup 2018 June 14 – July 15, 2018 Location: Russia
Events like:
Back to school
Black Friday UK
2016: November 25
2017: November 24
2018: November 23
Holiday time
Christmas sale
Results of this task and Interview
I spent hours on this task, and while I enjoyed working on it, I expected better results. I was told that my skills align more with those of a data engineer than an analyst, so I consider this project a failure. However, I will still leave it on my website, as I invested a lot of time in Tableau and WordPress. Unfortunately, my final interview was unsuccessful. It’s disappointing that when I apply for data engineer positions, I’m often not considered because I lack direct experience in that field.
