Reference no: EM133125038

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