Part I - Do together in class -
Bibitor, LLC asked you to complete due diligence on their wine and spirits business. Bibitor has 79 retail locations with approximately $441 million in sales. Their CFO is at the forefront of data analytics and created a Storyline the company uses for making decisions about their stores. To facilitate the investigation, you have Bibitor's sales file with more than 12 million records and their purchase price file with the cost of each inventory item.
Traditional spreadsheets cannot process all of the data, requiring the use of Tableau to complete your due diligence.
Prior to using Tableau, the CFO provides you with two Worksheets from their Sales
Profitability Storyline for the 12-months ended June 30, 2016.
Required - Do together in Class.
1. Using these two Dashboards, describe Sales and Cost of Goods Sold (COGS).
· The total number of stores
· Total Sales and Cost of Goods Sold for the company
· The average and median Sales and Cost of Goods Sold per store
· The 5 largest and smallest stores based on Sales and Cost of Goods Sold
· Are the 5 largest/smallest stores based on Sales the same as the 5 largest/smallest based on Cost of Goods Sold? Would you expect them to be the same? What could cause differences?
Part II - To do on your own and submit for a grade:
Close the Tableau desktop you are working on. And then reopen Tableau and connect to the Sales Final csv file. You will not need the PurchasePrice data / join for part two.
The Bibitor, LLC CFO wants some additional 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, popular bottle sizes for wine and spirits, and she is also interested in certain information at the Store level of detail.
For each question below you will start a new worksheet in your Tableau Workbook. You will submit your tableau workbook in eLearning.
Required:
Assist the CFO in creating Worksheets and Dashboards to answer these questions:
Q1: Worksheet 1. What is the total 2016 Sales Dollars of wine and spirits?
Hint 1: Use the Classification (1=Spirits and 2=Wine) and the Sales Dollars to separate the sales based on wine and spirits. Convert the data into a TreeMap (Show Me Tab) with labels. Name the worksheet YOUR NAME Q1.
Q2: Worksheet 2. What is the most popular Size for wine and for spirits based on 2016 Sales Dollars and Sales Quantity (include the total Sales Dollars and Quantity in your answer) for those sizes that sell > $1MN? Name the worksheet YOUR NAME Q2.
Question 3: Which Stores have the highest and lowest average 2015 sales price for wine and spirits? What is the overall average sales price. For each type, specify the store # and location name. Display the filter card, to allow for an easy toggle between Spirit and Wine. Add an average line. Name the worksheet YOUR NAME Q3.
Question 4: What seasons/months are sales dollars the highest and lowest for a specified date range? (Only have data from October 2015 - March 2016) Name the worksheet YOUR NAME Q4.