Dr ashwin shah monroe 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.

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
ABC Company Payroll
for Period Ending 9/7/2014
Name Department Rate of Pay Pay Basis HI Enrollment Hours Worked Regular Hours OT Hours Regular Pay OT Pay Gross Pay Federal Tax State Tax Local Tax FICA Health Ins Deduction Total Deductions Net Pay
Ackerman, Jerry English $ 12.00 Hourly I 40 40 0 $480.00 $0.00 $480.00 $134.40 $21.60 $10.56 $73.44 $27.00 $267.00 $213.00
Adams, Cecilia Business $ 250.00 Salary F 40 40 0 $250.00 $0.00 $250.00 $11.25 $11.25 $5.50 $38.25 $72.75 $139.00 $111.00
Arston, Jean Business $ 7.50 Hourly I 50 40 10 $300.00 $112.50 $412.50 $9.08 $18.56 $9.08 $63.11 $27.00 $126.83 $285.68
Aziz, Nashir Geography $ 8.00 Hourly F 32 32 0 $256.00 $0.00 $256.00 $39.17 $11.52 $5.63 $39.17 $72.75 $168.24 $87.76
Bertin, Julia Geography $ 550.00 Salary F 40 40 0 $550.00 $0.00 $550.00 $0.00 $24.75 $12.10 $84.15 $72.75 $193.75 $356.25
Booth, Wesley History $ 1,200.00 Salary X 42 42 0 $1,200.00 $0.00 $1,200.00 $0.00 $54.00 $26.40 $183.60 $0.00 $264.00 $936.00
Bose, Denise Sciences $ 18.75 Hourly X 40 40 0 $750.00 $0.00 $750.00 $0.00 $33.75 $16.50 $114.75 $0.00 $165.00 $585.00
Bows, Graham Geography $ 7.50 Hourly X 36 36 0 $270.00 $0.00 $270.00 $0.00 $12.15 $5.94 $41.31 $0.00 $59.40 $210.60
Brewski, Randy Business $ 600.00 Salary I 44 44 0 $600.00 $0.00 $600.00 $0.00 $27.00 $13.20 $91.80 $27.00 $159.00 $441.00
Brown, Raymond Agriculture $ 600.00 Salary I 40 40 0 $600.00 $0.00 $600.00 $0.00 $27.00 $13.20 $91.80 $27.00 $159.00 $441.00
Cameron, Jacqueline Health & Human Svcs $ 1,200.00 Salary I 25 25 0 $1,200.00 $0.00 $1,200.00 $0.00 $54.00 $26.40 $183.60 $27.00 $291.00 $909.00
Chan, Dorothy Business $ 9.50 Hourly F 46 40 6 $380.00 $85.50 $465.50 $0.00 $20.95 $10.24 $71.22 $72.75 $175.16 $290.34
Cooper, Sara Business $ 450.00 Salary F 40 40 0 $450.00 $0.00 $450.00 $0.00 $20.25 $9.90 $68.85 $72.75 $171.75 $278.25
DeFleur, Gregg Business $ 7.50 Hourly F 40 40 0 $300.00 $0.00 $300.00 $0.00 $13.50 $6.60 $45.90 $72.75 $138.75 $161.25
Drummond, Chris Business $ 7.30 Hourly F 55 40 15 $292.00 $164.25 $456.25 $0.00 $20.53 $10.04 $69.81 $72.75 $173.13 $283.13
Earle, Jenna Computer Science $ 550.00 Salary X 42 42 0 $550.00 $0.00 $550.00 $0.00 $24.75 $12.10 $84.15 $0.00 $121.00 $429.00
Edsell, Camilla Business $ 11.75 Hourly F 43 40 3 $470.00 $52.88 $522.88 $0.00 $23.53 $11.50 $80.00 $72.75 $187.78 $335.09
Farris, Linda Sciences $ 12.00 Hourly X 27 27 0 $324.00 $0.00 $324.00 $0.00 $14.58 $7.13 $49.57 $0.00 $71.28 $252.72
Flanders, Dawn Geography $ 350.00 Salary I 40 40 0 $350.00 $0.00 $350.00 $0.00 $15.75 $7.70 $53.55 $27.00 $104.00 $246.00
Garcia, Rosa History $ 10.00 Hourly I 48 40 8 $400.00 $120.00 $520.00 $0.00 $23.40 $11.44 $79.56 $27.00 $141.40 $378.60
Getz, Anthoney Fine Arts $ 275.00 Salary F 40 40 0 $275.00 $0.00 $275.00 $0.00 $12.38 $6.05 $42.08 $72.75 $133.25 $141.75
Goode, Barb Health & Human Svcs $ 12.00 Hourly I 43 40 3 $480.00 $54.00 $534.00 $0.00 $24.03 $11.75 $81.70 $27.00 $144.48 $389.52
Gray, Stacey Education $ 12.00 Hourly F 22 22 0 $264.00 $0.00 $264.00 $0.00 $11.88 $5.81 $40.39 $72.75 $130.83 $133.17
Guerroro, Tamara Computer Science $ 125.00 Salary F 40 40 0 $125.00 $0.00 $125.00 $0.00 $5.63 $2.75 $19.13 $72.75 $100.25 $24.75
Hamazaki, Jeff Business $ 10.00 Hourly X 40 40 0 $400.00 $0.00 $400.00 $0.00 $18.00 $8.80 $61.20 $0.00 $88.00 $312.00
Hamel, Darryl Health & Human Svcs $ 11.00 Hourly X 44 40 4 $440.00 $66.00 $506.00 $0.00 $22.77 $11.13 $77.42 $0.00 $111.32 $394.68
Hedges, John Education $ 150.00 Salary X 40 40 0 $150.00 $0.00 $150.00 $0.00 $6.75 $3.30 $22.95 $0.00 $33.00 $117.00
Henderson, Kela Business $ 12.00 Hourly I 40 40 0 $480.00 $0.00 $480.00 $0.00 $21.60 $10.56 $73.44 $27.00 $132.60 $347.40
Hendrickson, Mel Agriculture $ 250.00 Salary I 31 31 0 $250.00 $0.00 $250.00 $0.00 $11.25 $5.50 $38.25 $27.00 $82.00 $168.00
Hillman, Frances Business Admin $ 7.50 Hourly I 54 40 14 $300.00 $157.50 $457.50 $0.00 $20.59 $10.07 $70.00 $27.00 $127.65 $329.85
Horton, Samantha Geography $ 1,200.00 Salary F 40 40 0 $1,200.00 $0.00 $1,200.00 $0.00 $54.00 $26.40 $183.60 $72.75 $336.75 $863.25
Huberty, Tessa Business $ 750.00 Salary F 40 40 0 $750.00 $0.00 $750.00 $0.00 $33.75 $16.50 $114.75 $72.75 $237.75 $512.25
Hurley, Stuart Sciences $ 1,500.00 Salary F 40 40 0 $1,500.00 $0.00 $1,500.00 $0.00 $67.50 $33.00 $229.50 $72.75 $402.75 $1,097.25
Lee, Jerri Health & Human Svcs Hourly F 47 40 7 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $72.75 $72.75 ($72.75)
Lemiski, Ryan Business $ 650.00 Salary X 40 40 0 $650.00 $0.00 $650.00 $0.00 $29.25 $14.30 $99.45 $0.00 $143.00 $507.00
Lewis, Nathan Business $ 8.75 Hourly F 44 40 4 $350.00 $52.50 $402.50 $0.00 $18.11 $8.86 $61.58 $72.75 $161.30 $241.20
Lockwood, Shaine Education $ 1,200.00 Salary X 43 43 0 $1,200.00 $0.00 $1,200.00 $0.00 $54.00 $26.40 $183.60 $0.00 $264.00 $936.00
Lopez, Victor Sciences $ 550.00 Salary I 40 40 0 $550.00 $0.00 $550.00 $0.00 $24.75 $12.10 $84.15 $27.00 $148.00 $402.00
MacPherson, Jack Health & Human Svcs $ 1,200.00 Salary I 30 30 0 $1,200.00 $0.00 $1,200.00 $0.00 $54.00 $26.40 $183.60 $27.00 $291.00 $909.00
Maki, Ed Business $ 18.75 Hourly F 25 25 0 $468.75 $0.00 $468.75 $0.00 $21.09 $10.31 $71.72 $72.75 $175.88 $292.88
McLean, Barb Fine Arts $ 7.50 Hourly I 43 40 3 $300.00 $33.75 $333.75 $0.00 $15.02 $7.34 $51.06 $27.00 $100.43 $233.33
McLeish, Cindy English $ 600.00 Salary F 40 40 0 $600.00 $0.00 $600.00 $0.00 $27.00 $13.20 $91.80 $72.75 $204.75 $395.25
Morgan, Cindy Health & Human Svcs $ 600.00 Salary F 40 40 0 $600.00 $0.00 $600.00 $0.00 $27.00 $13.20 $91.80 $72.75 $204.75 $395.25
Morris, Alice Health & Human Svcs $ 1,200.00 Salary X 48 48 0 $1,200.00 $0.00 $1,200.00 $0.00 $54.00 $26.40 $183.60 $0.00 $264.00 $936.00
Mulvaney, Marcey Education $ 9.50 Hourly X 40 40 0 $380.00 $0.00 $380.00 $0.00 $17.10 $8.36 $58.14 $0.00 $83.60 $296.40
Murray, Dwayne History $ 450.00 Salary X 43 43 0 $450.00 $0.00 $450.00 $0.00 $20.25 $9.90 $68.85 $0.00 $99.00 $351.00
Palma, Emerald Computer Science $ 7.50 Hourly I 40 40 0 $300.00 $0.00 $300.00 $0.00 $13.50 $6.60 $45.90 $27.00 $93.00 $207.00
Pitt, Dennis English $ 7.30 Hourly I 40 40 0 $292.00 $0.00 $292.00 $0.00 $13.14 $6.42 $44.68 $27.00 $91.24 $200.76
Provost, Everett Computer Science $ 550.00 Salary I 40 40 0 $550.00 $0.00 $550.00 $0.00 $24.75 $12.10 $84.15 $27.00 $148.00 $402.00
Schuler, Presley Business $ 11.75 Hourly F 44 40 4 $470.00 $70.50 $540.50 $0.00 $24.32 $11.89 $82.70 $72.75 $191.66 $348.84
Singh, Balwant History $ 12.00 Hourly F 40 40 0 $480.00 $0.00 $480.00 $0.00 $21.60 $10.56 $73.44 $72.75 $178.35 $301.65
Skubiak, Jaime Education $ 350.00 Salary F 40 40 0 $350.00 $0.00 $350.00 $0.00 $15.75 $7.70 $53.55 $72.75 $149.75 $200.25
Smith, Albert Education $ 10.00 Hourly F 40 40 0 $400.00 $0.00 $400.00 $0.00 $18.00 $8.80 $61.20 $72.75 $160.75 $239.25
Suel, Ingvar Education $ 275.00 Salary X 54 54 0 $275.00 $0.00 $275.00 $0.00 $12.38 $6.05 $42.08 $0.00 $60.50 $214.50
Swift, Gilles Education $ 12.00 Hourly F 40 40 0 $480.00 $0.00 $480.00 $0.00 $21.60 $10.56 $73.44 $72.75 $178.35 $301.65
Tanaka, Brian Health & Human Svcs $ 12.00 Hourly X 40 40 0 $480.00 $0.00 $480.00 $0.00 $21.60 $10.56 $73.44 $0.00 $105.60 $374.40
Tang, Kim Health & Human Svcs $ 125.00 Salary I 40 40 0 $125.00 $0.00 $125.00 $0.00 $5.63 $2.75 $19.13 $27.00 $54.50 $70.50
Tarlton, Victor Health & Human Svcs $ 10.00 Hourly F 47 40 7 $400.00 $105.00 $505.00 $0.00 $22.73 $11.11 $77.27 $72.75 $183.85 $321.15
Thompson, Murray Business Admin $ 11.00 Hourly F 40 40 0 $440.00 $0.00 $440.00 $0.00 $19.80 $9.68 $67.32 $72.75 $169.55 $270.45
Todd, Shig Health & Human Svcs $ 12.50 Hourly F 44 40 4 $500.00 $75.00 $575.00 $0.00 $25.88 $12.65 $87.98 $72.75 $199.25 $375.75
Walsh, Moira Business $ 13.00 Hourly X 43 40 3 $520.00 $58.50 $578.50 $0.00 $26.03 $12.73 $88.51 $0.00 $127.27 $451.23
Williams, Peter Fine Arts $ 1,200.00 Salary X 40 40 0 $1,200.00 $0.00 $1,200.00 $0.00 $54.00 $26.40 $183.60 $0.00 $264.00 $936.00
Williams, Rich Business $ 750.00 Salary X 40 40 0 $750.00 $0.00 $750.00 $0.00 $33.75 $16.50 $114.75 $0.00 $165.00 $585.00
Winkler, Janice Business $ 5.50 Hourly X 40 40 0 $220.00 $0.00 $220.00 $0.00 $9.90 $4.84 $33.66 $0.00 $48.40 $171.60
Withers, Martin History $ 800.00 Salary I 40 40 0 $800.00 $0.00 $800.00 $0.00 $36.00 $17.60 $122.40 $27.00 $203.00 $597.00
Yap, Stevey Business $ 500.00 Salary I 40 40 0 $500.00 $0.00 $500.00 $0.00 $22.50 $11.00 $76.50 $27.00 $137.00 $363.00
Modano, Mike Sports Science $ 9.75 Hourly F 44 40 4 $390.00 $58.50 $448.50 $0.00 $20.18 $9.87 $68.62 $72.75 $171.42 $277.08
Totals $34,456.75 $1,266.38 $35,723.13 $193.89 $1,607.54 $785.91 $5,465.64 $2,577.00 $10,629.98 $25,093.14
&P &Z&F&D

