Dr lark dentist adrian mi

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.