a. Create a new worksheet in the same workbook and name it Problem 2. You need...

Transcribed Image Text:

a. 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 J3. b. 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: 1 - 10 11 - 25 26 - 100 Units 101 500 501 and up 0% 3% 4% 5% 12% Discount 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 Z if 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): A C с E F 1 B 2 Customer Product 3 ABC 9 JonCorp 10 Oras Widget Widget 4 Acme 5 Calispec Cog 6 CeeTech Widget 7 Cyberom Widget 8 FirstFed Cog Sprocket Sprocket 11 Technova Sprocket 12 TerraLoan Cog 13 Vondora Widget 14 Wallgo Widget 15 Wallings Cog 16 17 18 19 20 Totals 21 Averages 22 D Standard Units Cost $19.35 Ordered Price 26 20 100 115 505 40 12 6 17 65 93 125 185 Extended Price Break 100.69 $503.10 $503.10 4% 4% G Discounted Price $482.98 $482.98 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). c. 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.Complete Column C for the records using VLOOKUP formula. d. Column D contains the number of units the customer wants. Column E is simply the standard cost value multiplied by the quantity. Complete Column E for the records. e. 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. Complete Column F for the records using VLOOKUP formula. f. Column G then shows the discounted price which is the extended price from Column E* (1 - the discount rate in Column F). Complete Column G for the records. g. Notice that there are totals and averages that need to appear at the bottom of the spreadsheet too in rows 20 and 21. h. 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). a. 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 J3. b. 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: 1 - 10 11 - 25 26 - 100 Units 101 500 501 and up 0% 3% 4% 5% 12% Discount 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 Z if 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): A C с E F 1 B 2 Customer Product 3 ABC 9 JonCorp 10 Oras Widget Widget 4 Acme 5 Calispec Cog 6 CeeTech Widget 7 Cyberom Widget 8 FirstFed Cog Sprocket Sprocket 11 Technova Sprocket 12 TerraLoan Cog 13 Vondora Widget 14 Wallgo Widget 15 Wallings Cog 16 17 18 19 20 Totals 21 Averages 22 D Standard Units Cost $19.35 Ordered Price 26 20 100 115 505 40 12 6 17 65 93 125 185 Extended Price Break 100.69 $503.10 $503.10 4% 4% G Discounted Price $482.98 $482.98 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). c. 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.Complete Column C for the records using VLOOKUP formula. d. Column D contains the number of units the customer wants. Column E is simply the standard cost value multiplied by the quantity. Complete Column E for the records. e. 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. Complete Column F for the records using VLOOKUP formula. f. Column G then shows the discounted price which is the extended price from Column E* (1 - the discount rate in Column F). Complete Column G for the records. g. Notice that there are totals and averages that need to appear at the bottom of the spreadsheet too in rows 20 and 21. h. 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).

Answer rating: 100% (QA)

Solutions Step 1 Create a Unit Cost Spreadsheet Step a Create a new worksheet and set up the vlookup table for product prices Open Excel and create a ... View the full answer