Problem

Data File needed for this Case Problem: NP_EX_11-6.xlsx

Computer Discount Essentials Jamere Carter manages inventory for Computer Discount Essentials. Part of Jamere's job is to monitor the stocking levels in the company's warehouses to ensure that merchandise is restocked well before the warehouse runs out. Jamere stores inventory data in an Excel workbook with two tables in the Data Model: The Products table describes all the warehouse merchandise, including each item's initial stocking level and the level below which the item must be restocked. The Transactions table contains a daily record of transactions at the warehouse including items shipped out and items restocked. Jamere wants you to create a report on the current state of the warehouse, flagging those items that need immediate restocking and calculating the total value of all items in stock. Complete the following:

1. Open the NP_EX_11-6.xlsx workbook located in the Excel11 > Case2 folder included with your Data Files. Save the workbook as NP_EX_11_CDE in the location specified by your instructor.

2. In the Documentation sheet, enter your name and the date.

3. In the Inventory Report worksheet, in cell B7, insert a PivotTable named Inventory Pivot. Set the PivotTable layout to the tabular form with no grand totals. Set the layout to repeat all item labels.

4. Place the Category, SKU, and Description fields from the Products table in the Rows area.

5. Display the initial quantity of each item stocked in the warehouse by placing the Initial QTY field from the Products table in the Values area and change the field label from Sum of Initial QTY to Starting QTY.

6. Display the change in the item quantities over the past year by placing the Change field from the Inventory in the Values area and change the field label from Sum of Change to Change in QTY.

7. Determine the current quantity of each item left in the warehouse by adding a measure to the Products table named Current QTY with the description Current quantity in stock. Enter the DAX formula =[Sum of Initial QTY] + [Sum of Change]. Add the measure to the PivotTable, if needed.

8. Determine the value of the items in inventory by creating a measure for the Products table named Inventory Value with the description Total value of the items in the inventory. Enter the DAX formula =SUMX(Products, [Unit Value]*[Current QTY]) and display the value as currency to two decimal places. Add the measure to the PivotTable, if needed.

9. determine which items need to be restocked by creating a measure for the Products table named Restock Order with the description Quantity of item to reorder. Insert the following DAX formula that uses the SUMX and IF functions to calculate the amount of items that Jamere will have to order to bring the inventory up to the proper level:

=SUMX(Products, IF([Current QTY]<[Restock Level], [Restock Amount], 0))

Note that if an item is above the restocking level, this measure will return a value of zero (because no restocking is required). If you are having problems entering the formula, you can copy and paste the measure formula from the Data Summary worksheet.

Explore 10. Add a conditional format icon set to all cells in the PivotTable showing Restock Order values. If the numeric value of a cell is greater than zero, indicating that the item needs restocking, display a red flag; for all other values do not display any icon. (Hint: You can select individual icons by clicking the drop-down list box next to each icon image in the New Formatting Rule dialog box.)

Explore 11. Jamere wants the total value of all items in the inventory displayed above the PivotTable. Because this total is not part of the PivotTable, you need to retrieve the measure directly from the Data Model using the CUBEVALUE function. In cell H5, enter the following formula to calculate the total value of all items in the warehouse by retrieving the Inventory Value measure:

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Inventory Value]")

12. Resize the column widths of the PivotTable so that all values and labels can be read.

13. Jamere wants a table listing the names of the items by warehouse row and bin. In the Warehouse Grid worksheet, create a PivotTable in cell B4 named Warehouse, set the layout to the tabular form, and do not display any grand totals for the table.

14. Place the Warehouse Row field from the Products table in the Rows area and place the Storage Bin field from the Products table in the Columns area.

Explore 15. With DAX you can create measures that return text strings in place of numeric values by using the CONCATENATEX function. Display all the items stored in a warehouse row and bin in a comma-separated list by adding the following Item List measure to the Products table. Include the description List of items from the warehouse inventory and enter the measure formula:

=CONCATENATEX(Products, [Description], ", ")

Add the measure to the Values area of the PivotTable, if needed. Confirm that each cell in the PivotTable displays items stored in the matching storage bin and warehouse row.

16. Wrap the text in the cells containing the item lists within each cell. Resize the PivotTable column and row widths so that the contents are easy to read and do not take up too much space in the worksheet.

17. Save the workbook, and then close it.