1699 cook rd crossville tn

Excel-Hw/Excel - Exercise 5 Pivot Tables.doc
INFS 3250

Exercise 5 - Pivot Tables, Reports & Charts

Name: ____________________________ Points: ______ / 25
Due: See Blackboard Due Date! (2.5 pts deduction for each class period late)

This assignment requires for you to do some readings and research on the use and mechanics of Microsoft’s Pivot Table capabilities. Pivot tables replace the need to create summary tables as you have for the previous exercises. I think you will enjoy seeing a different way (and potentially easier way once you get the hang of it) of summarizing data into classifications.

Go to the following link: http://office.microsoft.com/en-us/support/results.aspx?qu=pivot+tables+Excel+2013&ex=1

There are 15 different help topics there – I suggest you follow each link and read through the information to gain a good understanding of Pivot Tables.

Once you have read through the material – download the assignment file: Excel Exercise 5.xlsx and complete the following operations (create each Pivot Table Report on its own worksheet):

Points

Description / Instructions

Each of the following Pivot Table operation is worth 2 points for a total of 20. Create the pivot table and/or chart. Pay attention to formatting. Save each table to its own worksheet with a descriptive tab name (for example: pivot-1, pivot-2, etc). Print each report and chart – clearly labeled and attach the prints to this grading sheet for submission.

1. Create a Pivot Table Report that summarizes the Payroll Data. Provide Totals for Gross Pay, Total Deductions and Total Net Pay by Department.

2. Create a Pivot Table Report that summarizes the Payroll Data by Hourly and Salary. Display counts, Total Gross Pay, Minimum Gross Pay, Average Gross Pay and Maximum Gross Pay.

3. Create a Pivot Table Report that summarizes the Payroll Data by Health Insurance Enrollment. Display by Department, the counts and total HI deduction for each enrollment (family, individual).

4. Create a Pivot Table Chart that summarizes the Payroll Data. Create a Pie chart that displays the % of Gross pay by department.

5. Create a Pivot Table Chart that summarizes the Payroll Data. Create a Bar chart that displays the Total Gross Pay and Total Deductions by Department.

6. Create a Pivot Table report that summarizes the Shipping Report. Provide totals of Qty, Product Cost, Shipping Costs, Cost perOrder and Projected Revenue by Vendor.

7. Create a Pivot Table report that summarizes the Shipping Report. Provide totals of Qty, Product Cost, Shipping Costs, Cost perOrder and Projected Revenue by Item#.

8. Create a Pivot Table report and chart from the Shipping Report that presents the total projected revenue by Vendor in a pie chart.

9. Create a Pivot Table report and chart from the Shipping Report that presents the total projected revenue by Item in a Bar Chart.

10. Create a Pivot Table report from the Shipping Report that presents the counts, totals, minimum, maximum and average of the Projected Revenue by Item#.

Organization of Workbook – WORTH 5 points

Rename worksheet tabs to reflect the Pivot table operation (you may simply use the corresponding step# from the above instructions). Properly label each required Pivot Table report and Chart (again, identifying the assignment step# would be very helpful) and print each report. Be sure to add report titles, chart titles, legends and anything else that would help the user read the reports.

Pay attention to aesthetic details. Be sure the user of the reports can immediately identify what is being reported. How you package this assignment together and identify your worksheets and will count as 5 points toward the total grade.

________

Total Points Awarded out of 25

Save your finalized document and post it to the Blackboard web site. Attach your printed reports to this document. Write your name on the top of this form and turn it in as a vehicle to communicate your graded project.

Excel-Hw/Excel - Exercise 6.doc
INFS 3250

In Class Project – Exercise 6

Text Functions

Name: ____________________________ Points: ______ / 20
Due: See Blackboard Date! (2 pts deduction for each class period late)

Download the Excel Exercise 6.xlsx spreadsheet and complete the following operations:

Text Manipulation Section

Import the external data file: Exercise6.txt into the worksheet titled TextFunctions. The file is a TAB Delimited file with headings. Manipulate the data using Excel’s Text functions to convert it in more meaningful and useful forms.

Rebuild the data – using the appropriate text functions on the original data. In most cases you will need to create one (or more) work-in-process columns to perform the text manipulations. Copy the manipulated data (properly converted) – and PASTE the VALUES onto the NewTextWorksheet. Leave the original TextFunctions spreadsheet intact so that I can see the formulas used in your solution.

1. 3 pts. Tax-ID’s are either individual SSN’s or Corporate Tax-IDs (that are formatted as xx-xxxxxxxx). Insert dashes in the SSN numbers in the Provider ID data – leaving the corporate tax-id data formatted as is. (This requires an IF() function to change only the SSNs).

2. 2 pts. Display the Provider Name in Proper Case.

3. 4 pts. Insert a column of data that displays the label “Medical Facility” for any Provider that has the word “Hospital” as part of its name, display “Dental Facility” for any “DDS” and leave all others blank. This requires the IF(), a FIND() and the ISNUMBER() or ISERROR() functions.

4. 2 pts. Display the Address without the ADDRESS1 prefix and in Proper case.

5. 3 pts. Separate the City & State data into 2 columns using the Left, Find and MID functions. Display the City in Proper text format and the State as upper case.

6. 2 pts. Display only the first 5 (Left 5) characters of the zip code.

7. 2pts. Insert the “/” (slashes) into the date data – so that date math can be performed on the data. Note that some dates are 7 characters in length while others are 8. For a 7-digit date (ie: 8292012), put the 0 on the front of the data so that it displays as a 2-digit month. Use the IF and LEN functions and then &, Left, Mid, and Right functions to build the formatted date.

8. 2 pts. Copy and paste special – values only the newly and correctly formatted data onto the Worksheet titled “RebuiltData”. Print ONLY the first page newly formatted data and attach it to the grading document.

Save your finalized document Excel Exercise 6.xlsx and post it to the Blackboard course site. Write your name on the top of this form, attach the printed report and turn it in as a tool to communicate your completed work and to receive back your graded project.

Excel-Hw/Excel Exercise 5 - Pivot Tables.xlsx
Payroll Weekly Data