B1: Go to the Inventory Sheet. This sheet contains a list of all the items the c
ID: 397828 • Letter: B
Question
B1: Go to the Inventory Sheet. This sheet contains a list of all the items the company keep in stock to build the bikes. Each part is identified by a unique part code. Convert the Range A3:J43 (Part Code to Reorder Qty in width and from Part Code heading to PART CODE "WH2778" in length) to a Table. Name the table Inventory
B2: Go to the Bill of Materials (BoM) Worksheet. This sheet contains a list of which parts (and how many) are needed to make two different types of bicycles, the ZF300 (no longer in production) and the X-800, the model currently produced. The table is incomplete as it doesn’t have the rest of the information about each part. Use appropriate formulas to lookup the corresponding Category, Product Description, Supplier and Unit Price for each of the Part Codes from the Inventory table. (Note: Both the BoM and the Inventory are frequently re-sorted, so the formulas must work regardless of sort order.)
B3: Still in the BoM sheet, ZF-300. In K4 (Groupset) create a formula to calculate the total price of parts of category Groupset for that product. Copy the formula down to K8 (wheels) to get a breakdown of Product Costs by Category.
B4: In K9 (Total Cost per Unnit) calculate the total cost to make one unit of the selected product.
PLEASE PROVIDE AN EXPLAINATION during each step
INVENTORY
Inventory Part Code Category Product Description Supplier Unit Price/Unit Weight (g) Weight (lbs) In Stock Reorder Qty FR1323 Frameset Nashbar Carbon Frame Nashbar item $499.20 1166 47 FR1235 Frameset J&L Fork Bikes Plus item $202.40 444 28 FR2360 Frameset J&L Plug Bikes Plus item $10.40 7.3 29 FR1934 Frameset Headset Bikes Plus item $0.00 69 38 FR1080 Frameset J&L Top Cap & Screw Cycle Warehouse item $4.90 7.5 31 FR1081 Frameset Mr. Ride Seat Clamp Cycle Warehouse item $14.88 8.5 39 WH1100 Wheels XR-270/Bitex Wheel Cycle Warehouse item $152.48 630 31 WH2373 Wheels Veloplugs Rim tape LBS item $4.86 3.9 35 WH2942 Wheels Titanium QR Skewers Cycle Warehouse item $5.15 22.3 17 WH2856 Wheels Forte LCR Tyres Performance item $20.93 171.1 28 WH1573 Wheels Forte Light Tubes Performance item $6.16 58 20 CO2357 Contact Points RockBros CrMo Saddle Cycle Warehouse item $20.47 214.2 40 CO1649 Contact Points KCNC Scandium Seatpost Cycle Warehouse item $85.44 159.6 34 CO2143 Contact Points Spacers Bikes Plus item $0.00 3.3 41 CO2202 Contact Points Uno 7050 Stem Cycle Warehouse item $22.80 106.7 33 CO1099 Contact Points Nashbar Carbon Bar Nashbar item $83.52 231 48 CO1469 Contact Points Generic Foam Tape Tape Cycle Warehouse cm $0.22 0.4 6 CO2904 Contact Points Custom Bar Plugs Cycle Warehouse item $0.99 2 37 GR2589 Groupset Ultegra Levers Nashbar item $99.51 178.5 7 GR1322 Groupset Ultegra RD Backcountry item $52.00 185.3 37 GR1172 Groupset Ultegra FD Nashbar item $31.61 96.1 48 GR1836 Groupset Ultegra Cassette Nashbar item $42.14 220.5 23 GR1215 Groupset Dura Ace Chain Nashbar item $35.84 228.7 42 GR1751 Groupset CNC brakes 200g Brakes Cycle Warehouse item $96.05 87 17 GR2009 Groupset Ultegra Cranks Nashbar item $143.10 646.7 33 GR1406 Groupset Ultegra BB Backcountry item $19.55 61.7 1 GR1550 Groupset Ultegra Cables Nashbar item $0.00 47.7 23 AC1508 Accessories and tuning Fairing M5 Bolts Cycle Warehouse item $0.41 1.1 27 AC1945 Accessories and tuning Alloy CR Bolts Cycle Warehouse item $2.23 0.3 10 AC2733 Accessories and tuning Titanium Stem Bolts Cycle Warehouse item $10.20 8.1 23 AC2964 Accessories and tuning Generic FD Adapter Cycle Warehouse item $2.58 17 20 AC1914 Accessories and tuning Nashbar Alpe d Huez Pedals Nashbar item $15.12 101.3 3 AC2716 Accessories and tuning Grease Cycle Warehouse grams $0.07 0.2 20 GR1432 Groupset Nashbar Levers Nashbar item $121.20 201.9 37 GR1770 Groupset Nashbar RD Nashbar item $51.15 216.5 41 GR1966 Groupset Nashbar FD Nashbar item $32.00 98.8 31 GR2655 Groupset Nashbar Cassette Nashbar item $44.10 199.3 40 AC2170 Accessories and tuning RockBros S60 Pedals Cycle Warehouse item $30.52 100.1 27 WH1260 Wheels CNC V18 Ultra Wheels Cycle Warehouse item $188.80 644 29 WH2778 Wheels CNC V16 Super Slim Wheels Cycle Warehouse item $183.60 680 20 CO1688 Contact Points Specialised Foam Tape Tape Cycle Warehouse cm $0.22 0.5 11Explanation / Answer
the inventory which has given in the graph is not visible and not able read. with this kind of information it is not possible to find the solution to the problem. by looking all the numbers and i had made all the calculations, and the total cost per unit is more than $1250.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.