Inventory Worksheet

$1.$ Use an XLOOKUP function to determine where in the store to place the item. Fill in Column E by assigning the appropriate Location with the Category in column D using the small table L$2$: M$7.$

$2.$ Use SUMIF to determine the Total Revenue for all the products in the Middle store location? Ensure that your answer is in Accounting format.

$$

$3.$ Use AVERAGEIF to determine the Average inventory amount for the Back store location? Use formatting tools to decrease the decimal so that the displayed value is a whole number.

$$

$4.$ Use COUNTIF to determine the number of products that are in the Inventory Type "Clothing Staples"?

$5.$Use the XLOOKUP function to determine which product has the Item ID $1254$#RT$.$

Transactions Worksheet

$6.$Identify the name of the column that is the Primary Key for the WeeklyTransactions Table data

$7.$Identify the name of the column that is the Foreign Key in the WeeklyTransactionsTable that connects to the ProductInventory Data named range

$$

$8.$ Create a PivotTable with the WeeklyTransactions Table in cell A$3$ on the Transaction Analysis worksheet. Name the PivotTable "TransactionsByDay" using the PivotTable Name input box on the Ribbon.

Transactions Analysis Worksheet

$9.$In your Pivot Table, add "Date" as a Row and "Transaction ID$"$ as a Value. Change the Transaction ID to show as Count, rather than Sum. If your PivotTable auto$-$populates with Year & Quarter, remove those fields so that only months are displayed.

$$

$10.$ Add "Time" as a Row nested under "Date"

$$

$11.$ Change the Table Heading for Column $1$ to "Transaction Date$/$Time$"$ and Column $2$ to "Count of Transactions"

$$

$12.$ Which month had the most transactions? $($Answer on the "Transactions" Worksheet for Q$12)$

$$

$13.$ Create a second Pivot Table with the WeeklyTransactions Table; add it to the existing worksheet Transaction Analysis $2$ in cell A$3.$ Name the PivotTable "TransactionsByCustomer" using the PivotTable Name input box on the Ribbon.

$14.$In your Pivot Table, add "Cust ID$"$ as a row and "Transaction ID$"$ as a value. Change the Transaction ID to show as Count.

$15.$Change the Table Heading for Column $1$ to "Customer ID$"$ and Column $2$ to "Count of Transactions"

$$

$16.$ Which customer ID has the most transactions? Consider sorting your PivotTable to find the answer quickly. $($Answer on the "Transactions" Worksheet for Q$16)$