Lab 2 Preparation
In this lab you will work with file ABC_Sales_data (The path
to the file will be shown in class)
Follow the steps below to design visualizations and answer
analysis questions
Part 1
- When you open the file ABC_Sales_data, the first
Sheet
is called
Main. On Main Sheet, create SIX List Boxes showing the following fields:
- CategoryName
- CompanyName
- ContactName
- SalesPerson
- Year
- Quarter
- Working with List Boxes you created, answer the following analysis
questions:
- Who is
the contact person for the company
Casual Clothing?
- Who is the Sales Person who sold
to the company Casual Clothing?
- In which years did the company
Das Alpen Shoe place orders?
- Which customers (Company Names)
have never
purchased a product from the category
Men's Footwear?
- Create New Sheet "Product Details"
- On Product Details Sheet build the BAR CHART titled "Sales by
Category" with the Dimension:
CategoryName
and Expression:
Sum(LineSalesAmount) and Label the expression Sales. Sort by Y-value Descending. On Style
tab
choose HORIZONTAL orientation. For the Number format setting, select Integer, and
write $ for Symbol, M$ for Million Symbol, K$ for Thousand Symbol
- Find the category with most sales
- Suppose you would like to compare sales between Q1 2014 and
Q1 2013
years for each category. You can use the chart you just created
and perform the following steps:
- On Main Sheet, select Q1 in List Box
Quarters and 2013 in List Box Year
- On Product Details Sheet, right-click the chart, and
select Set Reference. The bar's color changes to a
lighter blue
- On Main Sheet, select Year 2014 and keep Q1 selected.
Now, the darker blue bars represent Sales for Q1 2014 , and
the lighter blue for Q1 2013. Compare the sales -
describe 2014 vs. 2013 sales
- Clear all selecions
- Add the second dimension to the chart you created above to show
sales by CATEGORY AND QUARTER. Modify
the
chart by adding the second dimension, QUARTER. MAKE SURE, QUARTER IS THE SECOND
DIMENSION. In the Style tab select, Stacked. On the Presentation tab, select Show Legend,
and then click the Settings button. The Legend Settings dialog displays.
Select Reverse Order.
- Analyze sales by Quarter for each category. Try to describe the
sales pattern.
- Create additional bar chart titled: "Top 5 Products" showing top
5 selling products (use Dimension
Limits tab)
- Create additional bar chart titled: "Bottom 5 Products (use
Dimension
Limits tab and clear Supress Zero-values on Presentation tab to show products that have never been
sold)showing
bottom 5 products.
Part 2
- To show Ratio of Sales between Divisions,
design a
Pie
Chart titled "Sales by Division" using
Dimension:
DivisionName, Expression Sum(LineSalesAmount). Label this expression Sales and select
Relative to display relative values. In Presentation Tab, select Show Numbers in Legend
- Answer the following analysis question: write the ratio of sales
for each division. Find the division
with most sales.
- Build the Line Chart to show the Order Trend. Use Dimension: Month,
and Expression: Count(Distinct
OrderID). Sort Month by Numeric Value in Ascending order. On Axes tab choose Show Grid for Dimension and
Expression
- Write 2-3 analysis questions of your choice and answer your
questions.
- Add additional dimension to the line chart,
CategoryName and compare trends between
different categories.