Assignment Detail:- 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
Most Recent Questions