Tableau Assignment. BIBITOR, LLC DATASET SUPPORTING MATERIA

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Tableau Assignment.  

BIBITOR, LLC DATASET SUPPORTING MATERIAL

Store Profitability Analysis of June 30, 2019 Bibitor, LLC. Incorporating

Tableau.

AUTHORS:

William Goldman, Northeastern University

Charles W. Bame-Aldred, Northeastern University

Matt Atherton, Northeastern University

Tiffany Arredondo, Northeastern University

Copyright © 2019 William Goldman, Charles Bame-Aldred, Matt Atherton, Tiffany

Arredondo (the Authors) and the HUB of Analytics Education All rights reserved. For

Educational Purposes Only. No part of the Bibitor, LLC dataset nor the supporting

materials may be reproduced, distributed, or transmitted in any form or by any means,

including photocopying, recording, or other electronic or mechanical methods, without the

prior written permission of the Authors or the HUB of Analytics Education, except in the

case of brief quotations embodied in critical reviews and certain other noncommercial uses

permitted by copyright law. Indirect use for commercial purposes (i.e., materials referencing

or using these materials) is also prohibited, without the prior written permission of Charles

Bame-Aldred or the HUB of Analytics Education. For permission requests, email Charles

Bame-Aldred, at c.bamealdred@HUBae.org.

1

KEY TABLEAU TERMS:

Dimension: Fields that are discrete categorical information (should not be aggregated).

E.g. Store number/location.

Measure: Fields that contain quantitative information (can be aggregated)

Pill: Represents a variable that can be moved to create a field in the view

Worksheet: A sheet where you build views of your data by dragging fields onto shelves.

Dashboard: A combination of several views arranged on a single page. Use dashboards to

compare and monitor a variety of data simultaneously.

Storyline: A sheet that contains a sequence of views or dashboards that work together to

convey information.

Treemap: A treemap displays data using nested rectangles whose area is proportional to

the data it represents.

Calculated Field: Under the Analysis Tab in Tableau, the analyst can create new

variables.

Additional terms can be found using the Tableau glossary

2

Part I ( Difficulty: Beginner)

Bibitor, LLC asked you to complete due diligence on their wine and spirits business. Bibitor

has 79 retail locations with approximately $484 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 13 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

Dashboards from their Sales Profitability Storyline for the 12-months ended June 30,

2019. The Company uses a different visualization software package. Your

visualization will look different, but the data is the same

Summary Data Amount

Count 79

Sum 483,891,954

Average 6,125,215

Minimum 455,566

Maximum 24,111,751

Median 4,172,625

3

Summary Data Amount

Count 79

Sum 347,412,594

Average 4,397,628

Minimum 326,143

Maximum 17,187,435

Median 3,024,716

4

Required:

1. Using these two Dashboards, describe Sales and Cost of Goods Sold (COGS) in

a short memo. In your memo, include a discussion about:

• 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?

2. Using Tableau, recreate the first Dashboard (Sales by Store).

Here are some hints:

• Verify your sales total ($483,891,954) matches Bibitor’s Dashboard , Sales by Store.

• Convert the variable Store into a Dimension and the variable Sales Dollars a

Measure .

• To change Store to a Dimension , drag and drop the Store pill into the Dimension

section on Tableau.

• Using the Analytics tab in Tableau, show the average line and median line on the

graph.

• Sort the Store variable from smallest to largest.

• Under the Worksheet tab in Tableau, show the Summary .

5

Part II ( Difficulty : Beginner/Intermediate)

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 the most popular vendor for wine and spirits. She is also

interested in certain information at the Store level of detail.

Required:

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?

Hint: Use the Classification and Sales Dollars to separate the sales based on wine

and spirits. Convert the data into a Pie Chart (Show Me Tab) and the Analysis

Tab to convert data to percentages.

• What is the most popular Size for wine and for spirits based on Sales Dollars and

Quantity (include the total Sales Dollars and Quantity in your answer)? Provide

the CFO with some reasons why this is important for managing the business.

• Who is the most popular Vendor for wine and for spirits based on Sales Dollars and

Quantity (include the total Sales Dollars and Quantity in your answer)? Provide

the CFO with some reasons why this is important for managing the business.

• Which Stores have the highest and lowest weighted average sales price for wine and

spirits? How does the weighted average sales price compare to a simple average of

Sales Prices for SKUs (Brands) in each Store? What does a large difference between

the simple average and the weighted average mean?

Hint: Create a Calculated Field (Analysis Tab) and label it Average Sales

Price. To accomplish this, drag the Sales Dollars pill divided by Quantity pill.

You should see the Average Sales Price pill. Drag the Average Sales Price pill

into columns and convert the variable from “SUM” to “AVG” by using the

Dropdown Menu on the Pill . Drag the Classification pill into the Filter Card to

help you determine the Average Sales Price for wine versus spirits.

• What seasons/months are sales the highest and lowest? Provide the CFO with some

reasons why this is important for managing the business.

• If you were the CEO or CFO, what other data/variables would you want to collect to

improve your business and why?

6

Part III ( Difficulty: Intermediate) In order to create the Treemap for the Cost of

Goods Sold (COGS), you will have to link two data files. Tableau will do this

automatically. Start by uploading the sales file (you can use the same workbook as the

previous investigations; however, we always recommend you save your work as you go). Once

the sales file is uploaded, upload the purchase price file. The files should be a center join

based on Brand. Once the files are linked, verify your files uploaded and joined properly.

The total Sales Dollars should be $483,891,954. Verifying your COGS of $347,412,594 will

trickier as we need to create the variable (to be discussed below). To determine the COGS,

you’ll have to create the COGS variable by going to the Analysis Tab and clicking on a

Create a Calculated Field . Drag Purchase Price and then multiply by Sales

Quantity. You should see your new variable under the Measures .

• Once the files are merged, you’ll have to calculate the following variables

– COGS - $347,412,594

– Gross Profit: Gross Profit = Sales - COGS

– Gross Profit %: Gross Profit / Sales

• Create a Scatter Graph comparing each store’s Sales and Gross Profit.

– Does the relationship appear to be linear?

– What is the equation of the trend line? Hint: Use the Analysis Tab and drag it

onto your Worksheet .

– Why wouldn’t all of the stores be exactly on the trend line since each store has

access to the same inventory?

• Create a Worksheet to determine the percentage that each Store contributes to the

overall Gross Profit. Use the Classification variable to filter based on spirits and

wine. Convert your Worksheet into a Stacked Bar Chart . What percent does

store 76 contribute to the overall gross profit? What percent does Store 76 contribute

to Gross Profit based on wine only? Spirits only?

• Individually, which two stores have the highest Gross Profit %? Individually, which

two stores have the lowest Gross Profit %?

• What kind of decisions could be made when examining a store’s contribution to overall

gross profit for the company? What kind of decision could be made when comparing

individual store gross profit percentages?

Part IV (Difficulty: Intermediate)

Write a 1-page summary analyzing your finding regarding the profitability of the stores.

Include recommendations to improve profitability and additional information management

would want to collect to improve the company’s profitability. You can include dashboards as

appendices to support your argument.

7

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of