Data Analytics Case Inventory Management

Data analytics is the process of examining data sets in order to draw conclusions about the information they contain. If you have not completed any of the prior data analytics cases, follow the instructions listed in the Chapter 1 Data Analytics case to get set up. You will need to watch the videos referred to in the Chapters 1 - 3 Data Analytics cases. No additional videos are required for this case. All short training videos can be found here. In this case you are seeking to gain insight into the relative demand for the companys products in various regions of the country and various states in which it sells. Your focus is on assessing the companys inventory management relative to regional and local demand. Tableau Instructions: You have available to you an extensive data set that includes detailed financial data for the companys inventory sales. The data set is in the form of four Excel files available to download from Connect, or under Student Resources within the Library tab. Download the file "Inventory.xlsx" to the computer, save it, and open it in Tableau. For this case, you will create several calculations to produce a bar chart and a text chart to allow you to compare and contrast a companys inventory management. After you view the training videos and review instructions in Chapters 1-3, follow these steps to create the charts youll 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 Filters card. When the Filter window opens, click the "S" so only the sold items will be viewed. Click OK.
3. Under the Analysis tab, select Create Calculated Field and label it Total Sales. Drag Number under Dimensions to the Calculations Editor window. Type an asterisk next to it for multiplication, then drag Selling Price under Dimensions next to the asterisk. Make sure the window says that the calculation is valid and click OK.
4. Repeat the process outlined in Instruction #3 and create a calculated field for "Total Markups" that consists of "Number" multiplied by "Net Markups. Make sure the window says that the calculation is valid and click OK.
5. Repeat the process outlined in Instruction #3 and create a calculated field for "Total Markdowns" that consists of "Number" multiplied by "Net Markdowns." Make sure the window says that the calculation is valid and click OK.
6. From the "Measures" section on the middle left side of the canvas, drag Total Sales," "Total Markups," and "Total Markdowns" into Rows shelf.
7. Create a calculated field named "Additional Markup %" by dragging "Total Markups" from the Rows shelf into the Calculation Editor window, typing the division sign, and dragging "Total Sales" from the Rows shelf into the window next to it. Make sure the window says that the calculation is valid and click OK.
8. Repeat the process one more time by creating a calculated field "Markdown %" by dragging "Total Markdowns" from the Rows shelf into the Calculation Editor window, typing the division sign, and dragging "Total Sales" from the Rows shelf into the window next to it.
9. Drag "Total Sales," "Total Markups" and "Total Markdows" from the Rows shelf back under "Measures" so they no longer show on the canvas.
10. Drag "Additional Markup %" and "Markdown %," to the Rows shelf.
11. Click the "Show Me" box on right side of the toolbar and select the "side-by-side bars."
12. Show the labels by clicking on Label in the "Marks" card and checking "Show mark labels."
13. Change the title of the sheet to be "Inventory Management 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.
14. Format all lables on the sheet to Times New Roman, 9-point font, and black. You can also change the color variation by clicking "Color" on the "Marks" card and editing the color.
15. Click on the New Worksheet tab on the lower right ("Sheet 2" should open) and drag "State" to the Rows shelf. Select the drop-down arrow on "State" to format the state names to Times New Roman, 10-point font, bold and blue (the format screen will open to the left).
16. Double click "Additional Markup %" and "Markdown %." You will now see the "Measures" show up in the Data table. Right click on the drop-down menu of one of them and select "Format." Format to Times New Roman, bold, black, 10-point font, and center alignment (center alignment will be in the drop-down box beside alignment).
17. Right click on one of the numbers and select "Format." Click on "Fields" in the upper-right corner of the Format window, select "Measures values." Format the numbers to Times New Roman, bold, black, 10-point, align to center and "Percentage with two decimal places." Note that you can click on the right-side of each of the headings ("Additional Markup %" and "Markdown %") to sort from highest to lowest or lowest to highest. This may help you answer some of the questions.
18. Change the title of the sheet to be "Inventory Management 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.
19. Once complete, save the file as "DA9_Your initials.twbx."

