Reference no: EM133125068

Scenario

Lucy seems intrigued your presentation on Australia's sales.

Now, she wants to review the sales difference (growth) for Product Category and Sub Category between year 2015 and 2016. She wants to be able to filter the report by all the options available, including Customer Gender and Age Group.

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 Lab6Start v5.xlsx.

Lab 6

Download the lab file Lab6 Startv5.xlsx to answer the questions below.
You will create two reports. One with the sales for each year and the other with the % Change in Revenue from Year to Year. Each report is a cross-tabular format with Product Categories and Sub categories on the rows and Year on the columns, with Sum of Revenue as the aggregate data. When you are done, neither report will be based on a Pivot Table. However, you will enable a Pivot Table filter to allow the selection of Country, Customer Gender, and Age Group for these reports. Visually, you would like to have something like this:
Let's start by adding a new sheet named Growth. Starting in cell B1, add a new pivot table using SalesTable as the source with Country, Customer Gender, and Age Group as Filters, Product Category and Sub Category as Rows, Year as Columns, and Sum of Revenue as Values.
Now, copy the structure of the rows and columns to the cells next to the pivot table, that is, copy the Years labels in C6:H6 (excluding the Grand Total) to range L6:Q6, and the Product categories and Sub categories labels in B7:B26 (excluding the Grand Total) to range K7:K26. Highlight and copy range B6:H26, place your cursor in cell K6, right click and from the Paste Options, select Formatting (R).
Alternatively, you can select B6:H26, place your cursor in cell K6, right click and from the Paste Options, select Values (V) and then, once again right click in cell K6, and from the Paste Options, select Formatting (R). After which, delete the data area.
Populate the data area of the newly created cross tabular structure using SUMIFS() function.

Assuming that you have:
- K7: Product Category eg "Accessories"
- Q6: Year eg. "2016"
- C1: Country Filter
- C2: Customer Gender Filter
- C3: Age Group Filter
Then, the cell that corresponds to Year 2016 for the Product Category Accessories would have the following formula
=SUMIFS(SalesTable[Revenue],
SalesTable[Product Category],$K7,
SalesTable[Year],Q$6,
SalesTable[Country],IF($C$1="(All)","*",$C$1),
SalesTable[Customer Gender],IF($C$2="(All)","*",$C$2),
SalesTable[Age Group],IF($C$3="(All)","*",$C$3))

Confirm that your filter boxes (cells C1, C2 and C3) contain the same value that is used in the formula (All). If not, then replace the "(All)" with the exact value found in your filter boxes (it could be All or your language's word for All. (Note that you will have to confirm these values for the next set of formulas as well)
Now, copy this formula (using Ctrl C keyboard combination) and paste it (using Ctrl V keyboard combination) into the other Years and Product Category cells for all three categories (Accessories, Bikes, Clothing). When you are done, your calculated values in cells L7:Q7, L16:Q16 and L20:Q20 should match their respective pivot table values.

In addition, assuming that you have:
- K8: Sub Category eg "Bike Racks"
- Q6: Year eg. "2016"
- C1: Country Filter
- C2: Customer Gender Filter
- C3: Age Group Filter
Then, the cell that corresponds to Year 2016 for the Sub Category Bike Racks would have the following formula

=SUMIFS(SalesTable[Revenue],
SalesTable[Sub Category],$K8,
SalesTable[Year],Q$6,
SalesTable[Country],IF($C$1="(All)","*",$C$1),
SalesTable[Customer Gender],IF($C$2="(All)","*",$C$2),
SalesTable[Age Group],IF($C$3="(All)","*",$C$3))

Now, copy this formula (using Ctrl C keyboard combination) and paste it (using Ctrl V keyboard combination) into the other Years and Sub Category cells. When you are done, your calculated values in these cells should match their respective pivot table values.
Once your table is populated, remove the fields from the Rows, Columns, and Values of the Pivot table, so that only the filters remains. Align the filters with your cross tabular report and add sparklines (to show the series high point) next to the column containing the last year of data.
When you are done with the first cross tabular report, create another one with the same structure next to it (excluding the 2011 column). This time, for % Change in Revenue, which is essentially the difference between the two years, divided by the previous year.
For example, one formula could be:
=IF(C7<>0,(D7-C7)/C7,0)

Present the data as percentage without decimal places. Apply an Icon Sets conditional formatting to differentiate positive and negative growth.

Attachment:- Entity Relationship Modelling.rar