Learning outcomes: L04: Use Microsoft Excel to sort, filter, and group different types of data. L05: Apply basic functions and formulas. Name: 1. Instructions and guidelines (Read carefully) Instructions 1. Insert your name and surname in the space provided above, as well as in the le name. Save the file as: First name Surname M3 U3 Ongoing project e.g. Lilly Smith M3 U3 Ongoing project. NB! Please ensure that you use the name that appears in your student profile on the Onlr'ne Campus (OLC). 2. Submit your assignment in Excel only. No other file types will be accepted. Guideline51. There are seven sheets in this workbook: Instructions, Answer 1, Answer 2, Answer 3, Answer 4, Answer 5, and Answer 6. 2. Please answer Question 1 to 6 in the corresponding sheets provided. Make sure that you have carefully read and fully understood the questions before answering them. Answer the questions fully but concisely and as directly as possible. Follow all specic instructions for individual questions.3. Answer all questions in your own words. Do not copy any text from the notes, readings or other sources. The assignment must be your own work only. 2. Mark allocation Each question receives a mark allocation. However, you will only receive afinal percentage mark and will not be given individual marks for each question. The mark allocation is there to show you the weighting and length of each question. Question 1: Formatting a worksheet 8 Question 2: Advanced formatting 8 Question 3: Manually creating formulas 8 Question 4: Applying functions 5 Question 5: Applying advanced functions 4 Question 6: Applying advanced functions 3 TOTAL: 36 3. Questions Question 1 The worksheet in the sheet titled Answer 1 contains the Sonic Selection customer information. All the customers who register as subscribers have completed an online registration form. The data that has been gathered through this process is stored in this worksheet. Organise the data by applying the following formatting and sorting instructions to the worksheet: 1. Change the shading ofthe heading cells {i.e. Row 1) to a shade of your choice and make the text bold. 2. Change the "Customerld" column heading and Cell A2 to italics and use the paintbrush formatting tool to apply Cell A2's format to the rest ofthe cells in Column A. 3. Apply the "Freeze Pane" tool to ensure the heading row and Column A are always displayed when scrolling down through the worksheet. 4. Sort the dataset according to the "LastName" column in ascending alphabetical order {A to Z}. 5. Remove the values from the "State", "PostalCode", "Phone", and "Fax" columns. Question 2 The worksheet provided in the Answer 2 tab contains the data related to Sonic Selections invoices. Organise the data by applying the following formatting instructions: 1. Apply conditional formatting to the "Total" column so that the top 10 sales are highlighted in green. 2. Insert a column to the left ofthe "InvoiceDate" column and give it the heading "InvoiceMonth". Use the paintbrush function to copy the heading row formatting. 3. For the cells in the new column, make them reference the "InvoiceDate" cell in the corresponding row (i.e. Cell C2 must reference Cell D2}. 4. Format the "InvoiceMonth" column to the custom format mmm-yy. 5. Filter the data set to only show invoices for August and September 2012. Question 3 The worksheet in the Answer 3 tab contains the same invoice data as the Answer 2 tab. Follow the instructions to calculate the commission earned off track sales, using this invoice data. Note that Sonic Selection pays its sales agents 30.05% commission of the track sales. 1. Add the sales commission amount in the space provided in Cell L1 on the worksheet. Enter the commission as a decimal number (i.e. if the commission is 20%, the number should be 0,2}. 2. Format this cell to be represented as a percentage with two decimal places. 3. Calculate the sales commission for each track sale in the USA in the column labelled "Commission" (column I). Do this by manually creating a formula that correctly references the commission percentage cell. (Hint: Filterthe dataset by country before performing the calculation.) 4. Create a new column labelled "Margin" in Column J, and calculate the margin forthe sale ofthe track sales in the U.S.A. Apply the formula to all ofthe U.S.A's Margin column cells. The margin of a sales is the sales amount less cost of sales expenses. In this case, commission is the only cost of sale expense. Question 4 The worksheet in the Answer 4 tab relates to the tracks sold by Sonic Selection. Use the following instructions to search for words in the dataset: 1. In Cell K2, use the FIND function to look up a word within the "TrackName" column. The word you choose to find can be typed into the space provided in Cell N3, and the first letter of the word should be capitalised, as the FIND function is case sensitive. The FIND function will return the starting position ofthe string within the "TrackName" column ifthe word exists within "TrackName". The function will return a #VALUE! Error ifthe word is not found. 2. Applythe function to all ofthe cells in Column K. Remember you can apply the filter function if you would like to exclude all the songs that do not have the word you chose in the title. (Hint: Make sure you reference Cell N3's value as opposed to hardcoding it.) Question 5 The worksheet in the Answer 5 tab contains the same data as Answer 4 tab. Use the VLOOKUP function to find the album title and genre based on a given track name. 1. Use the function to lookup the track name "Top Top". 2. Your answer should appear in the spaces available in the Answer 5 tab {album title in Cell M2, genre in Cell M3} Question 6 Using the customer information in the Answer 6 tab, apply the COUNTIF function to calculate the number of customers that are supported by each ofthe support representatives. Calculate your answers in the spaces provided in Cells P2, P3, and P4. Ensure that you reference the Name of Support Rep relevant cell in your answers