Exp22_Excel_Ch05_Cumulative_Merchandise 1.1

Exp22 Excel Ch05 Cumulative Merchandise 1.1 

Excel Chapter 5 Cumulative – Merchandise

Project Description:

You manage the souvenir shop for a touring Broadway production. The shop sells apparel, souvenirs, and media items. You created datasets to analyze sales in the different categories. One sheet contains apparel sales for the first two weeks of the month. You will insert subtotal rows in this worksheet. The second sheet contains product information for the first quarter of the year. You will create a PivotTable, insert a timeline, and create a PivotChart using this data. You will create a second PivotTable so that you can filter data and calculate product prices if you run a sale. Finally , the last sheet contains two tables containing employee data. You will create a relationship and then create a PivotTable using that data.

     

Start Excel. Download and open   the file named Exp22_Excel_Ch05_Cumulative_Merchandise.xlsx.   Grader has automatically added your last name to the beginning of the   filename.

 

 

Your first task is to sort the   dataset on the Apparel worksheet.
 

  Ensure the Apparel worksheet is active. Sort the data by Week in alphabetical   order and further sort it by Category in alphabetical order.  

 

You want to focus on apparel   sales within one month. You will insert subtotal rows to display total sales   by week (1 and 2) and by category (Hoodie and T-shirt).
 

  Use the Subtotal feature to insert subtotal rows by Week to calculate the   totals for Qty Sold and Gross Revenue. Add a second subtotal (without   removing the first subtotal by Category to calculate the totals for Qty Sold   and Gross Revenue. 

 

To focus on the Gross Revenue,   you will apply an outline and collapse columns.
 

  Create an automatic outline and collapse the outline above Gross Revenue. 

 

Next, you want to create a blank   PivotTable and then name it.
 

  Display the Qtr1 worksheet and create a blank PivotTable on a new worksheet.   Do not add data to the data model. Name the PivotTable Qtr1 Sales. Change the name of the worksheet to Sales   PivotTable.

 

The PivotTable should determine   total number of items sold and the total gross revenue by category for the   first quarter.
 

  Place the Category field in rows. Place the Gross Revenue and Qty Sold fields   as values. 

 

You will format the values in   the PivotTable to look more professional and change the custom names that   display as column headings.
 

  Modify the Sum of Gross Revenue field with the custom name Total Gross   Revenue and   apply Accounting Number Format with 0 decimal places. 

 

Modify the Sum of Qty Sold field   with the custom name Total Qty Sold, apply Number format with 0 decimal places, and   select the Use 1000 Separator (,) option

 

Now you want to insert a   timeline so that you can filter the PivotTable by month.
 

  Insert a timeline for the Month field. Click or select March to filter the   PivotTable to display only March data. Cut the timeline and paste it in cell   A9. Set the timeline width to 3.3 inches. 

 

You want to create a PivotChart   to display percentages in a pie chart.
 

  Insert a PivotChart pie chart that depicts the categories and gross revenue.   Cut the chart and paste it in cell D1.  

 

Next, you want to remove the   legend and display data labels.
 

  Remove the legend. Display data labels in the best fit position with only the   Category Name and Percentage labels. 

 

You are ready to finish the   PivotChart by adding a title and changing the width.
 

Change the title to Gross Revenue. Change the   chart width to 3.9 inches. 

 

Extracting a value from the   PivotTable and displaying it on the main worksheet is helpful as you look   through the dataset.
 

  Click or select cell H1 in the Qtr1 worksheet. Insert the GETPIVOTDATA   function to retrieve the grand total in the Total Gross Revenue column in the   filtered PivotTable.

 

Next, you want to create a   recommended PivotTable to analyze product sales in the Apparel category.
 

  Use the data on the Qtr1 worksheet to create a recommended PivotTable using   the Sum of Retail Price by Category. Name the PivotTable Sale Prices. Change the name of the   worksheet to Sale Prices.

 

Move the Category field from the   Rows area to Filters area. Add the Product field to the Rows area. Modify the   Sum of Retail Price field with the custom name Item Retail Price and apply Accounting format   with zero decimal places.

 

Now you want to focus on only   the Souvenirs data.
 

  Set a filter to display on the Souvenirs category.

 

You will create a calculated   field to determine product retail prices if you offer a 15% sale, which is   85% of the current retail price.
 

  Create a calculated field using the default name Field1 to multiply the   Retail Price by .85. Modify the calculated field with the custom name Sale Price and apply Accounting Number   Format with zero decimal places.

 

Next, you will apply a different   PivotTable style to have a similar color scheme as the dataset.
 

  Apply Light Yellow, Pivot Style Light 19 to the Sale Prices PivotTable.

 

You are ready to insert and   customize a slicer to provide another way to filter the categories.
 

  Insert a slicer for Category. Move it so that the top-left corner is in cell   E1.

 

After inserting the slicer, you   want to change the dimensions and appearance of it.
 

  Change the button width to 1.5 inches. Change the slicer height to 1.4 inches. Apply Light Yellow, Slicer Style Light 4.

 

The Employee worksheet contains   two tables. The NAMES table contains a list of the four part-time souvenir   shop employees and their IDs. The HOURS table contains a list of the   employees’ hours per week for one month. You will create a relationship   between the tables.
 

  Create a relationship between the HOURS table using the ID field and the   NAMES table using the ID field.

 

Now that you built a   relationship between the tables, you can create a PivotTable using fields   from both tables.
 

  Create a blank PivotTable in cell E1 of the Employee worksheet and add the   data to the data model. Name the PivotTable as Monthly Hours.

 

Select the LastName field from   the NAMES table for rows. Select the Hours field from the HOURS table as   values. Change the Sum of Hours custom name to Monthly Hours. Type Names in cell E1.

 

You realize a value needs to be   changed in the main worksheet.
 

  Type 20 in cell C8 in the Employee   worksheet and then refresh the PivotTable.

 

Save and close Exp22_Excel_Ch05_Cumulative_Merchandise.xlsx.   Exit Excel. Submit the file as directed.