a. Create a new worksheet in the same workbook and name it Problem 2. You need...
Fantastic news! We've Found the answer you've been seeking!
Question:
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).
Expert Answer:
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
Related Book For
Posted Date:
Students also viewed these programming questions
-
Anjelica, single, is a bartender, living in Boise, Idaho (ID). She incurred the following taxes. How much can Anjelica deduct as state and local taxes as an itemized deduction? ID income tax $5,300...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
4 pts A lottery winner will receive $0.5 mulhon at the end of each of the next se yearn. What is the future value of her winnings at the time of her final payment, given that the interest rate is 6%...
-
The financial statements of Shoppers Drug Mart are presented in Appendix A at the end of this book. Instructions (a) Does Shoppers use the indirect or direct method of calculating operating...
-
What are the main ethical issues that researchers may encounter when conducting business research and how can they address these issues ?
-
What is the main difference between core and supplemental ecological indicators? Provide an example of each.Would it be useful to have all production site managers to report to top management on a...
-
Intuit Inc. develops and sells software products for the personal finance market, including popular titles such as Quicken and TurboTax. Classify each of the following costs and expenses for this...
-
The master budget at Western Company last period called for sales of 226,700 units at $10.70 each. The costs were estimated to be $3.92 variable per unit and $226,700 fixed. During the period, actual...
-
Pro-Craft Company computed the following activity rates to allocate overhead cost for the year. Activity Materials handling Quality inspection Activity Rate $ 65 per materials requisition $ 55 per...
-
3. You pay $10 to play the following game of chance. There is a bag containing 12 balls, five are red, three are green and the rest are yellow. You are to draw one ball from the bag. You will win $14...
-
Research on physics used in robotics lists 3 types of robots where these robots can be used components of a robot such as power unit and manipulator, sensors, actuators controller, analog sensors vs...
-
Explain at least four causes of organizational conflict. Explain the main barriers to change at the individual level that the internal change agents are likely to encounter as they introduce the new...
-
A photon with wavelength 0.1395 nm scatters from an electron that is initially at rest. What must be the angle between the direction of propagation of the incident and scattered photons if the speed...
-
Prepare a profit variance analysis for Savery Parts for March. Indicate the effect of each variance by selecting " F " ?for favorable, or " U " ?forunfavorable. If there is no effect, do not select...
-
1. A curling stone sliding on ice undergoes a constant acceleration of 5.1 cm/s [E] as it travels from initial position before coming to rest. When the stone was released, it had an initial velocity...
-
The standard and actual figures of product Z are as under: Standard Actual Material quantity 50 units 45 units Material price per unit ` 1.00 ` 0.80 CALCULATE material cost variances.
-
What is a content filter? Where is it placed in the network to gain the best result for the organization?
-
An object has a total energy of 5.0 1015 J and a kinetic energy of 2.0 1015 J. What is the magnitude of the object's relativistic momentum?
-
The components of vector are A x and A y (both positive), and the angle that it makes with respect to the positive x axis is q. Find the angle q if the components of the displacement vector are (a)...
-
The dark fringe for m = 0 in a Young's double-slit experiment is located at an angle of = 15. What is the angle that locates the dark fringe for m = 1?
-
The adjusted trial balance of Marshall Ltd. at December 31, 2020. Requirements 1. Prepare Marshall Ltd.'s 2020 income statement, statement of retained earnings, and balance sheet. List expenses...
-
Fred's Catering Ltd. had the following selected transactions during May 2020: Requirements 1. Show how each transaction would be handled using the cash basis and the accrual basis, Under each column,...
-
The balance sheet at December 31, 2018, 2019) and 2020 and income statement for the years ended December 31, 2018, 2019, and 2020 for Ojibway Inc. include the following data: Requirements Use the...
Study smarter with the SolutionInn App