Question 2 Tableau Question
The Tableau question has four parts. To answer the following questions, you need to download the datasets from the unit site under Content Assessment Resource IMPORTANT Examination Information. The files are named “sales” (sales record for different brands across different stores), “purchase price” (purchase prices for different brands) and “inventory count” (beginning and ending inventory count for different brands across different stores). A brief description of the variables in each data file is included in the Excel file of “dictionary”. You can keep up to two decimal points in your calculation.
Part I involves the use of the “sales” file. For Part II and III, you’ll need to merge the two files of “sales” and “purchase price” files in Tableau. Part IV additionally involves the use of inventory count files, i.e., “inventory count” and its merge with “purchase price”.
For Part II & III, start by uploading the “sales” file to Tableau. Once the “sales” file is uploaded, upload the “purchase price” file. The files should be an inner join based on Brand.
For Part IV, you will perform the inner join for “purchase price” and “inventory count”. The files should be an inner join based on Brand. You can then calculate the beginning inventory value and ending inventory value.
Part I Identifying Popular Products (5+3=8 marks)
The CFO of FirstLiquor Pty (headquartered in the US), asked you to perform analysis using Tableau. She is interested in differences between wine and spirits across the entire organisation including the differences in Sales Dollars and Quantities, popular bottle sizes, and the most popular vendors. She is also interested in some information at the Store level of detail.Assist the CFO by creating Tabs and Dashboards to answer these questions.
1. Determine 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. You can do this by pasting a screenshot of your dashboard below. Provide the CFO with one reason why this is important for managing the business.
2. What months are sales the highest and lowest in? Provide the CFO with one reason why this is important for managing the business.
The highest and lowest sales by month are as follows:
Highest sales by month is on the month of January with sales of $21,311,047 with 1,686,233 units sold.
Lowest sales by month is on the month of March with sales of $157,338 with 314,763 units sold.
This is important in managing in the business as this can give important insight such as:
- when the business is aware of when the sales are lowest, they can make strategies such as promotion, advertising or provide discounts to increase their sales.
Part II Investigating the Profitability (2+6=8 marks)
The CFO of FirstLiquor Pty asked you to analyse brand profitability of the wine and spirits business. FirstLiquor has 78 retail locations throughout the US with approximately $492 million in sales. The CFO wants to have a better understanding of FirstLiquor’s inventory management by investigating the most profitable brands by dollars and percentage. Additionally, the CFO wants to evaluate the inventory management system by examining the days inventory outstanding ratio, and seasonal brands (i.e., the brands that are popular in certain seasons). To carry out the initial investigation, you need to analyse FirstLiquor’s “sales” file with more than 13 million records and their “purchase price” file with the cost of each inventory item.
1. Using Tableau, create a Dashboard for the Top 10 brands in terms of Gross Margin Percentage and Dollars. Paste each dashboard below.
2. Using these two Dashboards, respond to the following questions:
a) Why are the top 5 brands with the highest gross margin in dollars different from the top 5 brands with the highest gross margin percentage?
b) Why are there blank cells in the Gross Margin Percentage by Brand dashboard? Is the gross margin percentage in the Grand Total column of the Gross Margin Percentage by Brand dashboard calculated using a simple average of the quarters or is it a weighted average for the brand? Describe how you determined your answer.
c) Compare the top 5 Brands in the Gross Margin Dollars by Brand Dashboard. What spirit (rum, whiskey, or vodka) appears to be the most important to company profitability based on the top 5 brands? Why is this important to know?
Part III Finding the Popular Brands (3+2+2=7 marks)
After completing your analysis, the CFO wants to examine the most popular products. Create a Dashboard that isolates the Brands with above average Gross Margin Dollars and Sales Quantity. Once you identify the most popular brands, examine the average Sales Price per unit and the average Gross Margin percentage per unit.
1. For the top 6 brands found in the ‘GM Dollars by Brand Dashboard’ in Part II, identify their Sales Quantity, Gross Margin %, and Sales Price. Paste a screenshot of your dashboard below.
2. Which brand of Vodka should the sales staff promote? Why?
3. Make recommendations to the management to improve the gross margin of brand 3405. Should sales staff promote selling larger quantities or selling higher margin products? Provide two recommendations for management to improve their margins.
Part IV Comparing Inventory Turnover (2+2+2=6 marks)
Finally, the CFO wants to explore the inventory turnover ratio of the company. To do this, the IT department provided a new inventory file that contains the inventory count by brand at the beginning and the end of the year (“inventory count”). You will also use the Cost of Goods Sold from the merged file in Part II and III to answer the following questions.
1. In Tableau, calculate the Inventory Turnover Ratio (ITR) and the Days Inventory Outstanding (DIO) for the company.
2. Compare the ITR and DIO for Brand 435 and Brand 1130.
3. How does the knowledge of the ITR and DIO based on the previous calculations impact your planning for the business? How could relying on annual averages only lead to poor inventory planning? (Please limit your answer to 100 words.)
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
