Question 1
The data for
question 1 is located on the excel worksheet tab named 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?).
a. 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. Calculate and
display total sales for each quarter for all software products in cells D95
through G95.
c. Using the Pivot
Table Output, what are the total average quarterly sales for all software
products? (Place the answer in cell B98)
d. Using the Pivot
Table Output, what are the total average quarterly sales for Dbaseintegrator?
(Place the answer in cell B99)
e. Using the Pivot
Table Output, what are the total average quarterly sales for Sales Person
Turley? (Place the answer in cell B100)
f. 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)