Required: Calculate the ratios as the additional markup or markdown amounts as a percentage of sales. (A more precise measure that would produce similar results would replace sales in the ratio denominator with the original selling price of both sales and unsold inventory.) Based upon what you find, answer the following questions: A. Which of the five regions operations has the most favorable inventory management relative to demand as measured by the additional markup percentage ratio and what is that ratio? (Round your answer to 1 decimal place.) B. Which of the five regions operations has the least favorable inventory management relative to demand as measured by markdown percentage ratio and what is that ratio? (Round your answer to 1 decimal place.) To view the state inventory management results more clearly, highlight the column "Additional Markup %" in the "Inventory Management by State" column and sort from largest to smallest. You can answer problem 4 below by following the same procedure for the "Markdown %" column. C. Which states operations has the most favorable inventory management relative to demand as measured by additional markup percentage ratio and what is that ratio? (Round your answer to 2 decimal places.) D. Which states operations has the least favorable inventory management relative to demand as measured by markdown percentage ratio and what is that ratio? (Round your answer to 2 decimal places.)

Required: Calculate the ratios as the additional markup or markdown amounts as a percentage of sales. (A more precise measure that would produce similar results would replace sales in the ratio denominator with the original selling price of both sales and unsold inventory.) Based upon what you find, answer the following questions: A. Which of the five regions' operations has the most favorable inventory management relative to demand as measured by the additional markup percentage ratio and what is that ratio? (Round your answer to 1 decimal place.) B. Which of the five regions' operations has the least favorable inventory management relative to demand as measured by markdown percentage ratio and what is that ratio? (Round your answer to 1 decimal place.) To view the state inventory management results more clearly, highlight the column "Additional Markup %" in the "Inventory Management by State" column and sort from largest to smallest. You can answer problem 4 below by following the same procedure for the "Markdown %" column. C. Which state's operations has the most favorable inventory management relative to demand as measured by additional markup percentage ratio and what is that ratio? (Round your answer to 2 decimal places.) D. Which state's operations has the least favorable inventory management relative to demand as measured by markdown percentage ratio and what is that ratio? (Round your answer to 2 decimal places.) A. The % % B. The C region's operations has the most favorable inventory management with an additional markup percentage of regions' operations has the least favorable inventory management relative to demand with a markdown percentage of operations has the most favorable inventory management relative to demand with an additional markup percentage ratio of operations has the least favorable inventory management relative demand with a markdown percentage ratio of %. D. %. Required: Calculate the ratios as the additional markup or markdown amounts as a percentage of sales. (A more precise measure that would produce similar results would replace sales in the ratio denominator with the original selling price of both sales and unsold inventory.) Based upon what you find, answer the following questions: A. Which of the five regions' operations has the most favorable inventory management relative to demand as measured by the additional markup percentage ratio and what is that ratio? (Round your answer to 1 decimal place.) B. Which of the five regions' operations has the least favorable inventory management relative to demand as measured by markdown percentage ratio and what is that ratio? (Round your answer to 1 decimal place.) To view the state inventory management results more clearly, highlight the column "Additional Markup %" in the "Inventory Management by State" column and sort from largest to smallest. You can answer problem 4 below by following the same procedure for the "Markdown %" column. C. Which state's operations has the most favorable inventory management relative to demand as measured by additional markup percentage ratio and what is that ratio? (Round your answer to 2 decimal places.) D. Which state's operations has the least favorable inventory management relative to demand as measured by markdown percentage ratio and what is that ratio? (Round your answer to 2 decimal places.) A. The % % B. The C region's operations has the most favorable inventory management with an additional markup percentage of regions' operations has the least favorable inventory management relative to demand with a markdown percentage of operations has the most favorable inventory management relative to demand with an additional markup percentage ratio of operations has the least favorable inventory management relative demand with a markdown percentage ratio of %. D. %