Reference no: EM133542103

Case: Many retailers have return policies that may allow customers to return merchandise several months after a purchase. However, a common requirement is that the item returned must have been purchased at one of the company's stores and must be an item that is sold by the company. In some cases return errors can occur such that a cashier accepts an item for return that is not sold by the store. If the store does not require a receipt, these returns can be done intentionally as a way to pilfer money from the company. These types of returns may also be the result of collusion between a customer and a store employee to pilfer money from the store. To identify if this is happening, inventory discrepancies can be investigated using data analytics.

The purpose of this exercise is to use the new skills demonstrated in this chapter to identify inventory return discrepancies for a hypothetical retailer. Begin the exercise by opening the excel file named Excel Lab M6 - Big Data. Questions that are preceded with the letters KO indicate you must only use your keyboard and not your mouse to execute the required skill.

1. KO Change the font style to Arial for the entire dataset in the Inv Transactions worksheet.
2. KO Apply the bold font and Wrap Text alignment to the range A1:H1.
3. KO Apply the Accounting format to all the data in the Amount column.
4. KO Insert a new worksheet into the workbook and change the worksheet tab name to Data Copy.
5. KO Copy the entire dataset in the Inv Transactions worksheet and paste it into the Data Copy worksheet.
6. KO Change the width to 15 points for columns C, D, E, F, G, and H on the Data Copy worksheet.
7. KO Open the Inv Transactions worksheet.
8. In cell I1 on the Inv Transactions worksheet, type the column heading "Region".
9. Enter a RIGHT function in cell I2 that will show the last two characters of the entry in cell A2. Define the "text" argument with cell A2 and define the "[num_chars]" argument with the number 2. The last two characters for the data shown in the District column is the abbreviation of the region. For example, district 10NE is district 10 in the Northeast region. The output of this function will allow the dataset to be summarized by region.
10. Copy cell I2 and paste it into all of the rows in the dataset by double clicking the Fill Handle.
11. In cell J1, type the column heading "Week".
12. Enter the WEEKNUM function in cell J2 that will show the week number related to the date in cell C2. Define the "serial_number" argument with cell C2. Define the "[return_type]" argument with the number 2, which assumes that Monday is the first day of the week. The output of this function will be useful if additional data is added to the dataset.
13. Copy cell J2 and paste it into all of the rows in the dataset by double clicking the Fill Handle.
14. In cell K1, type the column heading "Return Amount".
15. Enter an IF function in cell K2 that will show the absolute value of the returns in the Amount column. The logical test of the function should evaluate if cell F2 is less than 0. If the logical test is true, define the "[value_if_true]" argument with the ABS function. Define the "number" argument of the ABS function with cell F2. If the logical test is false, the output of the IF function should be 0.
16. Copy cell K2 and paste it into all of the rows in the dataset by double clicking the Fill Handle.
17. Click cell K1 and add a comment by clicking the New Comment button in the Review tab of the Ribbon. When the comment box opens, type the following: "Return amounts are shown as positive values". Click cell I1 to close the comment box.
18. In cell L1, type the column heading "Sale Amount".
19. Enter an IF function in cell L2 that shows just the positive values (sale transactions) in the Amount column. The logical test of the function should evaluate if cell F2 is greater than zero. If the logical test is true, define the "[value_if_true]" argument with cell F2. If the logical test is false, the output of the IF function should be 0.
20. Copy cell L2 and paste it into all of the rows in the dataset by double clicking the Fill Handle.
21. Sort the entire dataset in ascending order based on the following column sequence: District, Store, and Date. Highlight the entire dataset and then click the Sort button in the Data tab of the Ribbon. This is a critical step that will be required to eliminate the repeating values in the Store Report column.
22. In cell M1, type the column heading "Store Week".
23. Enter a CONCATENATE function into cell M2 that combines the Store and Week columns. Define the "text1" argument with cell B2, and the "[text2]" argument with cell J2.
24. Copy cell M2 and paste it into all of the rows in the dataset by double clicking the Fill Handle.
25. In cell N1, type the column heading "Weekly Store Sales".
26. Enter an IF function in cell N2 that displays the first instance of the values in the Store Report column. The logical test of the IF function should evaluate if cell M2 is equal to M1. If the logical test is true, the output of the function should be 0. If the logical test is false, the output of the function should be the value in cell H2.
27. Copy cell N2 and paste it into all of the rows in the dataset by double clicking the Fill Handle.
28. In cell O1, type the column heading "SKU Check".
29. Enter a VLOOKUP function into cell O2 that looks for the SKU (Stock Keeping Unit) number in cell G2 in the SKU List worksheet. Define the "lookup_value" argument with cell G2. Define the "table_array" argument with the range A2:A101 in the SKU List worksheet. Place an absolute reference on this cell range before defining the next argument. Define the "col_index_num" argument with the number 1 and the "[range_lookup]" argument with the word "FALSE" to look for an exact match to the lookup value.
30. Copy cell O2 and paste it into all of the rows in the dataset by double clicking the Fill Handle.
31. In cell P1, type the column heading "SKU Alert".
32. Enter an IF function in cell P2 that shows an output of 1 if the VLOOKUP function produced an error output in the SKU Check column. The logical test of the IF function should be defined with the ISERROR function. Define the "value" argument of the ISERROR function with cell O2. If the logical test is true, the output of the IF function should be the number 1. If the logical test is false, the output of the function should be 0.
33. Copy cell P2 and paste it into all of the rows in the dataset by double clicking the Fill Handle.
34. Create a PivotTable using the entire dataset in the Inv Transactions worksheet. Click cell A1 and click the PivotTable button in the Insert tab of the Ribbon. The Create PivotTable dialog box should automatically include the entire range of the dataset. The PivotTable should appear in a separate worksheet and the worksheet tab label should be changed to "Returns Ratio".
35. Add the Region and Store fields to the ROWS area of the PivotTable.
36. Change the layout of the PivotTable to the Outline Form by clicking the Report Layout button in the Design tab of the Ribbon.
37. Add the Transaction Number field to the VALUES area of the PivotTable.
38. Double click the Sum of Transaction Number column heading on the PivotTable to open the Value Field Settings dialog box. Change the output calculation to Count and the field name to "Transaction Count".
39. Add the Sale Amount field to the VALUES area of the PivotTable. Change the field name to "Cash Sales" and format the values to U.S. currency with zero decimal places.
40. Add the Return Amount field to the VALUES area of the PivotTable. Change the field name to "Cash Returns" and format the values to U.S. currency with zero decimal places.
41. Add a calculated field by clicking the Fields, Items, & Sets button in the PivotTable Analyze tab of the Ribbon. The field name should be Returns Ratio. The formula should be the Return Amount field divided by the Sale Amount field.
42. Format the values in the Sum of Returns Ratio field to a percentage with two decimal places.
43. Sort the stores on the PivotTable based on the values in the Sum of Returns Ratio field in descending order. The results of this PivotTable show stores that have a high percentage of returns with respect to sale transactions. These stores may require additional monitoring to ensure these are all legitimate returns.
44. Create a second PivotTable using the entire dataset in the Inv Transactions worksheet. The worksheet tab name should be "SKU ALERT".
45. Add the SKU Alert Field to the ROWS area of the PivotTable.
46. Change the layout of the PivotTable to the Tabular Form.
47. Click the down arrow next to the SKU Alert field name on the PivotTable and set a filter so only the value of 1 is displayed.
48. Add the District, Store, and SKU fields to the ROWS area of the PivotTable.
49. Add the Date and Time fields to the ROWS area of the PivotTable.
50. Remove all subtotals from the PivotTable, and increase the column width of the Date and SKU fields if needed.
51. Click the Time column heading on the PivotTable. Show all values in this field by clicking the Ungroup button in the PivotTable Analyze tab of the Ribbon.
52. Add the Amount field to the VALUES area of the PivotTable. The PivotTable shows that store 310 in district 30SE processed returns for SKU 82023138, which is an item that is not sold by the company. The pattern of these returns is interesting as they all occurred at or after 7:00 p.m., which may be right before the store closes or times when there are fewer people in the store. Also, notice that the same item was returned for different purchase amounts. In total, $282.96 was taken from the cash register for these returns.