Perform simple profitability analysis on the product

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


Helping Students for Excellence in Academics, GET Help with Assignment? Order Now