PROJECT 2 – PIVOT TABLES

Order Description

Introduction to data analysis using MS Excel

Dirt Bikes USA is a small company headquartered in Carbondale, Colorado that manufactures and sells its own brand of off-road motorcycles. It was founded in 1993 to produce dirt bikes that could be customized for racing and off-road recreational riding using the best quality components and parts from all over the world. The company has continued to grow and now faces a new set of challenges and opportunities. You have been asked to serve as a consultant to apply your information systems knowledge to help Dirt Bikes solve some of the problems it is encountering.

In this exercise, you will learn how to use spreadsheet software to improve management decisions about selecting suppliers. You will filter transactional data on suppliers based on several different criteria to select the best suppliers for your company.

Imagine you consult to a company that manufactures bikes. Dirt Bikes have many competitors who are trying to offer lower prices and better service to customers, and you are trying to determine whether you can benefit from better supply chain management. In this assignment, you will find a spreadsheet file that contains a list of all of the items that your firm has ordered from its suppliers during the past three months. The fields in the spreadsheet file include vendor name, vendor identification number, purchaser’s order number, item identification number and item description (for each item ordered from the vendor), cost per item, number of units of the item ordered (quantity), total cost of each order, vendor’s accounts payable terms, order date, and actual arrival date for each order.

Prepare a recommendation of how you can use the data in this spreadsheet database to improve your decisions about selecting suppliers. Some criteria to consider for identifying preferred suppliers include the supplier’s track record for on-time deliveries, suppliers offering the best accounts payable terms, and suppliers offering lower pricing when the same item can be provided by multiple suppliers. Use your spreadsheet software to prepare a report to support your recommendations.

Requirements:

1. Download the “P1 Excel raw data fileView in a new window” to use.

2. Summarize the data the excel file into meaningful information to select best suppliers.

a. You can look at accounts payable terms (A/P terms) as an indicator.

b. Delivery time can be another indicator for decision making (i.e. who is the vendor with the best on-time delivery track record?)

c. Cost is another (sometimes the most important) way to compare suppliers.

d. There are useful tools in MS Excel such as the “Pivot Table” or functions like “=INDEX(), =DMAX(), =AVERAGEIF()” for summarizing data.

3. Write an executive summary (Save as PDF document).

a. Make sure it clearly states favorable lender(s) in cost, A/P terms, and delivery time.

b. Should explain each new tab in your excel file and the reason behind its existence.

c. Outline (clearly state) your recommendation.

d. Your executive summary needs to look professional.

4. Create an Excel file (*.xls or *.xlsx format) supporting your recommendation.

a. Should include a cost analysis for items and suppliers.

b. Should have a summary of A/P terms.

c. Indicate average delivery time for items and suppliers.

Clarifications and Suggestions: (with special thanks to students who helped)

1. There is no font/space requirement for the executive summary but it needs to be easily readable and look professional.

2. “Professional look” means a good looking report format that includes relevant information.

3. Save executive summary as PDF. Submit executive summary in *.pdf format.

4. Your writing and grammar skills will also be graded in this project. Writing center is a good resource to get help if you need it.

5. Do not print anything on paper and save some trees. All files will be submitted under the assignment.

6. Do not convert the Excel file to PDF, only executive summary will be PDF. Keep Excel spreadsheet in *.xls or *.xlsx format and submit an Excel file.

Submission Format:

Post two files under the assignment.

1. Executive summary (*.pdf file)

2. Excel analysis (*.xls or *.xlsx file)