ADVENTURE WORKS

OBJECTIVE

After seeing the impressive work you've done on the Global Superstore
dashboard, your reputation in the market has soared. Your ability to transform
complex data into insightful and actionable visuals has garnered widespread
acclaim. Now, numerous companies are eager to collaborate with you,
recognizing your expertise and innovative approach.

One notable example is the retail giant Adventure Works, which is keen to
become your client. They have been impressed by your ability to create
comprehensive dashboards that address both logical and real-world business
challenges. Adventure Works sees great potential in leveraging your skills to
enhance their data analytics and reporting capabilities, ensuring they stay
ahead in the competitive retail landscape.

MAIL 1 (pg-1)
Adventure Works has a dedicated in-house business analyst team. This team
has meticulously reviewed their needs and has forwarded a comprehensive
list of requirements that they wish to see incorporated into the new dashboard.
These requirements should be adhered to and fulfilled with the aim of
enhancing data visualisation, improving decision-making processes, and
providing actionable insights to support their strategic objectives. By meeting
these specifications, the dashboard should serve the analytical needs of the
organisation, ultimately driving more informed business decisions.

They have provided you with their sales data.

1- The team requires the inclusion of cards displaying essential details such
as total sales, total costs, total orders handled and total quantity sold. These
cards should provide a quick and comprehensive overview of the company's
current market position. By summarising these key metrics, the dashboard
should enable the team to instantly assess their performance, and make
informed decisions to drive business growth and competitiveness in the
market.

2- The company now seeks a detailed visualisation that illustrates the sum of
sales and the sum of costs on a month-by-month basis. This chart should be
designed in a way to easily visualise the months where the cost amounts are
lower than the sales amounts. This visual should aim to provide the
comparative view, the company should be able to easily identify periods of
profitability, track financial performance trends over time, and pinpoint specific
months where their revenue exceeds expenses, this should also help
company to figure out if there are any months where the sum of cost is always
lower than sum of sales when compared to different years.

3- The company also requires a fundamental visual representation to track the

total quantity sold per quarter. This graph should also effectively measure
quarterly performance across different years, enabling the company to assess
sales trends and performance over time on a quarterly basis.

4- The company is interested in identifying the top 10 selling products annually

and assessing whether these products consistently rank among the top 10
across different years. This analysis should provide insights into product
performance trends over time, highlighting consistent best-sellers and
identifying any shifts in consumer preferences or market dynamics.

MAIL 2 (pg-1)

Their business analyst team was constantly monitoring your dashboard and
The adventure works have assigned a few more tasks for you.
1- On the report page, they are requesting a non-interactive visual that
remains fixed, providing a clear snapshot of two key metrics: total sales and
total orders placed. This visual is intended to provide immediate, at-a-glance
insights into these critical performance indicators .
Additionally, they require a dynamic visual that tracks progress towards their
ambitious sales target of $100 million for the year 2021. This target was
established as part of their strategic plan spanning from 2016 to 2021. The
visual will dynamically update to show how close the organisation is to
reaching this milestone, offering ongoing motivation and strategic alignment
with their long-term business objectives."

2- On the same page, the company requires two additional visuals: one
depicting the sales trend over the years, providing a historical perspective on
revenue performance, and another graph displaying the median unit price and
median sales price trends over the year. Together, these visuals should offer
invaluable insights into the company's financial trajectory and pricing
dynamics. They should enable strategic decision-making by highlighting long-
term sales patterns and pricing trends, empowering stakeholders to assess
market performance, optimise pricing strategies, and align business
operations with overarching strategic goals."

3- The company's Business Analyst team has also tasked you with enhancing
the report page to ensure all the aforementioned visuals dynamically respond
to product categories. This enhancement should provide a deeper, more
nuanced analysis of sales performance and pricing trends across different
product categories, empowering strategic insights and informed decision-
making to the organisation.

[YOUR MANAGER ASKED YOU TO ADD CLEAR SLICERS OPTION FROM

YOUR END SO THAT CLIENT DON'T HAVE TO FACE ANY ISSUE
REMOVING THE SELECTED VALUES]

MAIL 3
Company reached out to you to do some more additional changes in the
dashboard.

They wanted you to insert an image of the company logo on the first page of
the dashboard.

And on clicking on the photo it should forward you to the

link :-https://learnwith.campusx.in/courses/5-SQL-Case-Studies-
660daa929ecb7050edff6c71

MAIL 4 (pg-2)

They have provided you with a reseller dataset also now.

1. The Business Analysis team is preparing concise visuals for the CEO,
who requires quick insights without delving deeply into the data. They've
requested two map-based visualisations: one to depict sales by region,
and another to show sales by country-region.
The first visualisation should effectively visualise sales across regions,
in such a way that size of some shape should determine the sum of
sales of a region , more the sales greater will be the size of that shape.
The second map will categorise country-regions based on sales
volume: country-regions with sales under 1 crore should be marked in
 red, those between 1-5 crore in brown, and regions exceeding 5 crore in
green.

2. The Business Analyst team has also tasked you with visualising the
total sales and quantity sold over the years categorised by different
reseller types. They are interested in understanding how sales and
quantities have evolved annually across various types of resellers.
This visualisation will provide insights into the performance trends of
each reseller type over time, helping to identify which types contribute
most significantly to sales and quantity sold annually.

