PiesRUs is a family run company located in Barrie, Ontario engaged in the manufacture and sale of various baked goods, primarily apple pies and other pies. The company has a modern 110,000 square foot bakery (manufacturing facility). With 200 dedicated full-time employees, it has been making desserts for customers across North America and beyond since 1975.

PiesRUs has a manufacturing facility that makes various kinds of baked goods under its own brand [Pies"R"Us] and under brand name of large stores such as ValleMart, ValleMart US, People Choice, Wholesum Meals, Trader Stan, Cost Less Co, being some of them.

Refer to PiesRUs HANDOUT (Dataset) for listing of closing inventory of baked goods as at October 31, 2022. The Dataset provided contains the following fields:

Item # Unique Stock Keeping Unit (SKU) number for each category of inventory

Item Description Name of inventory SKU, including name of retailer for which packaged

Customer Name of the retailer

Quantity Number of cases of each type of inventory

Cost Cost per case

Value Total carrying cost of inventory (Quantity x Cost)

Batch Made Date on which the batch of inventory was manufactured

Required:

1) Using the Inventory by Item # tab, determine the total quantity and value of the inventory (using the SUM function)

2) Insert a column between Item # (column A) and Quantity (column C) on the Inventory Item # tab. In cell B6 type Item Description. Using the Vlookup function, insert the product name from the Inventory list tab into column B based on the Item # in column A on the Inventory by item# tab. Cell B6 should say Pastry Disks Pies"R"Us 70G. Copy the vlookup formula to cell B98.

3) Insert a column between the Item Description (column B) and Quantity (column C) on the Inventory Item # tab. In cell C6 type Customer. Using the Vlookup function, insert the customer name from the Inventory list tab into column C based on the Item # in column A on the Inventory by item# tab. Cell C7 should be PiesRUs. Copy the vlookup formula to cell C98. Submit a copy of your Excel file.

4) Using Pivot tables, answer the following questions. Include a screen shot of your Pivot table in your submission for each question. Microsofts snipping tool can be used for screen shots.

a) Which Item has the highest inventory value? Sort the value highest to lowest in the pivot table. State the item description, quantity and value of the item in your submission. Take a screen shot of the pivot table results.

b) What is the value of inventory by customer? Take a screen shot of the pivot table results.

c) insert a bar chart next to the pivot table answer chart based on b) value of inventory by customer above. Take a screen shot of your bar graph.

5) Based on inventory listing, state a question you want to answer. Create a pivot chart to answer your questions. In your submission, include the question you have and a copy of the pivot table you created to solve/ answer your question.