Answer the questions below by
placing the appropriate graph and/or answers in the designated cells of the
spreadsheet (spreadsheet is attached separately).
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)
