Return to que Tableau Instructions: You have available to you an extensive data set that includes detailed financial data for the company's inventory sales. The data set is in the form of an Excel file available to download from Connect, or under Student Resources within the Library tab. There are four Excel files available. The one for use in this case is named "inventory.xlsx. Download this file to the computer, save it, and open it in Tableau. All short training videos can be found here. For this case, you will create several calculations to produce a bar chart and a map of the gross profit to allow you to compare and contrast a company's inventory measurement After you view the training videos and review instructions in Chapters 1-3, follow these steps to create the charts you'll use for this case: 1. Open Tableau and connect to the Excel spreadsheet you downloaded. Click on the Sheet 1 tab, at the bottom of the canvas, to the right of the Data Source at the bottom of the screen. Drag "Region" under "Dimensions to the Columns shelf 2. Drag "status" under "Dimensions to the Filter's card. When the Filter window opens, click the "S" so only the sold items will be viewed. Click OK 3. Click on the "Analysis" tab from the Toolbar at the top of the screen and select "Create Calculated field." A calculation box will pop up. Name the calculation "Total Sales." Drag "Number" under "Dimensions to the Calculations Editor. Type a multiplication sign beside it, then drag "Selling Price" under "Measures" to the Calculations Editor. Make sure the window says that the calculation is valid and click OK. 4. Repeat the process outlined in Instruction 43 and create a calculated field for "Cost of Goods Sold" that consists of "Number" multiplied by "Cost." 5. Repeat the process outlined in Instruction 43 and create a calculated field for "Gross Profit" that consists of "Total Sales" minus "Cost of Goods Sold." 6. From the "Measures" section on the middle left side of the canvas, drag "Total Sales," "Cost of Goods Sold," and "Gross Profit" Into "Rows" shelf. 7. Click the "Show Me" box on right side of the toolbar and select the "side-by-side bars.' 8. Show the labels by clicking on the Label box on the "Marks" card and checking "Show mark labels." Format the labels to Times New Roman, bold, black and 9-point font. 9. Change the title of the sheet to be 'Total Sales, COGS, and Gross Profit by Region" by right-clicking and selecting "Edit title.' Format the title to Times New Roman, bold, black and 15-point font. Change the title of "Sheet 1' to match the sheet title by right-clicking, selecting "Rename" and typing in the new title. 0. Format all the labels on the sheet to Times New Roman, 10-point font, and black. 11. Click on the New Worksheet tab on the lower left ("Sheet 2 should open). 2. Drag "State" from "Dimensions to the Columns shelf. II. LIICK on the New Worksheet lou on the lower on Hel & Should open. 2. Drag "State" from "Dimensions to the Columns shelf 13. Drag "Gross Profit" and "Total Sales" from "Dimensions to the Rows shelf. 14. Click on the "Analysis" tab from the Toolbar at the top of the screen and select "Create Calculated field". Name the calculation field "Gross Profit Ratio by State" by dragging "Sum(Gross Profit)" from the Rows shelf and then dividing by "Sum(Total Sales)" from the Rows shelf to calculate an "aggregated" measure for each state. 15. Remove "Sum(Total Sales)" from the Rows shelf by dragging it back over to "Dimensions". 16. In the "Show Me" box on right side of the toolbar, select the "symbol map". You should see a map of the United States with circles appearing in states with data. Drag "Gross Profit Ratio by State" from "Measures" to the bottom of the "Marks" card. Click on the circle symbol beside the "Gross Profit Ratio by State and select "Color." The dots should now vary in size and color based on the size of the gross profit ratio and gross profit. Enlarge the dots by clicking "Size" on the "Marks" card and adjusting the slide. You can also change the color variation by clicking "Color on the "Marks" card and editing the color. 17. Change the title of the sheet to be "Gross Profit Ratio by State" by right-clicking and selecting "Edit title." Format the title to Times New Roman, bold, black and 15-point font. Change the title of "Sheet 2" to match the sheet title by right-clicking, selecting "Rename" and typing in the new title. 18. Once complete, save the file as "DA8_Your initials.twbx." Required: Based upon what you find, answer the following questions: A. Which of the five regions' operations has the highest gross profit? (Enter your answer in millions rounded to 1 decimal place (i.e.. 5,500,000 should be entered as 5.5).) B. Which of the five regions' operations has the lowbst gross profit? To help determine the relative profitability of each state's sales, get a better state-by-state comparison by analyzing the "map" chart. Once the chart is created, move your cursor to hover above various circles within states in the chart. Notice that an information box appears to reveal the pertinent gross profit and gross profit ratios for that state. C. Which state's operations provides the highest gross profit ratio? (Round your answer to 1 decimal place.) D. Which state's operations provides the lowest gross profit ratio? (Round your answer to 1 decimal place.) Required: Based upon what you find answer the following questions: A. Which of the five regions' operations has the highest gross profit? (Enter your answer in millions rounded to 1 decimal place (i.e. 5,500,000 should be entered as 5.5).) B. Which of the five regions' operations has the lowest gross profit? To help determine the relative profitability of each state's sales, get a better state-by-state comparison by analyzing the "map" chart. Once the chart is created, move your cursor to hover above various circles within states in the chart. Notice that an information box appears to reveal the pertinent gross profit and gross profit ratios for that state. C. Which state's operations provides the highest gross profit ratio? (Round your answer to 1 decimal place.) D. Which state's operations provides the lowest gross profit ratio? (Round your answer to 1 decimal place.) A. B. C. D. The West The East Kansas's Colorado's Answer is complete but not entirely correct. region's operations has the highest with a gross profit of about $ region's operations has the lowest with a groue profit of $ operations has the highest with a gross profit ratio of operations has the lowest with a gross profit ratio of 1.1 million 13 1.1 % 1.1 % Return to que Tableau Instructions: You have available to you an extensive data set that includes detailed financial data for the company's inventory sales. The data set is in the form of an Excel file available to download from Connect, or under Student Resources within the Library tab. There are four Excel files available. The one for use in this case is named "inventory.xlsx. Download this file to the computer, save it, and open it in Tableau. All short training videos can be found here. For this case, you will create several calculations to produce a bar chart and a map of the gross profit to allow you to compare and contrast a company's inventory measurement After you view the training videos and review instructions in Chapters 1-3, follow these steps to create the charts you'll use for this case: 1. Open Tableau and connect to the Excel spreadsheet you downloaded. Click on the Sheet 1 tab, at the bottom of the canvas, to the right of the Data Source at the bottom of the screen. Drag "Region" under "Dimensions to the Columns shelf 2. Drag "status" under "Dimensions to the Filter's card. When the Filter window opens, click the "S" so only the sold items will be viewed. Click OK 3. Click on the "Analysis" tab from the Toolbar at the top of the screen and select "Create Calculated field." A calculation box will pop up. Name the calculation "Total Sales." Drag "Number" under "Dimensions to the Calculations Editor. Type a multiplication sign beside it, then drag "Selling Price" under "Measures" to the Calculations Editor. Make sure the window says that the calculation is valid and click OK. 4. Repeat the process outlined in Instruction 43 and create a calculated field for "Cost of Goods Sold" that consists of "Number" multiplied by "Cost." 5. Repeat the process outlined in Instruction 43 and create a calculated field for "Gross Profit" that consists of "Total Sales" minus "Cost of Goods Sold." 6. From the "Measures" section on the middle left side of the canvas, drag "Total Sales," "Cost of Goods Sold," and "Gross Profit" Into "Rows" shelf. 7. Click the "Show Me" box on right side of the toolbar and select the "side-by-side bars.' 8. Show the labels by clicking on the Label box on the "Marks" card and checking "Show mark labels." Format the labels to Times New Roman, bold, black and 9-point font. 9. Change the title of the sheet to be 'Total Sales, COGS, and Gross Profit by Region" by right-clicking and selecting "Edit title.' Format the title to Times New Roman, bold, black and 15-point font. Change the title of "Sheet 1' to match the sheet title by right-clicking, selecting "Rename" and typing in the new title. 0. Format all the labels on the sheet to Times New Roman, 10-point font, and black. 11. Click on the New Worksheet tab on the lower left ("Sheet 2 should open). 2. Drag "State" from "Dimensions to the Columns shelf. II. LIICK on the New Worksheet lou on the lower on Hel & Should open. 2. Drag "State" from "Dimensions to the Columns shelf 13. Drag "Gross Profit" and "Total Sales" from "Dimensions to the Rows shelf. 14. Click on the "Analysis" tab from the Toolbar at the top of the screen and select "Create Calculated field". Name the calculation field "Gross Profit Ratio by State" by dragging "Sum(Gross Profit)" from the Rows shelf and then dividing by "Sum(Total Sales)" from the Rows shelf to calculate an "aggregated" measure for each state. 15. Remove "Sum(Total Sales)" from the Rows shelf by dragging it back over to "Dimensions". 16. In the "Show Me" box on right side of the toolbar, select the "symbol map". You should see a map of the United States with circles appearing in states with data. Drag "Gross Profit Ratio by State" from "Measures" to the bottom of the "Marks" card. Click on the circle symbol beside the "Gross Profit Ratio by State and select "Color." The dots should now vary in size and color based on the size of the gross profit ratio and gross profit. Enlarge the dots by clicking "Size" on the "Marks" card and adjusting the slide. You can also change the color variation by clicking "Color on the "Marks" card and editing the color. 17. Change the title of the sheet to be "Gross Profit Ratio by State" by right-clicking and selecting "Edit title." Format the title to Times New Roman, bold, black and 15-point font. Change the title of "Sheet 2" to match the sheet title by right-clicking, selecting "Rename" and typing in the new title. 18. Once complete, save the file as "DA8_Your initials.twbx." Required: Based upon what you find, answer the following questions: A. Which of the five regions' operations has the highest gross profit? (Enter your answer in millions rounded to 1 decimal place (i.e.. 5,500,000 should be entered as 5.5).) B. Which of the five regions' operations has the lowbst gross profit? To help determine the relative profitability of each state's sales, get a better state-by-state comparison by analyzing the "map" chart. Once the chart is created, move your cursor to hover above various circles within states in the chart. Notice that an information box appears to reveal the pertinent gross profit and gross profit ratios for that state. C. Which state's operations provides the highest gross profit ratio? (Round your answer to 1 decimal place.) D. Which state's operations provides the lowest gross profit ratio? (Round your answer to 1 decimal place.) Required: Based upon what you find answer the following questions: A. Which of the five regions' operations has the highest gross profit? (Enter your answer in millions rounded to 1 decimal place (i.e. 5,500,000 should be entered as 5.5).) B. Which of the five regions' operations has the lowest gross profit? To help determine the relative profitability of each state's sales, get a better state-by-state comparison by analyzing the "map" chart. Once the chart is created, move your cursor to hover above various circles within states in the chart. Notice that an information box appears to reveal the pertinent gross profit and gross profit ratios for that state. C. Which state's operations provides the highest gross profit ratio? (Round your answer to 1 decimal place.) D. Which state's operations provides the lowest gross profit ratio? (Round your answer to 1 decimal place.) A. B. C. D. The West The East Kansas's Colorado's Answer is complete but not entirely correct. region's operations has the highest with a gross profit of about $ region's operations has the lowest with a groue profit of $ operations has the highest with a gross profit ratio of operations has the lowest with a gross profit ratio of 1.1 million 13 1.1 % 1.1 %