Your Tasks for this A&V Test
The CEO of Global Superstore needs to have a good visibility on the global operations of the company, from financials to shipments. Budgeting, Investments, and targeted intervention of problem areas will depend on the CEO’s ability in developing an understanding which business areas and region perform well or otherwise. In order to support this, you have been tasked to conduct analyses on Global Superstore’s transactions data and provide the CEO with THREE self-service dashboards.
General Expectations on all Tasks (worth up to 0.5 mark for each task):
• Key data points must be labelled appropriately;
• All worksheet/dashboard/tiles must be appropriately labelled/named to reflect the particular analysis/task they represent;
• Appropriate use of colour and chart/viz type to highlight key areas and/or segments. Consistency of the colour scheme is maintained throughout the dashboards, worksheets, texts, charts, and graphs
Task 1: Market Performance (6 marks)
Create an interactive dashboard allowing the CEO to monitor the sales & profit/loss of the countries in specific Market(s):
1. (0.5 mark) Must allow the user to select a Market (i.e., APAC, EU, US, Africa, LATAM) and time period (i.e., year) to focus on;
2. (1 mark) The map showing all the countries in the chosen Market & year (clear label of country name, total sales value, and total profit value must be shown)
3. (2 mark) The 3 countries generating the highest total profit for the chosen Market & year (sorted highest to lowest)
0. For each country in this category, show the three most profitable product sub-categories
4. (2 mark) The 3 countries generating the least total profit (or the most losses) for the chosen Market & year (sorted lowest to highest profit)
0. For each country in this category, show the three least profitable product sub-categories
5. (0.5 mark) up to 0.5 mark is awarded upon meeting the General Expectations
Task 2: Product/Order Returns (6 marks)
We need to investigate the significance of Returned products/orders to our Company’s performance.
Create an interactive dashboard allowing the CEO to review the impact of returned products/orders:
1. (2 marks) Provide an overview of the returned orders:
. Based on specific time period (i.e., years) selected by the user
A. Show the 3 customers who returned orders with the highest total sales value for each Market. This is to be shown as/in a graph/chart (i.e., not a table)
B. The graph/chart must be colour-coded based on the Market
C. The number of returns and the total sales value of the returned orders must be shown for each customer
2. (1.5 marks) Provide a comparison between the actual Quarterly Sales value and the sales value of the returned orders in 2017-2020 for select Markets (User is to be able to choose the markets for this particular analysis & viz)
. Hint: The actual Quarterly Sales value is defined as the total quarterly sales value without the sales value of the returned orders. If you set up the table relationship properly and use the correct data field, Tableau will process the data automatically
3. (2 marks) Show the top 3 products (by Product Sub-Category) returned by customers for each Market
. Based on specific time period (i.e., years) selected by the user
A. Must be sorted descending based on the total number of returns per product sub-category for each Market
B. The total number of returns and the sales value must be shown for each product sub-category
4. (0.5 mark) up to 0.5 mark is awarded upon meeting the General Expectations
Task 3: Shipping Performance (8 marks)
We need to investigate the impact of On Time Shipment (OTS) to the Returned Products/Orders.
At Global Superstore, the Orders-Fulfillment department uses the following standards to set up the expectations for orders shipments:
Shipping Time Expectations
Ship Mode Expectation
Same Day same day shipment
First Class 2 days
Second Class 5 days
Standard Class 10 days
OTS (On Time Shipment) Category
Category Description
Shipped-On-Time (SOT) Actual Shipment (from order date to shipment date) is exactly on the Shipping Time Expectation
Early Shipment (ES) Actual Shipment (from order date to shipment date) is below the Shipping Time Expectation (not applicable for same day delivery)
Late Shipment (LS) Actual Shipment (from order date to shipment date) is beyond the Shipping Time Expectation
Create an interactive dashboard allowing the CEO to monitor the On-Time-Shipment of orders:
1. (0.5 mark) User is able to select a time-period (i.e., year(s)), Segment (i.e., Consumer, Home Office, Corporate);
2. (1 mark) A global map showing all countries of operations showing the name of the country and the number of Late Shipments for each country;
3. (2.5 marks) Showcase the proportion (or percentage) of On Time Shipment categorised as Shipped Late, Shipped Early, and Shipped on Time;
o [If you set this up correctly: If All segment is shown, the proportion of each On Time Shipment category is based on the total number of order for the specified year; if specific segment is (i.e., Home Office, Corporate, Consumer) selected then the proportion/%-age should be calculated based on the total number of order for that specific segment(s)]
4. (2.5 marks) Show the proportion of SOT, ES, and LS (Late Shipment) on the Returned products/orders – Maybe this can show us if there is a correlation between LS and Returned orders;
5. (1 mark) Show the projection of global Sales for the period between Jan 2021-Dec 2021 (this should be based on all of the data instead of the user-selected time-period).
o If you know how to, extend the forecast up to Dec 2022 (this is not compulsory)
6. (0.5 marks) up to 0.5 mark is awarded upon meeting the General Expectations
7. 1 (ONE) Mark Bonus point (Total result for this A&V Test will not exceed 20/20 – You will get a personal satisfaction, an email from me congratulating you and a bragging right) if you can provide a direct comparison of C & D in one viz tile.
