Data Management Assignment
Purpose: To demonstrate your ability to sort, format,
and query data in an Excel spreadsheet, and to use pivot tables to present
the data more clearly
You work for a firm that sells sporting goods throughout
the country. The firm tabulates production results in a spreadsheet,
but has had some difficulty in formatting the data to glean the information
management wants. Your job is to provide that formatting. The data is
stored in a file named BigStick2002.xls.
This project is worth 20 points. In order to earn 12 points
(60 percent), you must:
- Copy the file to your folder on the T drive
- Merge and center the first line as a header over the first 5 columns.
- Format the headers in the third row to wrap the text to fit within
the cells
- In the 5th column, calculate the percentage of sales returned. (Returns/Total
Sales) Format these cells to display a percent to 1 decimal place.
- Label the 5th column "Percent Returned".
- Rename the sheet "Input_Data"
In order to earn 14 points (70 percent) you must:
- Name the data block, including the labels in the third row, "Prod_Data".
This range must NOT include the first two rows.
- Sort the data to show results by region and then by quarter.
In order to earn 16 points (80 percent) you must:
- Create a criteria range with the same labels as row 3.
- Perform an advanced filter to show the number of units returned in
the Phoenix region for each quarter.
In order to earn 18 points (90 percent), you must:
- Create a second criteria range, again using the labels as in row 3.
- Use the DSUM( ) function to determine the total units returned for
the 3rd quarter in 2002.
- Use the DAVG( ) function to determine the average percent returned
in all regions for the same time period.
In order to earn 20 points (100 percent) you must
- Create a pivot table to show units returned. The columns should show
each of the regions, and the rows should show the quarters.
- This table should appear as a separate sheet with the name "Pivot_Table".
Enter your name in column A of the second row following any data. Save
the file, and submit it as an e-mail attachment.
|
