Reference no: EM133125057

Scenario

You have created several pivot tables and pivot charts for Lucy.

So far everything has been well received by her. However, she would like to have easier ways to slice and dice the reports and charts herself.
You sat down with Lucy, and come up with several different ways that Lucy could slice the data
- Year
- Country
- Customer Gender
- Age Group
- Product Category
- Sub Category
- Frame size

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. Please note that the first two pivot tables in the file include hierarchical data (Product Category can be expanded to display their Sub Categories and Countries can be expanded to display their States). The charts created from these two pivot tables may not work in all versions of Excel (i.e., Excel for Mac and Excel 2007) and may have to be re-created before you can continue working on them.
• File Lab4AStart v5.xlsx.

Lab 4A

Download the lab file Lab4AStart v5.xlsx to answer the questions below.
Start by adding a new sheet named Dashboard. Then move (Cut and Paste) the four charts that you have to that sheet. Arrange the charts as appropriate.
Hint: The Snap to Grid option located at the Align Object on the Page Layout tab might be useful.
For easy reference, let's give the charts titles if they don't have any, or rename them as appropriate. Name the four charts as follows:
- Yearly Sales by Country
- Yearly Sales by Category
- Sales by Frame Size
- Sales by Age Group
Hint: To add a chart title, select the chart, then on the Design tab, select Add Chart Element.
You can now add slicers to the sheet. Select the Yearly Sales by Country chart, and add seven slicers corresponding to Year, Country, Customer Gender, Age Group, Product Category, Sub Category and Frame Size. Arrange the slicers as appropriate.
The next thing you need to do is to connect these slicers to the charts. Let's do this chart by chart.
Hint: The Filter Connections option is available on the chart Analyze tab.
1. Connect the Yearly Sales by Country chart to all slicers, except the Year slicer. In another words, disconnect the Year slicer from the Yearly Sales by Country
2. Connect the Yearly Sales by Category chart to the Age Group, Country, Customer Gender, and Frame Size slicers.
3. Connect the Sales by Frame Size chart to all slicers, except the Frame Size slicer.
4. Connect the Sales by Age Group chart to all slicers, except the Age Group.
For niceties, you can clear the Gridlines on the Dashboard,
Hint: The Gridlines option is available on the View tab.
Once you've done the above, you're ready to present the dashboard to Lucy.
Create an additional pivot chart to show Sales by Country using Pie chart. Show percentages for each slice of the pie and connect the chart to all the slicers, except the Country slicer. Overall, Australia commands 25% of the company's total sales. But in some of the years, this proportion changes.
Hint: Create your new Pie chart based on a new pivot table that you will create on the Pivot worksheet.
Based on the previous answer, create an additional pivot chart to show Sales by Category using a Pie chart. Show percentages for each slice of the pie and connect the chart to all the slicers, except the Category slicer.
For the next two questions, filter the charts by Australia using the country slicer and play around with the Year filter. Notice for different years, the changes in composition of Australia's sales by Category.
Hint: Create your new Pie chart based on another new pivot table that you will create on the Pivot worksheet.

Lab 4B

Create an additional pivot chart to show Sales by Customer Gender using Pie chart. Show percentages for each slice of the pie and connect the chart to all the slicers, except the Customer Gender slicer.

What about Customer Gender vs age group? Right now the Sales by Age Group chart does not differentiate by Gender. Modify this chart to be a Column chart. Show the Customer Gender side-by-side for each age group. Ensure that the chart is connected to all slicers. Last but not least, sort the Age Group appropriately.

Attachment:- Performance Management Systems.rar