Sales Analysis

Completing this activity will help you learn 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: Atlanta, 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 invoice is listed on its own separate row.

Required:

Open spreadsheet

Complete one column on the Data worksheet: Invoice Amount. Use this information to complete the requirements below.

R$1.$ Cell I$2$: Use a formula referring to other amounts in the row to calculate total invoice amount for row $2$ only.

fill in the blank $1$ of $1$$

R$2.$ Copy down the entry in cell I$2$ to all the other rows with data.

Steps to create a pivot table:

S$1.$ Select a cell in the data on the Data worksheet.

S$2.$ Select Insert $/$ PivotTable. The data range will automatically be inserted. A $$Create PivotTable$$ window should appear.

S$3.$ In the window, under "Choose where you want the PivotTable report to be placed", select $$New Worksheet$.$ A new worksheet should appear with the PivotTable.

S$4.$ 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.

Q$1.$ What is the total amount billed on invoice $1098?$

fill in the blank $1$ of $1$$

Q$2.$ How many hats were sold to Madrid and Dakota during the year?

fill in the blank $1$ of $1$

Q$3.$ Which month has the highest dollar value of sweatshirt sales?

JanFebMarAprMayJunJulAugSepOctNovDec

Q$4.$ How many of invoices $1001$ through $1012$ include the sale of more than $30$ items?

fill in the blank $1$ of $1$

Q$5.$ Which of the following statements is true?

The quantity times the selling price of a product equals the amount to invoice for that product.

A different invoice number was issued for each product sold to a company on a particular date.

Gross profit can easily be determined based on the data given.

The company that buys the most sweatshirts must be the most profitable since sweatshirts sell for more than each of the other two products.