1. Casel Ivana's Ice Cream just finished its first six months of manufacturing and selling ice cream. The company has two main product lines, Ice cream cups and ice cream bars, both of which are available in vanilla or chocolate flavors. Ivana Cone, the sole owner of Ivana's Ice Cream, has carefully recorded basic accounting data, including sales transaction data over the company's first two quarters of operations using Microsoft Access. Unfortunately, Ivana lacks formal business and accounting training. You have been hired as a consultant to utilize Tableau data visualization software to analyze the company's data from the first two quarters of operations and to provide a detailed breakdown of sales, product mix, and gross profit by product line and geographic region. In addition, Ivana would like to expand the business in future years and you have been asked to provide insights and recommendations. First, you will need to understand the business and operations. The first step in any data analytics project is to thoroughly understand the data and its current format. When creating visualizations, it is important to consider which type of graph or chart best fits the data. Although there are numerous types of visualizations (graphs and charts) from which to choose, some of the most common include bar charts, line graphs, ple charts, and maps. The data can help you determine which visualizations would help convey the information most effectively. For example, bar charts are useful when data are separated into different categories, while line graphs are useful to show changes in data over time. Pie charts display categories as a portion of a total, and maps represent data geographically and should only be used if the data contains location information. Furthermore, Tableau has the ability to suggest a visualization based on the types of fields that you select; this can be a very convenient feature when learning to create visualizations. Before you examine the Access database Ivana has given you, she would like to tell you a little about the current state of her startup company and the three tables she created to store the company data. She created individual tables for customers, orders, and company products. Currently, Ivana's Ice Cream only sells boxes of ice cream cups and ice cream bars, and each come in either vanilla or chocolate. Below is the unit cost and sales price for each item Ivana's Ice Cream currently sells (see Exhibit 1); ProductD Sales price Ice Cream Cup $255 Ice Cream Cup Vanilla $4.00 Chocolate $3.00 $5.00 Ice Cream Bar $2.85 Product Unit cost Flavor Chocolate $4.00 $2.50 1 2 3 4 Ice Cream Bar $5.00 Vanilla Ehits Produitable Ivana's Ice Cream currently has twenty-two customers. In general, these customers are smaller retailers, including convenience stores and local supermarkets. Currently, Ivana's Ice Cream only sells to retailers in the Midwest states of Missouri and illinois. Below is a list of all of the customers with which Ivana's Ice Cream has done business with since its inception (see Exhibit 2). ID Customer name Address City State ZIP Region CO0101 Grandma's Grocery 6524 Oaktree D Springfield MO 6 S0R2 WMO C00102 K&K Quick Mart 4812 Main St Joliet 60403 ELL This case is adapted from from "Using Tableau to visualize data and drive decision-making" by Jamie Hoelscher and Amanda Mortimer, published in Journal of Accounting Education (2018) Text Predictions: On Accessibility investigate of 3259 wards Voice Editor Reus C00103 C00104 COOTOS C00105 C0010 COOT08 Styles Fresh Foods Sandy't Superstore Midwest Marit Food N Stuff Capital City Grocery John's Supermarket 436 Wide Blvd 1525 Patiei Dr. 6401 Mano Circle 9452 Brideportin 684 First Street 2174 Washington Peoria Rolla Galesburg Joplin Jefferson City Warrensburg IL MO IL MO MO MO 61602 64801 61401 64801 65101 64093 Editing WIL WMO WMO WMO EMO WMO Ave C00109 Mini Mart 3355 President Aurora IL 60503 . COOL10 MC Deli 42 Greetview Blvd Chaupaign IL 61820 E IL C00111 Paul's Groeral Store 5551 Market St Mount Vernon 62564 . C00112 Family Pantry 9RS Wilson Dr. Jacksonville IL 62650 WIL C00113 Garry's Mini Mart 7931 North 1.SE Sedalis MO 65301 W 20 000114 Market Deli 1 Harvard Dr Belleville 62223 WIL C00115 B&B Convenience 160 Grand Ave Columbia 65202 E MO Store C00116 Express Grocery 252 Wichinton St Bloomington IL 61704 E.IT c00117 Bargain Mart 9956 Maple St. Dekalo TL 60115 EL C00118 Quality Foods 112 Henley Jefferson City MO 65109 EMO CO0119 Local's Cupboard 1411 West Park Dr. Fulton MO 63251 C00120 Salem Wholesale 25 Willow D Sale MO 65560 EMIO C00121 Gas N Go 982 Prach St Nashville 11 6236 WIL 000122 Dole Deals 333 Main St. Poplar Blurr MO 63901 EMO Ivana's Ice Cream currently has four sales representatives. For simplicity, Ivana has assigned Levi Adams to western Missouri, Gabby Bennet to eastern Missouri, Russell Bishop to western Illinois, and Carly Marshall to eastern Illinois (see Exhibit 3). Sales representative Region Levi Adarns Western Mincuri Gabb Bennet Eastern Missouri Russell Bishop Western Illinois Carly Marshall Eastern Illinois bit Sales Your instructor will provide you with the Microsoft Access data files to complete the requirements of this case. Your instructor may also assign you specific training videos from the Tableau website (www.tableau.com) to familiarize you with the data visualization software, as well as information related to downloading your free student copy of Tableau. Watching the first few training videos at https://www.tableau.com/learn/training/20203 will help better prepare you for the requirements of this case study Before beginning the following case requirements, download the Microsoft Access file provided to you by your instructor and save it to your desktop or a personal flash drive. Open the Access file and examine each of the tables, as well the data types of each of the fields, and any primary keys identified. You will connect to Ivana's data and analyze the data using Tableau, as described below. 2. Requirements Being new to Tableau, a colleague has provided the following tips to help you begin your analysis of Ivana's Ice Cream using Tableau: 1. Ensure you have downloaded the latest version of Tableau. Visit https://www.tableau.com/academic/students to obtain a free one-year Tableau license for students. I focus Font 2. Ensure you have saved the Ivana's Ice Cream Access file provided by your instructor to your desktop or personal flash drive. 3. Double click on the Tableau Desktop icon to open Tableau. 4. Under Connect to a File," select "Access." S. Browse for the Ivana's Ice Cream file and select "Open"2 6. Drag the Orders table to the open pane. Click on the arrow on the Orders table box in the open pane. Click on "Open". Drag the Customers table inside the Orders table in the open pane. Drag the Products table inside the Orders table in the open pane. Make sure there are lines that connect the Customers and Products tables to the Orders table.' 7. Select Sheet 1 to go to your first worksheet. Before you conduct your own exploratory analysis, Ivana Cone would like to understand some basic information related to her enterprise. Specifically, she would like you to answer the following questions to be included and returned in a formal memo to her (below, your colleague has provided step-by-step instructions to enable you to successfully address each of Ivana's questions in Tableau). The answers to these questions are a requirement for the project. You will enter the answers in "Project 3 Case Answer Submission test in the "Project 3 and Tableau" folder on Blackboard: 1. What is the total quantity sold of chocolate ice cream cups? From the Dimensions pane, drag Flavor and Product to the Rows pane (by holding the Ctrl button down after you select Flavor and before you select the Product dimension, you can drag and drop both dimensions at the same time). From the Measures pane, drag Quantity to the Columns pane. Hover your pointer over the bar for chocolate ice cream cups and record the quantity of chocolate ice cream cups sold during the period. Click on the top of the graph where it is currently titled "Sheet 1." Rename the graph "Quantity Sold by Product." At the bottom of the toolbar where it also shows Sheet 1." right click on the tab and select "Rename Sheet." Rename the sheet "Q1: Quantity Sold by Product. 2. On what date were the most products sold (quantity-wise)? From the Measures pane, drag Quantity to the Rows pane. From the Dimensions pane, drag Order Date to the Columns pane. The order date is being aggregated at the yearly level. To disaggregate and get a daily sum, click on the+In the YEAR (Order Date) to expand to a quarterly measure. Continue to expand until you get a daily measure. . . . . . 2 you are unable to connect to your database in Tableau, please go to https://www.tableau.com/support/drivers and search for Microsoft Access to download the driver necessary to connect to your access file Oring the tables over in order so they may properly join. Notice how the Customer table and Orders table are inner joined on the Customer that in the end Text Predictions on Accessibility: Investigate olds . . a O . . View the line graph to identify the date with the largest quantity of products sold. Name the graph "Quantity Sold by Date. Rename the sheet"O2: Quantity Sold by Date." 3. What is the total dollar amount of sales for vanilla ice cream bars? In your new sheet, create a new calculated field, select "Analysis" from the tool bar, and then select "Create Calculated field." Name the newly calculated field "Sales Total." The sales total should be calculated by multiplying Quantity by Sales Price. (Sales Price] [Quantity). Drag Quantity into the box, enter *** and then drag Sales Price into the box. Notice you are told the calculation is valid." Select "OK." From the Dimensions pane, drag Flavor and Product to the Rows pane. From the Measures pane, drag your newly created field, Sales Total, to the Columns pane. Select the bar for vanilla ice cream bars to examine the sales total for this product line. Name the graph "Sales by Product." Rename the sheet Sales by Product." 4. Which item has the highest sales for the period? What is the amount of sales for that item? This answer can be determined by examining the "Sales by Product" visualization. Document which item has the highest dollar sales for the period and what the amount of sales were for that item. 5. Which flavor has the lowest sales for the period? What is the amount of sales for that flavor? This answer can also be determined by examining the "Sales by Product" visualization. Right click the tab for this worksheet and click "Duplicate". As this question only asks about flavors, and not a specific product, remove Product from the Rows pane by dragging and dropping Product back into the Dimensions pane. To make the visualizations accurately color coded, drag Flavor from the Dimension pane and drop it on Color in the Marks pane. o click on Color and then select "Edit Colors." Make chocolate brown and vanilla an off-white color. Select "OK" Identify which flavor has the lowest sales for the period and the amount of sales for that flavor. Rename the visualization and the sheet "as: Sales by Flavor." 6. How much of the chocolate flavor sales are attributed to sales of ice cream bars? . . 0 . This answer can be determined by examining the "Sales by Flavor" sheet. Make another duplicate sheet. From the Dimensions tab, select Product and drag and drop it on Color in the Marks pane. Vi PHHM nrhy hn quan tan ch M 8HPT Hn TP PHI | artHH 1 em trai 11 tran thi THPT D Focus . . . . You will now see how much of each flavor's sales are attributable to which product. Hover over the chocolate bar to determine how much of the chocolate flavor sales are attributed to the sale of ice cream bars. Rename the visualization and the sheet "Q6: Sales by Flavor and Product. 7. Which graph type would be recommended to show sales by day of the week? Which day of the week has the highest dollar amount of sales? What is the total dollar value of sales on that day of the week? In a new sheet, select Sales Total from the Measures pane, Ctrl and Day of the Week from the Dimensions Pane. From the Show Me toolbar, see the variety of visualizations that can be used, but note which chart is recommended (bordered) to visualize your specific data. Double click on the suggested visualization type Verify the Sales Total measure was brought to the Columns pane and the Day of Week dimension was brought to the Rows pane to illustrate your visualization. Examine which day of the week has the highest dollar amount of sales. Rename the graph and sheet "07: Sales by Day." 8. Which sales representative has the lowest dollar amount of sales on Mondays? What is the total dollar amount of that sales representative on Mondays? This answer can be determined by examining the "Sales by Day" sheet. Make a duplicate sheet. From the Dimensions pane, drag Sales Representative to the Rows pane. Examine which sales representative has the lowest dollar amount of sales on Mondays. Rename the graph and sheet "Q8: Sales Rep Sales by Day." 9. What are the total sales for the period? In a new sheet, drag Sales Total from the Measures tab to the Columns pane." Examine total sales for the period. Rename the graph and sheet "09: Total Sales." 10. Which sales representative has the highest dollar amount of sales? What was the dollar amount? Make a duplicate of "09: Total Sales". In the same sheet change the chart type from Automatic to Ple in the Marks card. Drag Sales Total from the Columns pane to Angle in the Marks card. Drag Sales Representative from the Dimensions pane to Color in the Marks card. For reference, in the Marks card, click on label. Select "Show Label and under "Marks to Label, select "All" to show the total sales revenue for each sales representative. Examine which sales representative has the highest dollar amount of sales. Rename the graph and sheet "010: Sales by Rep." 11. What is the total gross profit for Ivana's Ice Cream? In a new sheet, create a new calculated field. . . . . . . . wordt EN Ten Pedom On x Accesibility Investigate 3 . . . . o COGS=[cost] [Quantity] Select "OK" Create another calculated field. o GP=[Sales Total]-[COGS] Drag your newly created measure, GP, to the Columns pane. Examine the total gross profit for Ivana's Ice Cream. Rename the graph and sheet"Q11: Gross Profit" 12. What is the average gross profit among all sales representatives? Duplicate "011: Gross Profit" From the Dimensions pane, drag and drop Sales Representative into the Rows pane." To determine the average gross profit per sales representative, select the Analytics tab and drag and drop the average line into the Graphic pane. Examine the average gross profit per sales representative. Rename the graph and sheet "212: Average Gross Profit per Sales Rep." 13. Which geographic region has the least customers? If Tableau does not already recognize the ZIP code field as a geographical field (shown by the globe icon to the left of the dimension), you will have to assign a geographic role. Click on the down arrow next to ZIP code in the Dimensions pane. Hover your mouse over Geographic Role, and then select "ZIP Code/Postal Code." In a new sheet, hold down Ctrl while selecting Latitude (generated) and Longitude (generated) from the Measures pane and ZIP Code from the Dimensions pane. Select the recommended graph from the Show Me toolbar. Drag Region from the Dimensions pane and drop it on Color in the Marks card. Rename the graph "Q13: Customers by Region." Examine the customers by geographic region. 14. Which ZIP code generates the highest gross profit? Duplicate "013: Customers by Region In the same sheet, drag Gross Profit from the Measures pane to Size on the Marks card. Identify the ZIP code which has the largest gross profit. Rename the graph "Q14: Zip Code Gross Margin" . . Additional case requirements (the answers to these questions also need to be submitted in the test on Blackboard) Before you updated Ivana with the results of your previous analysis, Ivana emails you with a few additional questions. Unfortunately, your colleague who is a Tableau expert is on vacation and unable to provide you with guidance for the follow-up analysis. Address each of the following additional questions posed by Ivana: 15. Ivana: "I want to make sure we treat our biggest customers the best, as it is vital we do not lose their business. I might consider offering discounts to our best customers in the future. Who is our biggest customer (in terms of sales revenue), and what is the total revenue from that customer so far? Also, what is the total gross profit from that customer?" Open a new sheet and create a graph or graphs to show the answers (make sure to name these graphs appropriately) 16. "I want to make sure my best sales representatives are rewarded. Since Ivana's Ice Cream opened, 1 have been paying my sales representatives a flat rate, but I need to start paying them based on their production levels. Who sold the most units in September 2016? How many units did that sales representative sell?" Open a new sheet and create a graph or graphs to show the answers (make sure to name these graphs appropriately) . . Required: 1. Follow the directions and answer each of the questions posed in the case. You will submit your answers to each of the questions to the Blackboard test in the "Project 3 and Tableau" fold on Blackboard. Please be aware that several of the questions have multiple parts, and each has a separate question to answer in the test. Each of the basic questions (Questions 1-14) are each worth 1 point for a total of 20 points Each of the advanced questions (Questions 15-16) are each worth 2.5 points. You can only submit the test ONCE, so please make sure to have all of your answers ready to submit (you can save and go back to the test later, but you can only submit it for grading once). 2. Follow the directions and create the necessary graphs in Tableau Create and appropriately name each worksheet as directed in the instructions Submit the Tableau Workbook file (with file extension "twb") to the "Tableau Workbook Submission" assignment in the "Project 3 and Tableau" folder on Blackboard. These Tableau graphs are worth 20 points total Total project is worth 50 points . . . . 1. Casel Ivana's Ice Cream just finished its first six months of manufacturing and selling ice cream. The company has two main product lines, Ice cream cups and ice cream bars, both of which are available in vanilla or chocolate flavors. Ivana Cone, the sole owner of Ivana's Ice Cream, has carefully recorded basic accounting data, including sales transaction data over the company's first two quarters of operations using Microsoft Access. Unfortunately, Ivana lacks formal business and accounting training. You have been hired as a consultant to utilize Tableau data visualization software to analyze the company's data from the first two quarters of operations and to provide a detailed breakdown of sales, product mix, and gross profit by product line and geographic region. In addition, Ivana would like to expand the business in future years and you have been asked to provide insights and recommendations. First, you will need to understand the business and operations. The first step in any data analytics project is to thoroughly understand the data and its current format. When creating visualizations, it is important to consider which type of graph or chart best fits the data. Although there are numerous types of visualizations (graphs and charts) from which to choose, some of the most common include bar charts, line graphs, ple charts, and maps. The data can help you determine which visualizations would help convey the information most effectively. For example, bar charts are useful when data are separated into different categories, while line graphs are useful to show changes in data over time. Pie charts display categories as a portion of a total, and maps represent data geographically and should only be used if the data contains location information. Furthermore, Tableau has the ability to suggest a visualization based on the types of fields that you select; this can be a very convenient feature when learning to create visualizations. Before you examine the Access database Ivana has given you, she would like to tell you a little about the current state of her startup company and the three tables she created to store the company data. She created individual tables for customers, orders, and company products. Currently, Ivana's Ice Cream only sells boxes of ice cream cups and ice cream bars, and each come in either vanilla or chocolate. Below is the unit cost and sales price for each item Ivana's Ice Cream currently sells (see Exhibit 1); ProductD Sales price Ice Cream Cup $255 Ice Cream Cup Vanilla $4.00 Chocolate $3.00 $5.00 Ice Cream Bar $2.85 Product Unit cost Flavor Chocolate $4.00 $2.50 1 2 3 4 Ice Cream Bar $5.00 Vanilla Ehits Produitable Ivana's Ice Cream currently has twenty-two customers. In general, these customers are smaller retailers, including convenience stores and local supermarkets. Currently, Ivana's Ice Cream only sells to retailers in the Midwest states of Missouri and illinois. Below is a list of all of the customers with which Ivana's Ice Cream has done business with since its inception (see Exhibit 2). ID Customer name Address City State ZIP Region CO0101 Grandma's Grocery 6524 Oaktree D Springfield MO 6 S0R2 WMO C00102 K&K Quick Mart 4812 Main St Joliet 60403 ELL This case is adapted from from "Using Tableau to visualize data and drive decision-making" by Jamie Hoelscher and Amanda Mortimer, published in Journal of Accounting Education (2018) Text Predictions: On Accessibility investigate of 3259 wards Voice Editor Reus C00103 C00104 COOTOS C00105 C0010 COOT08 Styles Fresh Foods Sandy't Superstore Midwest Marit Food N Stuff Capital City Grocery John's Supermarket 436 Wide Blvd 1525 Patiei Dr. 6401 Mano Circle 9452 Brideportin 684 First Street 2174 Washington Peoria Rolla Galesburg Joplin Jefferson City Warrensburg IL MO IL MO MO MO 61602 64801 61401 64801 65101 64093 Editing WIL WMO WMO WMO EMO WMO Ave C00109 Mini Mart 3355 President Aurora IL 60503 . COOL10 MC Deli 42 Greetview Blvd Chaupaign IL 61820 E IL C00111 Paul's Groeral Store 5551 Market St Mount Vernon 62564 . C00112 Family Pantry 9RS Wilson Dr. Jacksonville IL 62650 WIL C00113 Garry's Mini Mart 7931 North 1.SE Sedalis MO 65301 W 20 000114 Market Deli 1 Harvard Dr Belleville 62223 WIL C00115 B&B Convenience 160 Grand Ave Columbia 65202 E MO Store C00116 Express Grocery 252 Wichinton St Bloomington IL 61704 E.IT c00117 Bargain Mart 9956 Maple St. Dekalo TL 60115 EL C00118 Quality Foods 112 Henley Jefferson City MO 65109 EMO CO0119 Local's Cupboard 1411 West Park Dr. Fulton MO 63251 C00120 Salem Wholesale 25 Willow D Sale MO 65560 EMIO C00121 Gas N Go 982 Prach St Nashville 11 6236 WIL 000122 Dole Deals 333 Main St. Poplar Blurr MO 63901 EMO Ivana's Ice Cream currently has four sales representatives. For simplicity, Ivana has assigned Levi Adams to western Missouri, Gabby Bennet to eastern Missouri, Russell Bishop to western Illinois, and Carly Marshall to eastern Illinois (see Exhibit 3). Sales representative Region Levi Adarns Western Mincuri Gabb Bennet Eastern Missouri Russell Bishop Western Illinois Carly Marshall Eastern Illinois bit Sales Your instructor will provide you with the Microsoft Access data files to complete the requirements of this case. Your instructor may also assign you specific training videos from the Tableau website (www.tableau.com) to familiarize you with the data visualization software, as well as information related to downloading your free student copy of Tableau. Watching the first few training videos at https://www.tableau.com/learn/training/20203 will help better prepare you for the requirements of this case study Before beginning the following case requirements, download the Microsoft Access file provided to you by your instructor and save it to your desktop or a personal flash drive. Open the Access file and examine each of the tables, as well the data types of each of the fields, and any primary keys identified. You will connect to Ivana's data and analyze the data using Tableau, as described below. 2. Requirements Being new to Tableau, a colleague has provided the following tips to help you begin your analysis of Ivana's Ice Cream using Tableau: 1. Ensure you have downloaded the latest version of Tableau. Visit https://www.tableau.com/academic/students to obtain a free one-year Tableau license for students. I focus Font 2. Ensure you have saved the Ivana's Ice Cream Access file provided by your instructor to your desktop or personal flash drive. 3. Double click on the Tableau Desktop icon to open Tableau. 4. Under Connect to a File," select "Access." S. Browse for the Ivana's Ice Cream file and select "Open"2 6. Drag the Orders table to the open pane. Click on the arrow on the Orders table box in the open pane. Click on "Open". Drag the Customers table inside the Orders table in the open pane. Drag the Products table inside the Orders table in the open pane. Make sure there are lines that connect the Customers and Products tables to the Orders table.' 7. Select Sheet 1 to go to your first worksheet. Before you conduct your own exploratory analysis, Ivana Cone would like to understand some basic information related to her enterprise. Specifically, she would like you to answer the following questions to be included and returned in a formal memo to her (below, your colleague has provided step-by-step instructions to enable you to successfully address each of Ivana's questions in Tableau). The answers to these questions are a requirement for the project. You will enter the answers in "Project 3 Case Answer Submission test in the "Project 3 and Tableau" folder on Blackboard: 1. What is the total quantity sold of chocolate ice cream cups? From the Dimensions pane, drag Flavor and Product to the Rows pane (by holding the Ctrl button down after you select Flavor and before you select the Product dimension, you can drag and drop both dimensions at the same time). From the Measures pane, drag Quantity to the Columns pane. Hover your pointer over the bar for chocolate ice cream cups and record the quantity of chocolate ice cream cups sold during the period. Click on the top of the graph where it is currently titled "Sheet 1." Rename the graph "Quantity Sold by Product." At the bottom of the toolbar where it also shows Sheet 1." right click on the tab and select "Rename Sheet." Rename the sheet "Q1: Quantity Sold by Product. 2. On what date were the most products sold (quantity-wise)? From the Measures pane, drag Quantity to the Rows pane. From the Dimensions pane, drag Order Date to the Columns pane. The order date is being aggregated at the yearly level. To disaggregate and get a daily sum, click on the+In the YEAR (Order Date) to expand to a quarterly measure. Continue to expand until you get a daily measure. . . . . . 2 you are unable to connect to your database in Tableau, please go to https://www.tableau.com/support/drivers and search for Microsoft Access to download the driver necessary to connect to your access file Oring the tables over in order so they may properly join. Notice how the Customer table and Orders table are inner joined on the Customer that in the end Text Predictions on Accessibility: Investigate olds . . a O . . View the line graph to identify the date with the largest quantity of products sold. Name the graph "Quantity Sold by Date. Rename the sheet"O2: Quantity Sold by Date." 3. What is the total dollar amount of sales for vanilla ice cream bars? In your new sheet, create a new calculated field, select "Analysis" from the tool bar, and then select "Create Calculated field." Name the newly calculated field "Sales Total." The sales total should be calculated by multiplying Quantity by Sales Price. (Sales Price] [Quantity). Drag Quantity into the box, enter *** and then drag Sales Price into the box. Notice you are told the calculation is valid." Select "OK." From the Dimensions pane, drag Flavor and Product to the Rows pane. From the Measures pane, drag your newly created field, Sales Total, to the Columns pane. Select the bar for vanilla ice cream bars to examine the sales total for this product line. Name the graph "Sales by Product." Rename the sheet Sales by Product." 4. Which item has the highest sales for the period? What is the amount of sales for that item? This answer can be determined by examining the "Sales by Product" visualization. Document which item has the highest dollar sales for the period and what the amount of sales were for that item. 5. Which flavor has the lowest sales for the period? What is the amount of sales for that flavor? This answer can also be determined by examining the "Sales by Product" visualization. Right click the tab for this worksheet and click "Duplicate". As this question only asks about flavors, and not a specific product, remove Product from the Rows pane by dragging and dropping Product back into the Dimensions pane. To make the visualizations accurately color coded, drag Flavor from the Dimension pane and drop it on Color in the Marks pane. o click on Color and then select "Edit Colors." Make chocolate brown and vanilla an off-white color. Select "OK" Identify which flavor has the lowest sales for the period and the amount of sales for that flavor. Rename the visualization and the sheet "as: Sales by Flavor." 6. How much of the chocolate flavor sales are attributed to sales of ice cream bars? . . 0 . This answer can be determined by examining the "Sales by Flavor" sheet. Make another duplicate sheet. From the Dimensions tab, select Product and drag and drop it on Color in the Marks pane. Vi PHHM nrhy hn quan tan ch M 8HPT Hn TP PHI | artHH 1 em trai 11 tran thi THPT D Focus . . . . You will now see how much of each flavor's sales are attributable to which product. Hover over the chocolate bar to determine how much of the chocolate flavor sales are attributed to the sale of ice cream bars. Rename the visualization and the sheet "Q6: Sales by Flavor and Product. 7. Which graph type would be recommended to show sales by day of the week? Which day of the week has the highest dollar amount of sales? What is the total dollar value of sales on that day of the week? In a new sheet, select Sales Total from the Measures pane, Ctrl and Day of the Week from the Dimensions Pane. From the Show Me toolbar, see the variety of visualizations that can be used, but note which chart is recommended (bordered) to visualize your specific data. Double click on the suggested visualization type Verify the Sales Total measure was brought to the Columns pane and the Day of Week dimension was brought to the Rows pane to illustrate your visualization. Examine which day of the week has the highest dollar amount of sales. Rename the graph and sheet "07: Sales by Day." 8. Which sales representative has the lowest dollar amount of sales on Mondays? What is the total dollar amount of that sales representative on Mondays? This answer can be determined by examining the "Sales by Day" sheet. Make a duplicate sheet. From the Dimensions pane, drag Sales Representative to the Rows pane. Examine which sales representative has the lowest dollar amount of sales on Mondays. Rename the graph and sheet "Q8: Sales Rep Sales by Day." 9. What are the total sales for the period? In a new sheet, drag Sales Total from the Measures tab to the Columns pane." Examine total sales for the period. Rename the graph and sheet "09: Total Sales." 10. Which sales representative has the highest dollar amount of sales? What was the dollar amount? Make a duplicate of "09: Total Sales". In the same sheet change the chart type from Automatic to Ple in the Marks card. Drag Sales Total from the Columns pane to Angle in the Marks card. Drag Sales Representative from the Dimensions pane to Color in the Marks card. For reference, in the Marks card, click on label. Select "Show Label and under "Marks to Label, select "All" to show the total sales revenue for each sales representative. Examine which sales representative has the highest dollar amount of sales. Rename the graph and sheet "010: Sales by Rep." 11. What is the total gross profit for Ivana's Ice Cream? In a new sheet, create a new calculated field. . . . . . . . wordt EN Ten Pedom On x Accesibility Investigate 3 . . . . o COGS=[cost] [Quantity] Select "OK" Create another calculated field. o GP=[Sales Total]-[COGS] Drag your newly created measure, GP, to the Columns pane. Examine the total gross profit for Ivana's Ice Cream. Rename the graph and sheet"Q11: Gross Profit" 12. What is the average gross profit among all sales representatives? Duplicate "011: Gross Profit" From the Dimensions pane, drag and drop Sales Representative into the Rows pane." To determine the average gross profit per sales representative, select the Analytics tab and drag and drop the average line into the Graphic pane. Examine the average gross profit per sales representative. Rename the graph and sheet "212: Average Gross Profit per Sales Rep." 13. Which geographic region has the least customers? If Tableau does not already recognize the ZIP code field as a geographical field (shown by the globe icon to the left of the dimension), you will have to assign a geographic role. Click on the down arrow next to ZIP code in the Dimensions pane. Hover your mouse over Geographic Role, and then select "ZIP Code/Postal Code." In a new sheet, hold down Ctrl while selecting Latitude (generated) and Longitude (generated) from the Measures pane and ZIP Code from the Dimensions pane. Select the recommended graph from the Show Me toolbar. Drag Region from the Dimensions pane and drop it on Color in the Marks card. Rename the graph "Q13: Customers by Region." Examine the customers by geographic region. 14. Which ZIP code generates the highest gross profit? Duplicate "013: Customers by Region In the same sheet, drag Gross Profit from the Measures pane to Size on the Marks card. Identify the ZIP code which has the largest gross profit. Rename the graph "Q14: Zip Code Gross Margin" . . Additional case requirements (the answers to these questions also need to be submitted in the test on Blackboard) Before you updated Ivana with the results of your previous analysis, Ivana emails you with a few additional questions. Unfortunately, your colleague who is a Tableau expert is on vacation and unable to provide you with guidance for the follow-up analysis. Address each of the following additional questions posed by Ivana: 15. Ivana: "I want to make sure we treat our biggest customers the best, as it is vital we do not lose their business. I might consider offering discounts to our best customers in the future. Who is our biggest customer (in terms of sales revenue), and what is the total revenue from that customer so far? Also, what is the total gross profit from that customer?" Open a new sheet and create a graph or graphs to show the answers (make sure to name these graphs appropriately) 16. "I want to make sure my best sales representatives are rewarded. Since Ivana's Ice Cream opened, 1 have been paying my sales representatives a flat rate, but I need to start paying them based on their production levels. Who sold the most units in September 2016? How many units did that sales representative sell?" Open a new sheet and create a graph or graphs to show the answers (make sure to name these graphs appropriately) . . Required: 1. Follow the directions and answer each of the questions posed in the case. You will submit your answers to each of the questions to the Blackboard test in the "Project 3 and Tableau" fold on Blackboard. Please be aware that several of the questions have multiple parts, and each has a separate question to answer in the test. Each of the basic questions (Questions 1-14) are each worth 1 point for a total of 20 points Each of the advanced questions (Questions 15-16) are each worth 2.5 points. You can only submit the test ONCE, so please make sure to have all of your answers ready to submit (you can save and go back to the test later, but you can only submit it for grading once). 2. Follow the directions and create the necessary graphs in Tableau Create and appropriately name each worksheet as directed in the instructions Submit the Tableau Workbook file (with file extension "twb") to the "Tableau Workbook Submission" assignment in the "Project 3 and Tableau" folder on Blackboard. These Tableau graphs are worth 20 points total Total project is worth 50 points