excel help.
Assignment 6 This assignment will examine the use of the logical, lookup and flash fill functions that we talked about. Problem 1) Create a new worksheet and name it Problem 1. Enter the following information: F 1 Payroll Report Hourly 2 Employee Hours Wage Pay Overtime? Absence? 3 Adams 28.00 11.50 4 Barkley 32.00 13.75 5 Billings 40.00 21.95 6 Davis 41.00 18.77 7 Dorring 39.75 15.44 8 Edwards 42.25 16.32 9 Fredricks 43.00 12.45 10 Harris 42.50 10.51 11 Jones 55.00 8.75 12 Jones 40.00 18.72 13 Lewis 40.00 23.50 14 Ocampo 40.00 21.22 15 Smith 40.25 19.74 16 Williams 47.00 19.75 17 18 Average 19 Total In column D. you are to create a formula that will determine a person's earnings. If the person had 40 or less hours, his/her pay is calculated by simply multiplying the hours the person worked by the hourly wage value. If the person worked more than 40 hours, then the pay is calculated using the following formula (Hours - 40) * 1.5 Hourly Wage) + (40 Hourly Wage). This will correctly pay time-and-a-half for any hours worked about 40 hours. In column E, you are to create a formula that will output "Yes if the person received overtime pay or "No" if he/she did not In column F, you are to create a formula that will output "Yes" if the person was absent during this pay period. You can determine this by looking to see if a full 40 hour work week was put in In row 18, calculate the average hours worked (B18) and the average hourly pay rate (C18). In row 19, calculate the total amount of payroll that was paid (119). Problem 2) Create a new worksheet in the same workbook and name it Problem 2. You need to design a unit cost price break spreadsheet for a company that currently sells three products: Sprockets for $22.50 a piece, Cogs at $16.00 a piece and Widgets for $19.35 apiece. This information should be placed in a vlookup table starting at cell 13 with the heading Product placed there and the heading "Standard Cost" in 13. The company also gives price breaks to customers who purchase larger quantities of items (the item types doesn't matter). The breakdown is shown in the following table: Units Discount 1-10 0% 11-25 26-100 101 - 500 501 and up This information should again be placed into a vlookup table starting at cell 110 with the heading "Units and J10 with the heading "Discount Remember that vlookup tables require the lookup items to be placed in ascending order (so lowest to highest numerically or A to Zif alphabetic). Here's the start of what your spreadsheet should look like I did not provide the lookup tables since you need to build them): 3% 4% 5% 12% Discounted Price $482.98 2 Standard Units Extended Price 2. Customer Product Cost Ordered Price Break 3 ABC Widget $19.95 26 $503.10 4 Acme Widget 20 5 Calipec Cog 100 6 CeeTech Widget 115 7 Cyberom Widget 505 FirstFed Cog 40 9 JonCorp Sprocket 12 10 Oras Sprocket 11 Technova Sprocket 17 12 TerraLoan Cog 65 13 vondora Widget 93 14 Wallgo Widget 15 Wallings Cog 185 16 17 10 19 20 Totals $50310 21 Averages 100.65 22 $40290 2 Column A has a customer name in it. Column B will be one of the three products that the company sells, Widget, Cog or Sprocket (watch your spelling or the lookup won't work right). Column C will perform a cost lookup from the first lookup table you built, bringing back the price of the product specified in Column B. Column D contains the number of units the customer wants. Column E is simply the standard cost value multiplied by the quantity. Column F is a discount lookup that use the quantity specified in Column D to determine the discount percentage from the second lookup table that you built. Column G then shows the discounted price which is the extended price from Column E* (1 the discount rate in Column F). Notice that there are totals and averages that need to appear at the bottom of the spreadsheet too in rows 20 and 21 Now to see how cool vlookups really are for you add a new product that our company will sell: Xylophones at $15.34 each. Make sure that you put the entry in the right location of your lookup table. Then on row 16 add a new company XYZCorp who wants to buy 63 Xylophones. Hint: you will need to edit your product price vlookup formula since the size of the table has changed. You should see that none of your old calculations will be affected but the new row's will since the vlookup will now be able to find the price of the Xylophone. Problem 3) Create a new worksheet named Problem 3 with the following data: E State Simplified ID First Name Last Name Combined Name City and State 2 Alice Adams Boston, MA 3 Andy Arbar Chicago, L 4 Barb Bilings Detroit M 5 B Bosthon Houston, TX 6 Bob Braddock Honolulu HI 7 Brad Byce Boise, D 8 Chuck Connors Biloxi, MS 9 Dale Davis Cleveland, OH 10 Dan Derring Reading, PA 11 Dave Dorr Seattle, WA 12 Donna Durant Charlotte, NC 13 Ed Ebling New Orleans, LA 14 Elzabeth Ersatz Richmond, VA 15 Francis Feo Dallas, TX 16 Frank Ford Milwaukee 17 Fred Fulbert Billings, MT G Full Emp ID AA-123-ER5 BA-145-543 CA-534-3JF DA-254-EF3 AA-756-34E BA-983-EDF AA 493-K34 AA-199-ERS BA-765-543 CA-504-3JF DA-294-EF3 AA 756-34E BA-983 EDF AA 493-K34 BA-125-RA4 CA-932-KJR Using Flash Fill (type an entry or two and you may be able to hit enter to fill or you may have to press the Flash Fill button on the Data tab), get the last name, first name into Column C (for example Adams, Alice). In Column E, just get the city name out of Column D. In Column F, just get the state abbreviation out of Column D. Finally, get the number between the two hyphens from the Full Emp ID in Column G to be the Simplified ID in Column H (so the first employee's simplified I should be 123)
