Reference no: EM133382469

Objectives: Learn and practice some functions and formulas in Excel for basic data analytics in Excel.

Tasks: Follow the instructions and submit the Excel file where you have a "Data" worksheet showing your table, new columns and calculations. This is an individual homework.

Instructions:

• Download the dataset from Canvas.
• Convert the data into an Excel table and name it "SalesTable". (10 points: 7 to make table with headers only for data not whole excel with empty cells; 3 for renaming the table.)
• Insert a new column to the right-hand side of the Revenue column, and name the new column, "Profit". Add a formula to subtract Total Cost from Revenue to derive the Profit. (10 points for profit column name and formula)
• Add Total Row for the table. Use the Sum aggregation to show the total of Profit column. (5 points for adding the total row to show sum of Profit)
• Add the SUM formula in the Total Row to calculate the sum of the Revenue column. (5 points for the sum of Revenue)
• In a row below the Total Row, use the AVERAGE formula to calculate the average of the Unit Price and Unit Cost columns. This new row doesn't need to be part of the SalesTable. (10 points: 5 each average formulas and output)
• Insert a new column to the right-hand side of the Year column and name it "Month" column, and use TEXT function to derive the month of sales from the Date column. The Month column then should include the name of the months (e.g. January) not the number. (15 points for the name of the column, formula and output).
• Insert a new column to the left-hand side of the Customer Gender, and use IF or IFS formula to derive the age group from the Customer Age column. Name the new column "Age Group". Group the customers based on the following criteria:
• Insert a new column to the left-hand side of the Order Quantity, and name it "Frame Size" and use a combination of the IF() (to first test for Product Category to be Bikes) and RIGHT() functions to derive the frame size of a bicycle from the last two characters of the Product column. Otherwise, leave it blank. (20 points - 8 points for the if function, 8 points for the right function, and 4 points for the correct combination and output)