Assignment Detail:- Scenario
While your first attempt to show the company's performance to Lucy was not bad, clearly she has a lot more requirements than what you provided- She wants to know more about the year over year sales, sliced into different categories, sub-categories, and countries- She also wants to see additional information such as customer demographics-
Jack has provided you with a different data source- This time the data has more than one hundred thousand rows-
Before you can create additional reports to Lucy, first you need to prepare the data-
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 Lab2Start v5-xlsx-
Download the lab file Lab2Start v5-xlsx to answer the questions below-
The first thing you need to do is to convert the data into an Excel table-
Once you do that, you can add total row, filter the data, and the total will reflect the total only for the filtered data- Let's try this- Add a total row for the table, and use the Sum aggregation to show the total of the Revenue column and then filter the data only for United States-
Now, you need to add several columns, derived from existing columns in the data- Before adding columns, it is good practice to clear any filters you previously applied-
First, let's add a "Month" column- Insert a new column to the left of the Customer ID column, and use formula to derive the month of sales from the Date column-
HINT: Use the Text-- function and look for a format code in the examples that would work on a date field-Next, let's add an "Age Group" column- Remember to clear any filters you previously applied- Insert a new column to the left of the Customer Gender, and use formula to derive the age group from the Customer Age column- Let's group the customers based on the following criteria:- Youth -<25-- Young Adults -25-34-- Adults -35-64-- Seniors ->64-HINT: Use the nested IF-- functions- Alternatively, you can use the IFS-- function if it is available in your version -2016 + updates from O365-
Now, let's add a "Frame Size" column- Insert a new column to the left of the Order Quantity, and use a combination of the IF-- and RIGHT-- functions to derive the frame size of a bicycle from the last two characters of the Product column, when the Product Category is Bikes- Otherwise, leave it blank-
HINT: Use the IF-- function to test for Product Category="Bikes" and if it does, use the RIGHT-- function to extract the last two characters of the Product column-
Last but not least, let's add a "Profit" column- Insert a new column to the right of the Revenue, and use formula to derive the Profit from both the Revenue and Cost columns- Show the total for the Profit column- Use the Sum aggregation in the total row of the table, for the Profit column-
HINT: Profit is Revenue minus Cost-
Attachment:- data analytics life cycle-rar
Most Recent Questions