Question 1

The data for question 1 is located on the worksheet names Software.The worksheet contains a database containing information related to a software company's sales of network and database software to corporate customers.The database reveals the software products, the sales region, the salesperson, and each salesperson's quarterly, total, and average sales (in dollars) for each product for the last four quarters.

The current worksheet is full of data, but the format doesn't provide much useful information.Using Pivot Tables, we can distill the data into information that will aid in describing many aspects of the business.We can use Pivot Tables to answers questions like

• What are the best and worst selling products?
• What regions have the highest and lowest sales?
• Which salespersons are performing best or worst?
• How do the four quarters compare (sales staying stable, trending up, trending down, etc?).

It is suggested that your GA Group read and study Example 2.11 from our text before addressing Question 1.

a.8 Points: Using the data provided in the spreadsheet, construct a Pivot Table displaying the Sum of Average Quarterly Sales (body of table), displayed by "Sales Person" (Row Fields) and "Software Product" (Column Fields).Have the table placed with the uppermost left corner in cell A105.

b.4 Points: Calculate and display total sales for each quarter for all software products in cells D95 through G95.

c.2 Points: Using the Pivot Table Output, what are the total average quarterly sales for all software products?(Place the answer in cell B98)

d.2 Points: Using the Pivot Table Output, what are the total average quarterly sales for Dbaseintegrator?(Place the answer in cell B99)

e.2 Points: Using the Pivot Table Output, what are the total average quarterly sales for Sales Person Turley?(Place the answer in cell B100)

f.2 Points: Using the Pivot Table Output, what are the total average quarterly sales for Sales Person Rollins of Software Product NetworkEX?(Place the answer in cell B101)