Shipping Report
ABC Company - Order & Shipping Report
Vendor Name Vendor No. Order No. Item No. Item Description Item Cost Quantity Product Cost Shipping Cost Cost Per Order Projected Revenue A/P Terms Promised Ship Date Promised Transit Time (Days) Actual Arrival Date Days Overdue
Disk Professionals 1 C5656 1122 Hard drives $ 55.00 1,450 $ 79,750.00 $ 1,607.50 81,357.50 $ 7,962.50 Net 30 6/13/13 8 6/24/13 -3
Disk Professionals 1 D2121 1234 Hard drives $ 55.00 1,750 $ 96,250.00 $ 1,937.50 98,187.50 $ 9,612.50 Net 30 7/10/13 9 7/28/13 -9
Disk Professionals 1 C2323 1333 Hard drives $ 55.00 1,550 $ 85,250.00 $ 1,717.50 86,967.50 $ 8,512.50 Net 30 5/20/13 9 5/28/13 1
Disk Professionals 1 C4545 1346 Hard drives $ 45.00 1,500 $ 67,500.00 $ 1,362.50 68,862.50 $ 6,737.50 Net 30 5/24/13 8 5/27/13 5
Disk Professionals 1 C1212 1369 Hard drives $ 44.00 1,950 $ 85,800.00 $ 1,728.50 87,528.50 $ 8,567.50 Net 30 4/20/13 8 4/26/13 2
Disk Professionals 1 C3434 1445 Hard drives $ 45.00 1,250 $ 56,250.00 $ 1,137.50 57,387.50 $ 5,612.50 Net 30 5/21/13 8 5/25/13 4
Disk Professionals 1 D3232 1788 Hard drives $ 60.00 1,700 $ 102,000.00 $ 2,052.50 104,052.50 $ 10,187.50 Net 30 6/26/13 9 7/4/13 1
Disk Professionals 1 C3232 1789 Hard drives $ 60.00 1,800 $ 108,000.00 $ 2,172.50 110,172.50 $ 10,787.50 Net 30 6/16/13 8 6/16/13 8
Disk Professionals 1 C6765 5066 Shielded Cable/ft. $ 2.00 2,500 $ 5,000.00 $ 112.50 5,112.50 $ 487.50 Net 30 5/21/13 9 6/4/13 -5
Disk Professionals 1 D1212 5066 Shielded Cable/ft. $ 1.95 17,500 $ 34,125.00 $ 695.00 34,820.00 $ 3,400.00 Net 30 5/31/13 9 6/18/13 -9
Disk Professionals 1 C8854 5166 Electrical Connector $ 2.25 5,425 $ 12,206.25 $ 256.63 12,462.88 $ 1,208.13 Net 30 5/26/13 6 5/31/13 1
Disk Professionals 1 C2929 5166 Electrical Connector $ 2.30 5,500 $ 12,650.00 $ 265.50 12,915.50 $ 1,252.50 Net 30 5/17/13 6 6/4/13 -12
Disk Professionals 1 C1313 5166 Electrical Connector $ 2.25 5,600 $ 12,600.00 $ 264.50 12,864.50 $ 1,247.50 Net 30 5/10/13 6 5/19/13 -3
Disk Professionals 1 C7875 5166 Electrical Connector $ 2.35 5,650 $ 13,277.50 $ 278.05 13,555.55 $ 1,315.25 Net 30 5/21/13 6 6/10/13 -14
Fastener Warehouse 2 A1449 4111 Bolt-nut package $ 4.30 4,600 $ 19,780.00 $ 408.10 20,188.10 $ 1,965.50 Net 25 6/20/13 16 6/29/13 7
Fastener Warehouse 2 A5689 4422 Bolt-nut package $ 4.60 4,585 $ 21,091.00 $ 434.32 21,525.32 $ 2,096.60 Net 25 5/26/13 16 6/8/13 3
Fastener Warehouse 2 A1222 4569 Bolt-nut package $ 4.80 4,200 $ 20,160.00 $ 415.70 20,575.70 $ 2,003.50 Net 25 5/31/13 21 6/15/13 6
Fastener Warehouse 2 A3467 4679 Bolt-nut package $ 4.70 4,800 $ 22,560.00 $ 463.70 23,023.70 $ 2,243.50 Net 25 5/21/13 10 5/24/13 7
Fastener Warehouse 2 A1445 4698 Bolt-nut package $ 4.80 4,200 $ 20,160.00 $ 415.70 20,575.70 $ 2,003.50 Net 25 6/10/13 25 6/25/13 10
Fastener Warehouse 2 A1444 4778 Bolt-nut package $ 4.80 4,250 $ 20,400.00 $ 420.50 20,820.50 $ 2,027.50 Net 25 6/5/13 14 6/25/13 -6
Fastener Warehouse 2 A0666 5066 Shielded Cable/ft. $ 2.10 15,000 $ 31,500.00 $ 642.50 32,142.50 $ 3,137.50 Net 25 6/16/13 10 6/17/13 9
Fastener Warehouse 2 A0111 6431 Hex nut $ 3.05 900 $ 2,745.00 $ 67.40 2,812.40 $ 262.00 Net 25 6/25/13 6 7/9/13 -8
Fastener Warehouse 2 A0777 6431 Hex nut $ 3.10 1,050 $ 3,255.00 $ 77.60 3,332.60 $ 313.00 Net 25 7/14/13 6 7/17/13 3
Fastener Warehouse 2 A0555 6431 Hex nut $ 3.00 1,100 $ 3,300.00 $ 78.50 3,378.50 $ 317.50 Net 25 6/20/13 6 7/8/13 -12
Fastener Warehouse 2 A0533 9764 Gasket $ 14.10 1,500 $ 21,150.00 $ 435.50 21,585.50 $ 2,102.50 Net 25 6/5/13 6 6/8/13 3
Fastener Warehouse 2 A0622 9764 Gasket $ 14.30 1,550 $ 22,165.00 $ 455.80 22,620.80 $ 2,204.00 Net 25 6/10/13 6 6/23/13 -7
Regent Products 3 B1666 1369 Hard drives $ 4.20 10,000 $ 42,000.00 $ 852.50 42,852.50 $ 4,187.50 Net 45 6/14/13 6 6/22/13 -2
Regent Products 3 A1567 1369 Hard drives $ 4.25 15,000 $ 63,750.00 $ 1,287.50 65,037.50 $ 6,362.50 Net 45 6/10/13 6 6/13/13 3
Regent Products 3 B1468 1369 Hard drives $ 4.35 14,000 $ 60,900.00 $ 1,230.50 62,130.50 $ 6,077.50 Net 45 6/12/13 7 6/17/13 2
Regent Products 3 A1457 4569 Bolt-nut package $ 3.50 3,900 $ 13,650.00 $ 285.50 13,935.50 $ 1,352.50 Net 45 6/20/13 6 6/26/13 0
Regent Products 3 B1589 5066 Shielded Cable/ft. $ 2.00 25,000 $ 50,000.00 $ 1,012.50 51,012.50 $ 4,987.50 Net 45 7/10/13 5 7/23/13 -8
Regent Products 3 A1346 9399 Gasket $ 13.60 1,985 $ 26,996.00 $ 552.42 27,548.42 $ 2,687.10 Net 45 6/20/13 6 7/1/13 -5
Regent Products 3 A1235 9632 Gasket $ 13.50 1,450 $ 19,575.00 $ 404.00 19,979.00 $ 1,945.00 Net 45 6/18/13 6 6/22/13 2
Regent Products 3 A1234 9764 Gasket $ 13.20 1,250 $ 16,500.00 $ 342.50 16,842.50 $ 1,637.50 Net 45 6/16/13 6 7/4/13 -12
Regent Products 3 A1345 9877 Gasket $ 13.80 1,470 $ 20,286.00 $ 418.22 20,704.22 $ 2,016.10 Net 45 6/22/13 6 7/12/13 -14
Endurance Hardware 4 B2537 5066 Shielded Cable/ft. $ 2.00 2,150 $ 4,300.00 $ 98.50 4,398.50 $ 417.50 Net 30 4/30/13 6 4/30/13 6
Endurance Hardware 4 B2519 5066 Shielded Cable/ft. $ 2.05 2,250 $ 4,612.50 $ 104.75 4,717.25 $ 448.75 Net 30 5/5/13 6 5/17/13 -6
Endurance Hardware 4 B2566 5066 Shielded Cable/ft. $ 2.00 2,300 $ 4,600.00 $ 104.50 4,704.50 $ 447.50 Net 30 4/25/13 6 5/3/13 -2
Endurance Hardware 4 C0333 5066 Shielded Cable/ft. $ 2.05 2,250 $ 4,612.50 $ 104.75 4,717.25 $ 448.75 Net 30 5/10/13 7 5/21/13 -4
Endurance Hardware 4 C0234 5166 Electrical Connector $ 2.25 5,650 $ 12,712.50 $ 266.75 12,979.25 $ 1,258.75 Net 30 6/16/13 6 6/21/13 1
Endurance Hardware 4 B2498 5689 Side Panel $ 6.10 150 $ 915.00 $ 30.80 945.80 $ 79.00 Net 30 7/17/13 8 7/18/13 7
Endurance Hardware 4 C0123 5689 Side Panel $ 6.15 155 $ 953.25 $ 31.57 984.82 $ 82.83 Net 30 7/10/13 9 7/15/13 4
Endurance Hardware 4 B2528 5689 Side Panel $ 6.10 175 $ 1,067.50 $ 33.85 1,101.35 $ 94.25 Net 30 7/21/13 8 8/6/13 -8
Endurance Hardware 4 B2356 6321 Hex nut $ 2.45 2,500 $ 6,125.00 $ 135.00 6,260.00 $ 600.00 Net 30 6/10/13 10 6/27/13 -7
Endurance Hardware 4 B2378 6355 Hex nut $ 2.75 1,500 $ 4,125.00 $ 95.00 4,220.00 $ 400.00 Net 30 7/10/13 9 7/18/13 1
Endurance Hardware 4 B2345 6377 Hex nut $ 2.50 1,200 $ 3,000.00 $ 72.50 3,072.50 $ 287.50 Net 30 5/28/13 10 6/1/13 6
Endurance Hardware 4 B2367 6399 Hex nut $ 2.60 1,250 $ 3,250.00 $ 77.50 3,327.50 $ 312.50 Net 30 6/27/13 9 7/8/13 -2
Endurance Hardware 4 B2333 6431 Hex nut $ 2.55 1,300 $ 3,315.00 $ 78.80 3,393.80 $ 319.00 Net 30 5/10/13 11 5/29/13 -8
Plastic Plus 5 A0443 1369 Hard drives $ 5.65 1,000 $ 5,650.00 $ 125.50 5,775.50 $ 552.50 1%/10, Net 30 4/23/13 6 4/23/13 6
Plastic Plus 5 B0567 1369 Hard drives $ 4.85 1,050 $ 5,092.50 $ 114.35 5,206.85 $ 496.75 1%/10, Net 30 6/25/13 7 7/6/13 -4
Plastic Plus 5 B0247 1369 Hard drives $ 4.75 9,000 $ 42,750.00 $ 867.50 43,617.50 $ 4,262.50 1%/10, Net 30 5/21/13 6 5/29/13 -2
Plastic Plus 5 A0223 4569 Bolt-nut package $ 4.95 4,500 $ 22,275.00 $ 458.00 22,733.00 $ 2,215.00 1%/10, Net 30 6/30/13 6 7/18/13 -12
Plastic Plus 5 B0479 5689 Side Panel $ 5.75 140 $ 805.00 $ 28.60 833.60 $ 68.00 1%/10, Net 30 7/14/13 6 7/21/13 -1
Plastic Plus 5 B0147 5689 Side Panel $ 5.75 150 $ 862.50 $ 29.75 892.25 $ 73.75 1%/10, Net 30 7/10/13 6 7/26/13 -10
Cable Ready Inc. 6 B0345 4569 Bolt-nut package $ 4.85 4,150 $ 20,127.50 $ 415.05 20,542.55 $ 2,000.25 1%/10, Net 30 5/19/13 6 5/23/13 2
Cable Ready Inc. 6 A0123 4569 Bolt-nut package $ 4.65 4,250 $ 19,762.50 $ 407.75 20,170.25 $ 1,963.75 1%/10, Net 30 5/10/13 6 5/28/13 -12
Cable Ready Inc. 6 A0207 4569 Bolt-nut package $ 4.75 4,200 $ 19,950.00 $ 411.50 20,361.50 $ 1,982.50 1%/10, Net 30 5/17/13 7 5/23/13 1
Cable Ready Inc. 6 C0589 5066 Shielded Cable/ft. $ 2.05 18,100 $ 37,105.00 $ 754.60 37,859.60 $ 3,698.00 1%/10, Net 30 5/10/13 11 5/25/13 -4
Cable Ready Inc. 6 A0115 5066 Shielded Cable/ft. $ 2.10 17,500 $ 36,750.00 $ 747.50 37,497.50 $ 3,662.50 1%/10, Net 30 5/5/13 12 5/16/13 1
Cable Ready Inc. 6 A0203 5066 Shielded Cable/ft. $ 2.00 16,500 $ 33,000.00 $ 672.50 33,672.50 $ 3,287.50 1%/10, Net 30 5/31/13 16 6/10/13 6
Cable Ready Inc. 6 B3011 5166 Electrical Connector $ 2.75 4,500 $ 12,375.00 $ 260.00 12,635.00 $ 1,225.00 1%/10, Net 30 5/13/13 6 5/20/13 -1
Cable Ready Inc. 6 B3012 5166 Electrical Connector $ 2.55 4,750 $ 12,112.50 $ 254.75 12,367.25 $ 1,198.75 1%/10, Net 30 5/21/13 6 5/23/13 4
Cable Ready Inc. 6 B3111 5166 Electrical Connector $ 2.65 4,850 $ 12,852.50 $ 269.55 13,122.05 $ 1,272.75 1%/10, Net 30 5/18/13 7 6/5/13 -11
Cable Ready Inc. 6 A0205 5689 Side Panel $ 6.05 120 $ 726.00 $ 27.02 753.02 $ 60.10 1%/10, Net 30 7/18/13 9 7/20/13 7
Cable Ready Inc. 6 B3022 5689 Side Panel $ 6.05 110 $ 665.50 $ 25.81 691.31 $ 54.05 1%/10, Net 30 7/21/13 10 7/29/13 2
Cable Ready Inc. 6 C0456 5689 Side Panel $ 6.05 130 $ 786.50 $ 28.23 814.73 $ 66.15 1%/10, Net 30 7/13/13 9 8/2/13 -11
TOTALS $ 691.90 $ 294,745.00 $ 1,629,818.00 $ 33,421.36 $ 1,663,239.36 $ 162,156.80
&Z&F &P

