Lab 2

In this lab you will work with file ABC_Sales_data (make sure you are openning an ORIGINAL FILE, not the modified one you created on Wed)

Follow the steps below to design visualizations and answer analysis questions

Submit the application file - send it by e-mail to ykortsarts@mail.widener.edu and the answers to the analysis questions (could be written on paper)

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
   • SalesPerson
   • Year
   • Quarter
   • Month

2. Working with List Boxes you created, answer the following analysis questions:
   1. What categories sells Company Bobby Socks? Who is the Sales Person who sold to this Company? In what year, quarter and month this Company placed orders?
   2. Which customers (Company Names) have never purchased a product from the category Baby Clothes

3. Create New Sheet "Product Details"

4. On Product Details Sheet build the BAR CHART titled "Sales by Product" with the Dimension: ProductName and Expression: Sum(LineSalesAmount) and Label the expression Sales. Sort by Y-value Descending. On Presentation Tab, Enable X-Axis Scrollbar. For the Number format setting, select Integer, and write $ for Symbol, M$ for Million Symbol, K$ for Thousand Symbol

5. Find the product from the Category Children's Clothes with most sales (Use an appropriate List Box on Main Sheet to make proper selection)

6. Add the second dimension to the chart you created above to show sales by Product AND Year. Modify the chart by adding the second dimension, Year. MAKE SURE, Year 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.

7. Analyze sales by Year for Top 3 products from Men's Footwear category. Try to describe the sales pattern.

8. To show Ratio of Sales between Categories, design a Pie Chart titled "Sales by Category" using Dimension: CategoryName, Expression Sum(LineSalesAmount). Label this expression Sales and select Relative to display relative values. In Presentation Tab, select Show Numbers in Legend

9. Answer the following analysis question: write the name and the ratio of sales for Category with most sales and for Category with least sales.

10. Build the Line Chart to show the Order Trend. Use Dimension: Year, and Expression: Count(Distinct OrderID). Sort Year by Numeric Value in Ascending order. On Axes tab choose Show Grid for Dimension and Expression

11. Add additional dimension to the line chart, CategoryName and compare trends between Baby Clothes and Children's Clothes (Hint: First, select both categories in the List Box CategoryName on the Main Sheet. To select both categories together, first select Baby Clothes, then hold Ctrl Key and select the second category)

12. Write 3 analysis questions of your choice and answer your questions. Use ALL charts you created in this lab.

13. Bonus Question: For the Line Chart Order Trend add the Cyclic Dimension Group that includes: Year, Quarter and Month. Call this group TIME. To create a dimension group: on the Dimensions Tab, select Edit Groups, then New. In Group Name type TIME and select Year, Month and Quarter as your Used Fields Box. Select Cyclic Group. Click OK twice to return to the Dimension Tab. Select Group TIME as your Dimension. Use expression: sum(LineSalesAmount). Observe the result and analyze Order Trend by Month, Year and Quarter