Which Stores have the highest and lowest average sales price for wine and spirits?

/in /by

ASSIGNMENT DETAILS:

The Bibitor, LLC CFO wants some additional Sales analysis using Tableau. She is interested in differences between wine and spirits across the entire organization. She wants to know the differences in Sales Dollars and Quantities between the two categories and popular bottle sizes for wine and spirits. She is also interested in certain information at the Store level of detail.

Required:

Start by connecting to the Data Source attached to this Assignment (Tableau considers a CSV file as a text file). Please note that, once unzipped, this is a fairly large file. Once loaded in Tableau, processing time should be minimal — I have limited the Assignment to one months’ worth of data in order to leverage the smallest usable file size.

 

Assist the CFO in creating Worksheets and Dashboards to answer these questions:

  • What is the total Sales Dollars and percentage breakdown of wine and spirits?
    • Let’s start by creating a Pie Chart by Sales Dollars. Use the Classification attribute (1=Spirits and 2=Wine) and the Sales Dollars to separate the sales based on wine and spirits. Convert the data into a Pie Chart (Show Me Tab -> Pie Chart)
    • It will be hard for the CFO to understand what the ‘1’ and ‘2’ represent for the Classification attribute. To make it easier, let’s create an Alias for each value. Click on the drop-down arrow for the Classification pill in the Dimensions section. Click on ‘Aliases…’ In the pop-up window, next to the Member of 1, click in the Value (Alias) column and type ‘Spirits’ (no quote marks). Next to the Member of 2, type ‘Wine’. Click OK
    • The Pie Chart looks nice, but it’s not clear what the dollar value is for each slice — let’s turn on the ‘Label’ Mark. The easiest way to do that is to click on Label in the Marks Card, then put a check mark in the box for ‘Show mark labels’
    • Those labels may be unnecessarily difficult to read — let’s format them so the numbers show a) as currency and b) rounded off to the nearest thousand. Right-click on SUM(sales) {either one} in the Marks card. On the left, you will see a Format window appear. Toward the top of that window, you have an option to click on Axis (default) or Pane — click on Pane. In the ‘Default’ section, click the icon next to the Numbers field in order to get the Numbers formatting pop-up. Currency (custom) will allow you to show a $ sign, remove decimals, and change the Display Units to Thousands
    • Your Pie Chart is probably a bit small and hard to see; the Labels also might overlap the pie slices, making them difficult to read — use Ctrl + Shift + B (PC) / CMD + Shift + B (Mac) several times to make the pie chart bigger (notice the labels also move off the pie slices and are easier to read too). An alternate is to click the drop-down arrow next to the word ‘Standard’ in the ribbon — choose the ‘Entire View’ option instead
    • Update the Tab name with something meaningful (this will update your visualization Header as well)
    • Hide the ‘Show Me’ options (if they are visible, just click on the words ‘Show Me’), so you can see your legend for the pie chart
    • TAKE A SCREENSHOT and label is BIB2-A; paste it into a Word doc that you will use to submit this assignment
    • Before we leave this worksheet, let’s use the Analysis Tab (menu option) to convert the sales data into percentages. Click on Analysis, hover over ‘Percentage of’ and select ‘Column’. This will give the CFO a clear sense of the relative proportion of Spirits vs. Wine sales
    • TAKE A SCREENSHOT and label is BIB2-B; paste it into a Word doc that you will use to submit this assignment.
    • Below both screenshots, affirmatively answer the CFO’s question — what is the total Sales Dollars and percentage allocation of wine vs. spirits?
  • What is the most popular Size for wine and for spirits based on Sales Dollars and Quantity?
    • Create a new worksheet for this new visualization
    • You are focused on Classification and package Size, so start by dragging these two Dimensions to the Columns shelf. Order matters!
    • Let’s start with the Sales Dollars question — drag the Sales Dollars Measure to the Rows shelf. Be sure you are calculating the sum of Sales Dollars
    • Consider what would be the most effective chart type to visualize Sales Dollars by product type and size. If your visualization was not automatically generated to reflect your chosen visualization, be sure to change it via the Show Me options
    • The order of marks in your visualization may not serve to highlight the largest values — it probably needs a sort. Sorting can be a bit tricky in Tableau when you have more than one Dimension featured in your visualization.
      • Start by selecting the Descending Sort icon in the ribbon (just above the Columns shelf). Check out the Spirits classification and the Wine classification … it doesn’t really look like either one is in descending order. That’s because, when using the Descending Sort icon, it considers the TOTAL across dimensions for the measure being sorted.
      • That’s not really what we want, though, because it makes it hard to answer our CFO’s question. Instead, go to the Sales Dollars label along your Y-axis (don’t have Sales Dollars on the Y-axis? You may want to revisit earlier steps). Just above the label (when you hover over it), you will see a little quick sort icon (looks like a graph) — click on that. Your sort order for each classification should make more sense now.
    • You may have quite a bit of distracting information in your chart related to clearly inconsequential package sizes. It would be great to help the CFO focus on the Top 5 package sizes within each product classification. If you did not have two different product classifications, it would be simple enough to create a filter on Sales Dollars and set the filter for ‘Top 5’. Unfortunately, that will achieve the desired effect here. We will accomplish this filtering a little differently:
      • From the Analysis Tab (menu item), select ‘Create Calculated Field’. Rename the field by replacing the ‘Calculation1’ name with the words ‘Top Size’. In the space below the light grey horizontal line, add the following formula: Rank(sum([Sales Price])). Then click OK
      • You’ll find your new field in the Measures list. Drag it to the Filters shelf
      • From the Filter pop-up, click the ‘At Most’ option and set the upper limit to 5
      • Now click OK and see the results of your Top 5 filter by Classification. Hmm – if you ended up with a total of 5 marks across the two Classifications, that’s ok. We can fix it.
      • Hover over the ‘Top Size’ Pill in the Filters shelf, then click on the drop-down arrow. Next hover over the ‘Compute Using’ menu item, then select ‘Pane (across)’ pop-out option
      • Follow the process above to select ‘At Most’ and set the upper limit to 5, click OK, and check your result
    • Be sure to rename the worksheet tab to something meaningful
    • TAKE A SCREENSHOT and label is BIB2-C; paste it into a Word doc that you will use to submit this assignment. Be sure to take note of the total Sales Dollars for the bottle size with the largest value in each Classification
    • Now let’s address the Quantity question — drag the Sales Quantity measure to the Rows shelf. Drag the Sales Dollars pill OFF the Rows shelf. You may have to click the ‘quick sort’ icon again next to Sales Quantity on the Y-axis
    • TAKE A SCREENSHOT and label it BIB2-D; paste it into a Word doc that you will use to submit this assignment. Be sure to take note of the total Sales Quantity for the bottle size with the largest value in each Classification
    • Report out the largest bottle size by Classification for Sales Dollars and for Sales Quantity; report the value/quantity for each. Also provide the CFO with some reasons why this information is important for managing the business
  • Which Stores have the highest and lowest average sales price for wine and spirits?
    • Create a new worksheet for this new visualization
    • Start by creating a Calculated Field – go to the Analysis Tab (menu item) and select Create Calculated Field. Label the field Average Sales Price in the pop-up window. Just below the light grey horizontal line, drag in the Sales Dollars pill, then type in the ‘/’ symbol, then drag in the Quantity pill
    • You should now see the Average Sales Price pill in the Measures listing. Drag the Average Sales Price pill into the Columns shelf and convert it from “SUM” to “AVG” by using the drop-down menu on the Average Sales Price Pill (change Measure(Sum) to Measure(Avg))
    • Now drag the Classification dimension to the Rows shelf
    • We need to look at Store for this analysis, but it currently exists as a Measure. We need it to be a Dimension. So drag the Store pill to the Dimensions listing.
    • Once Store is in your Dimensions listing, drag it to the Rows shelf — order is important here and it should appear AFTER the Classification pill
    • This visualization will best serve your purposes as a horizontal bar chart — if it doesn’t appear this way, work with the Show Me tab to generate such a chart (a vertical bar chart would be acceptable, as well)
    • Be sure to use the quick sort on the axis that has the Avg. Average Sales Price to sort in descending order
    • Again we have a lot of distracting data in our visualization — we can’t even see the Wine classification! Use an approach similar to that used in creating the Top Size calculated field and filter above.
      • This time, create a ‘Top Avg Sales Price’ field using the Rank function, embedding within it the average of Average Sales Price
      • Drag your new ‘Top Avg Sales Price’ field to the Filters shelf
      • Close out of the Filter pop-up menu for the moment
      • Click on the drop-down arrow of the Top Avg Sales Price pill in the Filters shelf, select the Compute Using option of ‘Pane (down)’ (or Pane (across) if you are using the vertical bar chart)
      • Now from the Filter pop-up menu, select At Most – 10 stores, then click OK
    • You’re just about done! Change the worksheet tab name to something meaningful and then …
    • TAKE A SCREENSHOT and label it BIB2-E; paste it into a Word doc that you will use to submit this assignment. Be sure to indicate which store had the highest average sales price for Spirits, which one had the highest average for Wine, and include the average amount for each.
  • The CFO has asked you, in your opinion, what other data/variables would you want to collect to improve the business and why?