Reference no: EM133125065

Scenario

Lucy was impressed with the dashboard you created.

With the dashboard, she is able to narrow down her interest. Specifically, she is interested with the sales in Australia. She would like to perform simple profitability analysis on the product category and sub-category, for specific year. Furthermore, she wants to have the option of going deeper to product level.

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

Lab 5

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

Start by adding a new sheet named Details. Add a pivot table that shows Order Quantity, Revenue, and Profit by Product Category. Add the Year and Country field to the filter list.

Add the Sub Category field to the rows of the pivot table. Make sure the rows are fully expanded. Now you have quite a few numerical data shown in the pivot table, which might not be as easy to analyse as before. Apply Data bars conditional formatting to the Order Quantity, Revenue and Profit column on Sub Category level. Use blue, green, and yellow respectively.

Filter the pivot table for Australia and 2016, and answer the following questions.

Now, add a calculated field to your pivot table, name the field Margin with the value derived from the Profit and Revenue column. Format the field as percentage with two decimal places.

HINT: Margin = Profit / Revenue

Now, apply Color scales conditional formatting to Margin for the Sub Category level.

Let's dig a little deeper. Add the Product field to the Rows, under Sub Category. Ensure that the Year and Country filter still select 2016 and Australia respectively. Apply Color scales conditional formatting only to the cells showing the margin for the Product.

Attachment:- Data Analytics methods and techniques.rar