Excel-Hw/Excel Exercise 6.xlsx
TextFunctions
Instructions: Import the Excercise6.txt file and use the necessary Text Functions to convert the data into a more usable form. For most of these requirements, you will need to create a new column of data using a text function(s). Then, Copy and Paste Values over to the NewTextWorksheet, rebuilding the data column by column. Leave the work-in-process columns in this worksheet with your formulas intact so I can see your formulas and solutions. 1) The Provider number is eithe r a Corporate Tax-ID number or an Individual SSN. Corporate tax-ID's have the format of xx-xxxxxxx (which appears correctly in the data). Insert the dashes in the SSNs so that they display 999-99-9999 ( no changes should be made to the Corporate IDs). This operation will require an IF() function to evaluate the data -- and determine if the data should be left as is or re-formatted. 2) Display the Provider name in Proper case 3) Insert a Column of data between Provider Name and Address - that displays "Medical Facility" if the word "Hospital" is anywhere in the Provider's Name, "Dental" for any providers with "dds" in the name. Leave that column blank for all other providers. You will need to use an IF() function - and a FIND() function... but the FIND() function returns an error when the text you seek isn't found. So check out the ISNUMBER() or ISERROR() functions that will allow you to test to see if the FIND() is sucessful -- and respond accordinly. 4) Remove the prefix: "ADDRESS1:" from the Address and display it in Proper case. 5) Split the City and State into 2 separate columns -- notice that there is a comma "," that delineates the data. Display the City as Proper data and the State as UPPERCASE. 6) Display only the first 5 digits of the zip code. 7) Add the "/" in the Date data so that it properly displays as a date. You will need to evaluate the length of the data, if the data has 7 characters (a 1 digit month) -- put the extra 0 on the front of the data. Use the Len, Left, Right and Mid functions to accomplish this. After properly building the data - and copying the new date to the NewTextWorksheet, select to display the data in the Date Format of dd-MON-yy 8) Copy and paste_special (values only) each of the manipulated and newly formmatted columns to rebuild the data on the worksheet titled: NewTextWorksheet. Print the NewTextWorksheet and attach it to the grading sheet.

NewTextWorksheet
Copy the Values (only) on this page - after completing your text manipulation. Leave your formulas on the TextFunctions sheet.
Tax-ID Provider Name Provider Type Address City State Zip Date Ad