3. You are also tasked with creating a visual representation of quantity

sold per country-region. The team has specified that this visual should
illustrate the performance relative to the top-performing country-region,
which will be set at 100%. Other regions' performance should be
compared as a percentage relative to this top performer.
This visualisation should aim to clearly highlight which country-regions
are leading in terms of quantity sold and how other regions compare in
relation to the top performer.

4. The Adventure Works strategy team has identified significant potential

in the bike segment. They are keen to analyse their current market
position within this segment by examining sales across different bike
categories over varying time frames: annually, quarterly, monthly, and
daily.
This analysis will provide insights into the performance trends of each
bike category over different time intervals, aiding in strategic decision-
making to capitalise on the identified market potential.

MAIL 5 pg(3)

1- The team is interested in understanding customer colour preferences

across different product categories. They have requested a visualisation
showing the number of articles sold in each colour within each category. This
analysis should provide insights into which colours are most popular within
each product category, helping the team tailor their inventory and marketing
strategies accordingly.

2- Companies have figured out that they are lagging in the trajectory to meet
the target that was set in 2016, to improve their sales they wanted to see how
the distribution of their resellers in each region and as per their type , so that if
there is any lag they can work on filling that lag, create a visualisation for the
same.

3- The company is undergoing restructuring and needs to assess the

workload of its sales employees. They want to categorise employees based
on the number of retailers they manage:

● Employees managing fewer than 1500 retailers are considered under

burdened (represented in pink).
● Those managing between 1500 and 6000 retailers are neither
overburdened nor underburdened (represented in green).
● Employees managing more than 6000 retailers are considered
overburdened (represented in red).

The company requests a table displaying each employee's workload status,

prioritising under burdened employees at the top. This visualisation should
help identify workload imbalances and guide restructuring efforts to optimise
employee efficiency.

4- The Business Analyst team is interested in understanding the distribution of

resellers under each employee across different countries. This analysis should
provide insights into how resellers are allocated among employees,
highlighting potential areas for reallocation or support based on workload and
geographical coverage.

5- Additionally, the BA team wants to visualise the sum of sales and costs per
state using bar charts

6- Finally, the BA team is interested in analysing the quantity of products sold

based on group, country. This analysis should provide a comprehensive view
of sales performance across different geographical categories, enabling
informed decision-making and targeted marketing strategies. Prepare a visual
for the same in a way that the team moves to countries of those groups only
where they are interested in.
MAIL 6

From the first visual (mail 5) client saw that product type components are
being sold in almost every colour. Now the client is more interested to know
more about this category.
They wanted that on the report page where there is the graph that you plotted
for first visual (mail 5), you should create something , by clicking on which
the BA team will land into the visuals created by you of Mail 1 and Mail 2 (pg-
1) filtered by the product category - component

NOTE- CLIENT HAVE NOW PROVIDED YOU WITH ADDITIONAL

DATSET ALSO , USE WHEN AND WHERE REQUIRED.

MAIL 7

1. Create various visualisations to depict sales and costs categorized

by financial quarters and financial months. These visuals will
provide insight into the company's financial performance over
different periods
2. The company aims to accurately assess the percentage of time
each employee is achieving their assigned targets.
For instance, consider Employee A, who is assigned weekly
targets for a duration of four weeks. If Employee A successfully
meets the targets in weeks 2, 3, and 4, but not in week 1, the
calculation of their target fulfilment percentage would (3/4)*100
3. The company wants to analyse the average sales and average
quantity sold on weekends compared to weekdays, represented on
a single graph. This will help understand sales patterns across
different days of the week.

MAIL 8
 1. The CEO of Adventure Works plans to reward the top 10 resellers
based on their Reseller Loyalty Score (RLS). The RLS is
calculated using the formula:
RLS=((0.50×total sales)+(0.20×duration of association)
+(0.10×quantity)+(0.20×profit)/total sales)×100

2. The company is facing a strategic challenge in optimising market

efforts and resource allocation across different regions. To address
this, the company seeks to identify cities with the highest number
of resellers. However, the focus is specifically on those cities
ranked between the 5th and 15th positions in terms of reseller
count, draw a visual for the same for the client.

3. Evaluate whether sales representatives who are overburdened

with tasks are still performing well.

MAIL 9

1. For the year 2019, illustrate the cumulative sum of sales for each
month and also show each month's contribution to the overall
sales of 2019. This detailed breakdown will help in understanding
the sales trends and performance throughout the year, enabling
better forecasting and strategic planning.
2. calculate the contribution of each sales representative to the
overall sales for each year in percentage terms. For example, if the
total sales in 2017 were 100 units, and Employee A made 20 units,
Employee B made 10 units, and Employee C made 70 units, then
the contributions would be calculated as follows:
Contribution of Employee A = 20%
Contribution of Employee B= 10%
Contribution of Employee C= 70%
This analysis will help in understanding individual contributions to
the company’s sales, enabling better performance reviews and
incentive distributions
3. For each year, show how the total sales deviated from the target
sales. This analysis will help in assessing the performance relative
to the set goals, identifying areas for improvement
4. The client wants to group colours into categories and analyse the
sum of sales for each category. The categories are defined as
follows:
Group A: Blue, Black, N/A
Group B: Multi, Red, Silver
Group C: Silver/Black, White, Yellow

Last Updated - 4th August, 2024