Reference no: EM133125043

Scenario

Now that you have prepared the data in an Excel table, you can start to create pivot tables to aggregate the data and create some reports.

You can download the prepared data below.

From your conversation with Lucy, you know that she is interested in looking into the yearly sales data broken down by countries, product categories, and age groups.

What You'll Need

• A computer with Microsoft Excel installed on it. We prefer Excel 2016, but you can still complete the lab using Excel 2013 or Excel 2010.
• File Lab3Start v5.xlsx.

Lab 3A

Download the lab file Lab3Start v5.xlsx to answer the questions below.

First, let's start by naming the Excel table. Name the Excel table prepared in the previous lab to SalesTable. From now on, every time you add a pivot table, it should be based on this SalesTable, unless mentioned otherwise.

Now, proceed to add your first pivot table. Insert a new pivot table based on the SalesTable to a new sheet. Arrange the layout so that the pivot table displays the Product Category and Sub Category in the Rows, Year in the Columns, and Revenue (Sum of) as the Values.

Insert another pivot table to the same sheet, next to the existing pivot table. Arrange the layout so that the pivot table displays the Country and State in the Rows, Year in the Columns, and Revenue (Sum of) as the Values. Sort the pivot table by Sum of Revenue so that the Country and State with the highest revenue is displayed first.

Let's add another pivot table. This time arrange the layout so that the pivot table displays the Frame Size in the Rows and Revenue (Sum of) as the Values. Hide the rows that do not have a Frame size (blank Frame size), then sort the pivot table by Sum of Revenue so that the Frame size with the highest revenue is displayed first.

Last but not least, add another pivot table with Age Group as the Rows and Revenue (Sum of) as the Values. You will learn how to custom sort the Age Group in the next module. But for now, sort the pivot table by Sum of Revenue so that the Age Group with the highest revenue is displayed first.

Save your Excel file - you will need this work in the next Exercise (Lab 3B).

Lab 3B

Now you can start adding some charts to the sheet.
First, add a pivot chart for the pivot table that shows yearly sales (revenue) by Country (the pivot table you created for question 2 in Lab 3A). Select a Line chart to display the yearly trend. Make sure that the Years are located in the X axis, the Revenue in the Y axis, and the Countries as categories.
Hint: You might find the Switch Row/Column in Select Data Source window useful. In addition, try Collapsing the Country fields in order to hide the States fields.
Add another pivot chart for the pivot table that shows yearly sales (revenue) by Product Category (the pivot table you created for question 1 in Lab 3A). Select a Column chart to display the yearly sales by category so that the years are together.
Add another pivot chart, this time for the pivot table that shows Revenue by Age Group (the pivot table you created for question 5 in Lab 3A). Select a Pie chart to display the proportion of each Age Group (remember the chart styles) with data labels, formatted to two decimal points.
Add another pivot chart, this time for the pivot table that shows Revenue by Frame size (the pivot table you created for question 4 in Lab 3A). Select a Bar chart to display the order of revenue by Frame size. Sort the Y axis to show the Frame size that has the highest revenue on the top.
Hint: Select the Categories in reverse order option in the Axis Options.

Attachment:- Business Reporting using performance.rar