Completing this activity will help you leam to: - Extract information from sales data. - Extract information from a data set using Excel formulas, functions, and/or pivot tables. Scenario: Company A is a new business that sells university merchandise online. The company sells hats for $10 each, t-shirts for $15 each, and sweatshirts for $30 each. The company sells wholesale to six customers: Attanta, Dakota, Madrid, Orlando, Phoenix, and Venice companies. To help plan for next year, the company is analyzing sales from the year that just ended. The Data worksheet contains a list of invoice numbers related to product sales. Each row provides information about an invoice number for one of the three products, including invoice date, product sold, customer name, quantity sold, and selling price per unit. Note that the same invoice number may be listed up to three times in column D. This is because each of the products on an involce is listed on its own separate row. Required: Use the information on the Data worksheet and plvot tables, formulas, and/or functions to answer the following questions. Q1. What is the total amount billed on invoice 1230 ? \$ Q2. How many hats were sold to Madrid and Phoenix during the year? Q3. Which month has the highest dollar value of sweatshirt sales? Q4. How many of invoices 1001 through 1012 include the sale of more than 30 itens? Q5. Which of the following statements is true? The quantry times the selling rice of a produrt equals the umount to umpice for that peoduct. A different involice number was issued for each product sold to a cormpany en a particular dale. Gross profit can rasily be desermined based on the data given. R1. Cell I2: Use a formula referring to other amounts in the row to calculate total invoice amount for row 2 only. $ R2. Copy down the entry in cell 12 to all the other rows with data. Steps to create a pivot table: S1. Select a cell in the data on the Data worksheet. S2. Select Insert / PivotTable. The data range will automatically be inserted. A "Create PivotTable" window should appear. S3. In the window, under "Choose where you want the PivotTable report to be placed", select "New Worksheet". A new worksheet should app with the PivotTable. S4. A PivotTable Field List should pop up. Drag fields into rows, columns, filters, and values areas as needed. (If this gray work box does not appear, right click in the pivot table and select "Show Field List".) Questions: Use the information on the Data worksheet and pivot tables, formulas, and/or functions to answer the following questions. Q1. What is the total amount billed on invoice 1230 ? $ Q2. How many hats were sold to Madrid and Phoenix during the year
