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

  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:

  2. Working with List Boxes you created, answer the following analysis questions:
    1. Who is the contact person for the company Casual Clothing?
    2. Who is the Sales Person who sold to the company Casual Clothing?
    3. In which years did the company Das Alpen Shoe place orders?
    4. Which customers (Company Names) have never purchased a product from the category Men's Footwear?

  3. Create New Sheet "Product Details"

  4. 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

  5. Find the category with most sales

  6. 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:
    1. On Main Sheet, select Q1 in List Box Quarters and 2013 in List Box Year

    2. On Product Details Sheet, right-click the chart, and select Set Reference. The bar's color changes to a lighter blue
    3. 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
    4. Clear all selecions

  7. 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.

  8. Analyze sales by Quarter for each category. Try to describe the sales pattern.

  9. Create additional bar chart titled: "Top 5 Products" showing top 5 selling products (use Dimension Limits tab)

  10. 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

  1. 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

  2. Answer the following analysis question: write the ratio of sales for each division. Find the division with most sales.

  3. 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

  4. Write 2-3 analysis questions of your choice and answer your questions.

  5. Add additional dimension to the line chart, CategoryName and compare trends between different categories.