BUS/CPT 357

Franklin College

Summer 2003

Erich Prisner,

### Excel-

# Project 3

First download this Excel file.

A) Use a Pivot table to compare prices for the same product from
different vendors.

B)

- B1) Use Pivot tables to display the total amount of units bought from different parts from each vendor.
- B2) Make also two Pivot Charts here, one where the vendors are on the x-axis and the
products are on the y-axis, and one the other way.
- B3) Use the charts to answer the
following questions: Which are the two parts that are bought most often? Who are
the main vendors for them?

C)

- C1) Use a pivot chart to display the total amount of dollars paid to the vendors for
different parts in different months (arrival day counts here).
- C2) You have to add a column where you automatically create the month in which
the order was placed before doing so.
- C3) What is your most important (amount of
money) vendor? How did sales change over time?

D) Use another Pivot table to compare average (actual) transit times.

E) Use another Pivot table to have both information, average (actual) transit time, and prices per unit in the same table.
What is your conclusion here?

F) Next make the assumption that delay of more than 8 days (difference between
promised transit time and actual transit time) is inacceptable, and we want to find out those vendors who
had at least once a delay of at least 8 days for a certain product.
Again create a Pivot table, use "Maximum" and conditional
formatting to highlight those vendor-product combinations that should
be avoided in the future.

G) Next assume we want a detailed analysis for different months.
For some reason we want to disregard orders placed in October.
First add a column where you automatically create the month in which
the order was placed. Then do the same Pivot table with
month as third parameter (for the pages).
What is your conclusion if October orders won't count? (you may exclude
October).

Up to main page
Erich Prisner, June 2003